Re: Sniped sessions and its affects (open cursors)

Hi Murthy,

This arrived just as I was about to feed the fish, kiss the kids and jump into bed. So I'll be quick (answers below).

"murthy" <muthy69_at_yahoo.com> wrote in message news:300217f3.0207040522.48cef184_at_posting.google.com...
> Hi,
> I performed a test and I would appreciate if anyone could help me
> better understand the results.
>
> I'am running the database in dedicated server mode and my server is a
> SUN FIRE E15K machine.
> I set SQLNET.TIMEOUT to 60
> I set the IDLE_TIME in the default profile to 10
>
> From my desktop
> I opened 2 sessions one as user x and the other as user sys.
> From the sys user session i kept running the file called sess.sql
> which contained this SQL
> select sid,serial#,username,program,machine,last_call_et "last
> call",status
> from v$session order by 7,6;
> I kept the user X session idle for more than 10 minutes and its
> session status changed to SNIPED. (infact it took more than 10
> minutes. The LAST_CALL_ET value for the idle session got more than 670
> before its session was sniped).
> 1) Which process Sniped this session ? (PMON ? SMON ?)

PMON
> 2) Why was there a delay after 600 seconds before the session got
> sniped ?

PMON is a bit like myself. Wakes up occasionally, checks to see if there's anything doing, does it, goes back to sleep. After the 600 seconds, the session is doomed once PMON detects it, which could be sometime after.

>
> Now i closed the SQL*PLUS window of the user X without typing in exit
> or anything.
> The row for this session disappeared from V$SESSION.
> 3) Did SQL*PLUS communicate with the database that it was exiting?
> (The reason i asked this question is related to question 4)

Yes. An exit or clean get out will send a message to the server to say adios amigo.

>
> I agained opened another SQL*PLUS session as user X
> I kept it IDLE for more than 10 mintes and its session status changed
> to SNIPED.
> I opened another session as user X.
> I disconnected the network cable of my desktop and now i closed the 2
> SQL*PLUS windows of the user X.
> Now i tried to run the sess.sql from the SYS session. i got an error
> saying TNS packet writer failure.
> So i Closed the SQL*PLUS window for the sys user as well.
> I opened an SQL*PLUS session for SYS user and again ran sess.sql
> Now i saw that the X users sessions remained in V$SESSION.
> One was with a status of SNIPED and other with a status of INACTIVE.
> The previous SYS user's session remained with a status of INACTIVE.
>
> 4) I think that SQL*PLUS communicates a diconnect to the database even
> if you close the window using ALT-F4 or simply closing the window. And
> the database removes the session (SNIPED or whatever status)row then.
> Since i removed the network cable, SQL*PLUS could not do this in the
> second case. Am i right?

Correct

>
> I checked the V$SESSION table after 1/2 an hour and the status of both
> X user's session's has changed to SNIPED. But the SYS user's session
> remained with a status of INACTIVE.
>
> 5) Wouldn't SYS connections be sniped even if they are idle beyond the
> idle time ?

SYS isn't affected by profiles so no (I think)

>
> Now i issued this SQL
> select sid,user_name username,sql_text from v$open_cursor order by 1;
> I saw that cursors are not open for SNIPED sessions.
> But a row exists in V$OPEN_CURSOR for the SYS session (which window i
> closed and whose status is recorded as INACTIVE in V$SESSION).
>
>
> I checked the V$SESSION after an hour and saw that the SYS session was
> removed from V$SESSION along with the SNIPED sessions for user X.
>
> 6)Was it remove because SQLNET.TIMEOUT was set to 60 minutes and net8
> discovered that the client was no longer there after 1 hour and
> cleaned up these sessions?

Correct.

> 7) Was this clean up done by PMON ?

Correct

>
>
> 8) What is the best way to deal with these SNIPED sessions?
> I wrote a script like this
>
> tmpfile=/tmp/tmp.$$
> for sid in sid1 sid2;do
> if pgrep -f pmon_${sid}>/dev/null;then
> ORACLE_SID=$sid
> export ORACLE_SID
> sqlplus '/ as sysdba'<<EOF >/dev/null
> spool $tmpfile
> select spid from v\$process a,v\$session b
> where a.addr=b.paddr
> and status='SNIPED'
> and a.addr not in(
> select paddr
> from v\$dispatcher);
> EOF
> for x in `cat $tmpfile|grep "^[0123456789]"`;do
> echo killing process $x
> kill -9 $x
> done
> fi
> done
> rm $tmpfile
>
> I dont want to kill the DISPATCHER process.
> I once saw that the SPID of a Sniped session pointed to a dispatcher.
> I would prefer to user ALTER SYSTEM SHUTDOWN ... to stop the
> dispatcher.
> Is this procedure and script O.K. ?

The script looks OK from my quick look but I prefer option 9 (it's what it's designed for).

>
> 9) Or is it better to reduce SQLNET.EXPIRE_TIME? (What will be the
> affect on network traffic ?)
>

This would be my recommendation. I guess it depends on how many connections you have but I would suggest the additional network traffic all things being equal would be minimal

>
> 10) What happens to the resources held by SNIPED sessions and INACTIVE
> sessions whose clients are no longer there (Their sessions have not
> been SNIPED yet) ?
> (i'am going to test this tomorrow - but appreciate any advance answer
> here)

as part of PMON's "cleaning" out process, it will release all resouces held by these sessions. Which is actually quite neat when you think about it.
>
> The reason i did this test was in our environment, user sessions are
> getting killed because of network breakdowns.
> And i'am looking for a way to deal with them.
>
> Thanks in advance for any help and advice.

I guess the best solution would be to fix the network problems (you wish).

Hope some of this helps. Sorry if it's a bit too brief.

Good night

Richard Received on Thu Jul 04 2002 - 08:53:51 CDT