Section 8.2. Fixed View Reference | Optimizing Oracle Performance

8.2 Fixed View Reference

In spite of their deficiencies, Oracle's fixed views provide value to the performance analyst in many situations. For this section, I describe a few Oracle fixed views that are important for you to understand in your role of performance analyst. All object descriptions shown were taken from an Oracle release 9.0.1.0.0 system.

8.2.1 V$SQL

Probably the most important fixed view for the performance analyst is V$SQL . This view shows several important attributes of the SQL statements whose header information currently reside in the shared pool. The columns in this view are as follows :

 SQL>  desc v$sql  Name                                   Null?    Type  -------------------------------------- -------- --------------------------  SQL_TEXT                                        VARCHAR2(1000)  SHARABLE_MEM                                    NUMBER  PERSISTENT_MEM                                  NUMBER  RUNTIME_MEM                                     NUMBER  SORTS                                           NUMBER  LOADED_VERSIONS                                 NUMBER  OPEN_VERSIONS                                   NUMBER  USERS_OPENING                                   NUMBER  EXECUTIONS                                      NUMBER  USERS_EXECUTING                                 NUMBER  LOADS                                           NUMBER  FIRST_LOAD_TIME                                 VARCHAR2(19)  INVALIDATIONS                                   NUMBER  PARSE_CALLS                                     NUMBER  DISK_READS                                      NUMBER  BUFFER_GETS                                     NUMBER  ROWS_PROCESSED                                  NUMBER  COMMAND_TYPE                                    NUMBER  OPTIMIZER_MODE                                  VARCHAR2(10)  OPTIMIZER_COST                                  NUMBER  PARSING_USER_ID                                 NUMBER  PARSING_SCHEMA_ID                               NUMBER  KEPT_VERSIONS                                   NUMBER  ADDRESS                                         RAW(4)  TYPE_CHK_HEAP                                   RAW(4)  HASH_VALUE                                      NUMBER  PLAN_HASH_VALUE                                 NUMBER  CHILD_NUMBER                                    NUMBER  MODULE                                          VARCHAR2(64)  MODULE_HASH                                     NUMBER  ACTION                                          VARCHAR2(64)  ACTION_HASH                                     NUMBER  SERIALIZABLE_ABORTS                             NUMBER  OUTLINE_CATEGORY                                VARCHAR2(64)  CPU_TIME                                        NUMBER  ELAPSED_TIME                                    NUMBER  OUTLINE_SID                                     NUMBER  CHILD_ADDRESS                                   RAW(4)  SQLTYPE                                         NUMBER  REMOTE                                          VARCHAR2(1)  OBJECT_STATUS                                   VARCHAR2(19)  LITERAL_HASH_VALUE                              NUMBER  LAST_LOAD_TIME                                  VARCHAR2(19)  IS_OBSOLETE                                     VARCHAR2(1)

With V$SQL , you can rank SQL statements in your system by the amount of work they do, or by whatever measure of efficiency you like (see Section 8.3.3 later in this chapter). By querying V$SQLTEXT_WITH_NEWLINES , you can see the entire text of a SQL statement, not just the first 1,000 bytes that are stored in V$SQL.SQL_TEXT :

 select sql_text from v$sqltext_with_newlines where hash_value=:hv and address=:addr order by piece

You can even sense the presence of how distinct SQL texts might have been able to make more effective use of bind variables :

 select count(*), min(hash_value), substr(sql_text,1,:len) from v$sql group by substr(sql_text,1,:len) having count(*)>=:threshold order by 1 desc, 3 asc

In this query, :len specifies a SQL text prefix length that defines whether two distinct statements are "similar" or not. For example, if :len=8 , then the strings select s alary ,... and select s .program ,... are similar, because their first eight characters are the same. Values like 32, 64, and 128 usually produce interesting results. The value of :threshold determines your threshold of tolerance for similar statements in your library cache. You'll normally want to set :threshold to at least three, because having only two similar SQL statements in your library cache is not really a problem. If your system is running amok in unshared SQL, then you'll want to set :threshold to a larger value so that you can focus on fixing a few unshared statements at a time.

8.2.2 V$SESS_IO

V$SESS_IO is a simple fixed view that allows you to measure the logical and so-called physical I/O that has been generated for a session:

 SQL>  desc v$sess_io  Name                                   Null?    Type  -------------------------------------- -------- --------------------------  SID                                             NUMBER  BLOCK_GETS                                      NUMBER  CONSISTENT_GETS                                 NUMBER  PHYSICAL_READS                                  NUMBER  BLOCK_CHANGES                                   NUMBER  CONSISTENT_CHANGES                              NUMBER

The statistics in V$SESS_IO map nicely to extended SQL trace statistics:

BLOCK_GETS: The equivalent of the cu statistic in raw trace data.
CONSISTENT_GETS: The equivalent of the cr statistic in raw trace data.
PHYSICAL_READS: The equivalent of the p statistic in raw trace data.

The number of logical I/Os (LIOs) is the sum of the values of BLOCK_GETS and CONSISTENT_GETS . When an Oracle session consumes massive amounts of CPU capacity with only intermittent executions of instrumented Oracle wait events, the session's trace will appear to "sit still." With repeated executions of the following query, you can observe whether a session that is running but emitting no trace data is executing LIO calls:

 select block_gets, consistent_gets from v$sess_io where sid=:sid

8.2.3 V$ SYSSTAT

V$SYSSTAT is one of the first fixed views I remember using. Its structure is simple:

 SQL>  desc v$sysstat  Name                                   Null?    Type  -------------------------------------- -------- --------------------------  STATISTIC#                                      NUMBER  NAME                                            VARCHAR2(64)  CLASS                                           NUMBER  VALUE                                           NUMBER

Each row in V$SYSSTAT contains an instance-wide statistic. Most statistics are tallies of operations that have occurred since the most recent instance startup. V$SYSSTAT rows are subject to overflow errors.

The denormalized structure of V$SYSSTAT makes it easy to find out what the system has done since the most recent instance startup, without having to do a join. The following query executed in Oracle9 i displays roughly 250 statistics that describe what the entire instance has done over its lifespan:

 select name, value from v$sysstat order by 1

The following query lists the values of several statistics related to parsing:

 select name, value from v$sysstat where name like 'parse%'

8.2.4 V$SESSTAT

As I described in Chapter 3, the system-wide scope is probably the incorrect action scope for your diagnostic data collection. V$SESSTAT contains the same statistics as V$SYSSTAT , except at the session level:

 SQL>  desc v$sesstat  Name                                   Null?    Type  -------------------------------------- -------- --------------------------  SID                                             NUMBER  STATISTIC#                                      NUMBER  VALUE                                           NUMBER

Each row in V$SESSTAT contains a tally of how many times a statistic has been incremented since the creation of a specified session.

V$SESSTAT is not denormalized like V$SYSSTAT , so finding a statistic by name requires a join with V$STATNAME . The following query lists all the statistics that have aggregated for a session since its birth:

 select name, value from v$statname n, v$sesstat s where sid=:sid and n.statistic#=s.statistic# and s.value>0 order by 2

The following query lists the approximate number of centiseconds' worth of CPU capacity consumed by a given session:

 select name, value from v$statname n, v$sesstat s where sid=:sid and n.statistic#=s.statistic# and n.name='CPU used by this session'

8.2.5 V$SYSTEM_EVENT

The V$SYSTEM_EVENT fixed view records aggregated statistics about instrumented code paths that the Oracle kernel has executed since its most recent instance startup:

 SQL>  desc v$system_event  Name                                   Null?    Type  -------------------------------------- -------- --------------------------  EVENT                                           VARCHAR2(64)  TOTAL_WAITS                                     NUMBER  TOTAL_TIMEOUTS                                  NUMBER  TIME_WAITED                                     NUMBER  AVERAGE_WAIT                                    NUMBER  TIME_WAITED_MICRO                               NUMBER

Each row in V$SYSTEM_EVENT contains information about the calls of a given event for the lifespan of the instance.

You might notice that there is no MAX_WAIT column in V$SYSTEM_EVENT . You can add this useful column to the definition of V$SYSTEM_EVENT , if you like, by following the instructions presented in [Lewis (2001b) 577-581].

On Oracle7 and Oracle8 i kernels , you can obtain resource consumption statistics about everything but CPU consumption with following query:

 select event, total_waits, time_waited/100 t from v$system_event order by 3 desc

With Oracle9 i kernels, you can obtain the same statistics displayed with microsecond precision by using the following query:

 select event, total_waits, time_waited_micro/1000000 t from v$system_event order by t desc

8.2.6 V$SESSION_EVENT

Once again, the system-wide scope is often the incorrect scope for diagnostic data collection. V$SESSION_EVENT provides the ability to collect properly session-scoped diagnostic data for Oracle kernel code paths:

 SQL>  desc v$session_event  Name                                   Null?    Type  -------------------------------------- -------- --------------------------  SID                                             NUMBER  EVENT                                           VARCHAR2(64)  TOTAL_WAITS                                     NUMBER  TOTAL_TIMEOUTS                                  NUMBER  TIME_WAITED                                     NUMBER  AVERAGE_WAIT                                    NUMBER  MAX_WAIT                                        NUMBER  TIME_WAITED_MICRO                               NUMBER

Each row in V$SESSION_EVENT contains information about the executions of a given segment of Oracle kernel code (a "wait event") for a given session since its birth. Thus, the information in V$SESSION_EVENT is an aggregation of the data that appears in extended SQL trace output:

EVENT: The name of an Oracle wait event. Note that each EVENT value corresponds to a nam value in the WAIT lines of Oracle's extended SQL trace data.
TOTAL_WAITS: The number of WAIT lines with nam= ' x ', where x is the value of the row's EVENT .
TIME_WAITED: The sum of the ela values for all WAIT lines with nam= ' x ', where x is the value of the row's EVENT .

The V$SESSION_EVENT fixed view contains no record of a session's CPU capacity consumption. You have to go to V$SESSTAT for that.

The following query will display information about the wait events that a given Oracle8 i session has executed over its lifespan:

 select event, total_waits, time_waited/100 t from v$session_event where sid=:sid order by t desc

The following query will display information about the wait events that a given Oracle9 i session has executed over its lifespan:

 select event, total_waits, time_waited_micro/1000000 t from v$session_event where sid=:sid order by t desc

8.2.7 V$SESSION_WAIT

Ask people what the "wait interface" is, and most will probably mention V$SESSION_WAIT . Unlike the V$SYSTEM_EVENT and V$SESSION_EVENT fixed views, V$SESSION_WAIT does not contain an aggregation of historical events. Instead, it provides a view into what a specified session is doing right now :

 SQL>  desc v$session_wait  Name                                   Null?    Type  -------------------------------------- -------- --------------------------  SID                                             NUMBER  SEQ#                                            NUMBER  EVENT                                           VARCHAR2(64)  P1TEXT                                          VARCHAR2(64)  P1                                              NUMBER  P1RAW                                           RAW(4)  P2TEXT                                          VARCHAR2(64)  P2                                              NUMBER  P2RAW                                           RAW(4)  P3TEXT                                          VARCHAR2(64)  P3                                              NUMBER  P3RAW                                           RAW(4)  WAIT_TIME                                       NUMBER  SECONDS_IN_WAIT                                 NUMBER  STATE                                           VARCHAR2(19)

Each row in V$SESSION_WAIT contains information about a session's present state. The statistics revealed by V$SESSION_WAIT include:

SEQ#

Each time an event completes, the Oracle kernel increments this sequence number.

WAIT_TIME

At the beginning of an instrumented wait event, the Oracle kernel sets the value of WAIT_TIME to zero. The value remains zero until the wait event is complete, when the kernel sets its value to one of those shown in Table 8-2. Note that the unit of measure is the centisecond, even in Oracle9 i . There is no WAIT_TIME_MICRO column at least through release 9.2.0.2.1, although the value of WAIT_TIME is derived from a microsecond value in its underlying X$ view.

SECONDS_IN_WAIT

At the beginning of an instrumented wait event, the Oracle kernel sets the value of SECONDS_IN_WAIT to zero. The session itself never updates the value again until the next instrumented wait event, whereupon the session resets the value back to zero again. The value of SECONDS_IN_WAIT is incremented by 3 approximately every three seconds by the log writer (LGWR) process. Note that the unit of measure is seconds, not centiseconds or microseconds.

Events that "time out" complicate matters somewhat. For example, an enqueue wait event times out roughly every two seconds, even for enqueue waits that last considerably longer. Upon each timeout, the Oracle kernel increments SEQ# , but it does not reset the value of SECONDS_IN_WAIT .

STATE

At the beginning of an instrumented wait event, the value of STATE becomes WAITING . The value remains WAITING until the wait event is complete, when the kernel sets its value to one of the values described in Table 8-2.

Table 8-2. Meanings of the values of the STATE and WAIT_TIME columns in V$SESSION_WAIT

STATE	WAIT_TIME	Implication
`WAITED UNKNOWN TIME`	`-` 2	The value of `TIMED_STATISTICS` was `FALSE` for the session when the event completed, so the actual duration is unknown.
`WAITED SHORT TIME`	`-` 1	The wait event has completed, but it began and ended within the same `gettimeofday` clock tick.
`WAITING`		The wait event is in process, pending completion.
`WAITED KNOWN TIME`	t	The wait event has completed, and it consumed t = t ₁ - t centiseconds of elapsed execution time (Chapter 7).

The following query will display information about the wait events that are presently executing on a given Oracle system:

 select sid, event, wait_time/100 t, seconds_in_wait w, state from v$session_wait order by 1

The following query will show a histogram of which activity your system's sessions are doing right now:

 select event, count(*) from v$session_wait where state='WAITING' group by event order by 2 desc

Don't write V$SESSION_WAIT queries with WAIT_TIME=0 in your where clause if what you really mean is STATE='WAITING '. Some analysts got into the habit of assuming that the predicates WAIT_TIME=0 and STATE='WAITING ' are equivalent, because in Oracle7 and Oracle8 i kernels, they were. However, in Oracle9 i kernels, the two predicates are not equivalent.

Oracle9 i kernels compute WAIT_TIME as round(x$ ksusecst .ksusstim/10000) , but the STATE value is a DECODE of the un-rounded value of KSUSSTIM . Therefore, WAIT_TIME can appear to be zero when its base data value is actually not. Hence, Oracle9 i kernels produce situations in which WAIT_TIME is zero, but STATE is something other than WAITING .

Oracle's Inauspicious Early Attempt to Document V$SESSION_WAIT

The kernel instrumentation described in this book and published back in 1992 has taken many years to catch on. Oracle Corporation's earliest documentation about the new capability didn't exactly hasten the feature's acceptance. For example, the Oracle7 Server Tuning guide shows the following V$SESSION_WAIT query output [Oracle (1996)]:

 SQL> SELECT sid, event, wait_time   2     FROM v$session_wait   3     ORDER BY wait_time, event;  SID EVENT                      WAIT_TIME ---- ------------------------- ---------- ...  205 latch free                4294967295  207 latch free                4294967295  209 latch free                4294967295  215 latch free                4294967295  293 latch free                4294967295  294 latch free                4294967295  117 log file sync             4294967295  129 log file sync             4294967295   22 virtual circuit status    4294967295

The guide then provides the following advice: "The unusually large wait times for the last several events signify that the sessions are currently waiting for that event [sic]. As you can see, there are currently several sessions waiting for a latch to be free and for a log file sync."

If the implication in the document were true, then the events pictured in this example had been waiting for 1.36193 years. Oops.

The problem began with omitting the STATE column from the query's select list. As it happens, the 32-bit hexadecimal representation of the decimal integer -1 is ffffffff . Print this value as an unsigned 32-bit integer, and you get 2 ³² - 1, or 4294967295.

The WAIT_TIME values shown here are actually -1. This value corresponds to the STATE value WAITED SHORT TIME (Table 8-2). Each of the "unusually large wait times" actually represents an event that had already completed, and that had in fact completed so quickly that it was measured as having a zero-centisecond duration.

Lots of authors made similar mistakes during the early years in their attempts to explain how to use the new "wait" and "event" data. To their credit, they were the pioneers who stimulated many early adopters of a new technology. But mistakes like the one described here, especially in the official Oracle Corporation documentation, did retard the rate of acceptance of Oracle's amazing new diagnostic features.

Top