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.

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

Listed below are the other V$ views that every DBA should know.

View	Description
V$LIBRARYCACHE	This view contains performance statistics about all activities in the library cache. These include cache_misses and cache_pins.
V$SESSION_WAIT	This view shows all Oracle sessions that are waiting on internal resources.
V$SGASTAT	This view shows internal SGA statistics such as free_memory.
V$SQLAREA	This view contains valuable information about executing SQL including the parse_count and the number of times the SQL was executed.
V$WAITSTAT	This view gives details about any sessions that are waiting on Oracle resources.
V$SESSION	This view gives detailed session information about all connected Oracle users.
V$SESSION_EVENT	This view is used to show the number of session waits and session timeouts.

The following paragraph discusses in greater detail the critical V$ performance views in Oracle.

The V$ views are actually public synonyms for corresponding SYS.V_$ views.
The following views are still used in Oracle 19c, and the columns specified exist in these views as they have not been deprecated.
Here's a quick summary of their relevance and purpose in Oracle 19c:

1. V$SYSSTAT (column: disk_sorts)
   • Purpose: Provides instance-level statistics.
   • disk_sorts: This statistic shows the number of sorts that were performed on disk rather than in memory. It is still relevant in Oracle 19c to assess memory allocation for sorting operations.
2. V$SGASTAT (column: free_memory)
   • Purpose: Displays detailed information about the System Global Area (SGA).
   • free_memory: Represents the amount of memory in bytes currently available in various components of the SGA. It is still relevant for monitoring and tuning memory usage.
3. V$SQLAREA (column: parse_count)
   • Purpose: Provides information about SQL statements currently in the library cache.
   • parse_count: Tracks the number of parse calls for a SQL statement. It is used for understanding parsing behavior and optimizing library cache performance.
4. V$LIBRARYCACHE (column: cache_pins)
   • Purpose: Shows information about library cache performance and activity.
   • cache_pins: Indicates the number of pin operations performed in the library cache. This column remains important for diagnosing contention or inefficiencies in the shared pool.
5. V$SESSION_EVENT (column: event_timeouts)
   • Purpose: Displays wait events experienced by sessions.
   • event_timeouts: Shows the number of times a session timed out waiting for a specific event. This column is critical for analyzing session performance and troubleshooting timeout issues.

Summary: All these views and their respective columns are still valid and widely used in Oracle 19c. They continue to be essential for performance tuning, diagnosing issues, and monitoring system behavior. However, ensure you review Oracle's official documentation to confirm any updates for newer releases or patches.

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.

• Instance Tuning Steps:
  These are the main steps in the Oracle performance method for instance tuning:
  1. Define the Problem: Get candid feedback from users about the scope of the performance problem.
  2. Examine the Host System and Examine the Oracle Statistics
  1. After obtaining a full set of operating system, database, and application statistics, examine the data for any evidence of performance problems.
  2. Consider the list of common performance errors to see whether the data gathered suggests that they are contributing to the problem.
  3. Build a conceptual model of what is happening on the system using the performance data gathered.
  3. 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.
  4. 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.