Sql query optimization and profiling

Question

Let's say I have poorly performing query. How do you usually go about sql optimization? What are the first things I should look in query execution plan? Is there a good article or book about this?

Answer 1

In order to make use of the execution plan (which is a description of how the database will implement your request), one has to understand what options are available to it (the database) and make a judgement on whether the choice made by the optimizer was the correct one. This requires a lot of knowledge and experience.

For the work I'm doing (ETL processing), performance "problems" usually falls into one of two categories:

1. The query takes a long time because reading lots of data takes lots of time :)
2. The optimizer made a mistake and picked the wrong execution plan

For (1), I have to decide whether I can restructure the data differently so I scan less data. Indexes are seldom of use since I'm interested in large enough subsets to make indexed access slower than a full table scan. For example, I might store a horizontal subset of the data (last 30 days) or a vertical subset of the data (10 columns instead of 80), or an aggregate of the data. Either way will reduce the size of the data to make an increase in processing speed. Of course, if the data is used only once, I've just moved the problem elsewhere.

For (2), I usually start by checking "Cardinality" (num rows) at the top line in the xplan. If I know that my query returns 5,000,000 rows, but it says 500, I can be pretty sure that the optimizer messed up somewhere. If total cardinality is in the right ball park, I start from the other end instead and check each step of the plan until I find the first big estimation error. If the cardinality is wrong, the join method is probably wrong between that table and the next, and this error will cascade through the rest of the plan.

Google for "Tuning by cardinality feedback", and you will find a paper written by Wolfgang Breitling who describes (in a much better way) the abobve approach. It's a really good read!

Also, be sure to hang around Jonathan Lewis Blog. if there is something about the Oracle optimizer he doesn't know, it's not worth knowing. He has written the best book on the subject as well. Check out Cost-Based Oracle fundamentals. If I could send one book back in time to myself, this would be it.

Expert Oracle Database Architecture by Tom Kyte, (the man behind "Ask tom"), is an awesome read as well. My first read of this book was a disappointment, because I was looking for "tuning tips" and found none. On my second read I realized that by knowing how the database works, you can eliminate entire classes of performance problems by "designing for performance" from start instead of "adding" performance at the end :)

SQL Tuning by Dan Tow, is another awesome read for the fundamentals of how exactly one could determine the optimal execution plan. That knowledge can be used as a way of troubleshooting an execution plan (or forcing the optimizer into doing it your way).

If you made it this far, now would be a good time to fiddle around with hints. Not before.

Answer 2

The Performance Tuning Guide is a great place to start but Jonathan Lewis's Cost Based Oracle Fundamentals is the canonical reference on what the optimizer is doing and why. Depending on the complexity of the problem, CBO Fundamentals may be radical overkill, though.

As a first pass, if you look at the estimated cardinality of each step in the plan, it's useful to ask whether that cardinality is reasonable. If the cardinalities are reasonable, it's likely that Oracle is picking the most efficient plan and you need to look for other approaches to tuning the query. If the cardinalities are wildly incorrect, on the other hand, it's likely that Oracle has chosen a poorly performing query plan and that something about the statistics needs to be adjusted.