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.

select s.BEGIN_INTERVAL_TIME,
 round(t.BUFFER_GETS_DELTA/
 0.0001+
 (extract (day from (s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME))*24*60)+
 (extract (HOUR from (s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME))*60)+
 (extract (MINUTE from (s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME)))+
 (extract (SECOND from (s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME))/60)
 ,0) as BG_PER_MINUTE
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:

bg-over-time-from-awr

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 :)
Martin

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.




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Oracle: Did my SQL get worse over time? (AWR query)”

  1. antony Says:

    Hi Martin, Good one.I guess you may also have to relate number of executions of a SQL_ID to higher buffer_gets.

  2. Sachin Says:

    How about this one ?
    this will give more detailed info, change PDT, PST or whatever timezone you want.

    set lin 250
    col btime foramt a15
    col apwpx format 99999.999 head ‘AppWms|PerX’
    col bgpx format 999999999 head ‘BGets|PerX’
    col conw format 99999.999 head ‘Conwms|PerX’
    col cpx format 99999999.999 head ‘CPUms|PerX’
    col drpx format 999999999.99 head ‘DReads|PerX’
    col fetchx format 99999 head ‘Fetchs|PerX’
    col sortx format 999 head ‘Sorts|PerX’
    col elpx format 99999999.999 head ‘Elapms|PerX’
    col exec format 999999999999 head ‘Execs’
    col iowpx format 99999999.999 head ‘IOWms|PerX’
    col latime format a11 head ‘Last Active’
    col lltime format a09 head ‘Last Load’
    col maxsnapid format 999999 head ‘Max|SnapId’
    col m format a01 trunc
    col snap_id format 999999
    col phash format 9999999999 head ‘PlanHash’
    col rwpx format 99999999.99 head ‘RwsP|PerX’
    col sql_id format a15 head ‘SQL Id’
    col ue format 999
    col cpct format 999 head ‘CPU|Pct’ trunc
    col ipct format 999 head ‘IO|Pct’ trunc
    col parses format 999999
    col loads format 9999
    col invals format 9999

    prompt
    prompt ===== from dba_hist_sqlstat =====

    break on phash skip 1
    select plan_hash_value phash
    ,ss.snap_id snap_id
    ,to_char(sn.begin_interval_time,’YY/MM/DD HH24:MI’) btime
    ,to_char(new_time(sn.begin_interval_time,’GMT’,’PDT’),’YY/MM/DD HH24:MI’) btime_PDT
    ,sum(decode(executions_delta,0,1,executions_delta)) exec
    ,sum(decode(PARSE_CALLS_DELTA,0,1,PARSE_CALLS_DELTA)) parses
    ,sum(decode(LOADS_DELTA,0,1,LOADS_DELTA)) loads
    ,sum(decode(INVALIDATIONS_DELTA,0,1,INVALIDATIONS_DELTA)) invals
    ,sum(cpu_time_delta)/sum(decode(executions_delta,0,1,executions_delta))/1000 cpx
    ,sum(elapsed_time_delta)/sum(decode(executions_delta,0,1,executions_delta))/1000 elpx
    ,(sum(cpu_time_delta)/sum(decode(elapsed_time_delta,0,1,elapsed_time_delta)))*100 cpct
    ,sum(buffer_gets_delta)/sum(decode(executions_delta,0,1,executions_delta)) bgpx
    ,sum(iowait_delta)/sum(decode(executions_delta,0,1,executions_delta))/1000 iowpx
    ,(sum(iowait_delta)/sum(decode(elapsed_time_delta,0,1,elapsed_time_delta)))*100 ipct
    ,sum(disk_reads_delta)/sum(decode(executions_delta,0,1,executions_delta)) drpx
    ,sum(apwait_delta)/sum(decode(executions_delta,0,1,executions_delta))/1000 apwpx
    ,sum(ccwait_delta)/sum(decode(executions_delta,0,1,executions_delta))/1000 conw
    ,sum(rows_processed_delta)/sum(decode(executions_delta,0,1,executions_delta)) rwpx
    ,sum(sorts_delta)/sum(decode(executions_delta,0,1,executions_delta)) sortx
    ,sum(end_of_fetch_count_delta)/sum(decode(executions_delta,0,1,executions_delta)) fetchx
    from dba_hist_sqlstat ss
    ,dba_hist_snapshot sn
    where sql_id = ‘&&sql_id’
    and ss.snap_id = sn.snap_id
    and to_char(new_time(sn.begin_interval_time,’GMT’,’PDT’),’YY/MM/DD HH24:MI’) between ‘&&start_time_PDT’ and ‘&&end_time_PDT’
    and EXECUTIONS_DELTA >0
    group by sql_id
    ,plan_hash_value
    ,ss.snap_id
    ,to_char(sn.begin_interval_time,’YY/MM/DD HH24:MI’)
    ,to_char(new_time(sn.begin_interval_time,’GMT’,’PDT’),’YY/MM/DD HH24:MI’)
    order by min(ss.snap_id)
    ;

    ===== from dba_hist_sqlstat =====

    CPUms Elapms CPU BGets IOWms IO DReads AppWms Conwms RwsP Sorts Fetchs
    PlanHash SNAP_ID Begin Time BTIME_PDT Execs PARSES LOADS INVALS PerX PerX Pct PerX PerX Pct PerX PerX PerX PerX PerX PerX
    ———– ——- ———– ————– ————- ——- —– —— ————- ————- —- ———- ————- —- ————- ———- ———- ———— —– ——
    1934851484 96494 15/06/05 18 15/06/05 11:00 84 33 1 1 86.415 771.678 11 2954 759.363 98 144.26 .000 2.055 380.02 0 1
    :00

    96495 15/06/05 18 15/06/05 11:15 9166 55 2 1 9.000 294.227 3 2253 287.625 98 62.52 .000 .000 383.88 0 1
    :15

    96496 15/06/05 18 15/06/05 11:30 8819 20 2 1 4.617 116.958 4 1223 113.129 97 25.09 .000 .000 384.19 0 1
    :30

    96590 15/06/06 18 15/06/06 11:00 180 60 1 1 30.440 1861.436 2 2164 1846.988 99 376.71 .000 .000 378.70 0 1
    :00

    96591 15/06/06 18 15/06/06 11:15 37 5 2 1 16.538 960.207 2 1855 950.933 99 192.03 .000 .000 335.35 0 1
    :15

    96645 15/06/07 07 15/06/07 00:45 2459 80 1 1 3.383 61.511 5 1092 58.710 95 13.02 .000 .000 381.27 0 1
    :45

    2360609759 96880 15/06/09 18 15/06/09 11:30 2 2 1 2 1263.308 20430.690 6 70487 19171.181 94 61581.00 .000 5.321 13.50 0 0
    :30

    568950966 96882 15/06/09 19 15/06/09 12:00 3189 69 1 1 8.383 264.627 3 658 261.349 99 65.68 .000 .022 374.02 0 1
    :00

    96883 15/06/09 19 15/06/09 12:15 7602 19 3 1 2.764 50.777 5 493 48.834 96 15.84 .000 .000 381.53 0 1
    :15

Leave a Reply