16
Dynamic Performance Views

This chapter explains how you can use V$ views for:

Dynamic performance views, or "V$" views, are useful for identifying instance-level performance problems. All V$ views are listed in the V$FIXED_TABLE view.

V$ view content is provided by underlying X$ tables. The X$ tables are internal data structures that can be modified by SQL statements. These tables are therefore only available when an instance is in a NOMOUNT or MOUNT state.

This chapter describes the most useful V$ views for performance tuning. V$ views are also useful for ad hoc investigation, for example, when users report sudden response time deterioration.

Although the V$ views belong to user SYS, users other than SYS have read-only access to V$ views. Oracle populates the V$ views and X$ tables at instance startup. Their contents are flushed when you shut down the instance.

The X$ tables and their associated V$ views are dynamic, so their contents are constantly changing. X$ tables retain timing information providing you have set the init.ora parameter TIME_STATISTICS to TRUE, or if you execute the SQL command:

   ALTER SYSTEM SET TIME_STATISTICS=true;

Instance-Level Views for Tuning

These views concern the instance as a whole and record statistics either since startup of the instance or (in the case of the SGA statistics) the current values, which will remain constant until altered by some need to reallocate SGA space. Cumulative statistics are from startup.

Table 16-1 Instance Level Views Important for Tuning

View	Notes
V$FIXED_TABLE	Lists the fixed objects present in the release.
V$INSTANCE	Shows the state of the current instance.
V$LATCH	Lists statistics for nonparent latches and summary statistics for parent latches.
V$LIBRARYCACHE	Contains statistics about library cache performance and activity.
V$ROLLSTAT	Lists the names of all online rollback segments.
V$ROWCACHE	Shows statistics for data dictionary activity.
V$SGA	Contains summary information on the system global area.
V$SGASTAT	Contains detailed information on the system global area.
V$SORT_USAGE	Shows the size of the temporary segments and the session creating them. This information can help you identify which processes are doing disk sorts.
V$SQLAREA	Lists statistics on shared SQL area; contains one row per SQL string. Provides statistics on SQL statements that are in memory, parsed, and ready for execution. Text limited to 1000 characters; full text is available in 64 byte chunks from V$SQLTEXT.
V$SQLTEXT	Contains the text of SQL statements belonging to shared SQL cursors in the SGA.
V$SYSSTAT	Contains basic instance statistics.
V$SYSTEM_EVENT	Contains information on total waits for an event.
V$WAITSTAT	Lists block contention statistics. Updated only when timed statistics are enabled.

The single most important fixed view is V$SYSSTAT, which contains the statistic name in addition to the value. The values from this table form the basic input to the instance tuning process.

Session-Level or Transient Views for Tuning

These views either operate at the session level or primarily concern transient values. Session data is cumulative from connect time.

Table 16-2 Session Level Views Important for Tuning

View	Notes
V$LOCK	Lists the locks currently held by the Oracle8 Server and outstanding requests for a lock or latch.
V$MYSTAT	Shows statistics from your current session.
V$PROCESS	Contains information about the currently active processes.
V$SESSION	Lists session information for each current session. Links SID to other session attributes. Contains row lock information.
V$SESSION_EVENT	Lists information on waits for an event by a session.
V$SESSION_WAIT	Lists the resources or events for which active sessions are waiting, where WAIT_TIME = 0 for current events.
V$SESSTAT	Lists user session statistics. Requires join to V$STATNAME, V$SESSION.

The structure of V$SESSION_WAIT makes it easy to check in real time whether any sessions are waiting, and if so, why. For example:

   SELECT sid,
     EVENT
     FROM V$SESSION_EVENT
     WHERE WAIT_TIME = 0;

You can then investigate to see whether such waits occur frequently and whether they can be correlated with other events, such as the use of particular modules.

Current Statistic Values and Rates of Change

This section describes procedures for:

Finding the Current Value of a Statistic

Key ratios are expressed in terms of instance statistics. For example, the consistent change ratio is consistent changes divided by consistent gets. The simplest effective SQL*Plus script for finding the current value of a statistic is of the form:

   COL NAME format a35
   COL VALUE format 999,999,990
   SELECT NAME, VALUE from V$SYSSTAT S
   WHERE lower(NAME) LIKE lower('%&stat_name%')
   /

Note:
Two LOWER functions in the preceding query make it case insensitive and allow it to report data from the 11 statistics whose names start with "CPU" or "DBWR". No other upper-case characters appear in statistic names.

You can use the following query, for example, to report all statistics containing the word "get" in their name:

   @STAT GET

It is preferable, however, to use mechanisms that record the change in the statistic(s) over a known period of time as described in the next section of this chapter.

Finding the Rate of Change of a Statistic

You can adapt the following script to show the rate of change for any statistic, latch, or event. For a given statistic, this script tells you the number of seconds between two checks of its value, and its rate of change.

   set veri off 
   define secs=0 
   define value=0 
   col value format 99,999,999,990 new_value value  
   col secs format a10 new_value secs noprint 
   col delta format 9,999,990 
   col delta_time format 9,990 
   col rate format 999,990.0 
   col name format a30 
   select name,value, to_char(sysdate,'sssss') secs, 
       (value - &value) delta,  
       (to_char(sysdate,'sssss') - &secs) delta_time, 
       (value - &value)/ (to_char(sysdate,'sssss') - &secs) rate 
       from v$sysstat 
       where name = '&&stat_name' 
   /

Note:
Run this script at least twice, because the first time you run it, it initializes the SQL*Plus variables.

16 Dynamic Performance Views