New and Improved Views

Before we get into details, we need to describe the new and modified sets of views that Oracle Database 10g provides for performance monitoring and tuning. The number of Dynamic performance views, also known as the V$ views, has steadily increased as the version levels increased. Oracle 8i provided 191, while Oracle 9i Release 2 provided 266. Oracle Database 10g has built on that with a massive count of 347! Although there are many new views that the larger Oracle community has not yet probed in depth, even in Oracle 9i, we need to only take note of a select number of these views.

Views upon Views: The X$ Views

The V$ views are actually more readable and understandable representations of other internal views, known as the X$ views. If you are really curious, you can determine the definition of the V$ views using the V$FIXED_VIEW_DEFINTION view. Contrary to popular myth, all X$ views are actually SQL accessible structures in the SGA, and viewing them will not cause performance problems unless there is excessive access. Although you could write direct SQL against these views that are seen only from the SYS account, this is not encouraged because these X$ view definitions, column names, and meanings are subject to change without documentation or notice.

The following section describes the performance-related V$ views in this version. It is important to know of these views because you might have to use them on occasion to detect current and past performance issues and to drill down into the details. Both the standalone Oracle Enterprise Manager (OEM) Database Control and Grid Control elements present the data from these views in easily accessible GUI screens. Knowledge of some of these views will help you to bypass these screens and get to the data quickly and to automate many of these tasks via scripts. As well, OEM does not view-enable all these views; hence, you need to get to the data in these views via the SQL interface alone.

Setting the Right STATISTICS_LEVEL Parameter

You must set the STATISTICS_LEVEL initialization parameter to either TYPICAL or ALL for statistics to be collected and exposed in most of the views noted in the following sections. Setting this parameter to BASIC will disable collection of many of these statistics. The default value of TYPICAL ensures that the most relevant statistics required for database self-management functionality is collected and provides best overall performance. The default value should be adequate for most environments. Note that TIMED_STATISTICS is automatically set to trUE if the STATISTICS_LEVEL parameter is set to at least TYPICAL. See Table 10.1 for what does and does not get collected or implemented for the various settings.

Table 10.1. Settings for STATISTICS_LEVEL and the Effect on Advisors and Statistics Collections

Advisor or Statistics	BASIC	TYPICAL	ALL
Buffer cache advice	None	X	X
MTTR advice	None	X	X
Shared pool advice	None	X	X
Segment-level statistics	None	X	X
PGA advice	None	X	X
Timed statistics	None	X	X
Timed OS statistics	None	None	X
Plan execution statistics	None	None	X

When we describe these views, we will highlight only the relevant uses of the view, making mention of columns only when required. We will also present the views in a logical sequence rather than alphabetically.

Some of the views are exposed as DBA_ and ALL_ views and are not fully documented. However, we have chosen to detail them although they are "hidden" because they sometimes provide additional information. Detailed information for some of the views can be obtained from the Oracle Database 10g Reference Manual.

V$MEtrIC, V$MEtrIC_HISTORY, V$METRICNAME, and V$MEtrICGROUP

These views expose the individual and grouped sets of metrics that are used for Server Generated Alerts (SGAs). Before you look at these views, however, you need to understand what metrics are.

Most of the database statistics that you know of and use with other versions are essentially cumulative counters. For example, the V$SYSTEM_EVENT shows the cumulative value of various Wait events in the database that have accumulated from the time the database started until the time when you chose to view it. Similarly, the V$SYSSTAT view displays accumulated database statistics since database startup. When performing reactive real-time performance diagnosis, however, it is the rate of change of the counter that is important, rather than its absolute value since instance startup. Knowing that the system performed 2,000 I/Os per second or three I/Os per transaction during the last minute is rather more helpful than knowing that the database has performed 27,000,000 I/Os since it was started. As such, you can call the rate of change a metric because you know that when a particular rate is excessive as compared to a previous lower rate of change, you might expect to see some performance issue. In other words, thresholds for alerting can often be set based on rates. In Oracle Database 10g, metrics are available in a precalculated manner, normalized by both time and transaction. Most metrics are maintained at a one-minute interval and are also available in a historical fashion.

You can use these views to determine the units of various metrics that you will encounter and the OEM agent would use. V$MEtrIC displays metrics from the immediate past, while V$MEtrIC_HISTORY exposes the history. V$METRICNAME names and describes the units for these metrics, while V$METRICGROUP groups them. These views are not documented in the Oracle Database 10g Reference Manual. For more details on how these views matter in Server Generated Alerts, look at MetaLink Note #266970.1.

V$SERVICES, V$SERVICEMETRIC, and V$SERVICEMETRIC_HISTORY

These views expose the metrics mentioned in the previous section, broken down by services. Services are essentially classifications of layers where work is done. For example, for a database named TS10G, you would see services such as TS10G, TS10GXDB, SYS$USERS, and SYS$BACKGROUND. V$SERVICES displays the service names available, V$SERVICEMETRIC displays the metrics by service for the immediate past (usually one minute), while V$SERVICEMETRIC_HISTORY keeps a rolling history for about 10 minutes.

V$SERVICE_STATS

This view displays sets of performance statistics by service name. In most cases, these individual values will be rolled up into statistics otherwise seen via the well-known V$SYSSTAT view. These statistics can be used to determine the amount of work done by a specific servicefor example, you can see the statistic db block changes broken down as shown in Listing 10.1.

Listing 10.1. Snapshot of V$SERVICE_STATS

 SQL> compute sum of value on report SQL> break on report skip 1 SQL> select service_name, stat_name, value   2  from v$service_stats where stat_name = 'db block changes'; SERVICE_NAME    STAT_NAME                           VALUE --------------- ------------------------------ ---------- TS10GXDB        db block changes                        0 SYS$USERS       db block changes                   585733 SYS$BACKGROUND  db block changes                  1240945 TS10G           db block changes                        8                                                ---------- sum                                               1826686 SQL> clear compute SQL> select name, value from v$sysstat   2  where name = 'db block changes'; NAME              VALUE ----------------- ---------- db block changes  1826686 

Note that these queries were run on an inactive database. There were minimal, if any, changes between the queries; thus, the total value as shown by the SQL*Plus COMPUTE clause and the rolled-up value from V$SYSSTAT for this statistic have the same value from both views. Also, the history of this data is presented via DBA_HIST_SERVICE_STAT.

View and Column Naming Inconsistencies

You may have noticed that the S is missing at the end in the view DBA_HIST_SERVICE_STAT even though it is connected to the V$SERVICE_STATS view, which does have a S at the end. Although we wish that Oracle Corporation would be consistent in naming its views, we will doubtless have to live with such small inconsistencies. This is not newmany a DBA has been tripped up by the inconsistently named DBA_DATA_FILE.FILE_ID and V$FILESTAT.FILE# columns, both of which mean the same thing and participate often in joins!

V$SERVICE_EVENT

This view displays sets of aggregated wait counts and wait times for each Wait event statistic, again by service name. As before, in most cases, these individual values will be rolled up into statistics seen via the well-known V$SYSTEM_EVENT view. Listing 10.2 shows an example for database reads and writes.

Listing 10.2. Snapshot of V$SERVICE_EVENT

 SQL> compute sum of total_waits on report SQL> compute sum of time_waited on report SQL> break on report skip 1 SQL> select service_name, event, total_waits, time_waited   2  from v$service_event   3  where event in ('db file parallel write','db file scattered read')   4  order by service_name, event; SERVICE_NAME    EVENT                     TOTAL_WAITS TIME_WAITED --------------- ------------------------- ----------- ----------- SYS$BACKGROUND  db file parallel write          52916        9698 SYS$BACKGROUND  db file scattered read           7279         585 SYS$USERS       db file scattered read           5462        1477                                           ----------- ----------- sum                                             65657       11760 SQL> select event, total_waits, time_waited   2  from v$system_event   3  where event in ('db file parallel write','db file scattered read')   4  order by event; EVENT                     TOTAL_WAITS TIME_WAITED ------------------------- ----------- ----------- db file parallel write          52916        9698 db file scattered read          12741        2063                           ----------- ----------- sum                             65657       11761 

As before, these queries were run on an inactive database, one after another, and the total waits and total time waited agree across these views. Expect some differences if you are running these queries on a busy system.

From Where do Database Writes Emanate?

The query of V$SERVICE_EVENT makes it clear that the db file parallel write event is associated only with background processes, while the db file scattered read event could originate from both background and foreground (or user) processes. In fact, by looking at V$SESSION_EVENT and V$SESSION, you can verify that the db file parallel write event, which is the Wait event signifying the writing to a database file, is present only in the DB writer processes. The only database writes that a user process is allowed are direct writes to the sort segments in the temporary tablespace.

V$EVENTMETRIC

This view displays values of Wait event metrics for the most recent and active 60-second interval and can be used to take a quick look at what happened in the last active minute. These values are built on a circular buffer in memory and are thus overwritten every minute or so. Note that on a quiet system, these values will live on past the minutethe start and end times for this quick snapshot can be seen from the BEGIN_TIME and END_TIME columns.

V$EVENT_HISTOGRAM

This is a very interesting view that displays a histogram of the number of waits, the maximum wait, and total wait time on an event basis. The histogram has buckets of time intervals from < 1 millisecond, < 2 milliseconds, < 4 milliseconds, < 8 milliseconds, and so on, in increasing powers of 2 up to 2^22 milliseconds. Note that the values are cumulative and reflect the current state since the instance started up. The bucket values for the histograms cannot be changed and the view is populated only when TIMED_STATISTICS is set. This is an excellent view to use when you want to see the spread of wait times for certain events since database startup. Listing 10.3 illustrates this.

Listing 10.3. Wait Event Histograms for Database Reads

 SQL> break on event skip 1 SQL> select event, wait_time_milli, wait_count   2  from v$event_histogram   3  where event in   4  ('db file sequential read','db file scattered read'); EVENT                     WAIT_TIME_MILLI WAIT_COUNT ------------------------- --------------- ---------- db file sequential read                 1      19403                                         2       6450                                         4        411                                         8       1853                                        16       2091                                        32        381                                        64        106                                       128         10                                       256          1 db file scattered read                  1      11193                                         2        451                                         4        291                                         8        184                                        16        394                                        32        218                                        64         84                                       128         24                                       256          1 

From Listing 10.3, one can conclude that a majority of single-block reads in the form of db file sequential read and multiblock reads in the form of db file scattered read events completed in 1 millisecond or less. You can see that during some unknown point of time, some indexed data-block reads (in the form of db file sequential read) took between 8 and 16 milliseconds. These values are acceptable if not excellent. Note the value 2091 for the 16-millisecond histogram; we will refer to this later in this chapter in the section titled "V$FILE_HISTOGRAM."

You can use this view to determine the existence of yet another issue. The SQL*Net message to client, SQL*Net more data to client, and SQL*Net break/reset to client events are normally considered idle events and are ignored. However, we have seen cases where these events have contributed significantly to performance issues, generally due to bad or misconfigured network hardware or saturated networks. In such cases, prior to Oracle Database 10g, there was no way to determine whether this was an issue by looking at the values from V$SYSTEM_EVENT, because the values there are averaged out. Now, with the V$EVENT_HISTOGRAM, you can look at the spread of wait times for these idle events. Recording these values before and after a problem occurs and looking at oddities can help point to the existence of network- and interface-related issues. Consider the example in Listing 10.4.

Listing 10.4. Event Histogram for SQL*Net Events

 SQL> column event format a30 SQL> select event, wait_time_milli, wait_count   2  from v$event_histogram   3  where event like 'SQL*Net %to client'; EVENT                          WAIT_TIME_MILLI WAIT_COUNT ------------------------------ --------------- ---------- SQL*Net message to client                    1       4866 SQL*Net more data to client                  1       1582 SQL*Net break/reset to client                1         60 

You can see from this snippet that most SQL*Net messages to the client completed within a millisecond, so this is not an issue.

Note that we did not include SQL*Net message from client in this discussion because this event caters to both program response time as well as the idle time, or think time, awaiting user input at a keyboard. Thus, an interactive session with a long think time when the user is inactive at the keyboard would produce an event with a large wait time. This is not necessarily a problem, because the user is inactive. The values will be skewed, however, and hence cannot be considered at the aggregated system level. If you observe large counts and wait times for this supposedly idle event in a batch or non-interactive program, however, then you should investigate this further. In summary, not all idle events are actually idle and thus do deserve some attention. You will need to see such idle events in context.

Breaking Out Certain Wait Events

One of the shortcomings that we have noticed with both the V$EVENT_HISTOGRAM and V$SYSTEM_EVENT views is the fact that the latch free event is lumped into one counter, unlike with the V$SESSION_EVENT view, which breaks out the latch free event into classified events such as latch: cache buffers chains, latch: cache buffers lru chain, latch: library cache, and latch: shared pool to name a few. Such classification becomes important when trying to resolve latch-related issues, and we wish Oracle Corporation had carried this classification forward into all levels. If you are actively using the V$SESSION_WAIT view, then you will notice that even this view breaks out the latch free and enqueue events.

V$FILEMETRIC and V$FILEMETRIC_HISTORY

These views expose file I/O metrics. The number and average duration of reads and writes to specific data files over the last 10 minutes is seen from V$FILEMETRIC, and V$FILEMETRIC_HISTORY stores these values for the past hour or so in an in-memory circular buffer.

V$FILE_HISTOGRAM

This is another interesting view that displays a histogram of single-block reads on a per-file basis. The histogram has buckets of time intervals from < 1 millisecond, < 2 milliseconds, < 4 milliseconds, < 8 milliseconds, and so on, in increasing powers of 2 up to 2^22 milliseconds. Note that the values are cumulative and reflect the current state since the instance started up. The bucket values for the histograms cannot be changed and the view is populated only when TIMED_STATISTICS is set. This is an excellent view to use when you want to see the spread of single-block read wait times by individual files since database startup. You will of course need to join this to DBA_DATA_FILES to get the filenames. Listing 10.5 provides an example.

Listing 10.5. File-Wise Read Response Histogram

 SQL> break on file_name skip 1 SQL> column file_name format a25 wrap SQL> select file_name,   2  singleblkrdtim_milli, singleblkrds   3  from v$file_histogram v, dba_data_files d   4  where v.file# = d.file_id   5  and file# = 3; FILE_NAME                 SINGLEBLKRDTIM_MILLI SINGLEBLKRDS ------------------------- -------------------- ------------ /u04/app/oracle/product/1                    1         7540 0.1.0/db_1/oradata/TS10G/ sysaux01.dbf                                              2         2811                                              4          196                                              8         1221                                             16         1449                                             32          206                                             64           51                                            128            8 

This example shows that most single-blocks read to file #3 (seen normally as db file sequential read events), which happen to be the SYSAUX01.DBF file, completed within 1 to 2 milliseconds. So what is the difference between this histogram and the previous one seen from V$EVENT_HISTOGRAM for db file sequential read? V$FILE_HISTOGRAM breaks out the histogram values for the db file sequential read alone into component values on a per-file basis. Thus, if you see odd patterns in the histogram values specifically for the db file sequential read event in the V$EVENT_HISTOGRAM, then you might be able to investigate further as to which file actually caused this skew in values. You saw in the earlier example that there were 2,091 single-block reads waits, each taking 16 milliseconds. The question as to which of these files contributed to this can be determined from the following query in Listing 10.6.

Listing 10.6. Determine Which File Contributed to the 16-Millisecond Response

 SQL> column file_name format a25 wrap SQL> select file_name, singleblkrdtim_milli, singleblkrds   2  from v$file_histogram v, dba_data_files d   3  where v.file# = d.file_id   4  and singleblkrdtim_milli = 16; FILE_NAME                 SINGLEBLKRDTIM_MILLI SINGLEBLKRDS ------------------------- -------------------- ------------ /u04/app/oracle/product/1                   16          623 0.1.0/db_1/oradata/TS10G/ system01.dbf /u04/app/oracle/product/1                   16            3 0.1.0/db_1/oradata/TS10G/ undotbs01.dbf /u04/app/oracle/product/1                   16         1477 0.1.0/db_1/oradata/TS10G/ sysaux01.dbf /u04/app/oracle/product/1                   16            8 0.1.0/db_1/oradata/TS10G/ users01.dbf /u04/app/oracle/product/1                   16           10 0.1.0/db_1/oradata/TS10G/ example01.dbf 

You can deduce from the numbers in the preceding code that the SYSAUX01.DBF file contributed most to the 16-millisecond read response time. You might use this route to investigate whether a particular data file is contributing to the high values, and decide to move it around to spread the I/O. As before, this was run on a quiet system; expect some difference if the system is active and busy. The principle in general will work, though.

V$TEMP_HISTOGRAM

This displays the same kind of histogram as V$FILE_HISTOGRAM, but specifically for TEMPFILE files, which are used for temporary tablespaces. This view can be used in the same way to locate TEMPFILE hot spots.

V$ENQUEUE_STATISTICS

This view displays statistics about the number and duration of enqueue (lock) requests for each type. The columns EQ_NAME, EQ_TYPE, and REQ_DESCRIPTION make for interesting reading. In prior versions, Oracle Corporation exposed the enqueue name in various views such as V$LOCK and V$SESSION_WAIT using bitmap values, and you had to translate them using CHR and BITAND functions. This new view provides the same information in clear text with good explanations and is worth looking at when you encounter locking issues. Interestingly enough, there is another view named V$ENQUEUE_STAT, which is a summarized, less-detailed version of this informative view. The latter view was available in Oracle 9i, and provides the same information as in Oracle Database 10g. We advise using V$ENQUEUE_STATISTICS in Oracle Database 10g instead, and recommend that you go directly to the internal X$KSQST view in older versions because this contains accurate numbers.

V$OSSTAT

This is a new, very useful view that displays the system-utilization statistics from the operating system. One row is returned for each system statistic, with statistics ranging from informational values such as NUM_CPUS (number of CPUs) as well as metrics such as number of bytes sent and received, CPU ticks used, and so on. These are shown in Listing 10.7.

Listing 10.7. Snapshot of V$OSSTAT

 SQL> set numwidth 12 SQL> column stat_name format a30 SQL> select * from v$osstat; STAT_NAME                             VALUE    OSSTAT_ID ------------------------------ ------------ ------------ NUM_CPUS                                  2            0 IDLE_TICKS                         11884181            1 BUSY_TICKS                           632793            2 USER_TICKS                           455624            3 SYS_TICKS                            177169            4 IOWAIT_TICKS                         393176            5 AVG_IDLE_TICKS                      5941049            7 AVG_BUSY_TICKS                       315355            8 AVG_USER_TICKS                       226774            9 AVG_SYS_TICKS                         87511           10 AVG_IOWAIT_TICKS                     195516           11 OS_CPU_WAIT_TIME                    1985600           13 RSRC_MGR_CPU_WAIT_TIME                    0           14 IN_BYTES                        21408317440         1000 OUT_BYTES                        8398913536         1001 FS_IN_BYTES                     11696422912         1002 FS_OUT_BYTES                     4750868480         1003 AVG_IN_BYTES                    10704158720         1004 AVG_OUT_BYTES                    4199456768         1005 AVG_FS_IN_BYTES                  5848211456         1006 AVG_FS_OUT_BYTES                 2375434240         1007 

Changes in Existing Views

In addition to introducing new views, Oracle Database 10g has also added or changed some existing ones, and we highlight the relevant ones here. You have seen that the size of some of the columnssuch as those holding instance, user, and other nameshave been increased in this version. Many RAW(4) columns have been expanded to RAW(8). Wait class information (columns WAIT_CLASS_ID, WAIT_CLASS, and WAIT_CLASS#) has been added to many views that describe events, such as V$EVENT_NAME, V$SESSION, and V$SESSION_WAIT.

Changes in V$SESSION

The very popular V$SESSION view has had a large number of columns added, essentially merging all the columns previously available in V$SESSION_WAIT, such as EVENT, P1, P2, P3, and their raw equivalents. As well, information about blocking sessions is made available within V$SESSION using the intuitively named BLOCKING_SESSION column. This will obviate the need to join the V$SESSION view to the V$SESSION_WAIT view to obtain current session-wait information or to the V$LOCK view to obtain blocking-session information. See the very simple SQL in Listing 10.8, which can detect the details of any session whose DML has been blocked by another.

Listing 10.8. Blocked-Session Details

 SQL> select sid, blocking_session, username, blocking_session_status,   2  program, event, seconds_in_wait   3  from v$session   4  where blocking_session_status = 'VALID';  SID BLOCKING_SESSION USERN BLOCKING_SE PROGRAM ---- ---------------- ----- ----------- -------------------- EVENT                                   SECONDS_IN_WAIT --------------------------------------- ---------------  146              134 HR    VALID       PLSQLDev.exe enq: TX - row lock contention                       132 

The SQL detects that an Oracle login user, HR, whose session ID is 146 and who is executing using the PLSQLDev.exe program, has been blocked for row update by another session, 134, and that the session has been waiting for 132 seconds. The event that describes this waiting is enq: TX row lock contention, which is a broken-out detail of the enqueue wait. In previous versions, you had to join V$LOCK, V$SESSION_WAIT, and V$SESSION to obtain the same information.

Changes in V$SQL and V$SQLAREA

Another major addition has been to the V$SQL and V$SQLAREA views; they now have additional columns named APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, and JAVA_EXEC_TIME that denote time waited for these different classifications. This makes it easy to pick out the "Top-N" resource consumers by whatever class of resource required. If you have scripts that use these views, you should change them to take advantage of this bonus!

We will discuss a number of other performance-related views in detail in the subsequent section and chapters, depending on their relevance. We will continue to make reference to the views described in this section as required later on.

Changes in Other Views

You haven't even looked at the other views, such as DBA_ and ALL_ views, that matter for performance, except to mention a few in passing. You can always use SQL and your favorite operating-system text-processing utilities to draw up and compare lists of views for both an Oracle Database 10g instance and an older Oracle 9i instance. Look up the new views in the Oracle Database 10g Reference Manual. MetaLink is slowly introducing an in-depth analysis of a number of them online as well.