cyan yellOW MaGenTa Black PanTOne 123 c BOOks fOR PROfessIOnals By PROfessIOnals® The eXPeRT’s VOIce® In ORacle Companion CCoovveerrss eBook Available Troubleshooting Oracle Performance OOrraaccllee DDaattaabbaassee 99iiRR22 tthhrroouugghh 1111ggRR11 T Dear Reader, r What do you do when your application isn’t running fast enough? You trouble- o shoot, of course. Finding the slow part of an application is often the easiest part u of the battle. The difficult part is finding a solution or, even better, avoiding the performance problem in the first place. Troubleshooting Oracle Performance b Troubleshooting helps by providing a systematic approach to addressing the underlying causes l of poor performance of applications based on the Oracle database engine. e Over the last decade I have spent a great deal of my time troubleshooting s performance problems. In writing this book, I hope to do three things: first, and most importantly, to share my experience in this area with you; second, to h show a methodical approach that avoids guesswork and helps you determine o Oracle beyond any doubt where the slow part of an application is; third, to explain o how the database engine processes SQL statements and show what features are available to ensure that SQL execution remains efficient. Specifically, this book t shows you how to do the following: i n • Identify performance problems using a systematic and repeatable approach. g • Configure the query optimizer to meet your application performance goals. Performance • Obtain and interpret execution plans as well as assess their efficiency. • Apply SQL tuning techniques such as hints, SQL profiles, stored outlines, O and SQL plan baselines. r • Minimize the impact of parsing without jeopardizing performance. a • Optimize data access, joins, and the physical design of your database. • Improve performance through parallel processing, materialized views, c and result caching. l e Whether you are a performance analyst, an application developer, or a database administrator, if you are involved in troubleshooting performance problems, P you will find something of use in this book. Christian Antognini e Methodically identify and solve performance problems r involving the Oracle database engine f o Companion eBook THE APRESS ROADMAP r m Troubleshooting Forecasting Expert Oracle Cost-Based Oracle Oracle Performance Oracle Performance Database Architecture Fundamentals a n See last page for details on $10 eBook version c e ISBN-13: 978-1-59059-917-4 Christian Antognini ISBN-10: 1-59059-917-9 SOURCE CODE ONLINE 90000 www.apress.com Antognini Foreword by Cary Millsap, chief executive of Method R Corporation, and Jonathan Lewis, author of Cost Based Oracle: Fundamentals Shelve in Databases/Oracle User level: Intermediate–Advanced 9 781590 599174 this print for content only—size & color not accurate 7" x 9-1/4" / CASEBOUND / MALLOY (1.1875 INCH BULK -- 616 pages -- 50# Thor) Antognini_9179FRONT.fm Page i Thursday, May 22, 2008 3:01 PM Troubleshooting Oracle Performance ■ ■ ■ Christian Antognini Antognini_9179FRONT.fm Page ii Thursday, May 22, 2008 3:01 PM Troubleshooting Oracle Performance Copyright © 2008 by Christian Antognini All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. ISBN-13: 978-1-59059-917-4 ISBN-10: 1-59059-917-9 ISBN-13 (electronic): 978-1-4302-0498-5 Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1 Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. Lead Editor: Jonathan Gennick Developmental Editor: Curtis Gautschi Technical Reviewers: Alberto Dell’Era, Francesco Renne, Jože Senegacnik, Urs Meier Editorial Board: Clay Andres, Steve Anglin, Ewan Buckingham, Tony Campbell, Gary Cornell, Jonathan Gennick, Matthew Moodie, Joseph Ottinger, Jeffrey Pepper, Frank Pohlmann, Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade, Tom Welsh Project Manager: Sofia Marchant Copy Editor: Kim Wimpsett Associate Production Director: Kari Brooks-Copony Production Editor: Laura Esterman Compositor: Susan Glinert Stevens Proofreader: Lisa Hamilton Indexer: Brenda Miller Artist: April Milne Cover Designer: Kurt Krames Manufacturing Director: Tom Debolski Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail [email protected], or visit http://www.springeronline.com. For information on translations, please contact Apress directly at 2855 Telegraph Avenue, Suite 600, Berkeley, CA 94705. Phone 510-549-5930, fax 510-549-5939, e-mail [email protected], or visit http:// www.apress.com. Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook Licensing web page at http://www.apress.com/info/bulksales. The information in this book is distributed on an “as is” basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. Antognini_9179FRONT.fm Page iii Thursday, May 22, 2008 3:01 PM A dédichi chésto libro a chí, che a rasón, i ga l’éva sü con mí perché a gó metú tròpp témp par scrival . . . a Michelle, Sofia, e Elia. Antognini_9179FRONT.fm Page iv Thursday, May 22, 2008 3:01 PM Contents at a Glance Forewords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix About the Technical Reviewers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxi Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxiii Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv About the OakTable Network. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxvii PART 1 Foundations ■ ■ ■ ■ CHAPTER 1 Performance Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 ■ CHAPTER 2 Key Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 PART 2 Identification ■ ■ ■ ■ CHAPTER 3 Identifying Performance Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 PART 3 Query Optimizer ■ ■ ■ ■ CHAPTER 4 System and Object Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 ■ CHAPTER 5 Configuring the Query Optimizer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 ■ CHAPTER 6 Execution Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 ■ CHAPTER 7 SQL Tuning Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247 PART 4 Optimization ■ ■ ■ ■ CHAPTER 8 Parsing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309 ■ CHAPTER 9 Optimizing Data Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339 ■ CHAPTER 10 Optimizing Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409 ■ CHAPTER 11 Beyond Data Access and Join Optimization . . . . . . . . . . . . . . . . . . . 459 ■ CHAPTER 12 Optimizing the Physical Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 527 iv Antognini_9179FRONT.fm Page v Thursday, May 22, 2008 3:01 PM PART 5 Appendixes ■ ■ ■ ■ APPENDIX A Downloadable Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 551 ■ APPENDIX B Bibliography . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 563 ■ INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 567 v Antognini_9179FRONT.fm Page vi Thursday, May 22, 2008 3:01 PM Antognini_9179FRONT.fm Page vii Thursday, May 22, 2008 3:01 PM Contents Forewords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix About the Technical Reviewers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxi Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxiii Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv About the OakTable Network. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxvii PART 1 Foundations ■ ■ ■ ■ CHAPTER 1 Performance Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Do You Need to Plan Performance? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Requirements Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Analysis and Design. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Coding and Unit Testing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Integration and Acceptance Testing. . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Do You Have Performance Problems? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 System Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Response-Time Monitoring. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Compulsive Tuning Disorder. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 How Do You Approach Performance Problems? . . . . . . . . . . . . . . . . . . . . 10 Business Perspective vs. System Perspective. . . . . . . . . . . . . . . . . . 10 Cataloging the Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Working the Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 On to Chapter 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 ■ CHAPTER 2 Key Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Selectivity and Cardinality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Life Cycle of a Cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 How Parsing Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Shareable Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Bind Variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Reading and Writing Blocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 On to Chapter 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 vii Antognini_9179FRONT.fm Page viii Thursday, May 22, 2008 3:01 PM viii ■ CONTENTS PART 2 Identification ■ ■ ■ ■ CHAPTER 3 Identifying Performance Problems . . . . . . . . . . . . . . . . . . . . . . . 35 Divide and Conquer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Analysis Road Map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Instrumentation vs. Profiling Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Instrumentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Application Code. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Database Calls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Profiling Application Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Concise Profiling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Detailed Profiling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 Tracing Database Calls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 SQL Trace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Structure of the Trace Files. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 Using TRCSESS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 Profilers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 Using TKPROF. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Using TVD$XTAT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 Profiling PL/SQL Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 Installing the Profiler. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 Installing the Output Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 Gathering the Profiling Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 Reporting the Profiling Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 The GUI Way . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 On to Chapter 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 PART 3 Query Optimizer ■ ■ ■ ■ CHAPTER 4 System and Object Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Overview of the Package dbms_stats . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 System Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 Data Dictionary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 Noworkload Statistics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 Workload Statistics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 Impact on the Query Optimizer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117