| 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: Process field on v$session
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Wednesday, April 14, 2004 12:06 PM
To: oracle-l_at_freelists.org
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=3D1Bytes=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
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_at_lifetouch.com
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |