Using memory structures to gather performance Data
Oracle provides numerous RAM structures that can be used to view Oracle performance. The X$ structures are the base views against the internal C structures in memory.
The V$ views are built upon the X$ views. Oracle provides these V$ views to capture information about the overall database status.
While we query the V$ views as if they were Oracle tables, they are not tables. They only exist in the SGA for the time that the instance is running, and they only accumulate values for that instance.
Querying the V$ performance views
While numerous V$ views exist, only a few are useful for performance and tuning. The first of these views we will discuss is the V$SYSTAT view.
The V$SYSSTAT view is an important source of many database-wide statistics. It is used to compute the following:
Data buffer hit ratio
Usually computed by using the bstat-estat utility, which reads values from V$SYSSTAT.
Number of disks
Used to set the sort_area_size parameter
Continued row fetches
Increments a counter whenever Oracle must read a chained row. If this value is high, performance is being compromised, and database reorganization may be necessary
Other important V$ views
Listed below are the other V$ views that every DBA should know:
The next lesson discusses how two queries written with different coding techniques may perform at different levels.
Click the link below to check your knowledge of the critical V$ performance views in Oracle. V$-performance views