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;
compare.sql
未经允许不得转载:徐万新之路 » compare.sql
最新评论
这个牛
放下欲望,男人从来不醉,充分且必要
勇气、责任、自信、创新,为天下先!
软件即数据,软件即服务,软件即管理,软件就是对人类各种社会活动的仿真和记录。软件很重要,但软件不可能凌驾于业务之上,尤其不可能高人一等。