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 ----- Original Message ----- 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 = 19633 and a.addr = b.paddr and b.sql_address = 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 -----------------------------------------------------------------