Re: Query hangs suddenly

  • From: V R <vragunat@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 28 Feb 2007 21:07:10 -0800 (PST)

Atleast two ways to debug what the current session is doing:
(1) Use dbms_system.set_sql_trace_in_session and trace the hung session
(2) If you are familiar with oradebug, attach to the process and dump errorstack

You may also want to see v$session_longops and see if there is indication of 
what is being done for the SID.

"Daniel W. Fink" <daniel.fink@xxxxxxxxxxxxxx> wrote:        Ram,
 Run 10046 traces on good and bad sessions. While running, extract the actual 
execution plan as it may be different than what autotrace is saying.
 Simply put, the session can be doing one of 4 things
 1) Performing CPU activity
 2) Waiting on available CPU cycles
 3) Waiting on the completion of Oracle instrumented wait/timed event
 4) Waiting on the completion of Oracle uninstrumented wait/timed event
 The information in v$session_wait indicates that it is not in #3 (WAITED KNOWN 
TIME says that the event completed). Increasing CPU time and logical i/o (there 
is not necessarily a direct correlation) indicates that there is likely a large 
amount of logical i/o (memory reads) going on. Unfortunately, there is not an 
Oracle supported facility to always diagnose what is happening. However...
 There are a couple of events that you can set that will dump logical i/o 
related information into a trace file. You can find a paper and some examples 
Daniel Fink

Oracle Performance, Diagnosis and Training

OptimalDBA Oracle Blog  Join me at Miracle Scotland DB Forum!
 Ram Raman wrote:    Thanks. 
   If it has not waited for anything since then should it not be producing the 
results. What is it doing then? Sorry I dont get it.
   On 2/28/07, Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:   At 12:13 PM 
2/28/2007, Ram Raman wrote:
 >Hi all,
 >We have a query which is run everyday and was running OK till last     
 >week. The query starts hanging for the past 2 days when run. Oracle
 >is 9.2.  We run this query for today's date ("audit_stamp" - See the
 >comment on the query) or yesterday's date and it returns data     
 >quickly. But when the audit_stamp is older than the past 2 days, it
 >hangs. The cost from the plan in all the cases is the almost the
 >same using autotrace traceonly.
 >Here is select from V$session_wait for the session:     
 >        SID EVENT
 >---------- ----------------------------------------------------------------
 >P1TEXT                                                                   P1
 >---------------------------------------------------------------- ----------   
 >P2TEXT                                                                   P2
 >---------------------------------------------------------------- ----------
 >---------- ----------
 >--------------- -------------------
 >         51 SQL*Net message to client
 >driver id                                                        1413697536   
 >#bytes                                                                    1
 >0         -1
 >             543 WAITED KNOWN TIME
 >I do not understand why it waits for "SQL*Net message to client".     
 It is not waiting. The status is "waited known time". The last known
 wait event was "SQL*Net message to client" and it hasn't waited for
 anything (instrumented) since.
 Wolfgang Breitling
 Centrex Consulting Corporation

Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.

Other related posts: