• Arun Kumar

Find SQL execution plan change

If you would like to find out change in SQL plan of a query, below script will help you find the SQL plan ID for previous executions and check if there is any change in SQL plan ID.

set pagesize 1000
set linesize 200
column begin_interval_time format a20
column milliseconds_per_execution format 999999990.999
column rows_per_execution format 999999990.9
column buffer_gets_per_execution format 999999990.9
column disk_reads_per_execution format 999999990.9
break on begin_interval_time skip 1
 to_char(s.begin_interval_time,'mm/dd hh24:mi')
 AS begin_interval_time,
 WHEN ss.executions_delta > 0
 THEN ss.elapsed_time_delta/ss.executions_delta/1000
 ELSE ss.elapsed_time_delta
 END AS milliseconds_per_execution,
 WHEN ss.executions_delta > 0
 THEN ss.rows_processed_delta/ss.executions_delta
 ELSE ss.rows_processed_delta
 END AS rows_per_execution,
 WHEN ss.executions_delta > 0
 THEN ss.buffer_gets_delta/ss.executions_delta
 ELSE ss.buffer_gets_delta
 END AS buffer_gets_per_execution,
 WHEN ss.executions_delta > 0
 THEN ss.disk_reads_delta/ss.executions_delta
 ELSE ss.disk_reads_delta
 END AS disk_reads_per_execution
FROM wrh$_sqlstat ss
INNER JOIN wrm$_snapshot s ON s.snap_id = ss.snap_id
WHERE ss.sql_id = '&sql_id'
AND ss.buffer_gets_delta > 0
ORDER BY s.snap_id, ss.plan_hash_value;


Recent Posts

See All

Reclaim Unused Space in Oracle

Over a period of time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space i

SQL Data-Type conversion functions

SQL conversion functions are single row functions designed to alter the nature of the data type of a column value, expression, or literal. TO_CHAR, TO_NUMBER, and TO_DATE are the three most widely use

Automatic SQL Tuning in Oracle

Database optimizer runs very fast and must select the best execution plan for a query within fraction of seconds. Due to time constraint (under normal query execution), sometimes optimizer will choose