Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (Osborne ORACLE Press Series) First Edition

For the last couple years I debated whether or not to buy this book, or for that matter any additional book, on the topic of Oracle's wait event interface. I felt that between my copy of "Oracle Performance Tuning 101", the enhanced wait event documentation in the free Oracle 11g Performance Tuning Guide, and the notes that I have collected from various web sites, that there was no need for another book in my collection which solely described wait events. To put it lightly, I was surprised by the depth of detail, the range of topics, the clarity of concepts, and the level of accuracy in this Oracle Press book, something I have come to expect usually only of the books offered by Apress.This book, now five years old and covering through (apparently) Oracle 10.1.0.2, makes a very good attempt at covering a large percentage of the most frequently occurring of the 800+ wait events in Oracle 10g (there are nearly 1,000 in Oracle 11.1.0.7). Not only does the book provide a typically detailed explanation of what each wait event means, but it also describes the related system/session statistics, related system parameters, and what the background processes might wait on while the foreground process waits on a different wait event (for example LOG FILE PARALLEL WRITE and LOG FILE SYNC, respectively).Utilizing a wide range of system views while identifying performance problems and methods for correcting those performance problems, the book describes views including: v$system_event, v$session_event, v$session_wait, v$session_wait_history, v$system_wait_class, v$session_wait_class, v$sql_workarea, v$sql_workarea_active, v$latch_misses, v$sess_time_model, v$sql_shared_cursor, v$rowcache, x$kcqrs, v$resource, v$lock, v$session, v$segment_statistics, x$kcbfwait, x$kcbwds, x$ksolsfts, and several others.Many system parameters, both normal and hidden, were described throughout the book. Better definitions of the system parameters are hard to find from other sources, especially in the case of the hidden parameters. The hidden parameters (those beginning with an _) should not be altered without first consulting Oracle support - I believe that the book only mentioned this fact once near the end. Having a good description of the meaning of the hidden parameters might help determine if another DBA working with the same database instance attempted to implement some sort of `silver bullet' fix by adjusting one of the hidden parameters, but it might also be helpful when trying to troubleshoot a situation where the Oracle instance is behaving in an unexpected way. The book describes the following parameters: hash_area_size, sort_area_size, db_writer_max_writes, db_writer_processes, session_cached_cursors, _db_writer_chunk_writes, _log_io_size, _max_exponential_sleep, _max_sleep_holding_latch, _spin_count, _kghdsidx_count, _kgl_latch_count, _optim_peek_user_binds, _db_block_hash_latches, _db_block_hash_buckets, _wait_for_sync, _log_io_size, fast_start_mttr_target, _lm_dynamic_remastering, _db_block_max_cr_dba, _db_aging_freeze_cr, _db_aging_touch_time, _fairness_threshold, _bump_highwater_mark_count, _ash_enable, _ash_disk_write_enable, _ash_size, and several others.There are a few sections of the book which may benefit from a re-write to update the information for Oracle 10g R2, 11g R1 or 11g R2 (currently in beta) versions of Oracle. There are also a few sections of the book which could use a bit more supporting information, and a few more sections which contain information which might not be 100% correct based on information made available on the Internet and in various books in the last five years. Regardless of any small problems which might be present in the book, it is an excellent companion to the books "Oracle Performance Tuning 101", "Optimizing Oracle Performance", "Cost-Based Oracle Fundamentals", "Troubleshooting Oracle Performance", and to some extent "Forecasting Oracle Performance".Possibly interesting comments that I noted as I read the book:* Describes various methods of enabling a 10046 trace including: ALTER SESSION, DBMS_SUPPORT.START_TRACE, DBMS_SUPPORT.START_TRACE_IN_SESSION, DBMS_SYSTEM.SET_EV, DBMS_MONITOR.SESSION_TRACE_ENABLE, and ORADEBUG* The discussion on page 28 and 29 of the V$SESSION_WAIT view is limited, and does not answer the question of when the times reported should be ignored. For example, it would have been helpful if the book stated something to the effect of: if the STATE column indicates that the session is Waiting, Waited Unknown Time, Waited Short Time, then the WAIT_TIME column is mostly meaningless. If the entry is reporting a STATE of Waited Known Time, then the WAIT_TIME is the number of 1/100th of a second that the session waited on the event. If the STATE is Waiting, then the SECONDS_IN_WAIT column is the actual number of seconds that the session has been in the wait, otherwise that column shows the approximate number of seconds since the last wait started.* A SQL statement is provided to decode the P1 value of an ENQUEUE type wait.* The SQL*Net message to client description on page 65 seems to be incorrect - the wait actually ends as soon as the data is handed off to the server's network stack - the event cannot be used to detect network latency as stated: "... or the network latency delays may be causing the message delivery to take longer."* Pages 143-144 contain a very nice summary of the differences between latches and locks.* The book frequently uses the adjective "high" when stating the boundary values between OK and problem conditions for wait events, but the book usually does not indicate the contextual meaning of "high" - for example on pages 151 and 157.* On page 184, the book states "Unindexed foreign keys are no longer an issue starting in Oracle 9i Database." This does not appear to be 100% correct as sessions will still block other sessions with TM enqueues on the child table without the foreign key index if the primary key (or a column defined as unique) on the parent table, to which the foreign key on the child table points, is modified (even when modified to the same value).* Page 190 suggests creating all data segments with a minimum of 2 freelist groups, even in a single-instance database. It would have been helpful if the authors discussed some of the possible side effects related to multiple freelist groups, although a nice table was provided to show some of the performance benefits.* It might have been helpful if the book discussed how to correlate the average time spent on the LOG FILE SYNC wait event with the average time spent on the LOG FILE PARALLEL WRITE wait event - what does it mean if the average times are roughly the same, what does it mean if the average times are significantly different. What happens to the average LOG FILE SYNC wait time when the CPUs in the server become very busy?* Pages 202-203 state "There are many things that you and your system administrator can do to provide some relief to the log file sync wait by increasing the I/O throughput to the log files... placing your log files on raw device and binding the LUN in RAID 0 or 0+1 instead of RAID 5." This advice comes a couple dozen pages after the book warned to make certain that the redo logs need to be on very reliable mount points. With that in mind, avoid RAID 0, and use RAID 10 rather than RAID 0+1.* The book states that a log buffer larger than 1MB is likely oversized, yet on recent versions of Oracle the log buffer size is controlled by the granule size, meaning that a 14MB log buffer is not out of the ordinary for database instances with large SGA sizes (resulting in a 16MB granule size).* The book suggests several times to increase the number of DBWR processes even on systems which support asynchronous writes. For example, on page 210 the book suggests modifying the number of DBWR processes before increasing the buffer cache size when encountering FREE BUFFER WAITS. It would have been helpful if the book explored some of the potential problems related to increasing the number of DBWR processes.* Very well written section on delayed block cleanout.* The section describing wait events in a RAC environment contains helpful information that is useful in non-RAC environments, so don't skip this section of the book if RAC is not used in your environment.* The indented time model statistics section in the book looks nearly identical to the indented time model in the Oracle documentation, yet that section of the book lacks the descriptions of the time model statistics which are now present in the Oracle documentation.* The _ASH_ENABLED parameter mentioned on page 254 is not the Oracle approved method of disabling ASH collection. Oracle has since released an approved method of disabling ASH collection, see Metalink for the DBMS_AWR.DISABLE_AWR function.* Aside from the brief hint on page 259, the book fails to mention that the use of AWR/ADDM requires an additional cost license.* Appendix B contains a list of Oracle 10g enqueue wait events and a brief description of each wait event. Any description of the more obscure 10g enqueue waits is difficult to locate from other sources, including Oracle documentation, Metalink, and searches of the Internet.* Appendix C shows how to create a variety of dumps using both ALTER SYSTEM/SESSION and ORADEBUG. Dumps include data block dumps, file headers, control files, library cache, processstate, systemstate, and more.* The book often lists in which Oracle version a specific feature or change in behavior takes place.

Great book, even after all these years. I am doing a second pass of this book after gaining all the years of experience. I can appreciate the book even better. Anyone working with Oracle and is responsible for internals and tuning should read this book. Most of what the book deals with is relevant today.It covers in detail the wait events, which is one of the core topics when it comes to performance tuning. Any tuner should learn this subject also. The book is great laying the foundation in that subject area. For further details, please read Charles Hooper's review as it is extensive.PS. Hope the authors will come up with an updated version of the book.

This book provides plenty of suggestions on how to determine the bottlenecks of Oracle database performance. What the book lacks is suggestions on how to cure those bottlenecks. Sure, there are examples, but am I supposed to understand Oracle performance tuning through examples?The book would have been 100% better if it had concentrated on indexing. After all, better indexing is by far the quickest and best way to improve query performance.The book's information on Wait statistics is worth its weight in gold. Overall, the authors are highly knowledgeable and has highly granular knowledge of Oracle.

You can read a lot of books about Oracle performance, but you still can not understand why some things happen.This book gives you a clear vision of OWI - thing that is the start point of performance at database level. This book is great addition to your Oracle database knowledge, which gives you an answer on simple question "Why response time of database clients is slow?".

This is a core fundamental book for Oracle DBAs because it covers the details of the wait events that are the heart and soul of the AWR reports and the v$active_session_history table.

In my opinion it is worth buying this book because you can use it as a reference when you come across a Wait Event that you have never seen in your life. I think it's not a book to read from begin to end because for me it's impossible to memorize all those wait events.

I bought it as Kindle edition,But when i tried to download it says that is not compatible with my kindle paperwhite.And just allows me to download to my ipad. Seriously amazon??

If you are a mid level to seasoned DBA I would highly recommend this book. Absolutely great! Easy to read, and good documentation. Do not why I wasted my money on some of the other performance books I bought a few months ago. GREAT BOOK for Oracle DBAs. Applicable to both 9i release 2 and Oracle 10g. -- Roger Eisentrager at Meridian IQ ([...]