ebook img

Troubleshooting Oracle Performance, 2nd Edition PDF

718 Pages·2014·10.834 MB·English
Save to my drive
Quick download
Download
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview Troubleshooting Oracle Performance, 2nd Edition

Antognini RELATED Troubleshooting Oracle Performance, 2nd Edition is a systematic guide to diagnosing and resolving performance problems in database-backed applications involving Oracle Database. It’s a book written for developers by a developer, who has learned by doing. Christian Antognini brings a decade and a half of experience to his topic. The book gives his systematic approach to solving database application performance problems, and it helps you plan for performance as you would for any other application requirement. This second edition has been rigorously updated to cover the latest developments in Oracle Database 11g Release 2 through Oracle Database 12c. What do you do when your database application isn’t running fast enough? You troubleshoot, of course. Finding the slow part of an application is often the easy part of the battle. It’s finding a solution that’s difficult. Troubleshooting Oracle Performance, 2nd Edition helps by providing a systematic approach to addressing the underlying causes of poor database application performance. The author freely shares his experience while explaining the underlying foundations of how SQL statements are executed by the data- base engine. You’ll be able to draw a solid foundation of theory and shared experience as you face head-on the performance challenges in your daily work. The Independent Oracle User Group (IOUG) is a not-for-profit organization that provides support and education for professional users of Oracle products. Most but not all of our Members are Database Administrators or other professionals who use Oracle database products and want to know that Oracle hears their feedback. We make sure your voice matters! Apress Media LLC is a technical publisher devoted to meeting the needs of IT professionals, software developers, and programmers, with more than 1,000 books in print and electronic formats. Apress provides high-quality, no-fluff content that helps serious technology professionals build a comprehensive pathway to career success. ISBN 978-1-4302-5758-5 Shelve in 55999 Databases/Oracle User level: Intermediate-Advanced SOURCE CODE ONLINE 9781430257585 SECOND EDITION www.it-ebooks.info For your convenience Apress has placed some of the front matter material after the index. Please use the Bookmarks and Contents at a Glance links to access them. www.it-ebooks.info Contents at a Glance Foreword by Jonathan Lewis ��������������������������������������������������������������������������������������������xix Foreword by Cary Millsap ��������������������������������������������������������������������������������������������������xxi Foreword from the First Edition ���������������������������������������������������������������������������������������xxiii About the Author ��������������������������������������������������������������������������������������������������������������xxv About the Technical Reviewers ��������������������������������������������������������������������������������������xxvii Acknowledgments �����������������������������������������������������������������������������������������������������������xxix Acknowledgments from the First Edition ������������������������������������������������������������������������xxxi Introduction �������������������������������������������������������������������������������������������������������������������xxxiii ■ Part I: Foundations ���������������������������������������������������������������������������������������1 ■ Chapter 1: Performance Problems ������������������������������������������������������������������������������������3 ■ Chapter 2: Key Concepts �������������������������������������������������������������������������������������������������19 ■ Part II: Identification �����������������������������������������������������������������������������������49 ■ Chapter 3: Analysis of Reproducible Problems ���������������������������������������������������������������51 ■ Chapter 4: Real-Time Analysis of Irreproducible Problems ������������������������������������������103 ■ Chapter 5: Postmortem Analysis of Irreproducible Problems ���������������������������������������151 ■ Part III: Query Optimizer ���������������������������������������������������������������������������167 ■ Chapter 6: Introducing the Query Optimizer ������������������������������������������������������������������169 ■ Chapter 7: System Statistics �����������������������������������������������������������������������������������������191 ■ Chapter 8: Object Statistics �������������������������������������������������������������������������������������������207 v www.it-ebooks.info ■ Contents at a GlanCe ■ Chapter 9: Configuring the Query Optimizer ������������������������������������������������������������������273 ■ Chapter 10: Execution Plans ������������������������������������������������������������������������������������������299 ■ Part IV: Optimization���������������������������������������������������������������������������������357 ■ Chapter 11: SQL Optimization Techniques ���������������������������������������������������������������������359 ■ Chapter 12: Parsing �������������������������������������������������������������������������������������������������������419 ■ Chapter 13: Optimizing Data Access �����������������������������������������������������������������������������449 ■ Chapter 14: Optimizing Joins ����������������������������������������������������������������������������������������529 ■ Chapter 15: Beyond Data Access and Join Optimization �����������������������������������������������575 ■ Chapter 16: Optimizing the Physical Design �����������������������������������������������������������������655 ■ Part V: Appendix ���������������������������������������������������������������������������������������679 ■ Bibliography ������������������������������������������������������������������������������������������������������������������681 Index ���������������������������������������������������������������������������������������������������������������������������������687 vi www.it-ebooks.info About IOUG Press IOUG Press is a joint effort by the Independent Oracle Users Group (the IOUG) and Apress to deliver some of the highest-quality content possible on Oracle Database and related topics. The IOUG is the world's leading, independent organization for professional users of Oracle products. Apress is a leading, independent technical publisher known for developing high-quality, no-fluff content for serious technology professionals. The IOUG and Apress have joined forces in IOUG Press to provide the best content and publishing opportunities to working professionals who use Oracle products. Our shared goals include: Developing content with excellence  Helping working professionals to succeed  Providing authoring and reviewing opportunities  Networking and raising the profiles of authors and readers  To learn more about Apress, visit our website at www.apress.com. Follow the link for IOUG Press to see the great content that is now available on a wide range of topics that matter to those in Oracle's technology sphere. Visit www.ioug.org to learn more about the Independent Oracle Users Group and its mission. Consider joining if you haven't already. Review the many benefits at www.ioug.org/join. Become a member. Get involved with peers. Boost your career.     www.it-ebooks.info Introduction Oracle Database has become a huge piece of software. This not only means that a single human can no longer be proficient in using all the features provided in recent versions, it also means that some features will rarely be used. Actually, in most situations, it’s enough to know and take advantage of a limited number of core features in order to use Oracle Database efficiently and successfully. This is precisely why this book covers only the features that, based on my experience, are necessary to troubleshoot most of the database-related performance problems you will encounter. The Structure of This Book This book is divided into four parts: Part 1 covers some basics that are required to read the rest of the book. Chapter 1, “Performance Problems,” explains not only why it’s essential to approach performance problems at the right moment and in a methodological way, but also why understanding business needs and problems is essential. It also describes the most common database-related design problems that lead to suboptimal performance. Chapter 2, “Key Concepts,” describes the operations carried out by the database engine when parsing and executing SQL statements and how to instrument application code and database calls. It also introduces some important terms that are frequently used in the book. Part 2 explains how to approach performance problems in an environment that uses Oracle Database. Chapter 3, “Analysis of Reproducible Problems,” describes how to identify performance problems with the help of SQL trace and PL/SQL profilers. Chapter 4, “Real-time Analysis of Irreproducible Problems,” describes how to take advantage of information provided by dynamic performance views. Several tools and techniques that can be used with them are also introduced. Chapter 5, “Postmortem Analysis of Irreproducible Problems,” describes how to analyze performance problems that happened in the past with the help of Automatic Workload Repository and Statspack. Part 3 describes the component that is responsible for turning SQL statements into execution plans: the query optimizer. Chapter 6, “Introducing the Query Optimizer,” provides an overview of what the query optimizer does and how it does it. Chapters 7 and 8, “System Statistics” and “Object Statistics,” describe what system statistics and object statistics are, how to gather them, and why they are important for the query optimizer. Chapter 9, “Configuring the Query Optimizer,” covers a configuration road map that you can use to find a good configuration for the query optimizer. Chapter 10, “Execution Plans,” describes in detail how to obtain, interpret, and judge the efficiency of execution plans. xxxiii www.it-ebooks.info ■ intRoduCtion Part 4 shows which features are provided by Oracle Database to execute SQL statements efficiently. Chapter 11, “SQL Optimization Techniques,” describes the techniques provided by Oracle Database to influence the execution plans that are generated by the query optimizer. Chapter 12, “Parsing,” describes how to identify, solve, and work around performance problems caused by parsing. Chapter 13, “Optimizing Data Access,” describes the methods available to access data and how to choose between them. Chapter 14, “Optimizing Joins,” discusses how to join several sets of data together efficiently. Chapter 15, “Beyond Data Access and Join Optimization,” describes advanced optimization techniques such as parallel processing, materialized views, and result caching. And Chapter 16, “Optimizing the Physical Design,” explains why it’s important to optimize the physical design of a database. Intended Audience This book is intended for performance analysts, application developers, and database administrators who are involved in troubleshooting performance problems in applications using Oracle Database. No specific knowledge in optimization is required. However, readers are expected to have a working knowledge of Oracle Database and to be proficient with SQL. Some sections of the book cover features that are specific to programming languages such as PL/SQL, Java, C#, PHP, and C. These features are covered only to provide a wide range of application developers with specific information about the programming language they’re using. You can pick out the ones you’re using or interested in and skip the others. Which Versions Are Covered? The most important concepts covered in this book are independent of the version of Oracle Database you’re using. It’s inevitable, however, that when details about the implementation are discussed, some information is version-specific. This book explicitly discusses the versions currently available from Oracle Database 10g Release 2 to Oracle Database 12c Release 1. They are as follows: • Oracle Database 10g Release 2, up to and including version 10.2.0.5.0 • Oracle Database 11g Release 1, up to and including version 11.1.0.7.0 • Oracle Database 11g Release 2, up to and including version 11.2.0.4.0 • Oracle Database 12c Release 1, version 12.1.0.1.0 Be aware that the granularity is the patch set level, and therefore, changes introduced by security and bundle patches aren’t discussed. If the text doesn’t explicitly mention that a feature is available for a specific version only, it’s available for all those versions. Online Resources You can download the files referenced through the book from http://top.antognini.ch. At the same URL, you will also find addenda and errata as soon as they are available. You can also send any type of feedback or questions about the book to [email protected]. xxxiv www.it-ebooks.info ■ intRoduCtion Differences between the First and the Second Editions The main goals set for the revision of the book were the following: • Add content about Oracle Database 11g Release 2 and Oracle Database 12c Release 1. • Remove content about Oracle9i and Oracle Database 10g Release 1. • Add content that was missing in the first edition (for example, features like hierarchical profiler, active session history, AWR, and Statspack). • Add information about PHP in the parts that cover features that are specific to programming languages. • Reorganize part of the material for better readability. For example, splitting the chapter about system and object statistics in two. • Fix errata and generally enhance the text. xxxv www.it-ebooks.info Part I Foundations Chi non fa e’ fondamenti prima, gli potrebbe con una grande virtú farli poi, ancora che si faccino con disagio dello architettore e periculo dello edifizio. He who has not first laid his foundations may be able with great ability to lay them afterwards, but they will be laid with trouble to the architect and danger to the building.1 —Niccoló Machiavelli, Il principe. 1532. 1Translated by W. K. Marriott. Available at http://www.gutenberg.org/files/1232/1232-h/1232-h.htm. www.it-ebooks.info Chapter 1 Performance Problems Too often, optimization begins when an application’s development is already finished. This is unfortunate because it implies that performance is not as important as other crucial requirements of the application. Performance is not merely optional, though; it is a key property of an application. Not only does poor performance jeopardize the acceptance of an application, it usually leads to a lower return on investment because of lower productivity of those using it. In fact, as shown in several IBM studies from the early 1980s, there is a close relationship between performance and user productivity. The studies showed a one-to-one decrease in user think time and error rates as system transaction rates increased. This was attributed to a user’s loss of attention because of longer wait times. In addition, poorly performing applications lead to higher costs for software, hardware, and maintenance. For these reasons, this chapter discusses why it is important to plan performance, which are the most common design mistakes that lead to sub-optimal performance, and how to know when an application is experiencing performance problems. Then, the chapter covers how to approach performance problems when they occur. Do You Need to Plan Performance? In software engineering, different models are used to manage development projects. Whether the model used is a sequential life cycle like a waterfall model or an iterative life cycle like the one used with agile methodologies, an application goes through a number of common phases (see Figure 1-1). These phases may occur once (in the waterfall model) or several times (in the iterative model) in development projects. Integration and Requirements Analysis and Coding and Acceptance Analysis Design Unit Testing Testing Figure 1-1. Essential phases in application development If you think carefully about the tasks to carry out for each of these phases, you may notice that performance is inherent to each of them. In spite of this, real development teams quite often forget about performance, at least until performance problems arise. At that point, it may be too late. Therefore, the following sections cover what you should not forget, from a performance point of view, the next time you are developing an application. 3 www.it-ebooks.info

See more

The list of books you might like

Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.