> "Jamadagni, Rajendra" wrote: > > I am looking at oracle supplied locking query ... any idea how to tune > 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 v$lock l, 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# > > Raj
Raj, It's a bit late here (midnight soon) so you will forgive me for not researching all the details, but here is just a bit of advice. The main problem are the dynamic views. I'd check in V$FIXED_VIEW_DEFINITION what are the X$ they are based on (look for the definition of GV$ rather than V$) and into V$INDEXED_FIXED_COLUMN which says what columns from X$ views are indexed (not many of them). And the important matter is of course deciding the angle of attack - which table first? The obvious candidate is V$SESSION, but it is not obvious that you have the proper indexes to join it to V$LOCK (which is itself a complicated join, if I remember well). I think that ORDERED plus a request for a hash join between v$lock and v$session, with tables or views listed as v$session, v$lock, sys.obj$ and sys.user$ should improve things. Next step would be to see how v$lock is built, and directly query the X$ views which are relevant, and them only. But I am erring. HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
