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 JUNE 16, 2022
Applies to:Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A 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. 126.96.36.199) :
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?
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