RE: how to find the most resource intensive sql?
- From: Feighery Raymond <Raymond.Feighery@xxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 11 Mar 2004 13:22:09 -0000
Ryan
There's two good scripts for this purpose, expensive_sql.sql and
suspicious_sql.sql on Steve Adams' site
http://www.ixora.com.au/scripts/query_opt.htm
Also, just check the high CPU statements currently executing:
set pages 2000
col username format a10 heading "Username"
col osuser format a8 heading "OS User"
col sid format 999 heading "SID"
col serial# format 99999 heading "Serial#"
col program format a25 heading "Program"
col connect_time format a11 heading "Logon Time"
col sqltext format a200 heading "Text of SQL command"
col value format 999,999 heading "CPU"
col com format a6 heading "Command"
spool what.lst
select
vs.username,
vs.osuser,
vs.sid,
vs.serial#,
vp.spid,
vs.program,
to_char(vs.logon_time, 'DD-MON-YYYY HH24:MI:SS') connect_time,
se.value,
nvl(decode(vs.command,1,'CRT TABL',2,'INSERT',3,'SELECT',4,'CRT
CLST',
5,'ALT CLST',6,'UPDATE',7,'DELETE',8,'DROP',9,'CRT INDX',
10,'DRP INDX',11,'ALT INDX',12,'DRP TABL',15,'ALT TABL',
17,'GRANT',18,'REVOKE',19,'CRT SYNM',20,'DRP SYNM',21,
'CRT VIEW',22,'DRP VIEW',26,'LOC TBLE',27,'NOTHING',28,
'RENAME',29,'COMMENT',30,'AUDIT',31,'NOAUDIT',32,'CRT XTDB',
33,'DRP XTDB',34,'CRT DBSE',35,'ALT DBSE',36,'CRT RSEG',
37,'ALT RSEG',38,'DRP RSEG',39,'CRT TBSP',40,'ALT TBSP',
41,'DRP TBSP',42,'ALT SESS',43,'ALT USER',44,'COMMIT',
45,'ROLLBACK',46,'SVEPOINT','IDLE'),'?') com,
decode(vq.sql_text,null,'No Open Cursors',vq.sql_text) sqltext
from
v$session vs,
v$process vp,
v$sqlarea vq,
v$sesstat se,
v$statname sn
where
vs.paddr = vp.addr
and
vs.type != 'BACKGROUND'
and
vs.sql_address = vq.address (+)
and
vs.sql_hash_value = vq.hash_value (+)
and
se.statistic# = sn.statistic#
and
sn.name like '%CPU used by this session%'
and
se.sid = vs.sid
and
vs.username is not null
order by
se.value desc
/
spool off
-----Original Message-----
From: ryan.gaffuri@xxxxxxx [mailto:ryan.gaffuri@xxxxxxx]
Sent: Thursday, March 11, 2004 1:12 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: how to find the most resource intensive sql?
assuming you can't use statspack? Is there a query for this? I was asked the
following and I'm not sure how to answer it:
'A user comes to you and says they get to a point in the application and hit
a button. It's slow. You do not have any access to the application. How do
you find the problem query in the database'
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
___________________________________________________________________________
This email and any attached to it are confidential and intended only for the
individual or entity to which it is addressed. If you are not the intended
recipient, please let us know by telephoning or emailing the sender. You
should also delete the email and any attachment from your systems and should
not copy the email or any attachment or disclose their content to any other
person or entity. The views expressed here are not necessarily those of
Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you.
Churchill Insurance Group plc. Company Registration Number - 2280426.
England.
Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1
1DP.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
