Oracle7 Tuning, release 7.3.3 Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

The Dynamic Performance Tables


Dynamic performance views are useful for identifying instance-level performance problems. Whereas X$ tables are a representation of internal data structures that can be processed by SQL statements, V$ views allow users other than SYS access to this data. This chapter describes views which are of the greatest use for both performance tuning and ad hoc investigation, as when users report a sudden deterioration in response time.

See Also: For complete information on all dynamic performance tables, see Oracle7 Server Reference.

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 19-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 non-parent 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

 

Dynamic view. Contains detailed information on the System Global Area.

 

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. Only updated 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. Cumulative session data is from connect time.

Table 19-2: Session Level Views Important for Tuning
View   Notes  

V$LOCK

 

Lists the locks currently held by the Oracle7 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 further to see whether such waits occur frequently and whether they can be correlated with other phenomena, such as the use of particular modules.

Current Statistic Value and Rate 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 / 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:

SQL> @STAT GET

It is preferable, however, to use some mechanism which records the change in the statistic(s) over a known period of time.

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: This script must be run at least twice, because the first time it is run, it will initialize the SQL*Plus variables.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index