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 16, 2016

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) :

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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms