Posted by I’m a big fan of giving developers the tools they need to tune their applications in Production if that is possible. Clearly some times that will not be the case due to data sensitivity etc, but if a controlled, secure facility can be built for developers to see their applications “in the wild”, then my experience has been that those applications are better supported, and generally perform better. I think (hope) that this is because developers are proud of what they build, and want to fix things if their customers are not happy.
Conversely, if developers have absolutely no means of knowing how their applications are performing in Production, its very hard for them to have a genuine investment in that application’s operation and ultimately its success.
This is why I’m a fan of giving developers (selective) access to things like the V$ views in the Oracle Database, so that they can monitor things like expensive SQL, or bottlenecks in their application. But this led to an interesting discovery recently, when I got some feedback from a friend on a tool I’d written for them a while back. The tool allowed access to objects like V$SESSION, V$SESSION_EVENT, V$SESSION_WAIT and V$SESSION_WAIT_HISTORY but these objects were wrapped in an additional view layer to limit the visibility of the data to just the current session.
In each case, the view would be encapsulated with something similar to:
create or replace
view MY_V$SESSION as
select *
from v$session
where sid = userenv('SID')
and MY_V$SESSION would be the object that a developer would be granted access to, and they could even factor queries to that into the application code to allow self-logging of performance metrics.
My friend had migrated some of their applications to the cloud, and reported to me that these views were now running less quickly. Even though the V$ views are not true tables (they could in fact be anything, eg, querying a V$ view could be just a hook into a memory structure, or perhaps the database controlfile or any other flat file), you can still perform an EXPLAIN PLAN command on them to examine the execution plan. You might be thinking that this is of little use, because if these are not genuine tables, then the only execution path that could be available would be similar to that of an external table, namely, a top to bottom scan. For many of the V$ views, this is indeed the case, and as developers will know, generally when you have a full scan that runs not as fast as you like, one of the options you would explore is the addition of an index. For example, with a standard table like below:
SQL> create table t as
2 select rownum r, d.*
3 from dba_objects d;
Table created.
SQL> select * from t where r = 983;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 136 | 443 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 136 | 443 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("R"=983)
If I don’t like the full scan, I can look at indexing the appropriate column
SQL> create index ix on t ( r ) ;
Index created.
SQL>
SQL> set autotrace traceonly explain
SQL> select * from t
2 where r = 983;
Execution Plan
----------------------------------------------------------
Plan hash value: 3947747388
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 136 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 136 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("R"=983)
We can’t create an index on a V$ view, but some of the views come with the concept of an index in-built. This may seem impossible, but if you have (say) a memory structure of elements and every element is of fixed size, then some simple byte offsets can provide the “illusion” of indexed access into that structure. We can see that by taking a structure like V$SESSION. Here is the execution plan for a query to V$SESSION for a known session (SID).
SQL> select * from v$session
2 where sid = 983;
Execution Plan
----------------------------------------------------------
Plan hash value: 1627146547
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 366 | 0 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN | | 1 | 366 | 0 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 122 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 58 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 64 | 0 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 1 | 244 | 0 (0)| 00:00:01 |
|* 6 | FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) | 1 | 244 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("W"."KSLWTSID"=983)
4 - filter("W"."KSLWTEVT"="E"."INDX")
6 - filter("S"."INDX"=983 AND BITAND("S"."KSUSEFLG",1)<>0 AND
BITAND("S"."KSSPAFLG",1)<>0 AND ("S"."CON_ID"=0 OR "S"."CON_ID"=3) AND
"S"."INST_ID"=USERENV('INSTANCE'))
Notice the elements in the plan “ind:1” and “ind:2”. These provide pseudo-index access into the structure. We can also see that V$SESSION is a view of multiple X$ structures. Very cool!
Here is where things get interesting in terms of performance. The SID value of 983 above coincides with the true SID of the session I’m currently connect as:
SQL> select userenv('SID') from dual;
USERENV('SID')
--------------
983
Thus it would be reasonable to expect that the query
SQL> select * from v$session
2 where sid = userenv('SID');
will also yield the same execution plan. Let’s take a look
SQL> select * from v$session
2 where sid = userenv('SID');
Execution Plan
----------------------------------------------------------
Plan hash value: 2422122865
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 366 | 0 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN | | 1 | 366 | 0 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 122 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$KSLWT | 1 | 58 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 64 | 0 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 1 | 244 | 0 (0)| 00:00:01 |
|* 6 | FIXED TABLE FULL | X$KSUSE | 1 | 244 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("W"."KSLWTSID"=USERENV('SID'))
4 - filter("W"."KSLWTEVT"="E"."INDX")
6 - filter("S"."INDX"=USERENV('SID') AND BITAND("S"."KSUSEFLG",1)<>0 AND
BITAND("S"."KSSPAFLG",1)<>0 AND ("S"."CON_ID"=0 OR "S"."CON_ID"=3) AND
"S"."INST_ID"=USERENV('INSTANCE'))
Notice the FIXED TABLE FULL on line 3. The simple change to the query has blocked usage of the pseudo-index. This does not seem to be costing decision, more just a limitation of what the optimizer can do with these pseudo-indexes (I’m just guessing here). For example, if I drill further down into the components of V$SESSION and just focus on the X$KSLWT object.
SQL> select * from X$KSLWT where KSLWTSID = 983;
Execution Plan
----------------------------------------------------------
Plan hash value: 3210902170
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 126 | 0 (0)| 00:00:01 |
|* 1 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 126 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("KSLWTSID"=123)
SQL> select * from X$KSLWT where KSLWTSID = userenv('SID');
Execution Plan
----------------------------------------------------------
Plan hash value: 3649698209
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 126 | 0 (0)| 00:00:01 |
|* 1 | FIXED TABLE FULL| X$KSLWT | 1 | 126 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("KSLWTSID"=USERENV('SID'))
Even this simple query cannot get the benefit of the index. There is no datatype mismatch here – you can dump the two values to see that they’re identical
SQL> select dump(userenv('SID')) from dual;
DUMP(USERENV('SID'))
-------------------------------------------------------
Typ=2 Len=3: 194,10,84
SQL> select userenv('SID') from dual;
USERENV('SID')
--------------
983
SQL> select dump(983) from dual;
DUMP(983)
----------------------
Typ=2 Len=3: 194,10,84
I initially thought that perhaps the predicate via index only works on a literal value, but as you can see below, a bind variable still utilises the index without issue:
SQL> variable n number
SQL> exec :n := userenv('SID');
PL/SQL procedure successfully completed.
SQL>
SQL> select * from v$session
2 where sid = :n;
Execution Plan
----------------------------------------------------------
Plan hash value: 1627146547
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 366 | 0 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN | | 1 | 366 | 0 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 122 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 58 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 64 | 0 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 1 | 244 | 0 (0)| 00:00:01 |
|* 6 | FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) | 1 | 244 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("W"."KSLWTSID"=TO_NUMBER(:N))
4 - filter("W"."KSLWTEVT"="E"."INDX")
6 - filter("S"."INDX"=TO_NUMBER(:N) AND BITAND("S"."KSUSEFLG",1)<>0 AND
BITAND("S"."KSSPAFLG",1)<>0 AND ("S"."CON_ID"=0 OR "S"."CON_ID"=3) AND
"S"."INST_ID"=USERENV('INSTANCE'))
Similarly, if I return to my normal relational table, we can also see that there is nothing specific about the USERENV function that blocks its inclusion in an index scan path:
SQL> select * from t
2 where r = userenv('SID');
Execution Plan
----------------------------------------------------------
Plan hash value: 3947747388
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 136 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 136 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("R"=USERENV('SID'))
Back to my friends performance concerns. Most of the time, you would probably hardly notice a top to bottom scan of many of the V$ views. However, once they migrated some apps to a shared environment configuration, namely, they could be a pluggable database amongst hundreds or thousands of pluggable databases in the same container. That means the memory structures that underpin the V$ views can be very large, even though each pluggable only sees a subset of that. Here’s an example of the difference in performance from one of my always-free database instances
SQL> select userenv('SID') from dual;
USERENV('SID')
--------------
13724
SQL> set feedback only
SQL> set timing on
SQL> select *
2 from v$session_event
3 where sid = 13724;
6 rows selected.
Elapsed: 00:00:00.16
SQL> select *
2 from v$session_event
3 where sid = userenv('SID');
6 rows selected.
Elapsed: 00:00:11.50
If you are going to be using USERENV(‘SID’) or any expression to probe into the V$ views on a column that might be indexed (see V$INDEXED_FIXED_COLUMN) , then consider the following workarounds
SQL> with mysid as
2 ( select /*+ materialize */ userenv('SID') n from dual )
3 select * from v$session, mysid
4 where sid = n;
Execution Plan
----------------------------------------------------------
Plan hash value: 2230424401
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 379 | 4 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D68CC_442B5807 | | | | |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 379 | 2 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 315 | 2 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 257 | 2 (0)| 00:00:01 |
| 7 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68CC_442B5807 | 1 | 13 | 2 (0)| 00:00:01 |
|* 9 | FIXED TABLE FIXED INDEX | X$KSUSE (ind:1) | 1 | 244 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) | 1 | 58 | 0 (0)| 00:00:01 |
|* 11 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 64 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("S"."INDX"="N" AND BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND
("S"."CON_ID"=0 OR "S"."CON_ID"=3) AND "S"."INST_ID"=USERENV('INSTANCE'))
10 - filter("S"."INDX"="W"."KSLWTSID")
11 - filter("W"."KSLWTEVT"="E"."INDX")
Or you can result cache the value:
SQL> with mysid as
2 ( select /*+ result_cache */ userenv('SID') n from dual )
3 select * from v$session, mysid
4 where sid = n;
Execution Plan
----------------------------------------------------------
Plan hash value: 420208790
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 379 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 379 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 315 | 2 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 257 | 2 (0)| 00:00:01 |
| 4 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 5 | RESULT CACHE | 1a4va6t1qm2uaayyj3ppfnvwf5 | 1 | | 2 (0)| 00:00:01 |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 7 | FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) | 1 | 244 | 0 (0)| 00:00:01 |
|* 8 | FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) | 1 | 58 | 0 (0)| 00:00:01 |
|* 9 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 64 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("S"."INDX"="N" AND BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND
("S"."CON_ID"=0 OR "S"."CON_ID"=3) AND "S"."INST_ID"=USERENV('INSTANCE'))
8 - filter("S"."INDX"="W"."KSLWTSID")
9 - filter("W"."KSLWTEVT"="E"."INDX")
If you’re querying the V$ views, it is always worth generating an execution plan and double-checking that you are getting any performance benefits you can, because those memory structures could be a lot larger than you expect depending on your configuration
Learning is not a spectator sport 