JL Computer Consultancy

V$ Views - A health warning

January 1999


Since the early days of Oracle 7, information about the internal performance monitoring (V$) views has becoming widely available, and increasingly used. Scripts are now floating around which take advantage of these views to supply detailed information about what is going on in the SGA in near-real time. It is not uncommon to see scripts which join v$session to v$sqlarea to v$sqltext to get details of what SQL is being run by which user right now and how expensive that SQL is.

There is a significant performance threat in such scripts, particularly for users running databases with large SGAs. The threat is the usual one with SQL - is there an efficient path to the information that you need.

In recent versions (7.3-ish) Oracle Corp. has recognised the proliferation of such scripts and has introduced two features to reduce the threat.. The first feature is that EXPLAIN PLAN now works against the V$ views and reports the X$ structures on which they hang; the second feature is that the X$ structures now have some 'pseudo-indexes' - fast-access paths into the structure that are recorded in the view V$INDEXED_FIXED_COLUMN.

Here, for example is a simple query against a couple of V$ views, with the corresponding execution path

Back to Index of Short Articles


select
        username,
        sql_text 
from 
        v$session      ses,
        v$sql          sql
where
        sql.hash_value = ses.sql_hash_value
and     sql.address = ses.sql_address
and     sid = 10
;
 
  Id  Par  Pos  Ins Plan
---- ---- ---- ---- --------------------------------------------------
   0         2        SELECT STATEMENT (first_rows)     Cost (2,1,40)
   1    0    1          NESTED LOOPS     Cost (2,1,40)
   2    1    1            FIXED TABLE  SYS X$KSUSE (fixed index #1)  Cost (1,1,20)
   3    1    2            FIXED TABLE  SYS X$KGLOB (fixed index #1)  Cost (1,1,20)

As you can see, this particular query seems to be following a very efficient access path (although you may have to pause for a moment to note that the usual 'INDEX ACCESS' rows do not appear in the plan).

Clearly the SID in v$session is an indexed column; you might want to know whether the hash_value or the address is the indexed column in v$sql. This highlights one minor problem with the v$indexed_fixed_column views - it references the X$ structures, not the V$ views, so you need to know which X$ matches which V$. v$fixed_view_definition is the place to start looking.


Select  view_definition 

from    V$fixed_view_definition

where   view_name = 'GV$SQL';
(being a friendly soul, I have re-formatted the output for readability)
 
select 
        inst_id, kglnaobj, 
        kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16, 
        kglobt08+kglobt11, 
        kglobt10, kglobt01, 
        decode(kglobhs6,0,0,1), 
        decode(kglhdlmd,0,0,1), 
        kglhdlkc,kglhdexc, kglobpc6, kglhdldc, 
        substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19), 
        kglhdivc, kglobt12, kglobt13, kglobt14, kglobt15, kglobt02, 
        decode(kglobt32, 
               0, 'NONE',        
               1, 'ALL_ROWS',        
               2, 'FIRST_ROWS',        
               3, 'RULE',        
               4, 'CHOOSE',           
                  'UNKNOWN'
        ), 
        kglobtn0, kglobt17, kglobt18, kglhdkmk, kglhdpar, kglobtp0, 
        kglnahsh, kglobt09, kglobts0, kglobt19, kglobts1, kglobt20, kglobt21 
from 
        x$kglcursor  
where 
        kglhdadr != kglhdpar  
and     kglobt02 != 0
;

And in this case even this doesn't help, because you still need to know that x$kglcursor is a subset of x$kglob, and you can only find that out if you look at the fixed object x$kqftdt.

You may note that the query I have used here selects the definition for gv$ql, rather than v$sql. This is an Oracle 8 thing - the GV$ views are global views for multiple instances for use with the Oracle Parallel Server and most of the V$ views in Oracle 8 are defined as:

        select {columns} from gv${view} where inst_id = userenv('instance');

Finally, however, we can check the indexes on v$sql, by looking at the indexing on x$glob, to find that the pseudo-index is on the hash_value column (which is not too surprising since the first test the optimiser makes on a new SQL statement is to hash the statement and see if there is anything in the SGA with a matching hash-value)


select * from v$indexed_fixed_column where table_name = 'X$KGLOB';
TABLE_NAME INDEX_NUMBER COLUMN_NAME    COLUMN_POSITION
---------- ------------ -------------- ---------------
X$KGLOB               1 KGLNAHSH                    0

When you start to work through the same sequence of checks on v$sqlarea, which is perhaps the most frequently accessed V$ view, you will find that it is a sorted, grouped variant of v$sql and almost all queries against it (short of a single input hash-value) result in a complete scan and sort of all the SQL in the SGA: for large SGAs this can be a painfully intensive hit on the CPU, hence the health warning in the title of this note.

There are four options for bypassing this issue when it arises-

  1. Write step by step SQL in SQL*Plus using the new_value column option to copy values from one SQL to the next
  2. Write SQL scripts that write and call SQL scripts
  3. Write PL/SQL using cursor loops
  4. Define your own pre-joined versions of the V$ views.

There is an example of option 1 in my script for finding out what a specific UNIX process id is currently doing.


Back to Index of Short Articles