V$Session and V$SQL revisited...

From: Tom <t.alexander_at_ads-tec.de>
Date: 11 Jul 2002 01:28:55 -0700
Message-ID: <697dbfee.0207110028.2d0a23a1@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 Thu Jul 11 2002 - 03:28:55 CDT