Learning to Optimize SQL Queries and Understand Execution Plans - Resources?

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 :)

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

StackExchange Database Administrators Q#20555, answer score: 7