My Oracle Support Banner

How to Avoid Being Mislead While Querying V$SESSION to Obtain Accurate, Real-Time Information on Waiting or Running Sessions (Doc ID 2171986.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

Goal

In the quest for accurate, real-time information, one avenue of investigation is to query the V$SESSION dynamic performance view (or similar views). In this activity, the interpretation of the various columns of data is vital, and misinterpretation can lead to poor troubleshooting, inaccurate diagnoses of issues, and the implementation of ineffective "solutions."

To demonstrate the issue, consider the following query, designed solely for the purpose of remaining on CPU as much as possible for illustration purposes  (tested on Oracle Database Rel. 12.1.0.2) :

SQL>
SELECT
    t1.owner,    t1.object_type,    COUNT ( * ) HOW_MANY 
FROM
    dba_objects t1,    dba_objects t2,    dba_objects t3,     dba_objects t4 
WHERE    t1.owner = t2.owner 
    AND    t2.owner = t3.owner 
    AND    t3.owner = t4.owner 
    AND    t1.object_name = t2.object_name 
    AND    t2.object_name = t3.object_name 
    AND    t3.object_name = t4.object_name 
    AND    t1.object_type = t2.object_type 
    AND    t2.object_type = t3.object_type 
    AND    t3.object_type = t4.object_type 
    AND    t1.object_id > 0 
    AND    t1.data_object_id > 0 
    AND    t1.object_id * dbms_random.value / ln ( t1.object_id ) < t1.data_object_id * dbms_random.value * ln ( t1.data_object_id ) 
GROUP BY
    t1.owner,    t1.object_type 
ORDER BY    1,    2;

OWNER           OBJECT_TYPE             HOW_MANY
--------------- ----------------------- ----------
ABCD_040200     INDEX                   1511
ABCD_040200     LOB                     193
ABCD_040200     TABLE                   446
ABCDSYS         TABLE                   4
   .
   .
   .

110 rows selected.

Elapsed: 00:01:25.99

Statistics
----------------------------------------------------------
96                 recursive calls
0                  db block gets
20293              consistent gets
17128              physical reads
0                  redo size
3978               bytes sent via SQL*Net to client
628                bytes received via SQL*Net from client
9                  SQL*Net roundtrips to/from client
1                  sorts (memory)
0                  sorts (disk)

110 rows processed

The session was initiated, and the elapsed time was approximately eighty-six seconds.

Before this query started, this session (session ID 75) had only consumed one centisecond of CPU time. Immediately after the query had completed, the VALUE column in V$SESSTAT for 'CPU used by this session' was "6588" (since the VALUE column is measured in centiseconds, this is 65.88 seconds) as follows:

SQL >
SELECT  VALUE
FROM    v$sesstat s, v$statname n
WHERE   s.sid = sys_context ('USERENV','SID')
  AND   s.statistic# = n.statistic#
  AND   n.name = 'CPU used by this session';

VALUE
----------
      6588

This value tells us that, of the eighty-six seconds of elapsed time, approximately sixty-five seconds were spent either:

Therefore, for seventy-five percent of the elapsed time (65 / 86 * 100), execution of the query was not in a wait state.

Now, let's look at what you would see in  a monitoring session (in this case looking at V$SESSION), while this query is executing.  The following query is executed from another terminal, every three seconds :

SQL >
SELECT   s.sid, s.event, s.seconds_in_wait s_in_wait, sql_text
FROM     v$session s, v$sql t
WHERE    s.sid = && SESSION_ID
  AND    s.sql_id = t.sql_id;

Enter value for session_id: 75
old 3: WHERE s.sid=&&SESSION_ID
new 3: WHERE s.sid=75 

SID        EVENT                    S_IN_WAIT  SQL_TEXT
---------- ------------------------ ---------- -------------------------
        75 direct path write temp            0 SELECT t1.owner, t1.objec
                                               t_type, count(*) HOW_MANY
                                               FROM dba_objects t1, dba
                                               ...
        75 direct path read                  1 SELECT t1.owner, t1.objec
                                               t_type, count(*) HOW_MANY
                                               FROM dba_objects t1, dba
                                               ...
        75 direct path read                  4 SELECT t1.owner, t1.objec
                                               t_type, count(*) HOW_MANY
                                               FROM dba_objects t1, dba
                                               ...

Judging from the wait event name, coupled with the increasing number of seconds in wait, you'd most likely assume that the session was waiting on direct-path I/O operations against a temporary tablespace.  If so, and if you were trying to tune the query against DBA_OBJECTS, then you'd be sadly mislead.  After all, it has already been established that the query spent three fourths of its elapsed time on CPU, or in the CPU run queue.  So, what was wrong with the syntax of this query against V$SESSION, that led you down this incorrect path?

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.