Several useful oracle dba_hist _ * query statements

Several useful oracle dba_hist _ * query statements consume the most CPU Of the 10 statements select * from (select s. SQL _ID, sum (s. CPU_TIME_DELTA), sum (s. DISK_READS_DELTA), count (*) from DBA_HIST_SQLSTAT s group by s. SQL _ID order by sum (s. CPU_TIME_DELTA) desc) www.2cto.com where rownum <11/last 7 days, specify the time period (-) The 10 statements that consume the most CPU select * from (select s. SQL _ID, sum (s. CPU_TIME_DELTA), sum (s. DISK_READS_DELTA), count (*) from DBA_HIST_SQLSTAT s, DBA_HIST_SNAPSHOT p where 1 = 1 and s. SNAP_ID = p. SNAP_ID and EXTRACT (hour from p. END_INTERVAL_TIME) between 8 and 16 and p. END_INTERVAL_TIME between SYSDATE-7 and SYSDATE group by s. SQL _ID order by sum (s. CPU_TIME_DELTA) desc) www.2cto.com where rownum <11/
The detailed SQL statement select * from (select s. SQL _ID, s. SQL _TEXT sum (s. CPU_TIME_DELTA), sum (s. DISK_READS_DELTA), count (*) from DBA_HIST_SQLSTAT s, DBA_HIST_SNAPSHOT p, DBA_HIST_SQLTEXT t where 1 = 1 and s. SNAP_ID = p. SNAP_ID and s. SQL _ID = t. SQL _ID and EXTRACT (HOUR FROM p. END_INTERVAL_TIME) between 8 and 16 and t. COMMAND_TYPE! = 47 -- Exclude PL/SQL blocks from output and p. END_INTERVAL_TIME between SYSDATE-7 and SYSDATE group by s. SQL _ID order by sum (s. CPU_TIME_DELTA) desc) where rownum <11/www.2cto.com analyzes the resource consumption of the execution plan for each SQL statement version select st. SQL _ID, st. PLAN_HASH_VALUE, sum (st. EXECUTIONS_DELTA) EXECUTIONS, sum (st. ROWS_PROCESSED_DELTA) CROWS, trunc (sum (st. CPU_TIME_DELTA)/1000000/60) CPU_MINS, trunc (sum (st. ELAPSED_TIME_DELT A)/1000000/60) ELA_MINS from DBA_HIST_SQLSTAT st where st. SQL _ID in ('& 1') group by st. SQL _ID, st. PLAN_HASH_VALUE order by st. SQL _ID, CPU_MINS; SELECT st2. SQL _ ID, st2.PLAN _ HASH_VALUE, st_long.PLAN_HASH_VALUE l_PLAN_HASH_VALUE, st2.CPU _ MINS, st_long.CPU_MINS l_CPU_MINS, st2.ELA _ MINS, st_long.ELA_MINS l_ELA_MINS, st2.EXECUTIONS, www.2cto.com st_long.EXECUTIONS l_EXECUTIONS, s T2.CROWS, st_long.CROWS l_CROWS, st2.CPU _ MINS_PER_ROW, st_long.CPU_MINS_PER_ROW l_CPU_MINS_PER_ROW FROM (SELECT st. SQL _ID, st. PLAN_HASH_VALUE, SUM (st. EXECUTIONS_DELTA) EXECUTIONS, SUM (st. ROWS_PROCESSED_DELTA) CROWS, TRUNC (SUM (st. CPU_TIME_DELTA)/1000000/60) CPU_MINS, www.2cto.com DECODE (SUM (st. ROWS_PROCESSED_DELTA), 0, 0, (SUM (st. CPU_TIME_DELTA)/1000000/60)/SUM (st. ROWS_PROCESSED_DELTA) CPU_M INS_PER_ROW, TRUNC (SUM (st. ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS FROM DBA_HIST_SQLSTAT st WHERE 1 = 1 AND (st. CPU_TIME_DELTA! = 0 OR st. ROWS_PROCESSED_DELTA! = 0) group by st. SQL _ID, st. PLAN_HASH_VALUE) st2, www.2cto.com (SELECT st. SQL _ID, st. PLAN_HASH_VALUE, SUM (st. EXECUTIONS_DELTA) EXECUTIONS, SUM (st. ROWS_PROCESSED_DELTA) CROWS, TRUNC (SUM (st. CPU_TIME_DELTA)/1000000/60) CPU_MINS, DECODE (SUM (st. ROWS_PROCESSED_DELTA), 0, 0, (SUM (st. CPU_TIME_DELTA)/1000000/60)/SUM (st. ROWS_PROCESSED_DELTA) CPU_MINS_PER_ROW, TRUNC (SUM (st. ELAPSED_TIME_DELTA)/1, 1000000 /60) ELA_MINS www.2cto.com FROM DBA_HIST_SQLSTAT st WHERE 1 = 1 AND (st. CPU_TIME_DELTA! = 0 OR st. ROWS_PROCESSED_DELTA! = 0) having trunc (SUM (st. CPU_TIME_DELTA)/1000000/60)> 10 group by st. SQL _ID, st. PLAN_HASH_VALUE) st_long WHERE 1 = 1 AND st2. SQL _ ID = numbers AND numbers/DECODE (st2.CPU _ MINS_PER_ROW, st2.CPU _ MINS_PER_ROW)> 2 orders order by l_CPU_MINS DESC, st2. SQL _ ID, st_long.CPU_MINS DESC, st2.PLAN _ HASH_VALUE;