RESOURCES ARE NOT FREED WHEN A SESSION DIES ABNORMALLY.

From: Asif Momen <asif_oracle_at_yahoo.com>
Date: Fri, 1 Aug 2008 09:30:30 -0700 (PDT)
Message-ID: <246097.5146.qm@web56610.mail.re3.yahoo.com>

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       

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Aug 01 2008 - 11:30:30 CDT