Monday, July 28, 2008

AWR top sql quarries

FOR EXECUTION : EXECUTIONS_DELTA
FOR CPU TIME: CPU_TIME_DELTA
FOR DISK READ: DISK_READS_DELTA
FOR BUFFER GETS: BUFFER_GETS_DELTA


SELECT SQL_TEXT,X.CPU_TIME FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHS.SQL_ID SQL_ID,SUM(DHS.CPU_TIME_DELTA) CPU_TIME
FROM DBA_HIST_SQLSTAT DHS
WHERE DHS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME>=TO_DATE('07/20/2008','MM/DD/YYYY')
AND END_INTERVAL_TIME<=TO_DATE('07/27/2008','MM/DD/YYYY'))
GROUP BY DHS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY X.CPU_TIME DESC;

No comments: