Hi folks,
Question:
How can we effectively use gv$sql when a SQL query has multiple plan hash values, and how can we compare the performance between them if we observe multiple records within a 10-minute interval?
Example:
Consider the sql_id ABCV6574gh, which had four different plan hash values over the last 10 minutes, with the following characteristics:
- Each plan hash value executed 10 times.
- One plan ran in 0.01 seconds per execution (good plan).
- Two plans took 0.04 seconds per execution.
- The last plan ran in 4 seconds per execution (bad plan).
In this scenario, the plan with 4 seconds execution time would be identified as a bad plan, while the plan with 0.01 seconds execution time would be the good plan. Based on this, I would apply a SQL profile to enforce the first plan hash value for consistent performance.
The challenge lies in identifying and comparing these plans efficiently within the 10-minute interval since gv$sql will return multiple records for the same sql_id. How can I implement this process to reliably identify and act upon the good and bad plans, especially when plans shift frequently due to bind peeking, stale statistics, or other factors?
I am trying to identify SQL queries that take more time to execute when their execution plan changes. Additionally, I am tracking these queries in a table at 10-minute intervals. After 10 minutes, I compare the snapshot from the first 10 minutes with the second 10 minutes and publish the results using gv$sqlstats.
However, since gv$sqlstats only retains the latest entry for a given sql_id, I checked gv$sql and found 4–5 entries with different plan hash values. This happens due to reasons such as bind variable changes, stale statistics, or other factors.
My primary goal is to identify SQL queries that have shifted from a good plan to a bad plan within the last 20 minutes. I want to repeat this process throughout the day. By identifying and fixing these "hot" SQL queries, I aim to avoid potential business impacts like application downtime or performance degradation
TRUNCATE TABLE appmon.t_sql_execution_monitor;
CREATE OR REPLACE PROCEDURE p_insert_initial_sql_executions AS
v_date DATE := trunc(sysdate, 'MI');
BEGIN
INSERT INTO appmon.t_sql_execution_monitor (
instance_id,
sql_id,
sql_text,
plan_hash_value,
executions,
elapsed_time,
buffer_gets,
last_captured_time
)
SELECT
s.inst_id,
s.sql_id,
s.sql_text,
s.plan_hash_value,
s.executions AS execs,
ROUND(s.elapsed_time / NVL(DECODE(s.executions, 0, 1, s.executions), 1) / 1000000, 3) AS elapsed_time_per_exec,
s.buffer_gets / NVL(DECODE(s.executions, 0, 1, s.executions), 1) AS buffer_gets,
v_date
FROM
gv$sqlstats s,
(
SELECT DISTINCT
sql_id,
l.inst_id
FROM
gv$sql l
WHERE
l.last_active_time >= SYSDATE - INTERVAL '10' MINUTE
AND l.parsing_schema_name NOT IN (
'SYSMON', 'GGSYS', 'DIP', 'ANONYMOUS', 'APPQOSSYS',
'DBSFWUSER', 'APP_ETL', 'APPMON', 'AUDSYS', 'SYS',
'SYSTEM', 'SQLTXADMIN', 'USDBLINK', 'TRCANLZR', 'XDB',
'XPS', 'DBA_REORG', 'DBSNMP', 'ORACLE_OCM', 'GSMADMIN_INTERNAL',
'GSMCATUSER', 'GSMUSER', 'OJVMSYS', 'OUTLN', 'REMOTE_SCHEDULER_AGENT',
'SYSBACKUP', 'SYSDG', 'SYSKM', 'SYSRAC', 'WMSYS',
'SYS$UMF', 'XS$NULL'
)
AND l.plan_hash_value <> 0
) l
WHERE
s.elapsed_time IS NOT NULL
AND s.sql_id = l.sql_id
AND s.inst_id = l.inst_id
AND s.plan_hash_value <> 0
AND (s.elapsed_time / NVL(DECODE(s.executions, 0, 1, s.executions), 1)) > 100000
AND s.last_active_time >= SYSDATE - INTERVAL '10' MINUTE;
COMMIT;
END;
/