Re: Re: table locked from n minutes

From: <solbeach_at_cox.net>
Date: Fri, 2 Apr 2004 11:39:08 -0500
Message-Id: <20040402163909.VRXO2430.fed1mtao02.cox.net@smtp.west.cox.net>

> From: Mladen Gogala <mladen_at_wangtrading.com>
> Date: 2004/04/02 Fri AM 10:43:53 EST
> To: oracle-l_at_freelists.org
> Subject: Re: table locked from n minutes
>
> Why do you want to do things with the sysdate?

The original query is MUCH faster than the suggested alternative.

      select object_name, do.object_id, session_id, serial#, osuser, username, locked_mode , start_time, module
      from   dba_objects do, v$session, v$locked_object lo, v$transaction
      where to_date(start_time,'MM/DD/YY HH24:MI:SS') < (sysdate-(10/1440))
       and  locked_mode in (3,5,6)
       and  session_id = sid
       and  saddr = ses_addr
       and  lo.object_id = do.object_id
      order by start_time desc

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.07       0.07          0          0          0           0
Execute      2      0.00       0.28          0          0          1           0
Fetch        1      0.26       0.30         10          0        806           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.33       0.65         10          0        807           0

=================================================

select object_name, do.object_id, ss.sid, serial#, osuser, username, lmode , ctime, module
from dba_objects do, v$session ss, v$lock lck where lck.ctime > 600
 and lmode in (3,5,6)

 and  lck.sid = ss.sid
 and  lck.id1 = do.object_id
 and  lck.id2 = 0

 order by ctime

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.07       0.07          0          0          0           0
Execute      2      0.03       1.11          0          0          3           0
Fetch        1     12.94      19.60       6181      46616       6394           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     13.04      20.78       6181      46616       6397           1


----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Apr 02 2004 - 10:36:31 CST