Sunday, October 19, 2008

Top N SQL Query Run within 30 minuts

When the database load increase rapidly it is even hard to log in Enterprise Manager (EM) to see what is the actual cause by seeing top activity or generating AWR report. Most of the time bad SQl is behind such problem. Here is a script which give you Top 20 (sort by elapsed time) SQL quer run on last 30 minutes

SELECT x.sql_id,x.sql_text,x.users,x.executions,x.disk_reads
,x.buffer_gets,x.cpu_time,x.elapsed_time FROM
(select sql_id,sql_text,PARSING_SCHEMA_NAME users
,executions, disk_reads,buffer_gets
,DECODE (executions,0,0,round(((cpu_time/1000000)/executions),2)) cpu_time
,DECODE (executions,0,0,round(((elapsed_time/1000000)/executions),2)) elapsed_time
from v$sqlarea
where parsing_user_id !=3D
and
LAST_ACTIVE_TIME<(sysdate-60/2600)
)x WHERE rownum <=20
ORDER BY x.elapsed_time DESC;

No comments: