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.
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.
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:
The equivalent of the cu statistic in raw trace data.
The equivalent of the cr statistic in raw trace data.
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
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%'
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'
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.
|
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
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:
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.
The number of WAIT lines with nam= ' x ', where x is the value of the row's EVENT .
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
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:
Each time an event completes, the Oracle kernel increments this sequence number.
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.
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 .
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.
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 1 - 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
|
Oracle's Inauspicious Early Attempt to Document V$SESSION_WAITThe 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 32 - 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 |