8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

How do I learn to tune SQL?

I'm regularly asked, "How do I tune a database?", to which I usually suggest, "How do I tune SQL?", is a better question, since the vast majority of problems with database performance are down to poorly written SQL, or good SQL written against badly designed data models.

There are whole books written about SQL tuning, so a brief article is going to be flawed, but I wanted to put down some ideas and thought processes that might help anyone new to this subject. This article will no doubt evolve over time, but I will try to keep it really light.

If you are experienced in SQL tuning, you may find some of the topics covered here an extreme over-simplification. That is intentional. Remember, it's for beginners.

Identify Resource Intensive SQL

You often know exactly which SQL statement to tune, since you are working on it already, or a user has reported performance problems with a specific report or piece of application functionality. If that's the case, no problem.

In some situations you are presented with a badly performing system that you know nothing about. There are several ways you can identify resource intensive SQL, including the following.

Once you've found a suspect statement, you can start trying to tune it.

Context and Realistic Goals

When you are looking at resource intensive SQL you need to take a step back and put it into context. Here are two extremes to consider.

Before you launch into tuning a SQL statement, think a little about the context and your aims for tuning the statement. You have to be realistic about what you can achieve. You should be asking yourself questions like these.

We are not necessarily looking for hard facts here. It's some context to understand what we have and what we are likely to achieve. If you are a DBA, you may look at these points and think it sounds a lot like development. Yes it does, because query tuning is more of a development task in my opinion, not a DBA task. If you are not well versed in SQL and application development, I think you will find query tuning a lot harder. Others may disagree.

Now we understand some of the context and have some realistic aims, we can move forward.

Quick Wins

Assuming you have the correct licenses, tools like the SQL Tuning Advisor and the SQL Access Advisor can sometimes give you some quick wins. Nearly every statement I'm asked to tune gets checked by these advisors. Sometimes their suggestions are great. Sometimes they are rubbish. Sometimes they are rubbish, but they give you a clue to what is really the problem. For the sake of a few minutes, it's worth trying them to see what you get. Please don't mindlessly apply their recommendations, but if you've paid for them and they help, great!

Execution Plans

With SQL tuning it's all about the execution plan. There are several ways to get them, but the best option is to use the DBMS_XPLAN package to display them, and it's important to look at the plan that was actually used (DISPLAY_CURSOR), rather than the estimated plan (DISPLAY) if possible.

If you have the appropriate licensing, the Real-Time SQL Monitoring feature is a great way to see the execution plan as the statement is actually running, but you should be comfortable with the basic approach too.

There are other tools too, like SQL Developer and TOAD etc. The important point is to make sure you are looking at the actual execution plan used for the last run, rather than an estimate of the plan.

You can find some basic information about reading execution plans here.

Cardinality is King

When we discuss cardinality in this context, what we are actually talking about is the number of rows processed by each operation in the execution plan. The optimizer uses database statistics to estimate the cost of each operation and ultimately the whole plan. It then compares several plans and picks the plan with the lowest cost. The cardinality estimate for each operation is a big factor in the cost of that operation.

When reading an execution plan it is really important to know the estimated and actual cardinalities for each operation. The GATHER_PLAN_STATISTICS hint can be used to display this information. If you see a big difference between the estimated and actual number of rows for an operation, it means the optimizer estimated the cardinality badly, so it is quite likely it made some bad choices in the execution plan. The types of bad choices could include the following.

Every new version of the database tries to reduce the chances of these bad decisions happening, or includes ways for the optimizer to learn from its mistakes to improve future decisions. Oracle 12c includes a variety of adaptive query optimizations to minimise the impact of bad cardinality estimates.

Bad cardinality estimates can be caused by a number of factors including the following.

Ultimately, the better the cardinality estimates, the more likely the optimizer will make the right choice. If you see big differences between the estimated and actual cardinalities of an operation, that's probably something to investigate further.

Don't Do It

You would be surprised how many databases spend a lot of time running SQL that is not necessary. If you can identify the source of this and remove it, it can result in substantial improvements to system performance. The types of things you might see include the following.

Filter Early

If you've not already read the reading execution plans link, you probably should before you continue here as I will make some reference to the terminology explained there.

You will sometimes hear the term "filter early" when discussing execution plans. When the optimizer is trying to determine the driving set, it will often pick the smallest set available. If we have the option to filter data out of one of the result sets, we can make it smaller and therefore make it more likely to be picked as the driving set. Often, the quicker we can eliminate rows from the query, the more efficient the statement will be, hence filter early. If we have filter predicates that can be applied against multiple tables, it might make sense to group them into a single table to make the driving set even smaller.

Imagine a situation where we are looking for active orders that were created in the last seven days. If both the orders and order_lines tables include a created_date column, and we assume the order and order lines will always be created on the same day, we could do this.

FROM   orders o
       JOIN order_lines ol ON ol.order_id = o.id
WHERE  o.status = 'ACTIVE'
AND    ol.create_date >= TRUNC(SYSDATE) - 7

We might find it is advantageous to move the created_date filter onto the orders table, allowing us to reduce the number of rows returned from this table even more, making it a more attractive and smaller driving set.

FROM   orders o
       JOIN order_lines ol ON ol.order_id = o.id
WHERE  o.status = 'ACTIVE'
AND    o.create_date >= TRUNC(SYSDATE) - 7

You may also hear the term "filter early" used when discussing the data sent to application servers. As discussed earlier, sending large amounts of data to an application server, or client application, only for it to filter it and throw most of the data away represents a big waste of database effort.

Efficient Function Calls

You often see PL/SQL function calls in SQL statements, which can cause some performance nightmares. The subject of efficient function calls in SQL is discussed here.

Books and Videos

There are lots of books, training courses and videos related to performance tuning, but people often overlook the free and obvious. A good place to start is to check out these free resources.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.