Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Locking Up

Re: Oracle Locking Up

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 30 Apr 2003 08:48:41 -0700
Message-ID: <336da121.0304300748.31629b67@posting.google.com>


Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3EAF6481.3427A466_at_exxesolutions.com>...
> Alex Filonov wrote:
>
> > Daniel Morgan wrote:
> > > Oracle 9.2.0.1.0 on a single CPU 1.7GHz Pentium IV
> > >
> > > One user doing SELECTS from various tables none with more than 10K rows
> > > and here's what I get:
> > >
> > > SQL> select event, seconds_in_wait
> > > 2 from v_$session_wait;
> > >
> > > EVENT SECONDS_IN_WAIT
> > > ------------------------------------------------------ ---------------
> > > null event 0
> > > pmon timer 3
> > > rdbms ipc message 0
> > > rdbms ipc message 0
> > > rdbms ipc message 154
> > > rdbms ipc message 3
> > > rdbms ipc message 3
> > > smon timer 208
> > > library cache pin 0
> > > library cache lock 3
> > > SQL*Net message from client 306
> > > SQL*Net message from client 926
> > > SQL*Net message from client 256
> > >
> > > The lock up and these waits are outrageous. Any ideas will be appreciated.
> > >
> > > It is a student's machine and I would like to try to fix the problem by
> > > email or phone rather than driving to their house.
> > >
> > > Thanks.
> > > --
> > > Daniel Morgan
> > > http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> > > damorgan_at_x.washington.edu
> > > (replace the x with a u to send email)
> > >
> >
> > Daniel, what is the problem? I don't think SQL*Net waits are
> > the problem, it usually happens when you have set pause on in sqlplus
> > session. The high value of smon timer looks like related to bug 2717586,
> > which says that it fixed in 9.2. Never mind, it might be one of the bugs
> > popping up in different versions. How long session was active? And the
> > main question is: does user really have big response times?
>
> Installs from the same CD on other machines have not duplicated this phenomena
> though none of the other machines are P4 1.7GHz. I'll look into getting the
> student the latest patchset. The session seems to need to be active for at least
> two hours for it to happen but almost all of that time is idle time.
>
> Not sure what you mean by big response times. SELECT COUNT(*) from a table with
> less than 10,000 rows should return in less than the hour+ the student waited.
> <g>
>

It should return in couple of seconds. But there is a strange thing here, I see that most waits are SQL*Net waits. And SQL*Net waits have nothing to do with the server, session is waiting for user response. Just to be sure, check if that session doesn't have "SET PAUSE ON" if it's sqlplus session. Wierd things happen sometimes.

> Thanks.
Received on Wed Apr 30 2003 - 10:48:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US