Compare historic executions plans
                                                 Last update (2008-03-21 12:28:57)
                                                                                                                   Date added (2008-03-21 12:25:26)

Summary
The query is looking for historic executions plans which used nested loop joins and producing a report that is attempting to produce some measures of work done by nested loops, showing the variation over time. In principal this could be a good thing as it allows us to ask things like: how did metric X today compare with metric X yesterday (or on the same day last week if we adjust the AWR defaults to keep two weeks of data); how did metric Y look different around 3:00 pm today when the users were complaining about the performance.

SELECT TO_CHAR(sn.begin_interval_time, 'yy-mm-dd hh24') snap_time,
        COUNT(*)                          ct,
        SUM(st.rows_processed_delta)      row_ct,
        SUM(st.disk_reads_delta)          disk,
        SUM(st.cpu_time_delta)            cpu
FROM    dba_hist_snapshot   sn,
        dba_hist_sqlstat    st,
        dba_hist_sql_plan   sp
WHERE   st.snap_id = sn.snap_id
AND     st.dbid = sn.dbid
AND     st.instance_number = sn.instance_number
AND     sp.sql_id = st.sql_id
AND     sp.dbid = st.dbid
AND     sp.plan_hash_value = st.plan_hash_value
AND     sp.operation = 'NESTED LOOPS'
GROUP BY TO_CHAR(sn.begin_interval_time,'yy-mm-dd hh24')
HAVING COUNT(*) > 50;