Wednesday, May 20, 2009

How to find TOP SQL in a particular period.

SELECT SQL_TEXT,X.CPU_TIME FROM DBA_HIST_SQLTEXT DHST, (SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIMEFROM DBA_HIST_SQLSTAT DHSSWHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOTWHERE BEGIN_INTERVAL_TIME>=TO_DATE('09/08/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:

Post a Comment

About Me

Working as Oracle DBA for Wipro Technologies, Chennai, India