RE: SQL*Net message to client

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Wed, 23 Nov 2005 12:16:31 -0800
Message-ID: <BEE6A332AA61424EAE305CF89D6F75C81E6DCD@USSCCEVS101.corp.hds.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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_at_hds.com> To: <stellr_at_cns.vt.edu>; "oracle-l" <oracle-l_at_freelists.org> 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 **

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 23 2005 - 14:18:57 CST