| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: How to tune this query ...
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,
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> </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> </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D=CB=CE=CC=E5 size=3D2>=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</FONT></DIV>
<DIV> </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 =
l.SID,s.serial#,s.username,s.terminal,
<BR> =
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> </DIV>
<DIV><SPAN class=3D152421422-08112002><FONT color=3D#0000ff=
face=3DArial
size=3D2> =
SUBSTR(t.NAME,1,20)
TAB,u.NAME owner,=
<BR>
l.id1,l.id2,=
<BR>
DECODE(l.lmode,1,'No Lock',
=
2,'Row Share',
=
3,'Row Exclusive',
=
4,'Share',
=
5,'Shr Row Excl',
=
6,'Exclusive',NULL) lmode,
<BR> =
DECODE(l.request,1,'No
Lock',
=
2,'Row Share',
=
3,'Row Excl',
=
4,'Share',
=
5,'Shr Row Excl',
=
6,'Exclusive',NULL) request <BR>FROM (select /*+ no_merge= */ * from
v$lock) l, (select /*+ no_merge */ * 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> </DIV>
<DIV><FONT color=3D#0000ff face=3DArial=
size=3D2></FONT> </DIV>
<DIV><SPAN class=3D152421422-08112002><FONT color=3D#008000=
face=3DArial
=
<DIV> </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
 |
 |