EASY STEPS : TOP 5 SQL QUERY

A performance tuning issue:

AS a DBA , you may need to find out top five sql query from your database server based on a specific time interval.
Try the following query,


sql> select sql_text,cpu_time,rownum from (SELECT SQL_TEXT,a.CPU_TIME FROM DBA_HIST_SQLTEXT b,
(SELECT c.SQL_ID SQL_ID,SUM(c.CPU_TIME_DELTA) CPU_TIME
FROM DBA_HIST_SQLSTAT c WHERE c.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME>=TO_DATE('09/15/2008','MM/DD/YYYY')AND END_INTERVAL_TIME<=TO_DATE('09/16/2008','MM/DD/YYYY')) GROUP BY c.SQL_ID) a WHERE a.SQL_ID=b.SQL_ID ORDER BY a.CPU_TIME DESC) where rownum<6;>

Note: if u require more information about a sql statement execution please try with the following views:

dba_hist_sqltext
dba_hist_sqlstat
dba_hist_snapshot
v$sql
v$sqlstats
v$sqltext


thanks , have a nice day.............

Winning isn't everything, but wanting to win is.
Vince Lombardi