Saturday, October 18, 2008

Select Top SQLs in Oracle

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

No comments:

search engine

Custom Search