Oracle Wait Interface - A Practical Guide to Performance Diagnostics Tuning

Richmond Shee
Kirtikumar Deshpande
K Gopalakrishnan

McGraw-Hill /Osborne
2100 Powell Street, 10th Floor
Emeryville, California 94608
U.S.A.

To arrange bulk purchase discounts for sales promotions, premiums, or fund-raisers, please contact McGraw-Hill/ Osborne at the above address. For information on translations or book distributors outside the U.S.A., please see the International Contact Information page immediately following the index of this book.

Oracle Wait Interface: A ‚ Practical Guide to Performance Diagnostics & Tuning

Copyright ‚ 2004 by The McGraw-Hill Companies, Inc. (Publisher). All rights reserved. Printed in the United States of America. Except as permitted under the Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of Publisher.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates .

Screen displays of copyrighted Oracle software programs have been reproduced herein with the permission of Oracle Corporation and/or its affiliates.

Excerpts of copyrighted Oracle user documentation have been reproduced herein with the permission of Oracle Corporation and/or its affiliates.

1234567890 FGR FGR 01987654

ISBN 0-07-222729-X

Publisher
Brandon A. Nordin

Vice President & Associate Publisher
Scott Rogers

Editorial Director
Wendy Rinaldi

Acquisitions Editor
Lisa McClain

Project Editor
Jenn Tust

Acquisitions Coordinator
Athena Honore

Technical Editors
Scott Gossett, Kyle Hailey, John Kanagaraj,
Craig Shallahamer, Graham Wood

Copy Editor
Sally Engelfried

Proofreader
Marian Selig

Indexer
Jack Lewis

Composition
Apollo Publishing Services

Series Design
Jani Beckwith, Peter F. Hancik

Cover Series Design
Damore Johann Design, Inc.

This book was composed with Corel VENTURA Publisher.

Information has been obtained by Publisher from sources believed to be reliable. However, because of the possibility of human or mechanical error by our sources, Publisher, or others, Publisher does not guarantee to the accuracy, adequacy, or completeness of any information included in this work and is not responsible for any errors or omissions or the results obtained from the use of such information.

Oracle Corporation does not make any representations or warranties as to the accuracy, adequacy, or completeness of any information contained in this Work, and is not responsible for any errors or omissions.

With gratitude to Jesus Christ for the gifts of life
and wisdom, and to my beloved wife, Jody.
‚Richmond Shee

To my family, who motivated
me to write and kept my spirits up.
‚Kirtikumar Deshpande

To my brother Dhans, who is the inspiration for
my life; to my parents and my beloved country.
‚K Gopalakrishnan

About the Authors

Richmond Shee is a Senior Database Architect for Sprint Corporation (www.sprint.com), a global integrated communications provider serving more than 26 million customers in over 100 countries . Richmond has worked with relational databases since 1984. He 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 is pioneering the use of the Oracle Wait Interface at Sprint. He also invented a patent-pending wait-based performance data collector. He is a recognized presenter at the International Oracle Users Group , and he speaks frequently at the Kansas City OUG. He can be reached at [e-mail].

Kirtikumar Deshpande (Kirti) has been working in the information technology field for over 24 years, including more than 10 years as an Oracle Database Administrator. He holds Bachelor of Science (Physics) and Bachelor of Engineering (Bio-Medical) degrees. He co- authored an Oracle Press book titled Oracle Performance Tuning 101 , published in May 2001. He has presented papers in the local Oracle user group meetings and at national and international Oracle user group conferences. He currently works for Verizon Information Services (www.superpages.com) as a Senior Oracle Database Administrator. He can be reached at [e-mail].

K Gopalakrishnan (Gopal) is a Principal Consultant with Oracle Solution Services (India), specializing exclusively in performance tuning, high availability, and disaster recovery. He is a recognized expert in Oracle RAC and Database Internals and has used his extensive expertise in solving many vexing performance issues all across the world for telecom giants, banks, and universities. He is also a prolific writer and frequently publishes articles in the Oracle Internals journal. He has more than eight years of experience backed by a degree in Computer Science and Engineering from the University of Madras, India. He can be reached at [e-mail].

About the Technical Editors

Scott Gossett is a Technical Manager for Oracle Corporation ‚ s Advanced Technology Solutions organization, specializing in performance and high availability. Prior to becoming a technical manager, Scott was a Senior Principal Instructor for Oracle Education for over 12 years, primarily teaching Oracle Internals, performance tuning, RAC, and database administration classes. In addition, Scott Gossett is the architect and one of the primary authors of the Oracle Certified Masters exam.

Kyle Hailey has worked for Oracle on and off since 1990. He started in Unix support, ported Oracle version 6 onto digital Unix machines, worked in Oracle France on performance and support problems for some of the largest European customers and benchmarks, and worked back in the U.S. in Oracle ‚ s kernel development group on performance issues. In between stints at Oracle he worked at a dot-com and at Quest software developing performance monitoring tools. Currently he works in the Enterprise Manager group at Oracle. His personal tuning website, including documentation and tools, can be found at http://oraperf. sourceforge .net.

John Kanagaraj is a Principal Consultant with DBSoft, Inc. He has been working with various flavors of Unix and Oracle since the mid-1980s, mostly as an Oracle DBA and System Administrator in various countries around the world. His specializations are Unix/Oracle performance management, backup and recovery, and system availability. John has been applying the Oracle Wait Interface since version 7 to solve Oracle performance issues with great success. He can be reached via his home page at http://www.geocities.com/john_sharmila.

Craig Shallahamer is a recognized Oracle server authority and ‚“an Oracle performance philosopher who has a special place in the history of Oracle performance management. ‚½ He is a keynote speaker, teacher, researcher, and publisher who specializes in improving Oracle performance management. He also founded the grid computing company BigBlueRiver. After nine years at Oracle Corporation, Craig started OraPub, Inc., in 1998. OraPub focuses on reactive and proactive Oracle performance management. Craig teaches performance management classes and was the key designer and engineer behind HoriZone, OraPub ‚ s web-based capacity planning service.

Graham Wood is an architect in database development at Oracle. Most of his 20 years of Oracle experience have been spent in performance- related areas, including designing and tuning large high-performance systems, benchmarking, and building monitoring tools (such as Statspack). More recently Graham has worked as part of the Oracle 10 g Manageability team tasked with simplifying the process of tuning the operation of the database.