Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: How to tune this query ...

Re: RE: How to tune this query ...

From: chao_ping <chao_ping_at_vip.163.com>
Date: Sat, 09 Nov 2002 23:38:40 -0800
Message-ID: <F001.004FFB83.20021109233840@fatcity.com>

This is a multi-part message in MIME format.

--=====002_Dragon373150002168_=====
Content-Type: text/plain;

      charset="GB2312"
Content-Transfer-Encoding: quoted-printable

Khedr, Waleed=A3=AC=C4=FA=BA=C3=A3=A1

=A1=A1=A1=A1Use rule hint gives out the answer most fast,according to my=  test in my several database.     

=3D=3D=3D=3D=3D=3D=3D=3D 2002-11-08 14:19:00 =C4=FA=D4=DA=C0=B4=D0=C5=D6=D0=D0=B4=B5=C0=A3=BA =3D=3D=3D=3D=3D=3D=3D=3D

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 =3D s.SID
AND s.TYPE !=3D 'BACKGROUND' 
AND t.obj# =3D l.id1 
AND u.USER# =3D t.owner# 
 
 

Waleed

=3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D 
=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=D6=C2
=C0=F1=A3=A1

=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1chao_ping
=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1chao_ping_at_vip.163.com =A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A12002-11-10

--=====002_Dragon373150002168_=====
Content-Type: text/html;

      charset="GB2312"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Dgb2312"=
 http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.3315.2870" name=3DGENERATOR></HEAD>
<BODY bgColor=3D#eaeaea><FONT size=3D2><FONT face=3D=CB=CE=CC=E5>Khedr,=
 Waleed=A3=AC=C4=FA=BA=C3=A3=A1</FONT>
</FONT>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3D=CB=CE=CC=E5 size=3D2>=A1=A1=A1=A1Use rule hint gives out the=
 answer most
fast,according to my test in my several database.</FONT></DIV>
<DIV><FONT face=3D=CB=CE=CC=E5 size=3D2>&nbsp;&nbsp;&nbsp; </FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3D=CB=CE=CC=E5 size=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=
 2002-11-08&nbsp;14:19:00&nbsp;=C4=FA=D4=DA=C0=B4=D0=C5=D6=D0=D0=B4=B5=C0=A3=BA =3D=3D=3D=3D=3D=3D=3D=3D</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>
<TABLE width=3D"100%">

  <TBODY>
  <TR>
    <TD width=3D"100%">

      <BLOCKQUOTE 
      style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px;=
 MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">
        <DIV><SPAN class=3D152421422-08112002><FONT color=3D#0000ff=
 face=3DArial 
        size=3D2>Try this:</FONT></SPAN></DIV>
        <DIV><SPAN class=3D152421422-08112002><FONT color=3D#0000ff=
 face=3DArial 
        size=3D2>SELECT&nbsp;=
 l.SID,s.serial#,s.username,s.terminal, 
        <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 DECODE(l.TYPE,'RW','RW - 
        Row Wait Enqueue', 'TM','TM - DML Enqueue', 'TX','TX -=
 Trans Enqueue', 
        'UL','UL - User',l.TYPE||'System')=
 res,</FONT></SPAN></DIV>
        <DIV>&nbsp;</DIV>
        <DIV><SPAN class=3D152421422-08112002><FONT color=3D#0000ff=
 face=3DArial 
        size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 SUBSTR(t.NAME,1,20) 
        TAB,u.NAME owner,=
 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
        l.id1,l.id2,=
 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
        DECODE(l.lmode,1,'No Lock', 
       =

 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        2,'Row Share', 
       =

 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        3,'Row Exclusive', 
       =

 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        4,'Share', 
       =

 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        5,'Shr Row Excl', 
       =

 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        6,'Exclusive',NULL) lmode, 
        <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 DECODE(l.request,1,'No 
        Lock', 
       =

 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        2,'Row Share', 
       =

 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        3,'Row Excl', 
       =

 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        4,'Share', 
       =

 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        5,'Shr Row Excl', 
       =

 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

        6,'Exclusive',NULL) request <BR>FROM (select /*+ no_merge=  */ * from

        v$lock) l, (select /*+ no_merge */ *&nbsp; from v$session=  )s,

        <BR>sys.USER$ u,sys.obj$ t <BR>WHERE l.SID =3D s.SID=  <BR>AND s.TYPE !=3D

        'BACKGROUND' <BR>AND t.obj# =3D l.id1 <BR>AND u.USER# =3D=  t.owner#

        </FONT></SPAN></DIV>
        <DIV><FONT color=3D#0000ff face=3DArial=
 size=3D2></FONT>&nbsp;</DIV>
        <DIV><FONT color=3D#0000ff face=3DArial=
 size=3D2></FONT>&nbsp;</DIV>
        <DIV><SPAN class=3D152421422-08112002><FONT color=3D#008000=
 face=3DArial 
       =

 size=3D2>Waleed</FONT></SPAN></DIV></BLOCKQUOTE></TD></TR></TBODY>=
</TABLE></FONT></DIV>
<DIV>
<P><FONT face=3D=CB=CE=CC=E5 size=3D2>=3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D=
 =3D =3D </FONT></P>
<P><FONT face=3D=CB=CE=CC=E5 size=3D2>=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=D6=C2<BR>=C0=F1=A3=A1</FONT></P>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3D=CB=CE=CC=E5=

 size=3D2>=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1chao_ping</FONT></DIV>
<DIV><FONT face=3D=CB=CE=CC=E5 size=3D2><FONT face=3D=CB=CE=CC=E5=
 size=3D2>=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1</FONT><A href=3D"mailto:chao_ping_at_vip.163.com">chao_ping_at_vip.163.com</A></FO= NT></DIV>
<DIV><FONT face=3D=CB=CE=CC=E5=

 size=3D2>=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A1=A12002-11-10</FONT></DIV>=

<DIV>&nbsp;</DIV></DIV></BODY></HTML>

--=====002_Dragon373150002168_=====--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: chao_ping
  INET: chao_ping_at_vip.163.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 Sun Nov 10 2002 - 01:38:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US