Oracle Scratchpad

October 11, 2019

v$session

Filed under: Execution plans,Infrastructure,Oracle,Performance — Jonathan Lewis @ 12:29 pm BST Oct 11,2019

Here’s an odd, and unpleasant, detail about querying v$session in the “most obvious” way. (And if you were wondering what made me resurrect and complete a draft on “my session id” a couple of days ago, this posting is the reason). Specifically if you want to select some information for your own session from v$session the query you’re likely to use in any recent version of Oracle will probably be of the form:


select {list for columns} from v$session where sid = to_number(sys_context('userenv','sid'));

Unfortunately that one little statement hides two anomalies – which you can see in the execution plan. Here’s a demonstration cut from an SQL*Plus session running under 19.3.0.0:


SQL> select * from table(dbms_xplan.display_cursor);

SQL_ID  gcfrzq9knynj3, child number 0
-------------------------------------
select program from V$session where sid = sys_context('userenv','sid')

Plan hash value: 2422122865

----------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |       |       |     1 (100)|
|   1 |  MERGE JOIN CARTESIAN     |                 |     1 |    33 |     0   (0)|
|   2 |   NESTED LOOPS            |                 |     1 |    12 |     0   (0)|
|*  3 |    FIXED TABLE FULL       | X$KSLWT         |     1 |     8 |     0   (0)|
|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |     4 |     0   (0)|
|   5 |   BUFFER SORT             |                 |     1 |    21 |     0   (0)|
|*  6 |    FIXED TABLE FULL       | X$KSUSE         |     1 |    21 |     0   (0)|
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------ -------------------------------------------
   3 - filter("W"."KSLWTSID"=TO_NUMBER(SYS_CONTEXT('userenv','sid')))
   4 - filter("W"."KSLWTEVT"="E"."INDX")
   6 - filter((BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND 
              "S"."INDX"=TO_NUMBER(SYS_CONTEXT('userenv','sid'))
              AND INTERNAL_FUNCTION("S"."CON_ID") AND "S"."INST_ID"=USERENV('INSTANCE')))

As you can see, v$session is a join of 3 separate structures – x$kslwt (v$session_wait), x$ksled (v$event_name), and x$ksuse (the original v$session as it was some time around 8i), and the plan shows two “full tablescans” and a Cartesian merge join. Tablescans and Cartesian merge joins are not necessarily bad – especially where small tables and tiny numbers of rows are concerned – but they do merit at least a brief glance.

x$ksuse is a C structure in the fixed SGA and that structure is a segmented array (which seems to be chunks of 126 entries in 19.3, and chunks of 209 entries in 12.2 – but that’s fairly irrelevant). The SID is simply the index into the array counting from 1, so if you have a query with a predicate like ‘SID = 99’ Oracle can work out the address of the 99th entry in the array and access it very quickly – which is why the SID column is reported as a “fixed index” column in the view v$indexed_fixed_column.

But we have two problems immediately visible:

  1. the optimizer is not using the “index” to access x$ksuse despite the fact that we’re giving it exactly the value we want to use (and we can see a suitable predicate at operation 6 in the plan)
  2. the optimizer has decided to start executing the query at the x$kslwt table

Before looking at why thing’s have gone wrong, let’s check the execution plan to see what would have happened if we’d copied the value from the sys_context() call into a bind variable and queried using the bind variable – which we’ll keep as a character type to make it a fair comparison:

SQL_ID  cm3ub1tctpdyt, child number 0
-------------------------------------
select program from v$session where sid = to_number(:v1)

Plan hash value: 1627146547

----------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |       |       |     1 (100)|
|   1 |  MERGE JOIN CARTESIAN     |                 |     1 |    32 |     0   (0)|
|   2 |   NESTED LOOPS            |                 |     1 |    12 |     0   (0)|
|*  3 |    FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) |     1 |     8 |     0   (0)|
|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |     4 |     0   (0)|
|   5 |   BUFFER SORT             |                 |     1 |    20 |     0   (0)|
|*  6 |    FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |     1 |    20 |     0   (0)|
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("W"."KSLWTSID"=TO_NUMBER(:V1))
   4 - filter("W"."KSLWTEVT"="E"."INDX")
   6 - filter(("S"."INDX"=TO_NUMBER(:V1) AND BITAND("S"."KSSPAFLG",1)<>0
              AND BITAND("S"."KSUSEFLG",1)<>0 AND INTERNAL_FUNCTION("S"."CON_ID") AND
              "S"."INST_ID"=USERENV('INSTANCE')))


When we have a (character) bind variable instead of a sys_context() value the optimizer manages to use the “fixed indexes”., but it’s still started executing at x$kslwt, and still doing a Cartesian merge join. The plan would be the same if the bind variable were a numeric type, and we’d still get the same plan if we replaced the bind variable with a literal number.

So problem number 1 is that Oracle only seems able to use the fixed index path for literal values and simple bind variables (plus a few “simple” functions). It doesn’t seem to use the fixed indexes for most functions (even deterministic ones) returning a value and the sys_context() function is a particular example of this.

Transitivity

Problem number 2 comes from a side-effect of something that I first described about 13 years ago – transitive closure. Take a look at the predicates in both the execution plans above. Where’s the join condition between x$ksuse and x$kslwt ? There should be one, because the underlying SQL defining [g]v$session  has the following joins:

from
      x$ksuse s,
      x$ksled e,
      x$kslwt w
where
      bitand(s.ksspaflg,1)!=0
and   bitand(s.ksuseflg,1)!=0
and   s.indx=w.kslwtsid       -- this is the SID column for v$session and v$session_wait
and   w.kslwtevt=e.indx
 

What’s happened here is that the optimizer has used transitive closure: “if a = b and b = c then a = c” to clone the predicate “s.indx = to_number(sys_context(…))” to “w.kslwtsid = to_number(sys_context(…))”. But at the same time the optmizer has eliminated the predicate “s.indx = w.kslwtsid”, which it shouldn’t do because this is 12.2.0.1 and ever since 10g we’ve had the parameter _optimizer_transitivity_retain = true — but SYS is ignoring the parameter setting.

So we no longer have a join condition between x$ksuse and x$kslwt – which means there has to be a cartesian merge join between them and the only question is whether this should take place before or after the join between x$kslwt and x$ksled. In fact, the order doesn’t really matter because there will be only one row identified in x$kslwt and one row in x$ksuse, and the join to x$ksled is simply a lookup (by undeclarable unique key) to translate an id into a name and it will take place only once whatever we do about the other two structures.

But there is a catch – especially if your sessions parameter is 25,000 (which it shouldn’t be) and the number of connected sessions is currently 20,000 (which it shouldn’t be) – the predicate against x$ksuse does a huge amount of work as it walks the entire array testing every row (and it doesn’t even do the indx test first – it does a couple of bitand() operations). Even then this wouldn’t be a disaster – we’re only talking a couple of hundredths of a second of CPU – until you find the applications that run this query a huge number of times.

We would prefer to avoid two full tablescans since the arrays could be quite large, and of the two it’s the tablescan of x$ksuse that is going to be the greater threat; so is there a way to bypass the threat?  Once we’ve identified the optimizer anomaly we’ve got a pointer to a solution. Transitivity is going wrong, so let’s attack the transitivity. Checking the hidden parameters we can find a parameter: _optimizer_generate_transitive_pred which defaults to true, so let’s set it to false for the query and check the plan:

select  /*+   opt_param('_optimizer_generate_transitive_pred','FALSE')
*/  program from  v$session where  sid = sys_context('userenv','sid')

Plan hash value: 3425234845

----------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |       |       |     1 (100)|
|   1 |  NESTED LOOPS             |                 |     1 |    32 |     0   (0)|
|   2 |   NESTED LOOPS            |                 |     1 |    28 |     0   (0)|
|   3 |    FIXED TABLE FULL       | X$KSLWT         |    47 |   376 |     0   (0)|
|*  4 |    FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |     1 |    20 |     0   (0)|
|*  5 |   FIXED TABLE FIXED INDEX | X$KSLED (ind:2) |     1 |     4 |     0   (0)|
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(("S"."INDX"="W"."KSLWTSID" AND BITAND("S"."KSSPAFLG",1)<>0
              AND BITAND("S"."KSUSEFLG",1)<>0 AND "S"."INDX"=TO_NUMBER(SYS_CONTEXT('user
              env','sid')) AND INTERNAL_FUNCTION("S"."CON_ID") AND
              "S"."INST_ID"=USERENV('INSTANCE')))
   5 - filter("W"."KSLWTEVT"="E"."INDX")


Although it’s not nice to insert hidden parameters into the optimizer activity we do have a result. We don’t have any filtering on x$kslwt – fortunately this seems to be limited in size (but see footnote) to the number of current sessions (unlike x$ksuse which has an array size defined by the sessions parameter or derived from the processes parameter). For each row in x$kslwt we do an access into x$ksuse using the “index” (note that we don’t see access predicates for the fixed tables, we just have to note the operation says FIXED INDEX and spot the “index-related” predicate in the filter predicate list), so this strategy has reduced the number of times we check the complex predicate on x$ksuse rows.

It’s still far from ideal, though. What we’d really like to do is access x$kslwt by index using the known value from sys_context(‘userenv’,’sid’). As it stands the path we get from using a hidden parameter which isn’t listed as legal for the opt_param() hint is a plan that we would get if we used an unhinted query that searched for audsid = sys_context(‘userenv’,’sessionid’).

SQL_ID  7f3f9b9f32u7z, child number 0
-------------------------------------
select  program from  v$session where  audsid =
sys_context('userenv','sessionid')

Plan hash value: 3425234845

----------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |       |       |     1 (100)|
|   1 |  NESTED LOOPS             |                 |     2 |    70 |     0   (0)|
|   2 |   NESTED LOOPS            |                 |     2 |    62 |     0   (0)|
|   3 |    FIXED TABLE FULL       | X$KSLWT         |    47 |   376 |     0   (0)|
|*  4 |    FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |     1 |    23 |     0   (0)|
|*  5 |   FIXED TABLE FIXED INDEX | X$KSLED (ind:2) |     1 |     4 |     0   (0)|
----------------------------------------------------------------------------------

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

   4 - filter(("S"."INDX"="W"."KSLWTSID" AND BITAND("S"."KSSPAFLG",1)<>0
              AND BITAND("S"."KSUSEFLG",1)<>0 AND "S"."KSUUDSES"=TO_NUMBER(SYS_CONTEXT('
              userenv','sessionid')) AND INTERNAL_FUNCTION("S"."CON_ID") AND
              "S"."INST_ID"=USERENV('INSTANCE')))
   5 - filter("W"."KSLWTEVT"="E"."INDX")


The bottom line, then, seems to be that if you need a query by SID against v$session to be as efficient as possible then your best bet is to load a numeric variable with the sys_context(‘userenv’,’sid’) and then select where “sid = :bindvariable”.  Otherwise query by audsid, or use a hidden parameter to affect the optimizer.

Until the the SYS schema follows the _optimizer_transitivity_retain parameter or treats sys_context() the same way it treats a bind variable there is always going to be some unnecessary work when querying v$session and that excess will grow with either the number of connected sessions (if you optimize the query) or with the value of the sessions parameter.

Footnote

In (much) older versions of Oracle v$session_wait sat on top of x$ksusecst, which was part of the same C structure as x$ksuse. In newer versions of Oracle x$kslwt is a structure that is created on demand in the PGA/UGA – I hope that there’s a short cut that allows Oracle to find the waiting elements in x$ksuse[cst] efficiently, rather than requiring a walk through the whole thing, otherwise a tablescan of the (nominally smaller) x$kslwt structure will be at least as expensive as a tablescan of the x$ksuse structure.

Update (just a few minutes after posting)

Bob Bryla has pointed out in a tweet that there are many “bugs” not fixed until 19.1 for which the workaround is to set “_optimizer_transitivity_retain” to false. So maybe this isn’t an example of SYS doing something particularly strange – it may be part of a general reworking of the mechanism that still has a couple of undesirable side effects.

Bob’s comment prompted me to clone the x$ tables into real tables in a non-SYS schema and model the fixed indexes with primary keys, and I found that the resulting plan (though very efficient) still discarded the join predicate. So we may be seeing the side effects of a code enhancement relating to generating predicates that produce unique key access paths. (A “contrary” test, the one in the 2013 article I linked to, still retains the join predicate for the query that has non-unique indexes.)

 

4 Comments »

  1. Thanks Jonathan, I had never really thought about this.

    It may be a case as well where a legacy function may be useful. The following are from a 19.3 2-node RAC system.

    The first query results in exactly the same behavior you reported. The second is somewhat better.

    
    SQL# select * from table(dbms_xplan.display_cursor);
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  51ntuaqnfc07v, child number 0
    -------------------------------------
    select sid, serial# from v$session where sid =
    to_number(sys_context('userenv','sid'))
    
    Plan hash value: 2422122865
    
    ----------------------------------------------------------------------------------
    | Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)|
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT          |                 |       |       |     1 (100)|
    |   1 |  MERGE JOIN CARTESIAN     |                 |     1 |    31 |     0   (0)|
    |   2 |   NESTED LOOPS            |                 |     1 |    12 |     0   (0)|
    |*  3 |    FIXED TABLE FULL       | X$KSLWT         |     1 |     8 |     0   (0)|
    |*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |     4 |     0   (0)|
    |   5 |   BUFFER SORT             |                 |     1 |    19 |     0   (0)|
    |*  6 |    FIXED TABLE FULL       | X$KSUSE         |     1 |    19 |     0   (0)|
    ----------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
       3 - filter("W"."KSLWTSID"=TO_NUMBER(SYS_CONTEXT('userenv','sid')))
       4 - filter("W"."KSLWTEVT"="E"."INDX")
       6 - filter((BITAND("S"."KSUSEFLG",1)0 AND
                  BITAND("S"."KSSPAFLG",1)0 AND "S"."INDX"=TO_NUMBER(SYS_CONTEXT('userenv'
                  ,'sid')) AND INTERNAL_FUNCTION("S"."CON_ID") AND
                  "S"."INST_ID"=USERENV('INSTANCE')))
    
    
    29 rows selected.
    
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  4kxccmb0724b0, child number 0
    -------------------------------------
    select sid, serial# from v$session where audsid = userenv('SESSIONID')
    
    Plan hash value: 3425234845
    
    ----------------------------------------------------------------------------------
    | Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)|
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT          |                 |       |       |     1 (100)|
    |   1 |  NESTED LOOPS             |                 |     6 |   204 |     0   (0)|
    |   2 |   NESTED LOOPS            |                 |     6 |   180 |     0   (0)|
    |   3 |    FIXED TABLE FULL       | X$KSLWT         |    78 |   624 |     0   (0)|
    |*  4 |    FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |     1 |    22 |     0   (0)|
    |*  5 |   FIXED TABLE FIXED INDEX | X$KSLED (ind:2) |     1 |     4 |     0   (0)|
    ----------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - filter(("S"."INDX"="W"."KSLWTSID" AND BITAND("S"."KSUSEFLG",1)0
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  AND BITAND("S"."KSSPAFLG",1)0 AND "S"."KSUUDSES"=USERENV('SESSIONID')
                  AND INTERNAL_FUNCTION("S"."CON_ID") AND
                  "S"."INST_ID"=USERENV('INSTANCE')))
       5 - filter("W"."KSLWTEVT"="E"."INDX")
    
    
    26 rows selected.
    
    
    

    Comment by jkstill — October 11, 2019 @ 5:00 pm BST Oct 11,2019 | Reply

    • The second plan matches the ‘better but less than ideal” one from the article. I just tried userenv(‘SESSIONID’) on a whim, as I have some scripts that still use it with v$session.audsid.

      Comment by jkstill — October 11, 2019 @ 5:19 pm BST Oct 11,2019 | Reply

  2. To me the usage of a fixed index (“FIXED TABLE FIXED INDEX”) seems to be restricted. It can be used when the predicate contains literals, bind variables, join conditions, or simple built-in SQL functions whose results are known at parse time. But it can not be used when the predicate contains a complex custom or built-in function (such as SYS_CONTEXT) or a subquery. (Based on a few quick and not thourough tests.)

    Example:

    SQL> set feedback off
    SQL> set sqlp ''
    
    explain plan for select * from X$KSLWT where KSLWTSID = SQRT(10000);
    select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 3210902170
    
    -------------------------------------------------------------------------------------------
    | Id  | Operation               | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |                 |     1 |   125 |     0   (0)| 00:00:01 |
    |*  1 |  FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) |     1 |   125 |     0   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("KSLWTSID"=100)

    The index was used for the fixed table.
    On a regular table, the database should be able to use the index on KSLWTSID in the below cases as well, but:

    explain plan for select * from X$KSLWT where KSLWTSID = (select 100 from dual);
    select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------
    Plan hash value: 3544907017
    
    ----------------------------------------------------------------------------
    | Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |         |     1 |   125 |     2   (0)| 00:00:01 |
    |*  1 |  FIXED TABLE FULL| X$KSLWT |     1 |   125 |     0   (0)| 00:00:01 |
    |   2 |   FAST DUAL      |         |     1 |       |     2   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("KSLWTSID"= (SELECT 100 FROM "DUAL" "DUAL"))
    
    explain plan for select * from X$KSLWT where KSLWTSID = sys_context('userenv','sid');
    select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------
    Plan hash value: 3649698209
    
    ----------------------------------------------------------------------------
    | Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |         |     1 |   125 |     0   (0)| 00:00:01 |
    |*  1 |  FIXED TABLE FULL| X$KSLWT |     1 |   125 |     0   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("KSLWTSID"=TO_NUMBER(SYS_CONTEXT('userenv','sid')))

    The index was not used for those simple queries.

    However, if the SID comes from a real table through a join, the database does exactly what I would expect from it:

    drop table t1 purge;
    create table t1 (sid number);
    insert into t1 values (sys_context('userenv', 'sid'));
    commit;
    exec dbms_stats.gather_table_stats(user, 'T1');
    
    explain plan for select program from v$session s join t1 on (s.sid = t1.sid);
    select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    Plan hash value: 4100545782
    
    ----------------------------------------------------------------------------------------------
    | Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT           |                 |     1 |    37 |     2   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS              |                 |     1 |    37 |     2   (0)| 00:00:01 |
    |   2 |   NESTED LOOPS             |                 |     1 |    33 |     2   (0)| 00:00:01 |
    |   3 |    NESTED LOOPS            |                 |     1 |    25 |     2   (0)| 00:00:01 |
    |   4 |     TABLE ACCESS FULL      | T1              |     1 |     4 |     2   (0)| 00:00:01 |
    |*  5 |     FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |     1 |    21 |     0   (0)| 00:00:01 |
    |*  6 |    FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) |     1 |     8 |     0   (0)| 00:00:01 |
    |*  7 |   FIXED TABLE FIXED INDEX  | X$KSLED (ind:2) |     1 |     4 |     0   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - filter("S"."INDX"="T1"."SID" AND BITAND("S"."KSUSEFLG",1)<>0 AND
                  BITAND("S"."KSSPAFLG",1)<>0 AND "S"."INST_ID"=USERENV('INSTANCE'))
       6 - filter("S"."INDX"="W"."KSLWTSID")
       7 - filter("W"."KSLWTEVT"="E"."INDX")

    Finally, without creating a table:

    explain plan for
    select s.program from v$session s
    join (select /*+ no_merge */ to_number(sys_context('userenv', 'sid')) as sid from dual) d
    on (s.sid = d.sid);
    
    select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    Plan hash value: 420208790
    
    ----------------------------------------------------------------------------------------------
    | Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT           |                 |     1 |    46 |     2   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS              |                 |     1 |    46 |     2   (0)| 00:00:01 |
    |   2 |   NESTED LOOPS             |                 |     1 |    42 |     2   (0)| 00:00:01 |
    |   3 |    NESTED LOOPS            |                 |     1 |    34 |     2   (0)| 00:00:01 |
    |   4 |     VIEW                   |                 |     1 |    13 |     2   (0)| 00:00:01 |
    |   5 |      FAST DUAL             |                 |     1 |       |     2   (0)| 00:00:01 |
    |*  6 |     FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |     1 |    21 |     0   (0)| 00:00:01 |
    |*  7 |    FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) |     1 |     8 |     0   (0)| 00:00:01 |
    |*  8 |   FIXED TABLE FIXED INDEX  | X$KSLED (ind:2) |     1 |     4 |     0   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       6 - filter("S"."INDX"="D"."SID" AND BITAND("S"."KSUSEFLG",1)<>0 AND
                  BITAND("S"."KSSPAFLG",1)<>0 AND "S"."INST_ID"=USERENV('INSTANCE'))
       7 - filter("S"."INDX"="W"."KSLWTSID")
       8 - filter("W"."KSLWTEVT"="E"."INDX")

    And the difference:

    select sql_text, executions, elapsed_time, elapsed_time/executions from v$sql where sql_id in ('bj4mh30jvy38h', '9cy4sh7h3d5dd');
    
    SQL_TEXT                                                                         EXECUTIONS ELAPSED_TIME ELAPSED_TIME/EXECUTIONS
    -------------------------------------------------------------------------------- ---------- ------------ -----------------------
    select s.program from v$session s join (select /*+ no_merge */ to_number(sys_con         50        15417                  308.34
    text('userenv', 'sid')) as sid from dual) d on (s.sid = d.sid)
    
    select program from v$session where sid = sys_context('userenv','sid')                   50      1003711                20074.22

    Comment by Balazs Papp — October 12, 2019 @ 12:46 am BST Oct 12,2019 | Reply

    • Balazs,

      Thanks for the comment.

      The join strategy is an excellent workaround for anyone who needs a pure SQL solution and doesn’t have scope to “pre-acquire” the sys_context value.

      The strategy also addresses another optimization anomaly of the dynamic performance views – IN-list queries result in full tablescans.

      Using v$sql as a demo to avoid the complication of the 3-table join in v$session:

      
      select sql_text from v$sql where sql_id in ('3rju5whzbh017','2z0udr4rc402m');
      
      --------------------------------------------------------------------------------------
      | Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT |                   |     1 |   536 |     0   (0)| 00:00:01 |
      |*  1 |  FIXED TABLE FULL| X$KGLCURSOR_CHILD |     1 |   536 |     0   (0)| 00:00:01 |
      --------------------------------------------------------------------------------------
      
      
      select sql_text from v$sql where sql_id = '2z0udr4rc402m';
      
      -----------------------------------------------------------------------------------------------------
      | Id  | Operation               | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
      -----------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT        |                           |     1 |   536 |     0   (0)| 00:00:01 |
      |*  1 |  FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD (ind:2) |     1 |   536 |     0   (0)| 00:00:01 |
      -----------------------------------------------------------------------------------------------------
      
      
      select  sql_text
      from    v$sql sq,
              (
               select '2z0udr4rc402m' sql_id from dual
               union all
               select '3rju5whzbh017' from dual
              ) v
      where   sq.sql_id = v.sql_id
      /
      
      ------------------------------------------------------------------------------------------------------
      | Id  | Operation                | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
      ------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT         |                           |     1 |   551 |     4   (0)| 00:00:01 |
      |   1 |  NESTED LOOPS            |                           |     1 |   551 |     4   (0)| 00:00:01 |
      |   2 |   VIEW                   |                           |     2 |    30 |     4   (0)| 00:00:01 |
      |   3 |    UNION-ALL             |                           |       |       |            |          |
      |   4 |     FAST DUAL            |                           |     1 |       |     2   (0)| 00:00:01 |
      |   5 |     FAST DUAL            |                           |     1 |       |     2   (0)| 00:00:01 |
      |*  6 |   FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD (ind:2) |     1 |   536 |     0   (0)| 00:00:01 |
      ------------------------------------------------------------------------------------------------------
      
      

      I’ll leave it as an exercise for any interested readers to work out how they might engineer an optimal execution plan for the classic query:

      select 
              sq.sql_id, sq.child_number, sq.sql_text 
      from 
              V$sql           sq,
              v$session       se
      where
              se.sid = sys_context('userenv','sid')
      and     (sq.sql_id, sq.child_number)  in (
                      (se.sql_id, se.sql_child_number),
                      (se.prev_sql_id, se.prev_child_number)
              )
      /
      
      

      Regards
      Jonathan Lewis

      P.S. Another feature of queries against the dynamic performance views that “loses” an optimisation option against v$session is the absence of referential integrity. With real tables, obvious primary keys and referential integrity, we’d see join elimination and x$ksled would be dropped from the plan.

      Comment by Jonathan Lewis — October 12, 2019 @ 11:14 am BST Oct 12,2019 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.