2/28/2002 -- As we get closer to March, my thoughts often turn to the upcoming Formula 1 racing season. Many of you in the U.S. may say that NASCAR is the way to race cars, but theres something about open-wheel racing on a street track that gets my blood pumping, which racing around in a circle (oval, whatever&) cant seem to do. Not to be completely focused on one type of racing, I also follow CART, which, ironically, also starts in March. So, here I am thinking about speed with a column to write. Whats the logical thing to do? Write a column on how improve the speed of your Oracle database.

I must be the one of the luckiest people in the world (in a quirky sort of way) because as Im thinking about speed I find on my desk two books from Oracle Press that talk about just that. The first is Oracle Performance Tuning 101 (ISBN: 0-07-219058-2) by Gaja Krishna Vaidyanatha, Kirtikumar Deshpande, and John A Kostelac, Jr. The second is Oracle High-Performance SQL Tuning (ISBN: 0-07-213145-4) by Donald K. Burleson. Both deal with improving the performance of your Oracle database, but from entirely different angles. However, you should know that neither book covers Oracle 9i specifically, which is not necessarily a bad things since many of the topics covered in the books still apply to the latest version of Oracles database.

Tuning Basics: You Begin a Race from the Start Line
Oracle Performance Tuning 101 is a great starting point on finding out what the tuning process involves and how it should be carried out. I have run across way to many database administrators whose tuning procedures revolve around responding to complaints from users and then fixing something that solves that problem but creates a whole new set. This is not to say that this may not occur anyway, but many DBAs fall into a reaction mode when dealing with database performance -- reading the first couple of chapters may help enlighten you to a new way of doing things. In this part of the book you will find out good answers to obvious questions that very many of us take for granted. Key among this is the emphasis presented that tuning requires a quantifiable goal in order to be effective.

Not all tuning will achieve better performance, as the authors outline in Chapter 2 where they state that "Tuning a database may make the database perform efficiently, but if the application, the I/O subsystem, and the operating system (OS) are not equally tuned" you may not get better performance. Those of you that have had to determine the cause of a bottleneck and perform some tuning can well appreciate the truth in this statement. The authors then proceed to introduce a methodology for tuning based on wait events in the database to tell you where problems exist.

Using wait events as a method to determine where you have bad performance is one possible approach. Finding out which resources processes are waiting to get access to will provide some information, but you also need to determine how efficiently the ones that they can have access to are working. As the authors also admin, Oracle wait events themselves are not the only measure operating system statistics, Oracle ratios, and other factors also need to be considered.

The remainder of Oracle Performance Tuning 101 deals with tuning different parts of Oracle, such as I/O, memory, as well as tuning for specific categories of data access, such as Online Transaction Processing (OLTP) databases versus decision support systems (DSS) or data warehouses, and even dealing with some guidelines on tuning the operating system. As a general guide on processes and tools to use for tuning Oracle, this book provides a good foundation, though not a lot of detail on the intricacies of each tool and the meaning of what you may find.

Efficient Aerodynamics Make the Car Run Better
Oracle High-Performance SQL Tuning deals with a specific element of getting better performance from your databases writing efficient SQL statements. It starts by outlining how SQL statements are processed in Oracle and the role of the optimizer in efficient execution of code. Details are provided on the different join operations and how they work, as well as the impact on some structures, such as VARRAYs and Nested Tables on the execution of SQL statements.

In writing Oracle High-Performance SQL Tuning, Donald Burleson seems to have asked himself the question "How can I explain the way SQL works in Oracle to allow the user to write better code?" and succeeded in doing so. Every possible way to improve your code, as well as description of what happens when specific changes are made, is detailed, demonstrated, analyzes, sliced, diced and crushed!! If this book got into any more detail, I think that I would wonder if I was being presented with the raw algorithms from the Oracle source code. However, while having all this data on the way things work, and ways to determine what is happening is great, how about telling us what should be done?

As a general rule, this book tells you how to find out what is happening and how to read the output of STATSPACK and other tools, without really telling you when to use the hints, materialized views, and other features. Sure, you are provided details on how to enable and configure these features but I did not get a lot of "Why should I do this?" questions answered. As such, Oracle High-Performance SQL Tuning is a great reference book that should be on a programmers and DBAs bookshelf -- it may not get dusted off a lot but will come in handy when you run across some code that just does not seem to perform as well as you think it should.

At the Finish Line
So which of these books should you buy? Here comes my favorite answer it depends. If you are new to the game of performance tuning, Oracle Performance Tuning 101 will provide a good foundation to build from. Just like every Formula 1 driver needs to start their career by racing go-karts or Formula Ford, you need to start your goal of getting a database performing efficiently by understanding the basics, which this book provides.

On the other hand, if you are an experienced DBA or developer and want to know how to tell when your SQL statements are not working as efficiently as they could, or what they are actually doing, then Oracle High-Performance SQL Tuning is a title worth having. Just like a driver trying to understand why a the car reacts a certain way when going around a specific corner, this book will provide insight into what your code is doing when it runs.

Well, I dont know about you, but I can already smell the exhaust and hear the roar of the engines. See you on the race track!