If a SQL statement’s performance suddenly degrades, one possible cause could be a change in its execution plan. The following query can be used to identify when and how the execution plan has changed:
-- This SQL query is used to track changes in the SQL execution plans.
-- Dated: July 2024
-- Author: Yuan Yao
define my_sql_id='f90zn75aphu4w'
with plan_ash as (
select
sql_id,
sql_child_number,
sql_exec_id,
to_char(sample_time, 'YYYY-MON-DD HH24:MI:SS') track_time,
sql_exec_start,
sql_plan_hash_value curr_sql_plan,
lag(sql_plan_hash_value, 1) over (
partition by sql_id
order by sql_exec_start, sql_exec_id, sample_time asc
) as prev_sql_plan
from
dba_hist_active_sess_history
where
sample_time > trunc(sysdate)
and sql_id = '&my_sql_id'
)
select
track_time,
sql_child_number,
sql_exec_id,
curr_sql_plan,
prev_sql_plan,
case
when curr_sql_plan <> prev_sql_plan then 'Y'
else 'N'
end changed
from
plan_ash
where
1 = 1
-- and curr_sql_plan <> prev_sql_plan
order by
track_time,
sql_child_number,
sql_exec_id;
The commented line in the SQL query serves as an optional filter condition. If uncommented, it would restrict the result set to only changes in the SQL execution plan.
An example of the output:
TRACK_TIME SQL_CHILD_NUMBER SQL_EXEC_ID CURR_SQL_PLAN PREV_SQL_PLAN CHANGED
2024-JUL-18 16:31:09 0 17861030 395199281 N
2024-JUL-18 16:31:39 0 17874954 395199281 395199281 N
2024-JUL-18 16:31:50 0 17879788 395199281 395199281 N
2024-JUL-18 16:32:00 0 17884607 395199281 395199281 N
2024-JUL-18 16:32:00 0 17884608 395199281 395199281 N
2024-JUL-18 16:32:41 0 17903718 395199281 395199281 N
2024-JUL-18 16:32:41 0 17903719 395199281 395199281 N
2024-JUL-18 16:33:01 0 17913185 395199281 395199281 N
2024-JUL-18 16:33:52 0 17936784 395199281 395199281 N
2024-JUL-18 16:33:52 0 17936785 395199281 395199281 N
2024-JUL-18 16:34:13 0 17946519 684215793 395199281 Y
2024-JUL-18 16:34:33 0 17955644 684215793 684215793 N
2024-JUL-18 16:34:44 0 17960331 684215793 684215793 N
2024-JUL-18 16:34:44 0 17960333 684215793 684215793 N
2024-JUL-18 16:34:44 0 17960334 684215793 684215793 N
The execution plan changes is observed at 2024-JUL-18 16:34:13 .
Yuan Yao, An Oracle ACE's Blog 
Leave a comment