Re: how to find the most resource intensive sql?

Can they start a sql trace from the app? If not, can you identify the session (i.e. By looking at things like the machine, program and username fields in v$session)? If so, have the user log on and start a trace in their session (check out the dbms_system package)... Have the user run through screens until the encounter the problem... Then examine the resulting trace file for the problem SQL... Usually, I skip straight to a 10046 at level 12 so I also get the bind variables and wait events... oops... make that timed events...

:-)

Tim

Lim, Binley wrote:

>
>
>>'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'
>>
>>
>
>In a (preferably) test system, ask the user to get to the point just before
>the "button".
>
>Flush the shared_pool, then push the button.
>
>This communication is confidential and may contain privileged material.
>If you are not the intended recipient you must not use, disclose, copy or retain it.
>If you have received it in error please immediately notify me by return email
>and delete the emails.
>Thank you.
>----------------------------------------------------------------
>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
>-----------------------------------------------------------------
>
>

-- 
Regards,
Tim Johnston
Tel: 978-322-4226
Fax: 978-322-4100


----------------------------------------------------------------
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
-----------------------------------------------------------------