Sql query optimization and profiling

Question

Let's say I have a poorly executed query. How do you usually do sql optimizations? What are the first things I should look at in terms of query execution? Is there a good article or book about this?

Answer 1

To use the execution plan (which is a description of how the database will fulfill your request), you need to understand what options are available for it (the database) and decide whether the Optimizer was made correctly. This requires a lot of knowledge and experience.

For the work that I do (ETL processing), performance “problems” usually fall into one of two categories:

• The request takes a lot of time because reading a lot of data takes a lot of time :)
• The optimizer made a mistake and chose the wrong execution plan.

For (1), I have to decide if I can restructure the data in different ways, so I scan less data. Indexes are rarely used, since I'm interested in large enough subsets to make indexed access slower than a full table scan. For example, I can save a horizontal subset of data (last 30 days) or a vertical subset of data (10 columns instead of 80) or a collection of data. In any case, this will reduce the size of the data to increase processing speed. Of course, if the data is used only once, I just moved the problem elsewhere.

For (2), I usually start by checking "Cardinality" (num rows) on the top line in xplan. If I know that my query returns 5,000,000 rows, but it says 500, I can be sure that the optimizer has gone wrong somewhere. If the full power is in the right ball park, I start from the other end and check each step of the plan until I find the first big error of the estimate. If the power is wrong, the connection method is probably wrong between this table and the next, and this error will be cascaded through the rest of the plan.

Google for “Power Feedback Tuning,” and you'll find an article written by Wolfgang Breitling that describes (in a much larger sense) an unsightly approach. It is really good to read!

Also, be sure to keep up the Jonathan Lewis Blog . if he does not know something about the Oracle optimizer, it is not worth knowing. He wrote the best book on the subject. Check out Oracle Cost-Based Fundamentals . If I could send one book on time to myself, that would be so.

Tom Kyte's Oracle Database Architecture expert (the person behind Ask tom) is also amazing. My first reading of this book was a disappointment, because I was looking for “tuning tips” and did not find any. During my second reading, I realized that by knowing how the database works, you can eliminate entire classes of performance problems by “designing for performance” from the start, rather than “adding” performance at the end :)

Dan Tow's SQL Tuning is another awesome read for the basics of how you can pinpoint your optimal execution plan. This knowledge can be used as a way to troubleshoot a run-time plan (or force the optimizer to run it).

If you've made it this far, now is the time to play around with tips. Not earlier.

Answer 2

Performance Tuning Guide is a great place, but Jonathan Lewis Oracle's Value-Based Basics is a canonical reference to what the optimizer does and why. Depending on the complexity of the problem, the underlying principles of CBO can be a radical overflow, however.

As a first pass, if you look at an estimate of the power of each step in the plan, it is useful to ask if that power is reasonable. If the capacities are reasonable, it is likely that Oracle is choosing the most efficient plan, and you need to look for other approaches to tuning the query. If capacities are heavily dictated, on the other hand, it is likely that Oracle has chosen a poorly performing query plan and that something about statistics needs to be adjusted.

Answer 3

Oracle's performance tuning guide will be a good start.