Introduction

‚“When the learned discern that the learning, which delights them, also delights the world, they love learning all the more. ‚½
‚ Kural : 399 Thiruvalluvar

Globalization of businesses creates new competitive landscapes , and the push to be the first to the market often compromises the conventional and proven application and database development paradigms . To gain a competitive edge, businesses leverage the latest computer hardware and storage technologies to capture and analyze a massive amount of data about their customers and spending patterns. This gives rise to very large databases, complex multitiered applications, and needless to say, performance issues. Database administrators are challenged as never before to troubleshoot and resolve performance issues that are always labeled ‚“database problems. ‚½

Prior to Oracle release 7.0.12, Oracle Corporation proposed the ratio-based method as a way to measure database performance. As a result, a model based on various ratios was developed to measure database performance. It was taught and used for a long time, and it is still practiced by some DBAs. However, it proved to be very inadequate in finding even the real bottlenecks in the system, much less finding the solutions. Despite the various ratios, the ratio-based method could not answer the question: what is causing the application to run slowly?

In Oracle release 7.0.12, Oracle Corporation introduced the wait-based method commonly known as the Oracle Wait Interface (OWI) to analyze the performance of the system. For the first time, DBAs could find out how much time processes spent on various resources and what resources were the bottlenecks in the system. The higher the wait time, the slower the process ‚ s response time. Now DBAs have a way to relate slow performance to the bottlenecks. The primary bottleneck can easily be identified, whether it is related to the application, database, or network. There is no need for guesswork. However, OWI knowledge was slow in reaching the mass DBA population. In the beginning, knowledge and correct usage of the wait-based performance diagnostics was limited to a handful of people at Oracle Corporation. Very little information was made available to others. Since then, Oracle Corporation has made substantial improvements to OWI, both in terms of the technology and knowledge transfer. It is proving to be an extremely dependable and reliable model for correctly identifying system bottlenecks and finding the corrective solutions. Oracle RDBMS releases 8, 8 i , 9 i , and 10 g have seen considerable improvements in the Oracle kernel to report wait times, and the wait-based model is maturing very rapidly .

During the past 10 years , several authors have written about methods and tools to tune and improve database performance. These range from SQL tuning to database internals. Unfortunately, the Wait Interface model lagged behind. This book is an effort to fill that void.

This book does not teach you how to tune a SQL statement or a PL/SQL program. It does, however, help you identify SQL statements and database structures that may need tuning. This book is for all levels of Oracle DBAs. We are confident that once you have read this book, you will begin using OWI to diagnose your next performance problem. When this happens, the case for writing this book rests.

We would love to hear your success stories and your comments, as well as criticisms and arguments. Learning is a never-ending process. By no means have we attempted to cover all the variables of performance tuning. We believe this is just the beginning. You can contact the authors by e-mail: [e-mail], [e-mail], and [e-mail].

How to Use This Book

Very rarely would one read a technical book from cover to cover in one sitting. Technical books are mostly used as references. However, we urge you to read all the chapters and appendixes in this book. You will certainly find something new or different in each chapter, regardless of your level of experience in Oracle Database tuning and performance troubleshooting.

This book has nine chapters and five appendixes as described next.

Chapter 1

Chapter 1 introduces you to the Oracle Wait Interface and lays the foundation for understanding why you should learn and use OWI. It explains why the hit-ratio based methodology does not work when it comes to diagnosing slow application response time.

Chapter 2

Chapter 2 discusses the OWI components in detail. It defines the Oracle wait event, describes the various OWI views and their applications, and explains how to use the extended SQL trace. You will learn where, when, and how to find the wait event information.

Chapter 3

Chapter 3 introduces you to the most commonly seen Oracle wait events. It describes these events in detail and shows what information Oracle provides to analyze these events. In addition, the chapter discusses how you can track CPU usage statistics.

Chapter 4

Chapter 4 explains why it is important to monitor and gather session-level wait event information and discusses the importance of historical performance data. It describes how you can put together a simple data collection tool to capture and store session-level Oracle wait event data.

Chapter 5

Chapter 5 discusses how you can diagnose and solve problems related to the most common I/O related wait events. The events discussed are the db file sequential read , db file scattered read, direct path read, direct path write, log file parallel write, db fil e parallel write, and control file parallel write .

Chapter 6

Chapter 6 presents an in-depth discussion of latch free , enqueue , and buffer busy waits wait events. You will learn just about all there is to learn about these wait events. It discusses the difference between a latch and an enqueue and explains how Oracle serializes access to memory structures. This chapter will teach you how to diagnose and solve problems related to locking and serialization. We believe at the present time there is no other publication with such comprehensive information on these wait events as presented in this chapter.

Chapter 7

Chapter 7 covers common latency-related wait events in detail. You will learn how to diagnose and solve problems related to log file sync, log buffer space, free buffer waits, write complete waits, log file switch completion, and log file switch (checkpoin t incomplete) wait events.

Chapter 8

Chapter 8 explains how buffer cache management works in an Oracle Real Application Clusters environment and discusses the Oracle wait events that are commonly seen in RAC environments. You will learn how to diagnose and solve problems related to global cache wait events.

Chapter 9

Chapter 9 introduces you to the Oracle Database 10 g new automated features for performance monitoring and diagnostics. It discusses different types of database statistics collected by Oracle Database 10 g . You will learn how Oracle collects performance statistics data and analyzes it to offer remedial solutions in terms of recommendations and advisories. You will also learn how the Automatic Workload Repository, Active Session History, and Automatic Database Diagnostics and Monitoring work. A series of Oracle Enterprise Manager screen shots will show how easy it is to find the root cause of performance issues.

Appendix A

Appendix A discusses Oracle diagnostic events. These events are different from Oracle wait events. You will learn how and when to use these diagnostic events.

Appendix B

Appendix B lists all the enqueue wait events in Oracle Database 10 g .

Appendix C

Appendix C introduces you to the utilities and procedures used in producing trace files for various data or memory dumps. Such trace files and dumps are often asked for by Oracle Support to diagnose the root cause of errors, such as ORA-0600 or ORA-7445.

Appendix D

Appendix D describes a method to access Oracle SGA directly through a non-SQL interface, such as a C language program. Such a method is the fastest for sampling performance related information in the SGA without introducing the overhead of parsing, latching, and so forth.

Appendix E

Appendix E lists the reference materials we used for this book.