Trouble locating current SQL in v$sqlarea for a session
Mark Powell
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.
Andrew Protasov
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
Jonathan Lewis
If you look at the 'indexed X$ columns' view, I think you will find
that there is a pseudo-index on the hash-value of v$sqlarea,
but not on the address column. This would affect the performance
if the Shared pool is large.
PS 'Indexed' x$ columns appeared fairly late, ca. 7.3
Andrew Protasov <ora...@protasov.kiev.ua> wrote in article
<AG6Bd...@protasov.kiev.ua>...