Martin Klier


Oracle: Did my SQL get worse over time? (AWR query)

Sometimes, we get statements to look at, and are told “it’s getting worse and worse”. Since DBAs are well advised not to take anything for granted and only to believe what they see with own eyes, here comes a SQL on AWR to see Buffer Gets per Minute, over time.

 (extract (day from (s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME))*24*60)+
from dba_hist_sqlstat t, 
 dba_hist_snapshot s
where t.snap_id = s.snap_id
 and t.dbid = s.dbid
 and t.instance_number = s.instance_number
 and t.SQL_ID='vwxyz'
 and s.begin_interval_time between sysdate-90 and sysdate
order by t.SNAP_ID 

Pseudocode explanation:
Get all SQL ID’s from the historical SQL STAT view. Refer it to the snapshot details to get real-world date/time of the events. Since nobody knows how long the AWR snapshot interval was at the time of interest, make BUFFER GETS relative per minute by dividing each BUFFER GETS DELTA by the length of its interval.
Configure the SQL_ID and the interval to be reviewed in WHERE.

You can create a chart like that when exporting the result to the spreadsheet software of your choice:


Basically, this concept will also work with all other columns available in dba_hist_sqlstat, such as CPU consumption, Interconnect load, Disk IO etc.

“Everybody lies”, says Dr. House 🙂

PS: Please keep in mind, the system(s) to run this query on, will need Oracle’s Diagnostics Pack licensed on top of Enterprise Edition.

Martin Klier in DOAG.tv Interview: Oracle Standard Edition
Oracle 12c Multitenant: impdp fails w/ ORA-31625 and ORA-01031 because of Database Vault

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.