RE: RESOURCES ARE NOT FREED WHEN A SESSION DIES ABNORMALLY.
- From: Asif Momen <asif_oracle@xxxxxxxxx>
- To: freelist freelist <oracle-l@xxxxxxxxxxxxx>, mark.powell@xxxxxxx
- Date: Fri, 1 Aug 2008 14:27:47 -0700 (PDT)
Hi,
Thanks to everybody for their replies.
Yes, the database is also on Windows box (I actually ran a query
against V$VERSION). I have opened an SR with Oracle Support and they
say they are unable to reproduce it on their test environment.
I know we can kill the waiting/blocking session using "ALTER SYSTEM" or
using orakill, but in a highly transactional database, this is really
not affordable.
Regards
Asif Momen
--- On Fri, 8/1/08, Powell, Mark D <mark.powell@xxxxxxx> wrote:
From: Powell, Mark D <mark.powell@xxxxxxx>
Subject: RE: RESOURCES ARE NOT FREED WHEN A SESSION DIES ABNORMALLY.
To: "freelist freelist" <oracle-l@xxxxxxxxxxxxx>
Date: Friday, August 1, 2008, 10:28 AM
Joe, If the database is on Windows then yes I would use
orakill rather than ALTER SYSTEM. Being that I do not normally run
Oracle on Windows I do not know if the official recommendation has changed but
it was to use orakill instead of ALTER SYSTEM when the utility was
first introduced. I am not sure from the OP if the database is on
Windows also or just the client was on Windows.
-- Mark D Powell --
Phone (313) 592-5148
From: Sweetser, Joe
[mailto:JSweetser@xxxxxxxx]
Sent: Friday, August 01, 2008 1:10
PM
To: Powell, Mark D; freelist freelist
Subject: RE:
RESOURCES ARE NOT FREED WHEN A SESSION DIES ABNORMALLY.
It's been a while since I've worked on Windows but I
think there is an Oracle-supplied utility called orakill that can help with
this task providing the database itself is on Windows.
-joe
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Powell, Mark
D
Sent: Friday, August 01, 2008 10:58 AM
To: freelist
freelist
Subject: RE: RESOURCES ARE NOT FREED WHEN A SESSION DIES
ABNORMALLY.
Closing the Windows screen did not terminate the
Oracle session. The fact you can see the session information
in your queries after the Windows application was closed is proof of
this fact. The session is still there and is probably waiting on a
command from the client. You will need to terminate the Oracle session
background process via Oracle using an ALTER SYSTEM KILL SESSION
'sid,serial#'
command. It is not difficult to write a script that looks for orphaned
sessions and terminates them.
-- Mark D Powell
--
Phone (313)
592-5148
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Asif
Momen
Sent: Friday, August 01, 2008 12:31 PM
To:
freelist freelist
Subject: RESOURCES ARE NOT FREED WHEN A SESSION
DIES ABNORMALLY.
Hi all,
Here is the test
case:
SQL> select * from
v$version;
BANNER
----------------------------------------------------------------
Oracle
Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL
Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS
for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version
10.2.0.4.0 - Production
Elapsed: 00:00:00.09
SQL>
create table t(acc number, amt number);
Table
created.
SQL>
SQL> insert into t values (123,
1000);
1 row created.
SQL>
SQL>
commit;
Commit complete.
Session
1:
========
SQL> select sys_context('USERENV','SID')
from
dual;
SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------
28
SQL>
select * from t where acc = 123 for
update;
ACC AMT
---------- ----------
123
1000
SQL>
Session
2:
========
SQL> conn
test/test
Connected.
SQL> select sys_context('USERENV','SID')
from
dual;
SYS_CONTEXT('USERENV','SID')
-------------------------------------------------------------
30
SQL>
select * from t where acc = 123 for
update;
Session 2
hangs (wait for session 1 to either commit or
rollback)
Session 3:
==========
SQL>
select sid, username, event, status, last_call_et,
2
blocking_session, wait_time, seconds_in_wait, state
3 from
v$session
4 where sid in (28, 30);
SID USERNAME EVENT STATUS
LAST_CALL_ET BLOCKING_SESSION
WAIT_TIME SECONDS_I
----------
------------------------------ ------------------------------
--------
------------ -----
28 TEST SQL*Net message from client INACTIVE 108
0
30 TEST enq: TX - row lock contention ACTIVE 33 28 0
Elapsed: 00:00:00.01
If "Session 1" is
abnormally terminated then "Session 2" keeps waiting forever and
following are the wait events:
At this point, abnormally
terminate "Session 1" by closing the SQL*Plus window.
SQL>
/
SID USERNAME EVENT STATUS LAST_CALL_ET BLOCKING_SESSION
WAIT_TIME
---------- ------------------------------
------------------------------ --------
------------ -----
28
TEST SQL*Net message from client INACTIVE 147 0
30 TEST enq: TX -
row lock contention ACTIVE
72 28 0
Elapsed:
00:00:00.01
SQL> /
SID USERNAME EVENT STATUS
LAST_CALL_ET BLOCKING_SESSION WAIT_TIME
----------
------------------------------ ------------------------------
--------
------------ -----
28 TEST SQL*Net message from client INACTIVE
168 0
30 TEST enq: TX - row lock contention ACTIVE
93 28 0
SQL> /
SID USERNAME EVENT STATUS LAST_CALL_ET
BLOCKING_SESSION WAIT_TIME
----------
------------------------------ ------------------------------
--------
------------ -----
28 TEST SQL*Net message from client INACTIVE 258
0
30 TEST enq: TX - row lock contention ACTIVE 183 28 0
Elapsed: 00:00:00.01
Notice that the
last_call_et keeps on ticking and session 2 is still waiting to
acquire
lock.
At this time, shouldn't PMON wake up, clean,
and releases all the resources occupied by "Session 1".
I
tried toying with SQLNET.EXPIRE_TIME parameter on both database
server
and the client-side:
with
"sqlnet.expire_time=2"
SID_SER_USER PROGRAM EVENT STATUS
LAST_CALL_ET
----------------------- -------------------------
------------------------------ -------- ----------
30 - 2362 - TEST
sqlplusw.exe enq: TX - row lock contention
ACTIVE 246
28 -
1624 - TEST sqlplusw.exe SQL*Net message from client INACTIVE 252
you may notice that it has already crossed 4 minutes
of idle time.
I have also tried the
following:
Subject: Orphaned Processes when DCD is enabled on
Windows
Doc ID: Note:462252.1 Type: PROBLEM
Last Revision Date:
21-APR-2008 Status: MODERATED
Yet, no success.
Any help
in this regard would be
appreciated.
Regards
Confidentiality
Note: This message contains information that may be confidential and/or
privileged. If you are not the intended recipient, you should not use, copy,
disclose, distribute or take any action based on this message. If you have
received this message in error, please advise the sender immediately by reply
email and delete this message. Although ICAT Holdings, LLC, Underwriters at
Lloyd's, Syndicate 4242, scans e-mail and attachments for viruses, it does
not
guarantee that either are virus-free and accepts no liability for any damage
sustained as a result of viruses. Thank you.
Other related posts:
