31 of 33 people found the following review helpful
1.0 out of 5 stars
Try before you buy, 28 Sep 2007
Download chapter 14 ("Using Statspack and the AWR report to tune waits and latches") from McGraw-Hill before you buy the book. The last line reads: "Rich Niemiec upgraded this chapter from 9i to 10g (painful!)", so it seems reasonable to use this chapter to evaluate the book. Here are some examples of the issues I have with this chapter:
For the "Log Buffer Space" wait, we are told: "When a change is made, the changed block is copied to the log buffer" Wrong. A typical redo record contains the smallest possible description of the changes made to the targeted block and associated undo block - and even that's not an accurate statement in 10gR2, which introduced "private redo threads" (and related statistics) for small transactions.
Under "Enqueue" we see the statement: "while you no longer get TM locks, which are table locks when you don't index foreign keys, be sure to still index them to avoid a performance issue." Even in 10g you still get TM/4 (or 5) locks on child tables if you update or delete parent keys and haven't indexed the foreign key columns; but a change made in 9i means the locks get released, or down-converted, sooner. (So the indexing decision is application dependent.)
We are told that "Buffer Busy Waits" may be waits for a buffer that is `being read into the buffer cache' - but that event was renamed "read by other session" in 10g.
We are told that dbms_scheduler runs job gather_stats_job to take an AWR snapshot - but the AWR collection is controlled by the mmon background process; gather_stats_job handles database statistics.
There are sundry discussions of internal mechanisms that are simply wrong. For example the following definition appears for short tables "fewer than 5 blocks for v7, fewer than 20 blocks for 8i, and 2% of the buffer cache for 9i and 10g" (and repeated for long tables). Apart from a couple of special cases for small caches the limit has been related to 2% of the buffer cache since at least Oracle 7.
A comment following an example of the "Load Profile" says: "a soft parse rate greater than 300/second could indicate program inefficiencies". But why 300; and does that limit change if we have 1,000 live sessions, or 5,000 live sessions? And where is the comment that should be there to tell us to cross-check with the instance statistics because the "soft parse" is virtually irrelevant if nearly all the parse calls were pre-empted by the session cursor cache. One number that "could" indicate a problem does not help people decide if they need to take action.
There are two "Top 5" examples (one from 9i the other from 8i) but neither gives any indication of the snapshot duration, number of CPUs, or number of sessions. When the author says that after one day's tuning of the 9i database "well over five terabytes of reads were eliminated in a 24 hour period" it certainly sounds good; but we have no idea of the difference that made to the users or the system. With no idea of scope the commentary is pretty meaningless.
Switching to the subjective viewpoint, I was surprised by some of the omissions. You always need to cross-check and counter-balance various parts of the Statspack/AWR report, and some of the new details are an enormous benefit in this task. Some examples:
In that 9i "Top 5" section, the author states that part of the problem was "slow disk access"; but the average read time was 6.41 milliseconds, which isn't usually considered slow. If the example had come from 10g we could have had a demonstration at this point of how the reports of v$event_histogram and v$file_histogram can expand the average time into more useful information, perhaps showing wide variations in read times; but these important new tools have been given no more than a cursory reference later in the chapter.
The author also failed to point out that excess I/O can lead to high CPU usage and large numbers of buffer busy waits (both visible in the snapshot), which may allow you to discount the immediate significance of those two sets of figures. Nor did the author mention the "log file switch (checkpoint incomplete)" figures, which record relatively little time but could actually be much more visible to users than the high volume I/O, and therefore be a more urgent problem (possibly fixable in just a few minutes).
Revisiting "Buffer Busy Waits": we are told to check the buffer waits section (or v$waitstat) when we see buffer busy waits; but wouldn't this be a good point to cross-reference to the segment statistics (v$segstat) section of the report, which again merits only a cursory description and screen shot.
There are several comments about the system statistics "sorts (rows)", "sorts(disk)" and "sorts(memory)". But where is the information about the extremely useful "workarea executions" statistics that appeared in 9i to tell us about other heavy duty operations such as hash joins and bitmap merges, and gave us a way of splitting disc operations into "onepass", and "multipass"? Where is the discussion of how to interpret the "PGA Aggr Target Histogram" section of the report that appeared in 9i to tell you about the size of data and amount of disc activity due to these operations? That section of the report deserves far more than the one line saying: "The AWR shows statistics on ...".
Maybe Rich Niemiec ran out of space but, with focused editing, low value material (such as an arbitrary list of bugs that had been fixed by 10g) could have been replaced with some of the very useful stuff that appeared in 9i and 10g.
In summary - there are a number of observations in this chapter which are highly arguable, inadequately analysed, or simply wrong and it contains little to mark it out as having any real added value for a 10g DBA. It does not encourage me to buy the book.