| Lesson 2 | The Oracle performance views |
| Objective | List the critical V$ views |
Using Oracle 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.
V$SYSSTAT view
The V$SYSSTAT view is an important source of many database-wide statistics. It is used to compute the following:
V$SYSSTAT view
| 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 following paragraph discusses in greater detail the critical V$ performance views in Oracle.
V$ performance Views in Oracle
The V$ views are actually public synonyms for corresponding SYS.V_$ views.
The correct matches are as follows:
The correct matches are as follows:
- V$SYSSTAT: disk_sorts
- V$SGASTAT: free_memory
- V$SQLAREA: parse_count
- V$LIBRARYCACHE: cache_pins
- V$SESSION_EVENT: event_timeouts
V$ Performance Views
The V$ views are the performance information sources used by all Oracle Database performance tuning tools. The V$ views are based on memory structures initialized at instance startup. The memory structures, and the views that represent them, are automatically maintained by Oracle Database at the life of the instance.
The V$ views are the performance information sources used by all Oracle performance tuning tools.
The V$ views are based on memory structures initialized at instance startup. The memory structures, and the views that represent them, are automatically maintained by Oracle throughout the life of the instance.
The V$ views are based on memory structures initialized at instance startup. The memory structures, and the views that represent them, are automatically maintained by Oracle throughout the life of the instance.
Instance Tuning Steps
These are the main steps in the Oracle performance method for instance tuning:
The next lesson discusses how two queries written with different coding techniques may perform at different levels.
- Define the Problem: Get candid feedback from users about the scope of the performance problem.
- Examine the Host System and Examine the Oracle Statistics
- After obtaining a full set of operating system, database, and application statistics, examine the data for any evidence of performance problems.
- Consider the list of common performance errors to see whether the data gathered suggests that they are contributing to the problem.
- Build a conceptual model of what is happening on the system using the performance data gathered.
- Implement and Measure Change: Propose changes to be made and the expected result of implementing the changes. Then, implement the changes and measure application performance.
- Determine whether the performance objective defined in step 1 has been met. If not, then repeat steps 2 and 3 until the performance goals are met.
The next lesson discusses how two queries written with different coding techniques may perform at different levels.