Re: Oracle Locking Up

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Wed, 30 Apr 2003 11:46:52 -0700
Message-ID: <3EB01A1C.E59A83E4@exxesolutions.com>

Alex Filonov wrote:

> 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.

Just got off the phone with the student and this is not the case.

All can work well for several hours of homework, the problem happen, performs a restart, and the problem may or may not reoccur for hours.

Baffling is as close as I can get to this one.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Wed Apr 30 2003 - 13:46:52 CDT