JL Computer Consultancy

Lost SQL in v$sql / v$session

July 2002


Update: 7th Sept 1999 - For the record, this note does not apply to Oracle 8.1.5, where all the columns listed are externalised as unsigned bytes.

If you have used any of the typical tools for tracking down the SQL statement currently being run by a user (e.g. by joining v$session to v$sql on the sql_hash_value column), you will have come across the odd fact that from time to time the SQL seems to be missing.

The reason for this is a quirky little feature (bug) relating to 32-bit arithmetic, and the translation from internal Oracle data types (in X$ objects) to external data types (in v$ objects); some of the v$ objects export the sql hash value as a signed 32 bit integer, and others export it as an unsigned 32 bit integer. Consequently, when your cunning little statement tries to link two different v$ objects by sql hash value, the join fails, and statements disappear.

The following table lists the relevant v$ objects, columns, and nature of the column

v$sql

hash_value

unsigned

v$sqlarea

hash_value

unsigned

v$object_dependency

to_hash

unsigned

v$object_dependency

from_hash

unsigned

 

 

 

v$session

sql_hash_value

signed

v$session

prev_hash_value

signed

v$sqltext

hash_value

signed

v$sqltext_with_newlines

hash_value

signed

v$open_cursor

hash_value

signed

v$sort_usage

sqlhash

signed

From this you can see that a join from v$session to v$sqltext or v$open_cursor will always work correctly; however, if the value of sql_hash_value in v$session goes negative, then a join from v$session to v$sql or v$sqlarea will fail since the matching row(s) in those views will have positive hash values somewhere between 2^31 (2,147,483,648) and 2^32 (4,294,967,296).

You could, of course, join on the sql_address/address columns, but (see my notes on the performance threat from using queries against the v$ views too casually) you should always avoid code that fails to use the 'internal indexes' on the x$ objects.

The workaround is at first sight quite simple: where an unsigned column (in the range 0 to power(2,32)) has to be joined to a signed column, then rewrite the code to add/subtract an optional power(2,32), along the lines of:

select
        username,
        sql_text 
from 
        v$session      ses,
        v$sql          sql
where
        sql.hash_value = ses.sql_hash_value + decode(sign(ses.sql_hash_value),-1,power(2,32),0)
and     sql.address = ses.sql_address
and     sid = 10
;

Unfortunately, even this fix-up is not sufficient. Our target execution plan is to index into v$session (x$ksuse), and then index into v$sql (x$kglob), however the simple addition of the decode results in a full scan of x$glob. Consequently the only option is to fall back to a multiple step process (e.g. select from v$session using new_value, select from v$sql using memory variable) as outlined in my notes on tracking down the code being run by the most resource-hungry user.

Warning: As I reached the end of this article, I realised that the note I had published (Guilty Party) about tracking down resource intensive SQL starting from the Unix process was incomplete as it did not cater for this 32-bit problem. I will be correcting the code in the next few weeks.

Back to Main Index of Topics