Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: About an old hint question
On our busy systems, the following can run F-O-R-E-V-E-R without the rule hint. With the rule hint, it usually runs in less than 5 seconds. This lists waiters and blockers; note that two of the columns in the query are commented out. I think this came off metalink, or from a book (don't remember). The original used the all rows hint. This might be a good test to see if the need for hints truly have gone away.
set lines 150
set pages 600
col mode_held for a12
col mode_requested for a12
select /*+ rule */ a.osuser waiter, nvl(b.osuser,'NOBODY') blocker,
w.lock_type, h.mode_held, w.mode_requested
-- w.lock_id1, w.lock_id2
from dba_locks w, dba_locks h, v$session a, v$session b
where h.blocking_others = 'Blocking' and h.mode_held != 'None'
and h.mode_held != 'Null' and w.mode_requested != 'None' and w.lock_type =
h.lock_type
and w.lock_id1 = h.lock_id1 and w.lock_id2 = h.lock_id2
and w.session_id in (select sid from v$session where last_call_et > 100 and
sid > 10 and osuser is not null)
and w.session_id = a.sid and h.session_id = b.sid;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephen Lee
INET: Stephen.Lee_at_DTAG.Com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu May 15 2003 - 10:51:22 CDT