Unlock the power of using Oracle Optimizer Hints to tune SQL statements

Common SQL tuning methods
SQL tuning is the process to improve a SQL statement’s performance up to the user’s expectation. There are at least three methods which are commonly used by developers or DBAs, These methods are creating new indexes, rewriting the SQL syntax and applying Oracle Optimizer Hints to the SQL statements. Each method has its pros and cons and suitable for different stages of application cycle. Let’s discuss these three methods in the following.

Create new indexes for SQL statement
Creating new indexes for SQL statements are a very common method to improve SQL performance, it is especially important during database development. As new indexes to a SQL statement are not only affecting current SQL, it is also affecting other SQL statements running on the same database. So, it should be used very carefully in in production database. Normally, users are required to make impact analysis to other relevant SQL statements for the newly-created indexes.

Rewrite SQL statement
There are a lot of people teaching SQL rewrite skills over the internet. Most of those rewrite rules are inherited from Oracle rule-based SQL optimizer or older version of cost-based SQL optimizer. For example, some people may think that the following SQL may have different performances:

Select * from A where A.unique_key in (select B.unique_key from B);

Select * from A where exists (select ‘x’ from B where A.unique_key=B.unique_key);

Actually, if you put these two SQLs into Oracle database, you may probably get the same query plan from Oracle; it is because Oracle SQL optimizer will rewrite these two SQLs into one standard form internally to enable better query plans generation. A stronger internal rewrite ability was developed by Oracle SQL optimizer in last two decades. So, some obvious problems such as “KEY IS NOT NULL” or “NVL(KEY,’ABC’) =’ABC’ ” were not able to use indexes are solved by Oracle already. Of course, there are still some limitations in Oracle SQL optimizer for complex SQL transformation, so experience users may still be able to tune a SQL statement through SQL rewrite to influence Oracle SQL optimizer to generate a better query plan. But this approach is getting more difficult to apply by DBAs since the relationship between SQL syntax and final query plan generation is getting weaker, this is because Oracle cost-based optimizer (CBO) is getting smarter and the syntax of the SQL text is no longer a dominating factor to the cost calculation and plans generation.

SQL rewrite is still useful both in development and production database, since it is an isolated change to a database and no other SQLs’ performance will be affected, and it is safer than building new indexes. But it requires SQL code changes in program sources, so unit test or integration test may still be necessary. In contrast, using hints to tune a SQL is relatively safer, since it is not easy to change the semantics of the SQL accidentally.

Apply hints to SQL statement
Most databases in the market provide some sorts of query plan control language to help its optimizer to better optimize a specific SQL statement. For example; Optimization Guidelines of IBM LUW and Plan Guides of SQL Server are provided for many years already. Oracle provides Optimizer Hints that embedded with SQL text as a remark to tell Oracle SQL optimizer how to optimize the SQL statement. As hints will not affect the semantic of the SQL statement, so it is relatively safer than SQL rewrite and building new indexes. There are more than a hundred of documented Optimize Hints provided by Oracle. Unless you are an expert in using Optimizer Hints; using the right hints to tune a SQL is not easy to be mastered even by a human expert, since it is close to a million of permutations if we just pick 3 hints out from a hundred of hints.

Let’s use a simple SQL example with the follow Optimizer Hints to show how Optimizer Hints works, the Hints is used to tell Oracle SQL optimizer to use index of DEPARTMENT table during query plan selection if possible. Oracle will try to find the lowest cost plan among all plans with index retrieval of DEPARTMENT table.

Select /*+ INDEX(@QB2 DEPARTMENT) */ *
From employee
Where emp_dept in (Select /*+ QB_NAME(QB2) */ dpt_id
From department
Where dpt_name LIKE 'D%');

Oracle SQL Hints is not a programing language
Oracle Optimizer Hints is not a programing language that come with proper syntax check or error return. It means an invalid Hints instruction to a SQL statement that Oracle SQL optimizer will not return with error message. Furthermore, even if it is a valid Hints instruction that Oracle SQL optimizer actually cannot comply with, there will be no error message returned too. So, users have to do a lot of trial and error before it can influence SQL optimizer to generate a specific better query plan.

Knowing the solution
It is a very common tuning practice that people are trying to find the best query plan for a bad performance SQL statement, it works like a human mimic Oracle SQL optimizer’s job to optimize a SQL statement with human’s knowledge. If the SQL statement is simple and Oracle SQL optimizer had made an obvious mistake, probably human intervention may work fine for this kind of simple SQL.

Here is an example:

Select * from A where A.KEY<’ABC’;

If Oracle SQL optimizer fails to use index to retrieve records from table A and using index of KEY1 is actually a better solution. You can use Optimizer Hints to instruct Oracle to use index instead of full table scan for this SQL statement.

Select /*+ INDEX(KEY1 KEY1_INX) */ from A where A.KEY1<’ABC’ and A.KEY2<123 and A.KEY3<’C’;

Knowing the best solution is easy for simple SQL statements, but it is difficult for complex SQL statements. Since there are a lot of execution steps in the query plan for complex SQL, human beings are not able to estimate each step and work out a series of query steps to compose the best performance query plan. So, using Oracle Optimizer Hints to instruct SQL optimizer to generate specific query plan for complex SQL may not be easy even for an experienced SQL tuning expert.

Knowing the problem
Instead of knowing the solution of a SQL statement, it is relatively easier for a human expert to find where the problem is in a complex query plan. The way to tell Oracle to bypass the problem is applying hints with prefix “NO_” such as NO_INDEX or NO_USE_HASH. It tells Oracle not to use the specified operation in the query plan and select another operation instead with the lowest cost. This approach is not commonly adopted in the market due to people are normally bound by solution oriented thinking.

For example:

Select /*+ NO_INDEX(KEY2 KEY2_INX) */ * from A where A.KEY1<’ABC’ and A.KEY2<123 and A.KEY3<’C’;

If you think the KEY2_INX is not a good index to retrieve records from table A, you can disable the KEY2 index by applying /*+ NO_INDEX(KEY2 KEY2_INX) */ and let Oracle to select other index to retrieve the records.

Unlock the potential power of Oracle optimizer hints
Whether you are using solution oriented approach or problem bypassing approach, you need to know the details of the SQL’s query plan, data distribution, cost of each step, and try to predict what will be the final actual aggregated cost of the plan. Sometimes you have to use both techniques for complex SQL statements; you have to provide the best query steps for parts of the query plan and use “NO_*” to bypass those known issues in the query plan. It is a very complicated process and it is not easy to carry out by common SQL developers without in-depth knowledge of SQL tuning.
.......
Please download the attached PDF for details.......