Re: V$Session and V$SQL revisited...

On 11 Jul 2002 01:28:55 -0700, t.alexander_at_ads-tec.de (Tom) wrote:

>Hi all,
>
>After doing some research I found that Views V_$Session hold an SID of
>an application and V_$SQL holds SQL_Text of SQL activities. I created
>a select based on these views and then joined these Tables on the
>column HASH_VALUE.
>
>SELECT
>VSS.SID, VSS."SERIAL#", VSS.PROGRAM, VSQL.SQL_TEXT,
>VSQL.LAST_LOAD_TIME
>FROM
>SYS.V_$SESSION VSS, SYS."V_$SQL VSQL
>WHERE
>VSS.SQL_HASH_VALUE = VSQL.HASH_VALUE AND
>VSS.SID=<nn>
>
>At first it looked alright but then I realized that somehow I was
>missing SQL_Text based on the selected SID and the Relation.
>Q1: What is the relationship between V_$Session and V_$SQL and how can
>I join these two views correctly?
>--------
> I also noticed that not all SQL activities are logged into V_$SQL. It
>seemed like some simple queries are skipped (mabey my ORADB is messed
>up??)
>I am aware of enabling/disabeling SQL trace on Sessions and using
>tkprof to formatthe output but, I just wanted to have a quick view of
>all activities without the a big hassel of formating and opening a
>Text document.
>Q2: Is V_$SQL a reliable source to view >ALL< SQL activities?
>
>Any comments appreciated
>
>Tom

V$session has the hash_value and the address for the *current* statement.

q2 yes, but you should never use the v_$ views *directly*

hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Jul 11 2002 - 12:15:52 CDT