How to Avoid Being Mislead While Querying V$SESSION to Obtain Accurate, Real-Time Information on Waiting or Running Sessions
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.
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. 188.8.131.52) :
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?
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms