Re: Oracle Locking Up

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Wed, 30 Apr 2003 11:44:59 -0700
Message-ID: <3EB019AB.8FCF174C@exxesolutions.com>

Mark D Powell wrote:

> Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3EAF63E5.CE227221_at_exxesolutions.com>...
> > Mark D Powell wrote:
> >
> > > Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3EAEC8D1.95FCED50_at_exxesolutions.com>...
> > > > 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, is the problem repeatable after booting the machine?
> > > Do queries against the rdbms dictionary run OK?
> > >
> > > HTH -- Mark D Powell --
> >
> > The problem is sporadic. When it locks up Oracle can be shut down with another SQL*Plus session / as sysdba
> > and shutdown immediate. Then it may happen again hours later. Same goes for rebooting the machine. It will
> > happen again but hours later.
> >
> > And what is strange is that it never seems to interfere with a SQL statement that is executing. The statement
> > will finish, commit but never return to the SQL> prompt.

>

> Ok, what is the OS? How much memory does it have? And how much
> virual memory is configured? Since the session has to be idle for a
> couple of hours for the problem to show up then in the meantime is the
> user firing up any games or other pieces of software? That is I am
> looking at the possibility of a specific conflict due to memory or
> possibly video.

>

> Being that this is a student, and that the session has to sit idle a
> while, then as a work around could the student just stop the database
> when they are not going to be using it and start it up when they need
> it (at least uptil someone hits upon something useful)?
>
> HTH -- Mark D Powell --

Student is a 45 year old professional with 20 years experience in the industry ... just not with Oracle. Nothing else is on the machine except the O/S and Oracle. 512MB RAM. Not sure about virtual memory but probably the default from a Win2K install as I can't imagine it being changed without reason.

Let me clarify on the idle issue. The machine is on 8-10 hours at a stretch. Never locks up on its own. And the lockups are strictly SQL*Plus. Everything else works just fine. Which is why starting another SQL*Plus session as SYS and doing a shutdown works just fine. The problems occur randomly and when running very simple SQL either in SQL*Plus ... like select * from or select count(*) from or with stored procedures containing nothing more exotic than instring and substring.

I find this all fascinating and appreciate any and all help.

Thanks.

--
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:44:59 CDT