Using Oracle Optimization Hints When Tuning SQL

This tip explains how the Oracle optimization hints are applied to the SQL statements to generate more execution plans in Quest SQL Optimizer for Oracle.

One technique that you can use to improve the performance of a SQL statement is to add an Oracle optimization hint. Oracle provides “hints” that can be added to the syntax of a SQL statement to attempt to influence the database optimizer to use a particular execution plan to execute the SQL statement.

Quest SQL Optimizer for Oracle uses this technique in its SQL optimization process. After the syntax of a SQL statement is rewritten, Quest SQL Optimizer adds the Oracle optimization hints to the original SQL statement and the SQL alternatives that it has generated. It is seeing if it can find more unique execution plans using the Oracle hints.

This is a straight forward process when the SQL statement has only one SELECT statement. In this case, each hint is applied to that one SELECT statement. But when the SQL statement has multiple SELECTs within it, you can apply the hints to each SELECT in a plethora of combines.

Here is the approach that Quest SQL Optimizer uses to apply the hints for a SQL statement with multiple SELECTs such as this one with three SELECTs:

SELECT *
  FROM EMPLOYEEx,
       DEPARTMENTx DEPARTMENT1,
       GRADEx
 WHERE EMP_GRADE = GRD_ID
   AND EMP_DEPT = DPT_ID
   AND EXISTS (SELECT 'X'
                 FROM DEPARTMENTx DEPARTMENT2
                WHERE DPT_AVG_SALARY IN (SELECT MIN(DPT_AVG_SALARY)
                                           FROM DEPARTMENTx DEPARTMENT3)
                  AND DPT_ID = EMPLOYEEx.EMP_DEPT)

The hints are applied in a manner like this until most of the possible combinations with all the hints is tried or until the quota setting is reached.

The SQL Optimizer tries most of the  possible combinations of the first hint, in this case /*+ hint1 */.  In this example, there are 7 different transformations using only the first hint.

SELECT /*+ hint1 */ …
   AND EXISTS (SELECT …
                  (SELECT …)

SELECT …
   AND EXISTS (SELECT /*+ hint1 */ …
                  (SELECT …)

SELECT …
   AND EXISTS (SELECT …
                  (SELECT /*+ hint1 */…)

SELECT /*+ hint1 */ …
   AND EXISTS (SELECT /*+ hint1 */…
                  (SELECT …)

SELECT /*+ hint1 */ …
   AND EXISTS (SELECT …
                  (SELECT /*+ hint1 */…)

SELECT …
   AND EXISTS (SELECT /*+ hint1 */ …
                  (SELECT /*+ hint1 */…)

SELECT /*+ hint1 */ …
   AND EXISTS (SELECT /*+ hint1 */ …
                  (SELECT /*+ hint1 */ …)

Then it moves on to the second hint. It applies the first hint to one SELECT and the second hint to another SELECT like this:

SELECT /*+ hint1 */ …
   AND EXISTS (SELECT /*+ hint2 */ …
                  (SELECT …)

So, it tries most of the possible combinations of applying all the selected hints. Since this can be quite a large number of combinations, you can limit the maximum number of SQL alternatives that are created by applying the Oracle optimization hints using the Hints quota setting.

On the other hand, if you would like the optimization process to apply several of the Oracle optimization hints when there are multiple SELECT statements in one SQL statement you will need to use a large Hints quota.