Re: Trouble locating current SQL in v$sqlarea for a session

From: Andrew Protasov <oracle_at_protasov.kiev.ua>
Date: Mon, 30 Nov 98 12:26:14 +0200
Message-ID: <AG6BdOsK42@protasov.kiev.ua>

Hi,

Why do you need to join on sql_hash_value? I have similar query and it works fine without join on this column:

select
 distinct

  o.sql_text              "SQL text"
 ,s.username              "User name"
 ,s.schemaname            "Schema name"
 ,s.lockwait              "Lock wait"
 ,s.status                "Status"
 ,s.sid                   "Session ID"
 ,s.serial#               "Serial N"
 ,s.server                "Server"
 ,s.osuser                "OS user"
 ,s.machine               "Machine"
 ,s.terminal              "Terminal"
 ,s.program               "Program"

from sys.v_$session s

    ,sys.v_$sql o
where s.username is not null
  and s.sql_address=o.address(+)
;

Andrew Protasov

> Back in 1995 I wrote a couple of queries that would show me the current SQL
> being executed by a particular session. Whenever the status in v$session
> was 'ACTIVE' I could use these scripts to see what the session was doing.
> These scripts worked pretty well in 7.1.3, 7.1.6, and 7.2.3, but recently
> I noticed that the queries would sometimes return 'no rows' for sessions
> with a status of 'ACTIVE' under version 7.3.3.5. I have since determined
> that the v$session sql_hash_value will be negative when this occurs.
>
> Who out there knows how to handle this so that the query will once again
> work all the time? Here is one of the queries, the other uses v$sqltext.
> I haven't had much call to use these scripts recently, but when I noticed
> the problem I started to look into it, but I have a little too much to do
> right now and I haven't tried to look for an underlying x$ table.
>
> set echo off
> rem
> rem filename: sql_user.sql
> rem SQL*Plus script to display the sql being executed for a particular
> rem Oracle session.
> rem
> rem 11/27/95 s3527 m d powell new script
> rem
> set verify off
> select sid, username, command, lockwait, status,
> osuser, sql_text
> from v$session s, v$sqlarea q
> where s.sql_hash_value = q.hash_value
> and s.sql_address = q.address
> and s.sid = &usrsid
> /
>
> I have verified that the sql_hash_value goes negative sometimes under
> Oracle version 7.3.3.5 on both Pyramid Dcosx and Sequent Dynix machines and
> when it does the queries do not find the SQL.
>
>
>
Received on Mon Nov 30 1998 - 04:26:14 CST