Re: How to tune this query ...

> "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: sfaroult_at_oriole.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).