Re: Dealing with "orphaned" sessions

On Wed, 30 May 2001 23:49:40 +0200, "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:

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

After going over this some more with the application programmer, I'm beginning to wonder if I'm chasing a red herring.

While all of the "orphaned" sessions look suspicious, from what he tells me, there really wouldn't be that much activity to roll back on a SHUTDOWN IMMEDIATE. The app is an ordering system running on a kiosk -- a cabinet containing a completely enclosed PC, with the only user access being the face of a touch-screen monitor. When they begin a session, a row is updated to "flag" that the application is in use. As they step their way through several pages of info, the only database activity would be a series of SELECTs. Only when they confirm a completed order does the app issue an INSERT command, after which it immediately does a disconnect and recycles back to its "Welcome" screen.

Is there anything else I should/could be looking at that could cause inordinately long shutdowns?

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)