RE: Process field on v$session
- From: "QuijadaReina, Julio C" <QuijadJC@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 14 Apr 2004 15:13:55 -0400
Thanks Jonathan. The results of this enhanced query are much more
meaningful since it shows sql running at query time. Like Dennis said,
nothing is better than the word of an expert!
Regards,
Julio
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis
Sent: Wednesday, April 14, 2004 12:06 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Process field on v$session
Dennis,
Could I suggest the following as an enhancement
to your SQL.
select
/*+ ordered */
sql_text
from
v$process a,
v$session b,
v$sqltext c
where
a.spid =3D 19633
and a.addr =3D b.paddr
and b.sql_address =3D c.address
and b.sql_address !=3D '00' -- extra line
and b.sql_hash_value !=3D 0 -- extra line
and c.hash_value =3D b.sql_hash_value -- important extra line
order by
address,
hash_value,
piece
/
The HASH_VALUE is (usually) the only efficient
access path into things like v$sql and v$sql_text,
using a pseudo-index. Your code would require
a full scan of v$sql_text, which would hammer the
library cache on a system with a large shared_pool
setting.
This code eliminates spurious sessions early
(the zero checks) and then uses an index path
to precisely the required entries in v$sql_text,
which should reduce the latch costs.
New path
0 SELECT STATEMENT Optimizer=3DALL_ROWS(Cost=3D84 Card=3D1 =
Bytes=3D188)
1 0 SORT (ORDER BY) (Cost=3D84 Card=3D1 Bytes=3D188)
2 1 NESTED LOOPS (Cost=3D83 Card=3D1 Bytes=3D188)
3 2 HASH JOIN (Cost=3D56 Card=3D1 Bytes=3D98)
4 3 FIXED TABLE (FULL) OF 'X$KSUPR' (Cost=3D28 Card=3D1
Bytes=3D38)
5 3 FIXED TABLE (FULL) OF 'X$KSUSE' (Cost=3D28 Card=3D1
Bytes=3D60)
6 2 FIXED TABLE (FIXED INDEX) OF 'X$KGLNA (ind:1)'
Old path
0 SELECT STATEMENT Optimizer=3DALL_ROWS (Cost=3D84 Card=3D1 =
Bytes=3D188)
1 0 SORT (ORDER BY) (Cost=3D84 Card=3D1 Bytes=3D188)
2 1 HASH JOIN (Cost=3D83 Card=3D1 Bytes=3D188)
3 2 NESTED LOOPS (Cost=3D55 Card=3D1 Bytes=3D98)
4 3 FIXED TABLE (FULL) OF 'X$KSUSE' (Cost=3D28 Card=3D1
Bytes=3D60)
5 3 FIXED TABLE (FIXED INDEX) OF 'X$KSUPR (ind:1)'
6 2 FIXED TABLE (FULL) OF 'X$KGLNA' (Cost=3D28 Card=3D1
Bytes=3D90)
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland http://www.index.is/oracleday.php
June 2004 UK - Optimising Oracle Seminar
----- Original Message -----=20
From: "DENNIS WILLIAMS" <DWILLIAMS@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, April 13, 2004 10:23 PM
Subject: RE: Process field on v$session
Julio
Here is the SQL query I use, where the spid is the unix process I.D.
that
you mentioned that you already have (replace 19633 with your 2800). This
isn't the best query for this, but I use it a lot and I'm on my way out
the
door for the day. Good luck.
select sql_text
from v$process a, v$session b, v$sqltext c
where spid =3D 19633
and a.addr =3D b.paddr
and b.sql_address =3D c.address
order by address, hash_value, piece
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams@xxxxxxxxxxxxx
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
