• 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
SELECT
 to_char(s.begin_interval_time,'mm/dd hh24:mi')
 AS begin_interval_time,
 ss.plan_hash_value,
 ss.executions_delta,
 CASE
 WHEN ss.executions_delta > 0
 THEN ss.elapsed_time_delta/ss.executions_delta/1000
 ELSE ss.elapsed_time_delta
 END AS milliseconds_per_execution,
 CASE
 WHEN ss.executions_delta > 0
 THEN ss.rows_processed_delta/ss.executions_delta
 ELSE ss.rows_processed_delta
 END AS rows_per_execution,
 CASE
 WHEN ss.executions_delta > 0
 THEN ss.buffer_gets_delta/ss.executions_delta
 ELSE ss.buffer_gets_delta
 END AS buffer_gets_per_execution,
 CASE
 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

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

Top resource intensive SQL

Database performance is a major concern for a DBA. SQLs are the ones which needs proper DB management in order to execute well. At times the application team might tell you that the database is runnin

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback