What is this book about?
How many of us have been woken at some antisocial hour because a SQL statement that was performing well
suddenly started behaving badly because of a changed execution plan? In most circumstances, Oracle would probably
recommend that customers suffering repeatedly from such issues investigate the use of SQL Plan Baselines, Oracle’s
strategic feature for stabilizing execution plans in a production environment. This book introduces TSTATS, the name
given by one of my clients to a controversial technology that bypasses altogether the need to gather object statistics on
a production system and can be thought of as an alternative to SQL Plan Baselines.
Although Chapter 6 and Chapter 20 are dedicated to the issue of deploying and managing statistics in a
production environment, the main theme of the book is tuning SQL for Oracle databases. There are other excellent
books covering Oracle database performance in general, but this book is focused specifically on SQL tuning.
In my opinion, the key to finding the best solution to a SQL tuning problem usually lies in fully understanding the
problem that you are addressing as well as in understanding the technologies at your disposal. A large portion of this
book is dedicated to a study of how the cost-based optimizer (CBO) and the runtime engine work and how to obtain
and interpret diagnostic data, such as the execution plans displayed by functions from the DBMS_XPLAN package.
Some readers may find it surprising is that I make very little reference to the famous 10046 and 10053 traces that
form the foundation of many books on SQL performance. In practice, I use a 10046 trace about once a year and a
10053 trace about once every three years. In my opinion, there are easier ways to diagnose the vast majority of SQL
tuning problems, and I will explain the techniques that I use in detail.
You will notice that I have used the term “in my opinion” twice in the last few paragraphs, and I will use it
many more times throughout this book. The fear of alienating audiences dissuades many authors from expressing
controversial opinions, particularly if they are not perfectly aligned with Oracle-recommended practice. But there is
often more than one way to look at a topic, and I hope this book provides you with something new to think about.
But don’t be too worried that this book is all about philosophy and grandstanding. There is a lot of technical
content in this book that you won’t find in other books or in blogs and plenty of examples to help you through.
Why did I write this book?
The process that led me to write this book began with a talk by Kyle Hailey (http://www.oraclerealworld.com/)
at the 2010 UK Oracle User Group (UKOUG) national conference. The topic of Kyle’s talk is immaterial, but he
mentioned en passant a book called SQL Tuning written in 2003 by Dan Tow and published by O’Reilly. I was sitting
next to Jonathan Lewis (http://jonathanlewis.wordpress.com/) in the audience, and Jonathan agreed with Kyle
that this was an excellent book, one of only a handful that he recommends on his blog. I felt obliged to buy the book
and can confirm that it is an outstanding publication.
The key messages of the book
As with most problems in life, solving a SQL tuning problem or a production instability problem can be made much
easier, and sometimes trivial, by fully understanding it. An obvious statement, perhaps, but I have lost count of
the number of times I have seen people trying to solve a SQL performance problem without understanding it. For
example, it may be that the best solution to a performance problem is to gather statistics. Perhaps you just need to
stop and restart the SQL. Perhaps you need to run the SQL Tuning Advisor and create a SQL profile. But don’t just pick
one of these options at random and work through the list when it doesn’t work. For example, if your SQL statement
includes a temporary table then the SQL Tuning Advisor is unlikely to be of much use because the temporary table
will be empty when the SQL Tuning Advisor runs. You need to begin by reviewing the SQL statement!
Why are so many problems with poorly performing SQL approached in a haphazard way? One reason is the
pressure that technicians are put under, often at antisocial hours, to do something quickly. The other reason is that
very few people have enough knowledge to approach a performance problem in a systematic way. I can’t help you
with the first of these two problems, but hopefully after reading this book you will at least have the knowledge and the
skill, if not always the time, to approach your performance problems in a systematic way, starting with the problem
and working towards a solution, rather than the other way around.
I want to end this introduction with a second message. Enjoy yourself! Enjoy reading this book and take pride
and pleasure in your work. It will take time to master all the principles in this book, but the journey will hopefully be a
rewarding one for your clients, your employers, and, above all, yourself