Re: Dealing with "orphaned" sessions

Comments and answers embedded

Sybrand Bakker, Oracle DBA

"Ed Stevens" <Ed_Stevens_at_nospam.noway.nohow> wrote in message news:3b1517ce.14775856_at_ausnews.austin.ibm.com...
> Subject: Dealing with "orphaned" sessions
>
> Platform: Oracle 8.0.5, Std. Ed.; Windows NT 4.0, sp6
>
> Every Saturday evening we have a short maintenance window, during
> which we have the servers hosting our Oracle db's rebooted. Most of
> the servers host multiple Oracle databases. I have created a command
> file that the operators execute before cycling the server. One of the
> things this file does is execute a SQL file to log on to each database
> and issue a SHUTDOWN IMMEDIATE.
>
> For several weeks, this process seems to hang at one particular
> database. The logs indicate that the connection is successful and the
> SHUTDOWN is issued, but up to an hour later we are still waiting for
> the shutdown to complete. Due to time constraints we eventually have
> to pull the plug and depend on crash recovery when we restart.
>
> Looking at V$SESSIONS and V$SESSION_WAIT, I see an inordinate number
> of sessions on this database. The application only runs on 4 walk-up
> touch-screen kiosks. Those and a couple of admin machines are all the
> connections the db should be getting, yet there will be well over 100
> sessions. Further investigation of V$SESSION_WAIT shows that the
> vast majority of these sessions are waiting for "SQL*Net message from
> client" with SECONDS_IN_WAIT ranging up to several days! The nature
> of the application allows users to walk away, but the application
> developer (with whom I have a very good working relationship, and
> trust to be shooting straight) insists the app checks this and issues
> a disconnect after a few minutes of inactivity.
>
> Question #1: Am I mis-interpreting V$SESSION_WAIT when I assume that a
> session showing "SQL*Net message from client" and SECONDS_IN_WAIT of
> 179909 (49.97 hours) probably represents a session that didn't get
> disconnected and someone initiated a new session from the same
> machine?
>

You'r 100% correct. You could also check the command column in v$session. It will be 0 is the program is doing nothing. Similar info is in last_call_et, which seems to be the number of milliseconds since the last call.

> Question #2: Would waiting for SHUTDOWN IMMEDIATE to roll these back
> be causing my shutdown problems? If not, what else should I be
> looking for? If so . . .

Likely
>
> Question #3: If I were to write a script to detect these and
> terminate them, would that not generate the same rollback delays as
> simply issuing a SHUTDOWN IMMEDIATE.

My experience tells me as long as you get the spid of the associated v$process record (use session.paddr to join to process.addres) and issue a kill -9 (Unixes) or orakill (NT on that process), you don't have that problem.

>
>
>
> --
> Ed Stevens
> (Opinions expressed do not necessarily represent those of my employer.)
Received on Wed May 30 2001 - 16:49:40 CDT