Find Top N resource absorbing SQL Queries

At times database performance problems arise that require your diagnosis and correction. Sometimes problems are brought to your attention by users who complain about slow performance. Other times you might notice performance spikes in the Host CPU chart on the home page. Suppose user reported slow performance and this occurred in night or in holiday. Now You need to see which sql queries were run at that specific time along with their resources usages details.

With default settings, in every hour a snapshots taken by Automatic Workload Repository (AWR) and those snapshots are available for next 7 days. Here is a SQL query that give the desired top N resource sensitive queries :

SELECT * FROM
(SELECT X.SNAP_ID, X.SQL_ID,SQL_TEXT
,ROUND(X.ELAPSED_TIME/1000000/X.EXECUTIONS_DELTA,3) AVG_ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000/X.EXECUTIONS_DELTA,3) AVG_CPU_TIME_SEC
,X.ELAPSED_TIME TOTAL_ELAPSED_TIME_MIC_SEC
,X.CPU_TIME TOTAL_CPU_TIME_MIC_SEC
,X.EXECUTIONS_DELTA EXECUTIONS
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SNAP_ID,DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME, SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE
BEGIN_INTERVAL_TIME >= TO_DATE('18-feb-2009 18:00', 'dd-mon-yyyy hh24:mi')
AND END_INTERVAL_TIME <= TO_DATE('18-feb-2009 18:40', 'dd-mon-yyyy hh24:mi'))
AND PARSING_SCHEMA_NAME='SCOTT'
GROUP BY DHSS.SQL_ID,DHSS.SNAP_ID) X
WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY AVG_ELAPSED_TIME_SEC DESC
) WHERE rownum < =200;