Sniped sessions and its affects (open cursors)

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 ?) 2) Why was there a delay after 600 seconds before the session got sniped ?

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)

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?  

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 ?

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?
7) Was this clean up done by PMON ?

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

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

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

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. Received on Thu Jul 04 2002 - 08:22:23 CDT