Product Description

Exclusively from Oracle Press 

The definitive guide to Oracle performance tuning has been fully expanded to cover every aspect of Oracle Database 10g. This comprehensive resource contains everything database administrators and developers need to know to keep Oracle Database 10g performing at its peak. Real-world case studies and in-depth examples illustrate best practices and highlight the latest tuning options.

Product Details

• Amazon Sales Rank: #112529 in Books
• Published on: 2007-08-01
• Original language: English
• Number of items: 1
• Binding: Paperback
• 967 pages

From the Back Cover

Maintain a High-Performance Oracle Database 10g Environment

Monitor, analyze, and optimize Oracle Database 10g using the tested methods and insider advice contained in this definitive volume. Written by Oracle Certified Master Richard Niemiec, Oracle Database 10g Performance Tuning Tips & Techniques is packed with detailed case studies, best practices, and code samples illustrating Oracle's new tuning features. Find out how to select optimal indexing options, effectively manage drives and disk arrays, troubleshoot queries, and reliably predict future performance. You'll also get detailed coverage of PL/SQL performance enhancements, initialization file tweaks, and the latest database surveying and reporting utilities.

"Rich does it again with a book that teaches to multiple levels in a bold and inspiring way." --Ari Kaplan, President, Independent Oracle Users Group

"There is no doubt about Rich's grasp on Oracle tuning and the way he simplifies intricate details in it." --Muhammad Shuja Uddin, Vice President and Head of Technical Support, Ora-Tech Systems; President, Pakistan Oracle Users Group

"Rich Niemiec is THE expert on Oracle tuning. This book is a ‘must have' for every Oracle DBA's bookshelf--there is something for everyone to learn. It is an excellent reference that proves useful over and over again." --Kimberly Floss, Manager, Database Services, PepsiCo; Former President, Independent Oracle Users Group

"Any book with Rich Niemiec as the author is a ‘must buy.' The man knows Oracle inside and out." --Karen Brownfield, Board of Directors, Oracle Applications Users Group

About the Author
Richard J. Niemiec is chief executive officer of TUSC, an Inc. 500 full-service consulting and training company specializing in Oracle technology. Rich has been named by Oracle Corporation as an Oracle Certified Master--one of only six originally so recognized around the world. He is the former president of the Independent Oracle Users Group (IOUG) and president of the Midwest Oracle Users Group. Rich has been named the Top Speaker of the IOUG Conference five times and is a member of the Entrepreneurial Hall of Fame.

Customer Reviews

Try before you buy
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.