RE: SQL*Net message to client
- From: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Nov 2005 12:16:31 -0800
Hi Jonathan,
I do see your point. The use of the word "hanging" does not cover every
eventuality, such as what you have pointed out. However, there are many
occasions where the client front-end "went away" and the backend process
hangs on this message - this is a much more common occurrence than the
'create index' you describe and this can be used to alert the DBA of
such a hang. If the session in question is supposed to be doing
something useful (such as create this index), then I would assume that
the DBA does not kill the session and restart it in the hope that it
would 'go faster' this time around ;-)
If we do get around to a re-print, I will keep this in mind and add this
in as a sidebar.
Thanks!
John
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis
Sent: Wednesday, November 23, 2005 11:56 AM
To: oracle-l
Subject: Re: SQL*Net message to client
John,
I would tend to disagree with that comment.
If there are no wait events being recorded, and the process is consuming
CPU, then my usual experience is that something brutal and inefficient
is happening - such as extreme PL/SQL, or massive scanning and pinning
(perhaps through bitmap indexes) with vast amounts of logical I/O. I've
seen 'create index' take 30 minutes of CPU with no wait events (after
the db file waits for the initial tablescan were over).
I may have come very late to this thread, so someone has probably said
already that
wait_time=-1 means less than 1 hundredth of a second for 8i, and less
than 50 milliseconds for later versions of 9.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005
----- Original Message -----
From: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
To: <stellr@xxxxxxxxxx>; "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, November 23, 2005 7:43 PM
Subject: RE: SQL*Net message to client
Ray,
>What does WAIT_TIME = -1 indicate?
Another
pointer is V$SESSION_WAIT.SEQ# - the value uniquely identifies one
episode of the Wait event. This is incremented every time one Wait event
completes and another is recorded. You can track the progress of a
session from this column.
More importantly, when this
value
does not
increment even though the session consumes CPU, it is a sure indicator
that the session is hanging.
Regards,
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
Co-Author: Oracle Database 10g Insider Solutions
http://www.samspublishing.com/title/0672327910
** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
