| 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
Wow! From the expert himself. Thanks Jonathan and thanks for all your
participation on this list.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----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 11:06 AM
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 = 19633
and a.addr = b.paddr
and b.sql_address = c.address
and b.sql_address != '00' -- extra line
and b.sql_hash_value != 0 -- extra line
and c.hash_value = 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=ALL_ROWS(Cost=84 Card=1 Bytes=188) 1 0 SORT (ORDER BY) (Cost=84 Card=1 Bytes=188)
2 1 NESTED LOOPS (Cost=83 Card=1 Bytes=188) 3 2 HASH JOIN (Cost=56 Card=1 Bytes=98) 4 3 FIXED TABLE (FULL) OF 'X$KSUPR' (Cost=28 Card=1 Bytes=38) 5 3 FIXED TABLE (FULL) OF 'X$KSUSE' (Cost=28 Card=1 Bytes=60) 6 2 FIXED TABLE (FIXED INDEX) OF 'X$KGLNA (ind:1)'
Old path
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=84 Card=1 Bytes=188) 1 0 SORT (ORDER BY) (Cost=84 Card=1 Bytes=188)
2 1 HASH JOIN (Cost=83 Card=1 Bytes=188) 3 2 NESTED LOOPS (Cost=55 Card=1 Bytes=98) 4 3 FIXED TABLE (FULL) OF 'X$KSUSE' (Cost=28 Card=1 Bytes=60) 5 3 FIXED TABLE (FIXED INDEX) OF 'X$KSUPR (ind:1)' 6 2 FIXED TABLE (FULL) OF 'X$KGLNA' (Cost=28 Card=1 Bytes=90)
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 = 19633
and a.addr = b.paddr
and b.sql_address = 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
 |
 |