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@xxxxxxxxxxxxx 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jonathan Lewis
Sent: Wednesday, April 14, 2004 11:06 AM
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 = 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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: