compare.sql

COL AVG_ETIME FOR 99999999
COL AVG_BUF_GETS FOR 9999999999
COL AVG_DISK_RS FOR 9999999999
COL AVG_ROW_PROC FOR 99999999
COL FIRST_TIME FOR a20
COL LAST_TIME FOR a20
SET LINE 200 PAGESIZE 100
UNDEFINE 1
DEFINE sqlid=&&1
SELECT ROWNUM,'AWR' as SRC,t1.plan_hash_value PLAN_HASH,TO_CHAR(t1.avg_elapsed_time_secs, '99999999999990D990') AVG_ETIME,
t1.avg_buffer_gets AVG_BUF_GETS,t1.avg_disk_reads AVG_DISK_RS,t1.avg_rows_processed AVG_ROW_PROC,
TO_CHAR(t1.first_snap_time, 'YYYY-MM-DD/HH24:MI:SS') FIRST_TIME,TO_CHAR(t1.last_snap_time, 'YYYY-MM-DD/HH24:MI:SS') LAST_TIME
  FROM (SELECT /*+ NO_MERGE */ h.plan_hash_value,
       ROUND((SUM(h.elapsed_time_delta)/SUM(GREATEST(h.executions_delta, 1))) / 1e6, 3) avg_elapsed_time_secs,
       ROUND(SUM(h.buffer_gets_delta)/SUM(GREATEST(h.executions_delta, 1))) avg_buffer_gets,
       ROUND(SUM(h.disk_reads_delta)/SUM(GREATEST(h.executions_delta, 1))) avg_disk_reads,
       ROUND(SUM(h.rows_processed_delta)/SUM(GREATEST(h.executions_delta, 1))) avg_rows_processed,
       MIN(s.end_interval_time) first_snap_time,
       MAX(s.end_interval_time) last_snap_time
  FROM dba_hist_sqlstat h, dba_hist_snapshot s
WHERE h.sql_id = '&sqlid' AND h.snap_id = s.snap_id AND h.dbid = s.dbid AND h.instance_number = s.instance_number
GROUP BY h.plan_hash_value ORDER BY 3) t1
union all
SELECT ROWNUM,'GV$' as SRC,v.plan_hash_value PLAN_HASH,TO_CHAR(v.avg_elapsed_time_secs, '99999999999990D990') AVG_ETIME,
v.avg_buffer_gets AVG_BUF_GETS,v.avg_disk_reads AVG_DISK_RS,v.avg_rows_processed AVG_ROW_PROC,
v.first_load_time FIRST_TIME,v.last_load_time LAST_TIME
  FROM (SELECT /*+ NO_MERGE */ plan_hash_value,
       ROUND((SUM(elapsed_time)/SUM(GREATEST(executions, 1))) / 1e6, 3) avg_elapsed_time_secs,
       ROUND(SUM(buffer_gets)/SUM(GREATEST(executions, 1))) avg_buffer_gets,
       ROUND(SUM(disk_reads)/SUM(GREATEST(executions, 1))) avg_disk_reads,
       ROUND(SUM(rows_processed)/SUM(GREATEST(executions, 1))) avg_rows_processed,
       MIN(first_load_time) first_load_time,
       MAX(last_load_time) last_load_time
  FROM gv$sql WHERE sql_id = '&sqlid' GROUP BY plan_hash_value ORDER BY 3) v;