Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I find myself writing more and more SQL queries at work (mostly Oracle 11g, but some SQL Server 2005-2008) and have started creating some pretty complex views for the rest of the analyst team.

They mostly all run quite well, but some of them not so well. So...

  • How do I learn to tune my queries?
  • Do I need to learn to read/act on Execution Plans?

And...

  • What books/websites can you recommend to learn about SQL query tuning 1) in general 2) specifically for Oracle 11g?

We have some good DBAs here, but they're just too swamped to help us tune every query we write.

Most of the books that I've found on Amazon for Oracle all seem to be geared toward overall database optimization and/or were written 8-10 years ago.

Thanks kindly for your advice :)

share|improve this question
    
For SQL Server, Grant's book: simple-talk.com/sql/performance/execution-plan-basics –  Aaron Bertrand Jul 9 '12 at 1:58
add comment

2 Answers 2

up vote 6 down vote accepted

I would say that learning how to understand explain plans is a vital skill in helping you to optimise SQL statements. I've found Christian Antognini's book, Troubleshooting Oracle Performance, very useful in detailing how these work, as well as explaining how to approach database optimization. While a few years old, you'll still learn a lot that's still relevant from it.

If you get more advanced you could look at Jonathan Lewis' books, but these are more in-depth so probably not a good starting point. Cost-based Oracle Fundamentals is quite old now, but much of it is still relevant. I've not read Oracle Core: Essential Internals for Troubleshooting yet, but it's received good reviews from the Oracle community.

As you're on 11g, if you have queries that take more than a few seconds I'd definitely recommend looking at the real-time SQL monitor (assuming you're appropriately licensed). As the name suggests, it shows the progress of an SQL statement in real-time, breaking down how long each operation has taken with details of rows fetched so far. It also keeps details of recently executed queries for a short while so can see how your changes affect a statement.

Oracle SQL Monitoring documentation: http://docs.oracle.com/cd/E11882_01/server.112/e16638/instance_tune.htm#PFGRF94543

Learning how to tune queries is something that will take time and practice. A few things I've learned:

  • Write queries to fetch as few rows as possible as soon as possible (e.g. you don't want to full scan a 10 million row table if you only need 100 rows from it)
  • Verify that number of rows expected in each step of an explain (expected) plan match those returned in the actual execution plan. When these are orders of magnitude different it's likely the optimizer isn't choosing the "best" plan.
  • Understand the principles of good indexing: how they work and when they should/shouldn't be used when executing a query (Richard Foote has a very in-depth blog discussing indexes in Oracle)

Mostly you'll learn by writing queries, looking at the (expected) explain plans and comparing these to the actual execution plans (either via tracing the query or using the SQL monitor). Then re-write the query, add/remove indexes, etc. and see how it affects the plans and execution times

share|improve this answer
add comment

As you are looking for Oracle specific information, I would recommend the Ask Tom blog at Oracle. In general, I think you will find the advice is not to tune the query. You will get good advice on how to write a query the optimizer can optimize. The Oracle documentation is online as well, and I usually look there for up to date information on Oracle. I haven't worked with SQLServer so I don't have any recommendations for it.

I haven't seen a lot new in the field of optimizing queries over the last few years. The big change is the deprecation of the rule based optimizer, which I can barely remember working with. However, I understand SQLServer still uses a rule based optimizer, so understanding its rules can help.

A tool where you can edit a query, execute it, and generate an explain plan helps in understanding what changes get you a query that performs well. I have had good results with AquaData Studio, and really like its tree view. SQL Developer should do as well.

As with any optimization, you need to have quantitative data about it's performance. Then you can determine if you actually did optimize it.

How to optimize a query depends in part on how the parser builds and optimizes the query. To a larger extent it depends on the distribution of the data you are querying. In an Oracle database if the result set makes up four percent or more of a table and are randomly distributed, a table scan is usually faster than an index.

I have worked to optimizing queries for a team of developers. Only two or three queries a year required serious optimization. Most queries are simple enough that they don't need optimization. The rest could usually be handled by adding missing join paths.

For Oracle there are three tunable settings which can significantly impact performance. The costing for index and data lookups interact to change the conditions under which an in index will or won't be used. These two can be tuned on a per session basis. The defaults are often not optimal. The other value controls how many alternatives the optimizer will try. Increasing this value often helps.

Optimization is significantly impacted by data distribution and volume. When optimizing it it best to use a copy of the production database, or at least a database with the same data distribution and volumes. I have severely broken the testing environment, optimizing a query for production order database. The testing and development databases had significantly different data distribution which caused the query to fail even with significantly less data.

share|improve this answer
    
You may want to consider putting more substance in here. This is actually borderline "not an answer" as it currently stands. –  JNK Jul 9 '12 at 12:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.