You know the basics of SQL tuning. So why is it so hard in practice?
Indexes, execution plans, and hints are familiar concepts in SQL tuning. But when you actually face a slow query, it is hard to know where to start. You have to decide what to check first and what criteria to use when choosing a direction for improvement. Knowing a technique and knowing in what order to apply it are two different problems.
We see this in the field too. In the pre-survey for the September 3 seminar, 43.5% of respondents said, "I know the basic concepts, but applying them in practice is hard." In practice, deciding what to prioritize and on what grounds is as big a wall as knowing individual techniques, if not bigger.
EXEM's annual Oracle SQL Tuning Tech Seminar drew a lot of interest again this year. As in March, the September session filled up quickly after registration opened.
As if answering this concern, the seminar started from the question "What should we tune first?" and laid out the order of performance improvement in the following 4 steps.
1. What to fix first: selecting SQL tuning targets
It was a SQL tuning seminar, but the first session did not jump straight into tuning techniques. It started with "What should we tune first?" If you start without deciding which SQL to fix, individual queries may get faster while the load on the system as a whole stays the same.
When selecting tuning targets, the criteria you use to extract SQL matter. If the problem is CPU usage but you pull the top SQL by elapsed time or I/O, you can improve every SQL on the list and still not bring CPU usage down. You need criteria that match the problem you are trying to solve before tuning turns into real performance gains.
The session grouped the ways to find tuning targets into 3 paths: requirements from business owners, queries against Oracle Dictionary Views, and extraction through a performance management solution such as MaxGauge. The 3 paths complement each other rather than replace one another. Requirements point the direction, queries provide the evidence, and a solution lets you check areas people would otherwise miss.
As an example, the seminar showed Literal SQL, which uses constants directly instead of bind variables. These statements share the same structure and differ only in their values, yet each is counted as a separate SQL. So each one accounts for only 0.01-0.02% of CPU and never stands out in the top SQL list. But when grouped by pattern, the top 3 groups took 20%, 15%, and 10% of total CPU. This is why you should not pick tuning targets from a single list or a single metric.
💡 SQL tuning starts with deciding "what to fix first," not "how to fix it."
2. Why it behaves that way: reading execution plans and using hints
Once you have chosen a tuning target, the next step is to find out why that SQL behaves the way it does. The execution plan is your evidence for finding the cause.
When reading an execution plan, look at the optimizer's estimates and the actual execution results together. If the row count the optimizer predicted differs greatly from the actual row count, check whether the statistics or the access path are appropriate. The session covered how to check actual execution statistics with DBMS_XPLAN and 10046 Trace. DBMS_XPLAN shows estimated and actual row counts side by side for each step of the plan, while 10046 Trace records the full execution history, including wait events and bind values. Neither replaces the other. You choose based on what you want to check.
After reading the plan, the next step is changing it. Hints are the most direct way to change an execution plan. They leave the SQL logic as is and only specify how it should be processed, so unlike rewriting the SQL, you can adjust the plan without risking different result data.
That said, using a hint means a person is overriding the optimizer's judgment. The optimizer builds its plan from statistics, and if those statistics drift from the actual state of the data, its judgment drifts too. The textbook fix is to correct the statistics, but refreshing statistics can also change the plans of other SQL that were already running well, so it calls for caution on a production system. That is why you sometimes need to pin the plan with a hint only for the problem SQL. In return, you take on the burden of making sure the hint stays valid as the data changes.
💡 The execution plan is the clue for judging why SQL behaves as it does, and a hint is the tool for turning that judgment into action.
3. What you only see when you know the structure: indexes and joins
Once you have read the plan and set a direction with hints, the next step is to look at how to access the data efficiently. Indexes and joins are the two axes that determine that access method.
An index is sorted in column order. If the leading column is missing from the condition or is used in a range condition, the columns after it cannot help narrow the scan range. That is why two indexes on the same columns can be used to very different extents depending on column order and the form of the condition. So rather than simply adding an index, first check how the SQL is using the indexes that already exist. Performance often degrades not because an index is missing but because an existing one is not used properly.
With joins, you need to look at two things: the method used to connect tables, and the order in which they are connected. The session put Nested Loops Join, Hash Join, and Sort Merge Join side by side and compared the conditions where each one wins. Nested Loops Join works well for finding and connecting small amounts of data through an index, Hash Join for connecting large amounts of data with equality conditions, and Sort Merge Join for data that is already sorted or for non-equality conditions. Join order matters just as much. In Nested Loops Join in particular, which table you read first to look up the other determines the number of iterations, and that translates directly into a performance difference.
Index and join tuning goes beyond knowing what each feature does. It starts when you can read, within the execution plan, which path the data takes and how much of it is processed.
4. The answer lies in the situation, not the code: SQL Tuning methodology
With the target chosen, the plan read, and the access structure understood, what remains is how to fix it. That is what the last session covered.
There is no single answer in SQL tuning that works in every situation. The same SQL structure or tuning technique affects performance differently depending on the volume of data to process and the execution environment. Rather than applying a technique as if it were the answer, first understand the cause of the current performance problem and how the data is processed, then choose the method that fits.
The scalar subquery example from the seminar shows this well. A scalar subquery runs once for every row of the main query's result. If you need to extract 500,000 rows, the subquery also runs 500,000 times and drags performance down. If you extract only 10 rows, it finishes with 10 light lookups and is actually an efficient approach. The same technique is the cause in one case and the fix in another because the volume of data being processed, not the technique itself, decided the outcome. That is why the state of the data you are processing right now, rather than which technique to use, should be your criterion.
In the end, the core of SQL tuning this seminar highlighted is not individual techniques but the order in which you approach a performance problem. Decide what to tune first, find the cause through the execution plan, examine the data processing structure such as indexes and joins, and finally choose the method that fits the current situation.
The starting point of this order, "What should we fix first?", depends on how accurately you understand the current state of your system. That is also why the first session named performance management solutions as one of the paths for finding tuning targets. exemONE Observability gathers performance data from across your IT infrastructure, including the DB, in one place and shows you first what has changed and where delays are occurring. The final call is still yours, but with the right information at hand, identifying the problem and deciding how to fix it goes much faster.
EXEM will keep sharing practical skills and know-how on a range of DBMS topics.
On November 5, we will host an online PostgreSQL tech webinar with the authors of "PostgreSQL Wait Interface." Registration is scheduled to open on the EXEM website on September 29.
If you want a preview of the topic before the webinar, check out the key ideas of the book in our interview on the release of "PostgreSQL Wait Interface" (Korean).
Curious about unified management, from SQL performance to your entire IT infrastructure?
Related articles
