Joining V$SESSION and V$SQL in 11gR2

I’ve been using a little script joining V$SESSION with V$SQL to obtain an idea of what SQL is currently running in an instance. This has been working reasonably swiftly in various Oracle versions, but with upgrading to 11gR2, I’ve noticed that it has become painfully slow.

This is the trivial bit of SQL that is causing me the trouble:


SELECT /*+ gather_plan_statistics */ OSUSER, SERIAL#, SID, executions, sql.SQL_ID ,sql.child_number, SQL_TEXT
FROM V$SESSION sess JOIN V$SQL sql 
on  (sess.SQL_ADDRESS = sql.ADDRESS) 
where sess.STATUS = 'ACTIVE'

Plan hash value: 2709903618

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		 | Name 	     | Starts | E-Rows | A-Rows |   A-Time   |	OMem |	1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS		 |		     |	    1 |      1 |      1 |00:00:00.02 |	     |	     |		|
|*  2 |   HASH JOIN		 |		     |	    1 |      1 |      1 |00:00:06.00 |	 963K|	 963K|	578K (0)|
|*  3 |    FIXED TABLE FULL	 | X$KSUSE	     |	    1 |      1 |     22 |00:00:00.01 |	     |	     |		|
|*  4 |    FIXED TABLE FULL	 | X$KGLCURSOR_CHILD |	    1 |      1 |  56509 |00:00:00.79 |	     |	     |		|
|*  5 |   FIXED TABLE FIXED INDEX| X$KSLED (ind:2)   |	    1 |      1 |      1 |00:00:00.01 |	     |	     |		|
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("S"."KSUSESQL"="KGLHDPAR")
   3 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0
	      AND DECODE(BITAND("S"."KSUSEIDL",11),1,'ACTIVE',0,DECODE(BITAND("S"."KSUSEFLG",4096),0,'INACTIVE','CACHED'),2,'SN
	      IPED',3,'SNIPED','KILLED')='ACTIVE'))
   4 - filter("INST_ID"=USERENV('INSTANCE'))
   5 - filter("S"."KSUSEOPC"="E"."INDX")


27 rows selected.

Elapsed: 00:00:00.58

On 10.2.0.4 this query returns in considerably under a second. I’ve seen this query taking 18 seconds on a fairly quiet 11gR2 instance.

Lets look at the plan that 11gR2 uses for this:


SELECT /*+ gather_plan_statistics */ OSUSER, SERIAL#, SID, executions, sql.SQL_ID ,sql.child_number, sql_text 
FROM V$SESSION sess JOIN V$SQL
sql on	(sess.SQL_ADDRESS = sql.ADDRESS) 
where sess.STATUS = 'ACTIVE'

Plan hash value: 3784909302

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		  | Name	      | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	  |		      |      1 |	|      1 |00:00:00.01 |       |       | 	 |
|   1 |  NESTED LOOPS		  |		      |      1 |      1 |      1 |00:00:00.01 |       |       | 	 |
|   2 |   NESTED LOOPS		  |		      |      1 |      1 |      1 |00:00:00.16 |       |       | 	 |
|   3 |    MERGE JOIN CARTESIAN   |		      |      1 |     32 |  14368 |00:00:00.55 |       |       | 	 |
|*  4 |     FIXED TABLE FULL	  | X$KGLCURSOR_CHILD |      1 |      1 |    449 |00:00:00.01 |       |       | 	 |
|   5 |     BUFFER SORT 	  |		      |    449 |     32 |  14368 |00:00:00.03 |  2048 |  2048 | 2048  (0)|
|   6 |      FIXED TABLE FULL	  | X$KSLWT	      |      1 |     32 |     32 |00:00:00.01 |       |       | 	 |
|*  7 |    FIXED TABLE FIXED INDEX| X$KSUSE (ind:1)   |  14368 |      1 |      1 |00:00:00.09 |       |       | 	 |
|*  8 |   FIXED TABLE FIXED INDEX | X$KSLED (ind:2)   |      1 |      1 |      1 |00:00:00.01 |       |       | 	 |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("INST_ID"=USERENV('INSTANCE'))
   7 - filter((BITAND("S"."KSUSEFLG",1)<>0 AND DECODE(BITAND("S"."KSUSEIDL",11),1,'ACTIVE',0,DECODE(BITAND("S"."KS
	      USEFLG",4096),0,'INACTIVE','CACHED'),2,'SNIPED',3,'SNIPED','KILLED')='ACTIVE' AND BITAND("S"."KSSPAFLG",1)<>0 AND
	      "S"."INST_ID"=USERENV('INSTANCE') AND "S"."KSUSESQL"="KGLHDPAR" AND "S"."INDX"="W"."KSLWTSID"))
   8 - filter("W"."KSLWTEVT"="E"."INDX")


31 rows selected.

Tried running DBMS_STATS.GATHER_FIXED_OBJECTS_STATS, tried GATHER_DICTIONARY_STATS. All to no avail.

Thankfully there is more than one way to join V$SESSION to V$SQL. It seems that joining on SQL_ID is much more efficient.


SELECT /*+ gather_plan_statistics */ OSUSER, SERIAL#, SID, executions,
sql.SQL_ID ,sql.child_number, SQL_TEXT 
FROM V$SESSION sess JOIN V$SQL sql 
on	(sess.SQL_ID = sql.SQL_ID) 
where sess.STATUS = 'ACTIVE'

Plan hash value: 1351996005

--------------------------------------------------------------------------------------------------------
| Id  | Operation		   | Name		       | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	   |			       |      1 |	 |     28 |00:00:00.01 |
|   1 |  NESTED LOOPS		   |			       |      1 |      1 |     28 |00:00:00.01 |
|   2 |   NESTED LOOPS		   |			       |      1 |      1 |     56 |00:00:00.01 |
|   3 |    NESTED LOOPS 	   |			       |      1 |      1 |     56 |00:00:00.01 |
|*  4 |     FIXED TABLE FULL	   | X$KSUSE		       |      1 |      1 |     56 |00:00:00.01 |
|*  5 |     FIXED TABLE FIXED INDEX| X$KSLWT (ind:1)	       |     56 |      1 |     56 |00:00:00.01 |
|*  6 |    FIXED TABLE FIXED INDEX | X$KSLED (ind:2)	       |     56 |      1 |     56 |00:00:00.01 |
|*  7 |   FIXED TABLE FIXED INDEX  | X$KGLCURSOR_CHILD (ind:2) |     56 |      1 |     28 |00:00:00.01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
	      BITAND("S"."KSUSEFLG",1)<>0 AND DECODE(BITAND("S"."KSUSEIDL",11),1,'ACTIVE',0,DECODE(BITAND("S".
	      "KSUSEFLG",4096),0,'INACTIVE','CACHED'),2,'SNIPED',3,'SNIPED','KILLED')='ACTIVE'))
   5 - filter("S"."INDX"="W"."KSLWTSID")
   6 - filter("W"."KSLWTEVT"="E"."INDX")
   7 - filter(("INST_ID"=USERENV('INSTANCE') AND "S"."KSUSESQI"="KGLOBT03"))


31 rows selected.


I think the moral of this post is that things change, and what worked well in the past on previous versions, is not guaranteed to work well in the future!

Advertisements

7 thoughts on “Joining V$SESSION and V$SQL in 11gR2

  1. Hi Jason

    It looks to me as though some of your code samples are a bit mangled (I’ve done that myself in the past)In particular your 10gR2 plan seems to come from the (better) sql_id join.

  2. Hi Niall,

    Thanks for spotting that. Does not give the reader confidence in the veracity of the information presented!

    Updated with the *correct* 10gR2 plan.

    jason.

  3. Hi, Jeil.

    I believe that this is not a version problem. It’s just that your query is a bit flawed.

    V$SQL represents child cursor. So you need to append CHILD_NUMBER condition(and HASH_VALUE) to your query.

    SELECT /*+ gather_plan_statistics */ OSUSER, SERIAL#, SID, executions, sql.SQL_ID ,sql.child_number, SQL_TEXT
    FROM V$SESSION sess JOIN V$SQL sql
    on  (sess.SQL_ADDRESS = sql.ADDRESS and sess.SQL_HASH_VALUE = sql.HASH_VALUE and sess.SQL_CHILD_NUMBER = sql.CHILD_NUMBER)
    where sess.status = 'ACTIVE';
    

    If your request is extracting SQL text, the V$SQLAREA view joined with ADDRESS and HASH_VALUE would suffice your need.

    • He Dion,

      I have reviewed this thread and finally this is the query for Oracle 11g. It is running fine Oracle 11g R2 RAC Database.

      SELECT /*+ gather_plan_statistics */ ss.sid, ss.SERIAL#, sql.sql_id, ss.OSUSER, ss.username, sql.sql_text as “Running SQL Query”, sql.executions,sql.child_number
      FROM V$SESSION ss, V$SQL sql, v$sqlarea sa
      where (ss.SQL_ADDRESS = sql.ADDRESS
      and ss.SQL_HASH_VALUE = sql.HASH_VALUE
      and ss.SQL_CHILD_NUMBER = sql.CHILD_NUMBER)
      AND ss.sql_hash_value = sa.hash_value
      and ss.sql_address = sa.address
      and sa.sql_id = sql.sql_id
      ORDER BY 1;


  4. col sid format 999999
    col serial# format 999999
    col spid format 999999
    col username format a12
    col terminal format a15
    col machine format a24
    col program format a32
    col sql_text format a120
    select /*+use_nl(a,b,c) ordered*/distinct a.sid,a.serial#,a.username,a.terminal,a.machine,a.program,b.spid,c.hash_value,c.sql_text
    from v$session a,v$process b,v$sql c
    where a.paddr = b.addr(+)
    and a.sql_hash_value = c.hash_value
    and a.sql_address = c.address
    and a.audsid userenv('SESSIONID')
    and a.status = 'ACTIVE'
    and a.type = 'USER'
    /

    This is my Oracle 9i Version.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s