RE: How to tune this query ...

[Cary Millsap]

Title: How to tune this query ...

Watch out, though. The RULE hint probably goes away in 10i (my guess, nothing official).   Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Dec 9–11 Honolulu - 2003 Hotsos Symposium on Oracle® System Performance, Feb 9–12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19–21 Dallas -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Jamadagni, Rajendra Sent: Friday, November 08, 2002 4:59 PM To: Multiple recipients of list ORACLE-L Subject: RE: How to tune this query ...   This works nice .... Barbara Baker also hinted me that a good old RULE hint fixes the problem.   Thank you Waleed and Barbara ... Raj ______________________________________________________ Rajendra Jamadagni              MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -----Original Message----- From: Khedr, Waleed [mailto:[EMAIL PROTECTED]] Sent: Friday, November 08, 2002 5:20 PM To: Multiple recipients of list ORACLE-L Subject: RE: How to tune this query ... Try this: SELECT  l.SID,s.serial#,s.username,s.terminal,         DECODE(l.TYPE,'RW','RW - Row Wait Enqueue', 'TM','TM - DML Enqueue', 'TX','TX - Trans Enqueue', 'UL','UL - User',l.TYPE||'System') res,           SUBSTR(t.NAME,1,20) TAB,u.NAME owner,         l.id1,l.id2,         DECODE(l.lmode,1,'No Lock',                 2,'Row Share',                 3,'Row Exclusive',                 4,'Share',                 5,'Shr Row Excl',                 6,'Exclusive',NULL) lmode,         DECODE(l.request,1,'No Lock',                 2,'Row Share',                 3,'Row Excl',                 4,'Share',                 5,'Shr Row Excl',                 6,'Exclusive',NULL) request FROM (select /*+ no_merge */ * from v$lock) l, (select /*+ no_merge */ *  from v$session )s, sys.USER$ u,sys.obj$ t WHERE l.SID = s.SID AND s.TYPE != 'BACKGROUND' AND t.obj# = l.id1 AND u.USER# = t.owner#     Waleed