Unique Columns For V$Sql

Hi,

Until yesterday I was saying SQL_ID and CHILD_NUMBER is enough to get a sql child cursor from V$SQL and today I know I was wrong.

Probably many times you needed the sql statement that a session is running. To do that you query V$session and find the session information and then use SQL_ID column to reach out V$Sql. if you write a query like this:

select * 
from v$session ses
       join v$sql sql on ses.sql_id = sql.sql_id and
--where username = :some_user -- you filter
;

select *

from v$session ses

join v$sql sql on ses.sql_id = sql.sql_id and

--where username = :some_user -- you filter

;

you can write any filter you want here but you will immediately realized that some session rows are multiplied. Why? Because you didn’t use “CHILD_NUMBER” while reaching v$sql. V$SQL is not main data source for unique sql statements in your database. V$SQLAREA is. So instead of v$sql, you can use v$sqlarea in this query but if you need specific information about the exact sql cursor that session is currently running then you must use v$sql.

Note: I will not go deep Parent-Child cursors here. basically, every sql statements has child cursors. they all same sql but for some reason they have different properties and your session is running a “child cursor”.

so your query should be like this:

select * 
from v$session ses
       join v$sql sql on ses.sql_id = sql.sql_id and
                         ses.sql_child_number = sql.child_number
--where username = :some_user -- you filter
;

select *

from v$session ses

join v$sql sql on ses.sql_id = sql.sql_id and

ses.sql_child_number = sql.child_number

--where username = :some_user -- you filter

;

and now you reached to exact correct sql child cursor that is running for that session. right? well, I did know so but I find out, it is not! On one of my clients system, I discovered that this query returns 2 rows from v$sql which means for same SQL_ID and CHILD_NUMBER there are 2 rows in v$sql. I couldn’t find anything on docs or google (maybe couldn’t search enough) and started to dig about this sql statements.

here is the necessary information about this sql:

select Address, child_address, hash_value, sql_id, child_number,PLAN_HASH_VALUE,FULL_PLAN_HASH_VALUE ,IS_ROLLING_INVALID, IS_ROLLING_REFRESH_INVALID
from v$sql
where sql_id = '6r317atx6ny3u' and child_number = 469;

ADDRESS          CHILD_ADDRESS    HASH_VALUE SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE I I
---------------- ---------------- ---------- ------------- ------------ --------------- -------------------- - -
00000001AAFF9FF0 00000000C3F40750 2053771386 6r317atx6ny3u          469      3635437843             11086287 N N
00000001AAD5C000 0000000139C4E8C0 2053771386 6r317atx6ny3u          469      3635437843             11086287 N N

select Address, child_address, hash_value, sql_id, child_number,PLAN_HASH_VALUE,FULL_PLAN_HASH_VALUE ,IS_ROLLING_INVALID, IS_ROLLING_REFRESH_INVALID

from v$sql

where sql_id = '6r317atx6ny3u' and child_number = 469;

ADDRESS CHILD_ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE I I

---------------- ---------------- ---------- ------------- ------------ --------------- -------------------- - -

00000001AAFF9FF0 00000000C3F40750 2053771386 6r317atx6ny3u 469 3635437843 11086287 N N

00000001AAD5C000 0000000139C4E8C0 2053771386 6r317atx6ny3u 469 3635437843 11086287 N N

almost everything is same but ADDRESS and CHILD_ADDRESS. CHILD_ADDRESS is the memory address that this cursor resides on so it must be different obviously. Only thing that create this difference is ADDRESS column. ADDRESS column in V$sql is the address of the PARENT cursor which is in V$SQLAREA view. So let’s check this view:

Select Address, Hash_Value, Sql_Id, Plan_Hash_Value,Full_Plan_Hash_Value
From V$sqlarea
Where Address In ('00000001AAFF9FF0', '00000001AAD5C000');

ADDRESS          HASH_VALUE SQL_ID        PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE
---------------- ---------- ------------- --------------- --------------------
00000001AAD5C000 2053771386 6r317atx6ny3u      3635437843             11086287

Select Address, Hash_Value, Sql_Id, Plan_Hash_Value,Full_Plan_Hash_Value

From V$sqlarea

Where Address In ('00000001AAFF9FF0', '00000001AAD5C000');

ADDRESS HASH_VALUE SQL_ID PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE

---------------- ---------- ------------- --------------- --------------------

00000001AAD5C000 2053771386 6r317atx6ny3u 3635437843 11086287

there is only one of them exists. So some of those child cursors in v$sql are not (I am not sure the term) “valid”. they might be used before (and they were in my example) but now they are remaining of a past sql. So which child cursor in v$sql is the sql statements that session is currently running? Now we need to use ADDRESS column too. SQL_ADDRESS column in v$session is the parent address of the sql statement. So, I should add it into my queries too:

select * 
from v$session ses
       join v$sql sql on ses.sql_id           = sql.sql_id and
                         ses.sql_child_number = sql.child_number and
                         ses.sql_Address      = sql.address
--where username = :some_user -- you filter
;

-- OR for the parent cursor only

select /*+ ordered */ * 
from v$session ses
       join v$sqlarea sql on ses.sql_id           = sql.sql_id and
                             ses.sql_Address      = sql.address
--where username = :some_user -- you filter
;

select *

from v$session ses

join v$sql sql on ses.sql_id = sql.sql_id and

ses.sql_child_number = sql.child_number and

ses.sql_Address = sql.address

--where username = :some_user -- you filter

;

-- OR for the parent cursor only

select /*+ ordered */ *

from v$session ses

join v$sqlarea sql on ses.sql_id = sql.sql_id and

ses.sql_Address = sql.address

--where username = :some_user -- you filter

;

thanks for reading. comments are welcome. wish you healthy happy days.

Leave a Reply Cancel reply