RE: how to find the most resource intensive sql?

From: Feighery Raymond <Raymond.Feighery_at_churchill.com>
Date: Thu, 11 Mar 2004 13:22:09 -0000
Message-ID: <817D2444710B934B9F7B8A1DAAF432D601F2ADE0@brcexm03>

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_at_cox.net [mailto:ryan.gaffuri_at_cox.net] Sent: Thursday, March 11, 2004 1:12 PM
To: oracle-l_at_freelists.org
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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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

Received on Thu Mar 11 2004 - 07:20:13 CST