Re: how to find the most resource intensive sql?

  • From: Tim Johnston <tjohnston@xxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 11 Mar 2004 19:22:32 -0500

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



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