Oracle Performance Tuning 101

Chapter 1: Introduction to Oracle Performance Management

---

Myth & Folklore
If you upgrade your system with faster CPUs, you will get better performance.

Fact
Upgrading your system with faster CPUs to gain better performance as an attempt to fix an existing performance problem (when the CPU is really not the bottleneck) will result in significant degradation in performance. This is because your CPUs got faster without a corresponding increase in the I/O system, resulting in an imbalanced system. For example, if you doubled the speed of your CPUs, jobs that were already hampered by I/O bottlenecks could potentially experience twice as much contention for I/O. More powerful CPUs will process jobs twice as fast, resulting in an increased demand for I/O resources. Do your homework before upgrading the CPUs.

---

Welcome to the world of Oracle Performance Management. Performance tuning engagements with Oracle systems have developed a reputation as part science, part art, and part wizardry. The book you are holding-and if you are like most DBAs you are probably holding it over your head in bed-is a product of our passion for Oracle and our desire to share accurate and relevant information. If fact, we think the information is so important that you won't hesitate to give up your first born before giving up this book. This literary work is also a response to the misconception that tuning is a secret affair practiced by Oracle magicians under the cover of darkness. But you can count on us to ward off all aspects of magic and superstition and make sound principles of Performance Management available to everyone. Though you may not be working on the multi-stage fuel propulsion system for the Space Shuttle or the docking interface module of the International Space Station, you can apply all of these principles to all of your systems. After all, Oracle Performance Management is not rocket science.

Oracle Performance Management is a step-by-step process of iteratively investigating, determining, and implementing tuning solutions using a proven methodology. You will be convinced that this is the right way to address and fix an Oracle performance problem by the time you read this book from cover to cover. We want to steer you away from the bad habits of simply throwing more memory at Oracle's shared memory areas. just because the instructor said to do something or a tuning expert told you so doesn't make it so.

We want to encourage a significant change in the way troubleshooting is done. We also want to encourage you into analysis of performance problems. At the end of the day, the goal is to remove the bottleneck, increase performance, and may be even spend some time with your family and have a life. The extra time might prompt you to ask questions like "What is Life? What is a family? Who are these people? Do I really have a family?" If you find yourself saying, "But I thought my co-workers were my family?" or "What is the meaning of life without an Oracle database?" we are just in time, as this book (along with some professional help) is probably required. But until you put these principles into action, you can at least start with some fresh air, water, sunlight, and sources of nutrition other than the ones from the vending machine. Then read on.

Our goal is to provide you with a way to optimize Oracle-based systems, not just tune a database or an instance. Additionally, we will debunk many common myths about system performance and tuning Oracle. Each chapter (like this one) will contain a section on the "Myth and Folklore" applicable to the content of that chapter. These are things that everybody "knows" but that aren't true. We will provide you with techniques to manage the various components of your entire system, not just arbitrarily increase the size of the Oracle shared pool area, database buffer cache, or the redo log buffer.

We will address issues specific to the major versions of Oracle, as well as common topics that are relevant to Oracle versions 7.3 and above. We will also look at the major platforms and provide information on areas of concern for each (when applicable). We will assist you with the dreaded chore of tuning applications, even in the cases where you can't quite get access to the SQL (as in packaged applications). We will show you things beyond just changing initialization parameters, simply creating additional indexes, or simply adding hints to change the execution plans of the queries.

One of the key differences between this book and other Oracle tuning books is the time-tested and field-proven methodology. This methodology looks at Performance Management as having a system-wide scope; it takes a holistic approach to Performance Management. While the New Age folks may have popularized the holistic approach, we can think of someone else who also approached helping people in a holistic manner by dealing with their body, soul, and spirit. For more information, visit www.orapub.com and read the paper "Total Performance Management (An introduction to the method)," by Craig A. Shallahammer.

By examining how Oracle, the operating system, and the application interact, it is possible to accurately pinpoint bottlenecks and identify tuning avenues that provide significant increases in performance. You need to alleviate the bottleneck(s) in your system to fix your problem, and the goal is to cure the disease, not just the symptoms.

Performance increases need to be measurable and perceptible by the user community. This book is dedicated to the idea that the bulk of possible performance gains can be achieved quickly and with minimal effort if you truly identify and understand the nature of your system, its constraints, and more importantly its bottlenecks. Managing performance on Oracle-based systems can be exciting, frustrating, and rewarding all at the same time. With the information in this book, you will be able to enjoy the exciting rewards of being a DBA while minimizing the level of frustration. And with this new job satisfaction, you will amaze your friends and confound your enemies.

What Is Tuning?

Tuning is a process of making a system meet one or more goals by purposefully changing one or more components of your system. More specific to Oracle performance, it is making purposeful component level changes to improve system performance-that is, increased throughput and decreased response time.

In its simplest form, tuning means providing the correct amount of resources to Oracle so that users are not kept waiting for data they need to do their jobs. All right, that seems straightforward enough. Let's just buy the biggest most robust server in the market. No, while that may forestall a problem, it will not prevent it. Again, you need to treat the disease not just the symptoms.

Sometimes supplying enough of the correct resources does not just mean throwing more memory or CPUs into a server. It may mean redesigning parts of the system or scheduling work and batch jobs appropriately to better utilize the existing resources in a uniform fashion. When tuning is done in the context of Performance Management, it takes into consideration all parts of the system and balances the needs of one part against others.

Needless to say, the list of tuning avenues is endless. That means you may have to think about all the issues in your environment. Some examples of such issues are how moving data files or striping a table will affect the utilization of your I/O controllers. Or, if you utilize NFS file systems to store some output files (say, from an export or a job), how will that impact network traffic? How does that in turn affect the network performance of data returned to interactive users? You must consider changes made to SQL statements and how they will affect the amount of data being manipulated at the client or at the server. Therefore, tuning in the context of Performance Management means maximizing the use of all system resources to improve performance.

Why Is Tuning Necessary?

So why tune? Consider an automobile. A manufacturer decides to create a really exciting high-performance car. He sends the specifications to a designer. The designer creates a blueprint calling for the finest composite materials and the most powerful lightweight engine. Now, between budget constraints and lack of availability of that lightweight engine, the builder has to use heavier materials and a different engine. The sales manager notifies the builder that two-seat sports cars or sport coupes are in vogue, so another change is made. Finally, the finished car is delivered. But does it perform the way it was conceived? No. As it ages, will it continue to perform as it did on the first day? No. When it is replaced with a newer car and if that car is used just for short trips within the neighborhood, can it be used as a racing car? No....