RE: About an old hint question

From: Stephen Lee <Stephen.Lee_at_DTAG.Com>
Date: Thu, 15 May 2003 07:51:22 -0800
Message-ID: <F001.00599AAD.20030515075122@fatcity.com>

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