Re: Query hangs suddenly
- From: "Daniel W. Fink" <daniel.fink@xxxxxxxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Feb 2007 20:16:16 -0700
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...
*** DO NOT DO THIS ON A PRODUCTION SYSTEM ***
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 at http://www.optimaldba.com/papers/TracingLogicalIOs.zip
*** DO NOT DO THIS ON A PRODUCTION SYSTEM ***
--
Daniel Fink
Oracle Performance, Diagnosis and Training
OptimalDBA http://www.optimaldba.com
Oracle Blog http://optimaldba.blogspot.com
Join me at Miracle Scotland DB Forum!
http://www.miracleltd.com/index.asp?page=167&page2=343
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
<mailto: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
>----------------------------------------------------------------
----------
>P3TEXT
>P3 WAIT_TIME
>----------------------------------------------------------------
>---------- ----------
>SECONDS_IN_WAIT STATE
>--------------- -------------------
> 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".
>Thanks.
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.
>
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com <http://www.centrexcc.com>
Other related posts:
