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

hallo,

-snip
> 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?

-snip

i have also found this (writing my own monitor). as far as i know there is a wrap arround in the address. don't know if this is true for 9.x.

hth.

s.kapitza

t.alexander_at_ads-tec.de (Tom) wrote in message news:<697dbfee.0207110028.2d0a23a1_at_posting.google.com>...
> 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
Received on Fri Jul 12 2002 - 17:33:42 CDT