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

Last updated on NOVEMBER 07, 2023

Applies to:

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 misled.  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

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