- Save 10% on selected children’s books, compliments of Amazon Family Promotion exclusive for Prime members .
Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning: A Practical Guide to Performance Diagnostics and Tuning (Oracle Press) Paperback – 1 Jul 2004
|New from||Used from|
- Choose from over 13,000 locations across the UK
- Prime members get unlimited deliveries at no additional cost
- Find your preferred location and add it to your address book
- Dispatch to this address when you check out
Special offers and product promotions
Customers who bought this item also bought
Enter your mobile number or email address below and we'll send you a link to download the free Kindle App. Then you can start reading Kindle books on your smartphone, tablet, or computer - no Kindle device required.
To get the free app, enter your mobile phone number.
If you are a seller for this product, would you like to suggest updates through seller support?
From the Back Cover
Identify and Resolve System Bottlenecks Quickly
Troubleshoot, diagnose, and optimize your Oracle database efficiently and successfully every time. This exclusive Oracle Press guide explains how to take full advantage of the revolutionary Oracle Wait Interface (OWI) to quickly pinpoint--and solve--core problems and bottlenecks, and increase productivity exponentially. Get extensive details on all the OWI features, including the wait event views, their applications, and the extended SQL trace file. This invaluable resource will help you maximize the most advanced diagnostics tool available and minimize processing time.
- Identify performance problems using wait event statistics
- Monitor session-level wait events and collect historical data for root cause analysis
- Interpret common I/O-related wait events
- Diagnose and solve problems related to locking and serialization
- Analyze latency-related wait events
- Identify and resolve bottlenecks in the Oracle Real Application Clusters environment
- Learn about the Oracle Database 10g revolutionary approach to performance diagnostics and tuning
About the Author
Richmond Shee (Olathe, KS) is a Senior Database Architect for Sprint Corporation, with more than 14 years of RDBMS experience, including 8 years working with Oracle databases. Richmond has also spent 2 years working with the Oracle Core Technology Group. He oversees the Performance Consultation Team, mentors DBAs and helps set the direction for implementing Oracle RDBMS technology throughout the company. Richmond leads the tuning efforts on all of Sprint’s most critical databases. Among his many accomplishments, he pioneered the use of the Oracle Wait Interface at Sprint. He also invented a patent-pending wait-based tuning tool. This tool is being deployed throughout Sprint Corporation & Sprint PCS. He frequently speaks at the Kansas City OUG, contributes to the Oracle-L listserver, and volunteers his time by mentoring DBAs in the Kansas City area.
Kirtikumar Deshpande (Kirti Deshpande) has been working in the Information Technology field for over 20 years, including more than seven years as an Oracle DBA. Although a Biomedical Engineer by profession, he has chosen Information Technology as his career. He currently works with Verizon Information Services as a Senior Oracle Database Administrator and is a frequent contributor on the Oracle-L and LazyDBA listservers. He can be reached at kirti_deshpande64hotmail.com.
K. Gopalakrishnan (Gopal) is an award-winning coauthor ("Oracle Author of the Year 2005"-Oracle Magazine) of Oracle Wait Interface (Oracle Press). He also authored Oracle Database 10g Real Application Clusters also from Oracle Press (Nov 2006). He has published articles in various international Magazines and is recognized as one of the leading database performance tuning experts in the world. Oracle Technology Network also recognizes him as an Oracle ACE. Gopal has worked with several of the biggest and busiest databases on the planet and has deep experience in the performance management and tuning of very large online transactional databases.
Top customer reviews
To date I have been looking for a single resource (book) to use for diagnosing performance issues. This is it! It doesn't matter if you are on Oracle 7 or 10g, this solution gathers the information you need with little overhead.
The book isn't selling third party products like many other books in its league.
Not finished the book yet but I already feel more confident in troubleshooting performance issues.
The book covers many performance tuning diagnostic methods which do not rely on this pack, but there are no warnings about the ones that do. In the interests of fairness, I would suggest this as an update to a subsequent edition.
Most helpful customer reviews on Amazon.com
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 184.108.40.206). 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.
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?".