OWI Limitations


As good as the OWI is, it has a few issues and limitations. We would do you a disservice if we left them out of our discussion because being aware of them will help you better understand how OWI works and how to use it. In the next few sections are some of the issues and limitations of OWI not discussed elsewhere.

No CPU Statistics

We introduced response time tuning methodology in Chapter 1, which takes into account the service time. Service time is the CPU time, but OWI does not offer the CPU time information. However, this information can be obtained from the V$SESSTAT view.

No End-to-End Visibility

One thing you may hear a lot from software vendors that sell proprietary performance monitoring tools is that OWI is limited to the database and has no end-to-end visibility. They emphasize the importance of end-to-end monitoring in today ‚ s complex N-tier application architectures, many involving middle- tier connection pooling, XA, Tuxedo, and so on. This is a valid concern. Performance- impacting bottlenecks exist in many areas outside the database instance. As we mentioned in Chapter 1, the end-user response time goes beyond the database response time. If you are to put yourself in the end-users ‚ shoes, you need to experience performance as they experience it ‚ the end-user response time. To calculate the end- user response time, you need to be able to access and account for the latencies in every layer of the application architecture, and this is not possible with OWI. This is where some of the third-party vendors find their niche.

A good end-to-end performance-monitoring tool is indeed appealing, but don ‚ t expect your management to rush into buying it ‚ for two main reasons. One, the vendor normally wants an arm and a leg for this kind of tool, and two, the jurisdiction issue. In most companies, the database department is responsible only for the database and not the hardware, network, middle tier products, application codes, and so on. So why should the database department spend the money from its budget to buy an end-to-end monitoring tool? Unless you have a corporate-level ‚“Uncle Sam ‚½ department, you are not likely to have such a tool. Even if you do have such a tool and are able to find problems in other areas, you are still limited to your jurisdiction and may find it difficult to bring about changes to correct the problems.

OWI is useful and powerful within the database, and seasoned DBAs practicing OWI will tell you that it is easy to deal with database- related performance problems. The challenge is when the performance problem is outside the database. However, OWI is still useful as a tool to conclusively prove whether the problem is within the database or not: if the problem is not within the database, it has to be somewhere else.

No Historical Data

A big missing piece from OWI is the historical data feature. When a performance issue is raised, DBAs have no way to find out what happened to the session or the database. Unfortunately, this happens far too often and DBAs are always in the dark. The only advice they can give to their unhappy customers is to rerun the job and offer to baby-sit it. Many DBAs resort to developing homegrown monitoring programs or using a high overhead utility, such as the extended SQL trace, to obtain historical data. (Chapter 4 discusses historical data collection in detail.)

This deficiency also opens a wide door of opportunity for third-party vendors, and needless to say, they cash in on it. The performance-monitoring tools market is a fierce battleground, with each vendor offering its bells and whistles. Oracle Corporation noticed this deficiency and is offering many good self-managing features in Oracle Database 10 g Release 1 that shake the battleground and cause many third-party vendors to tremble. Oracle has always been self-monitoring, it just hasn ‚ t always been self-tuning ‚ but that is changing in Oracle Database 10 g Release 1. The Active Session History (ASH) component collects OWI data every second. The Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM) provide history and advisories. You can read more about these features in Chapter 9.

Inaccuracies

One of the main complaints among the Wait Interface believers is that OWI statistics are not 100 percent accurate. This is true. Some of the inaccuracies are bug-related, and some are architecture-related. We won ‚ t discuss the bug-related inaccuracies here because Oracle will correct them sooner or later, but the architecture-related inaccuracies that are commonly sighted by OWI DBAs are listed here:

  • Inaccurate timing The centisecond unit of measurement used in versions prior to Oracle9 i Database is too coarse for today ‚ s fast computers. Many activities are completed in less than one centisecond, and those times are reported as zero. In this case, Oracle misses the time. Whenever an activity begins and ends across a centisecond tick mark but the elapsed time for the activity is less than one centisecond, Oracle counts the time as a full centisecond. In this case, Oracle overcharges the time. You should not be concerned with such under- and overcharging because in the big picture, over time, they do average out. However, Oracle resolved this problem with the microsecond timing, which was first introduced in Oracle9 i Database, to report timing more accurately. Unfortunately, the microsecond timing is not used across the board. Wait event timeouts and CPU timings in the V$SYSSTAT and V$SESSTAT views are still reported in centiseconds; you must remember to convert the CPU time into microseconds when calculating response time in Oracle version 9 i and 10 g if you are using the TIME_WAITED_MICRO for your wait time. However, you need not do so if you take your wait time from the TIME_WAITED column because it is already in the centisecond unit. The CPU_TIME in the V$SQL and V$SQLAREA views is reported in microsecond, but it is ‚“fake ‚½ in the sense that it is derived from centisecond. You will always see four significant zeros (1 centisecond * 10,000 = 1 microsecond). In other words, you will never see any row returning from the following query:

     select cpu_time 
    from v$sqlarea
    where cpu_time between 1 and 999;
  • Incomplete total wait time This comes from the fact that not all waits are accounted for. OWI reports waits that are architected in the Oracle kernel, so, obviously, the waits that are not architected are missed. This problem is more significant in earlier versions such as Oracle7 and Oracle8.0, but not as much in Oracle9 i Database and Oracle Database 10 g . In Oracle Database 10 g , Oracle keeps track of over 800 different wait events compared to only 104 in release 7.0.12.

  • Accounting discrepancies No, we are not talking about Enron or WorldCom! What we are talking about is that you may see different results from different views that are reporting on the same thing. For example, there are four views in Oracle9 i Database that show the magnitude of the buffer busy waits contention in the database instance. If you looked at the output from these views, as shown next, you will see how the results vary. Such discrepancies occur mainly because Oracle does not maintain read consistency for V$ views and not all X$ structures are protected by latches. Therefore, in a high concurrency environment, you are bound to see this kind of accounting discrepancy. You should note that this is not an OWI methodology problem, rather it is an Oracle architectural problem that affects V$ views and X$ structures.

     select a.value A, b.value B, c.value C, d.value D 
    from (select sum(bbwait) AS value
    from x$kcbwds) a,
    (select total_waits AS value
    from v$system_event
    where event = 'buffer busy waits') b,
    (select sum(count) AS value
    from v$waitstat) c,
    (select sum(value) AS value
    from v$segment_statistics
    where statistic_name = 'buffer busy waits') d;

    A B C D
    ---------- ---------- ---------- ----------
    48216984 48221287 48155238 40947971

There you have it. Once you begin working with OWI, you may run into the preceding issues or limitations. If you have been working with OWI for some time, you can probably list a few more. However, Oracle Database 10 g Release 1 does a better a job of keeping such discrepancies to a minimum.

Even with these limitations and issues, the V$ wait event views and the OWI methodology is the only way to quickly identify performance bottlenecks. In the grand scheme of things, if you have a batch job that runs for hours or days, a few seconds or even a few minutes of discrepancy is not going to misguide you down the wrong path when diagnosing performance problems. Using OWI, you will still be able to find the major bottlenecks that plague the session. Database administration and performance troubleshooting do not require rocket science precision.




Oracle Wait Interface
Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (Osborne ORACLE Press Series)
ISBN: 007222729X
EAN: 2147483647
Year: 2004
Pages: 114

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net