RESOURCES ARE NOT FREED WHEN A SESSION DIES ABNORMALLY.
- From: Asif Momen <asif_oracle@xxxxxxxxx>
- To: freelist freelist <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 1 Aug 2008 09:30:30 -0700 (PDT)
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
Other related posts:
