Section 8.1. Deficiencies of Fixed View Data | Optimizing Oracle Performance

8.1 Deficiencies of Fixed View Data

Oracle's fixed views are invaluable. You'll see several good uses of V$ queries soon. For example, for every line of data that the Oracle kernel emits to a trace file, there can be thousands of operations that you'll never discover unless you examine your V$ data. However, Oracle's V$ fixed views contain several deficiencies which many Oracle performance analysts are not aware of. The following sections describe the deficiencies that my colleagues and I have encountered in attempts to use Oracle fixed view data as our primary performance-diagnostic data source.

8.1.1 Too Many Data Sources

It is possible to construct an approximate resource profile for a specified session with queries of fixed data. This chapter shows how. However, the resource profile is just the tip of the data you really need, and you won't know what drill-down you'll need next until after you've assessed the resource profile. Consequently, the only way to ensure that you'll have everything you might need is to collect everything you might need for the targeted time scope and action scope. Doing this with fixed view data is virtually impossible .

8.1.2 Lack of Detail

The documented Oracle fixed views make it intensely difficult to acquire several types of detailed data that are easy to acquire from extended SQL trace data. Using only Oracle's fixed views, for example, it is very difficult to:

Observe trends in durations of individual Oracle kernel actions
Attribute individual I/O calls to their target devices
Attribute capacity consumption to individual database calls
Determine recursion relationships among database calls

The vast majority of Oracle's fixed views reveal only statistics that are aggregated either by session (for example, V$SESSTAT ) or by instance (for example, V$SYSSTAT ). Aggregate statistics introduce unnecessary analysis complexity, because of course aggregates conceal details.

X$TRACE and V$SESSION_WAIT are notable exceptions that reveal in-process data. However, using X$TRACE at least through Oracle9 i release 2 is a bad idea because it is undocumented, unsupported, and unreliable. V$SESSION_WAIT is of course supported, but to acquire the same level of detail from V$SESSION_WAIT as you can get from an Oracle7 extended SQL trace file, you would have to poll the view at a rate of more than 100 queries per second. You can't do this with SQL (see Section 8.1.3). To acquire the same level of detail from V$SESSION_WAIT as you can get from an Oracle9 i extended SQL trace file, you would have to poll at a rate of 1,000,000 queries per second.

8.1.3 Measurement Intrusion Effect of Polling

Using SQL to poll Oracle fixed views imposes an overwhelming measurement intrusion effect upon the system. It is simply impossible to use SQL to acquire fine granularity operational statistics in real time. Example 8-1 illustrates the problem. Typical behavior on our 800-MHz Linux server is fewer than 50 polls per second on a 50-row V$SESSION fixed view:

 $  perl polling.pl --username=system --password=manager  sessions       50           polls     1000         elapsed   21.176   user-mode CPU   14.910 kernel-mode CPU    0.110       polls/sec   47.223

The verdict: you can't use SQL to poll even one small V$ view a hundred times per second.

Example 8-1. A Perl program that demonstrates a fundamental limitation of polling with SQL. Note that the program carefully parses only once and also uses array fetching instead of fetching one row at a time

 #!/usr/bin/perl     # $Header: /home/cvs/cvm-book1/polling/polling.pl, v1.6 2003/04/23 03:49:37 # Cary Millsap (cary.millsap@hotsos.com)     use strict; use warnings; use DBI; use DBD::Oracle; use Getopt::Long; use Time::HiRes qw(gettimeofday);     my @dbh;     # list of database connection handles my $dbh;     # "foreground" session database connection handle my $sth;     # Oracle statement handle     my $hostname = ""; my $username = "/"; my $password = ""; my %attr = (     RaiseError => 1,     AutoCommit => 0, ); my %opt = (     sessions    => 50,      # number of Oracle sessions     polls       => 1_000,   # number of polls on the v$ object     hostname    => "",     username    => "/",     password    => "",     debug       => 0, );     # Get command line options and arguments. GetOptions(     "sessions=i"    => $opt{sessions},     "polls=i"       => $opt{polls},     "debug"         => $opt{debug},     "hostname=s"    => $opt{hostname},     "username=s"    => $opt{username},     "password=s"    => $opt{password}, );     # Fill v$session with "background" connections. for (1 .. $opt{sessions}) {     push @dbh, DBI->connect("dbi:Oracle:$opt{hostname}", $opt{username}, $opt{password}, \ %attr);     print "." if $opt{debug}; } print "$opt{sessions} sessions connected\n" if $opt{debug};     # Execute the query to trace. $dbh = DBI->connect("dbi:Oracle:$opt{hostname}", $opt{username}, $opt{password}, \%attr); $sth = $dbh->prepare(q(select * from v$session)); my $t0 = gettimeofday; my ($u0, $s0) = times; for (1 .. $opt{polls}) {     $sth->execute(  );     $sth->fetchall_arrayref; } my ($u1, $s1) = times; my $t1 = gettimeofday; $dbh->disconnect; print "$opt{polls} polls completed\n" if $opt{debug};     # Print test results. my $ela = $t1 - $t0; my $usr = $u1 - $u0; my $sys = $s1 - $s0; printf "%15s %8d\n", "sessions", $opt{sessions}; printf "%15s %8d\n", "polls", $opt{polls}; printf "%15s %8.3f\n", "elapsed", $ela; printf "%15s %8.3f\n", "user-mode CPU", $usr; printf "%15s %8.3f\n", "kernel-mode CPU", $sys; printf "%15s %8.3f\n", "polls/sec", $opt{polls}/$ela;     # Disconnect "background" connections from Oracle. for my $c (@dbh) {     $c->disconnect;     print "." if $opt{debug}; } print "$opt{sessions} sessions disconnected\n" if $opt{debug};     _ _END_ _     =head1 NAME     polling - test the polling rate of SQL upon V$SESSION         =head1 SYNOPSIS     polling   [--sessions=I<s>]   [--polls=I<p>]   [--hostname=I<h>]   [--username=I<u>]   [--password=I<p>]   [--debug=I<d>]         =head1 DESCRIPTION     B<polling> makes I<s> Oracle connections and then issues I<p> queries of B<V$SESSION>. It prints performance statistics about the polls, including the elapsed duration, the user- and kernel-mode CPU consumption, and the number of polls per second exeucted. The program is useful for demonstrating the polling capacity of an Oracle system.         =head2 Options     =over 4     =item B<--sessions=>I<s>     The number of Oracle connections that are created before the polling begins. The default value is 50.     =item B<--polls=>I<p>     The number of queries that sill be executed. The default value is 1,000.     =item B<--hostname=>I<u>     The name of Oracle host. The default value is "" (the empty string).     =item B<--username=>I<u>     The name of the Oracle schema to which B<polling> will connect. The default value is "/".     =item B<--password=>I<p>     The Oracle password that B<polling> will use to connect. The default value is "" (the empty string).     =item B<--debug=>I<d>     When set to 1, B<polling> dumps its internal data structures in addition to its normal output. The default value is 0.     =back         =head1 EXAMPLES     Use of B<polling> will resemble the following example:       $ perl polling.pl --username=system --password=manager          sessions       50             polls     1000           elapsed   15.734     user-mode CPU    7.111   kernel-mode CPU    0.741         polls/sec   63.557         =head1 AUTHOR     Cary Millsap (cary.millsap@hotsos.com)         =head1 COPYRIGHT     Copyright (c) 2003 by Hotsos Enterprises, Ltd. All rights reserved.

8.1.4 Difficulty of Proper Action-Scoping

Most V$ data sources have no session label attribute. To see why this is a problem, imagine that the resource profile reveals that waits for latch free dominate its response time. V$LATCH shows that two different latches were accessed heavily during the user action's time scope. Which latch is responsible for the user action's response time? It could be one, the other, or even both. How will you determine whether the session you are monitoring is responsible for motivating the activity, or if it's just some other session that happened to be running at the same time? Learning the answers with only properly time-scoped V$ data at your disposal consumes significantly more analysis time than learning the answers from extended SQL trace data.

A similar argument cuts the other way as well. The Oracle kernel emits a latch free wait event only when a latch acquisition attempt spins and fails, resulting in a system call in which the Oracle kernel process voluntarily yields the CPU to some other process. Nothing appears in the trace file when a latch acquisition attempt results in an acquisition, even if the Oracle kernel process had to execute many spin iterations to acquire it [Millsap (2001c)].

The combination of extended SQL trace data and good V$ tools like Tom Kyte's test harness (described later in this chapter) provide much more capability than either a trace file or V$ output by itself.

8.1.5 Difficulty of Proper Time-Scoping

One of the nagging problems that motivated me to abandon the big www.hotsos.com fixed view diagnosis project was the incessant difficulty in acquiring properly time-scoped data. If an observation interval boundary occurs in the middle of an event, it is important to know how much of the event's duration should be included within the interval and how much should be discarded. For example, if you query V$SESSION_WAIT at time t and find a db file scattered read event in progress, then how can you determine how long the event has been executing? It appears impossible to know to within 0.01 seconds unless you can poll at a rate of 100 or more times per second.

Another annoyance is the problem of what to do if a session disconnects before you can collect all the fixed view data you needed at the end of the desired observation interval. If you don't query the various V$ views that contain session information before the disconnect takes place, then the data you need are lost forever. Again, fine-resolution polling would help solve this problem, but fine-resolution requires that you access Oracle shared memory contents through means other than SQL.

8.1.6 Susceptibility to Overflow and Other Errors

Another nagging problem is fixed views' susceptibility to overflow errors. The problem is that an n -bit counter variable can store only 2 ⁿ -1 distinct values. When an n -bit unsigned integer in the Oracle kernel takes on the value 2 ⁿ -1, then the next time it is incremented, its value becomes zero. Overflow errors cause a supposed " accumulator " statistic to have a smaller value than it had at some time in the past. If an Oracle kernel developer has chosen to regard a counter variable as a signed integer, then you may notice values that turn negative after getting very large. To repair overflow data is not complicated, but it's one more thing that analyses of V$ data sometimes require and that analyses of extended SQL trace data don't.

Other aggravations with erroneous statistics include issues with the Oracle statistic called CPU used by this session , including Oracle bug numbers 2327249, 2707060, 1286684, and others. When you can't trust your system's measurements of what should be the dominant consumer of response time on an optimized system, it puts a big dent in your progress.

8.1.7 Lack of Database Call Duration Data

Search Oracle's V$ view definitions and I believe you won't find an equivalent of the e statistic anywhere . Without knowing a database call's elapsed duration, it is impossible even to detect the existence of unaccounted-for time that should be attributed to the call. Of course, if you can't prove that unaccounted-for time even exists, then you certainly can't measure its duration. As I describe in Chapter 6, Chapter 9, and Chapter 12, quantifying a user action's unaccounted-for time is the key to being able to positively identify, for example, paging or swapping problems from viewing only operating system- independent Oracle data.

The absence of database call duration data from Oracle's V$ data creates an irony that I hope you'll enjoy with me. Some analysts regard the "problem of missing time" in trace files as proof that V$ data provide superior value to the performance analyst. But, remember, Oracle V$ data come from the same system calls that extended SQL trace data come from (the ones I explained in Chapter 7). Thus, Oracle V$ data suffer from the same "missing time" problems from which extended SQL trace files allegedly "suffer." Proving that V$ data are superior to extended SQL trace data because of the "missing time" issue is analogous to proving that it's safer to be in a room with a hungry bear if you'll just close your eyes.

8.1.8 Lack of Read Consistency

As if the problems you've read about so far weren't enough, the problem of read consistency was something of a technical sword in the heart of our ambition to create the "mother of all V$ analyzers." The root of the read consistency problem is that Oracle makes performance data available via peeks into shared memory, not through standard tables. Thus, Oracle fixed views don't use the standard Oracle read consistency model that uses undo blocks to construct a read-consistent image of a block at a specified point in the past.

Oracle Corporation can't impose the overhead of read consistency upon its fixed views. To do so would intensify the overhead of accessing those views so much that it would render the V$ views practically useless.

You have two choices for obtaining Oracle V$ data: either you can peek into shared memory yourself, or you can use SQL to peek via Oracle's published V$ fixed views. The peek-into- shared-memory yourself approach has the much touted benefit of avoiding a tremendous amount of extra SQL processing workload on your Oracle server (which is presumably already burdened with a performance problem). However, neither approach provides a read-consistent image of your performance data. When we query a V$ view, the output does not represent the system at a point in time. Rather, the output slurs over the duration of the query.

Reading a large chunk of memory is not an atomic operation. To construct a read-consistent image of a memory segment, you must either lock the segment for the duration of the query, or you must use a more complicated read consistency mechanism like the one the Oracle kernel uses for real tables. Otherwise, the output of the query may represent a system state that has never actually existed. Figure 8-1 illustrates the problem. A scan of a memory segment begins at time t and concludes at time t ₃ . A dark box indicates a memory location whose contents are being changed at a given time. A lightly shaded box indicates the memory location whose contents are being copied to the output stream at a given time. Because reading a large chunk of memory is not an atomic operation, the output stream can contain a state that has never actually existed in memory at any time in the past.

Figure 8-1. The problem caused by lack of read consistency: an output stream can contain a state that has never actually existed in memory at any time in the past

The magnitude of the read consistency problem increases with the execution duration of a snapshot. Imagine that fetching data for 2,000 Oracle sessions from a simple query upon V$SESSION motivates the sequence of events depicted in Table 8-1. The query's result set is not a snapshot, but a collection of rows that all represent slightly different system states smeared across the 0.40 seconds of the query's total elapsed time.

Table 8-1. The sequence of events motivated by a query of V$SESSION

Time	Event
0:00:00.00	`select sid from v$session` ; there are 2,000 sessions connected
0:00:00.01	First row of output is returned
0:00:00.12	Session number 1297 disconnects
0:00:00.26	The location in shared memory that contained information for session number 1297 no longer contains information about session 1297; hence, no data about session number 1297 (which was active at 10:00:00.00) is returned
0:00:00.40	Final row of output is returned

Of course, the result of a query without a read-consistency guarantee is prone to be incorrect. The problem compounds when you attempt to include multiple data sources in your snapshots. Imagine that you have decided that each operational data snapshot you need contains data from each of the following Oracle fixed views:

V$BH

V$DB_OBJECT_CACHE

V$FILESTAT

V$LATCH

V$LIBRARYCACHE

V$LOCK

V$OPEN_CURSOR

V$PARAMETER

V$PROCESS

V$ROLLSTAT

V$ROWCACHE

V$SESSION

V$SESSION_EVENT

V$SESSION_WAIT

V$SESSTAT

V$SQL

V$SQLTEXT

V$TIMER

V$TRANSACTION

V$WAITSTAT

You would love to believe that all of the data collected during a single snapshot actually represent a single instant in time. However, it's not true. For fixed views with only a small number of relatively nonvolatile rows, this is not a big problem. But for fixed views with thousands of rows, you can create strange results with simple SELECT statements. The problem is even worse if you have such a long list of fixed views across which you wish to construct a snapshot. If these were real Oracle tables, you would probably use the following technique to force several queries to behave as though they were participants in a single atomic event:

 set transaction readonly; select * from v$bh; select * from v$db_object_cache; ... select * from v$waitstat; commit;

However, this strategy won't work for V$ fixed views because they're not real tables. Regardless of how you collect the data for your snapshot, the data will be slurred over the duration of the snapshot collection query set. The time-state of the first row of the V$BH query will differ from the time-state of the last row of the V$WAITSTAT query by the accumulated duration of these statements' executions. The duration in this example will likely be more than a whole second. No program can scan gigabytes or even hundreds of megabytes of memory in a single atomic operation.

It is very difficult to do time-based correlation among data sources, even for data collected within a single snapshot. The problem, of course, takes on even more complexity if you introduce operating system statistics into the collected data set.

Top