Oracle SQL Tuning: Impossible Optimizer Hints

from

Deutsche Version hier: https://www.dbconcepts.at/oracle-sql-tuning-unmoegliche-optimizer-hints/

Query Blocks

Until recently I believed that optimizer hints only works within its query block, unless you qualify the hint with the query block name [1].

For example, view names do not work in a hint.

In my investigation to improve the Statspack [2] I saw that this was not always true and wanted to examine it more closely.

The test

I create my test examples in the Scott scheme so that everyone can repeat my test.

With the help of the optimizer hint I will force a bad plan so that it is clear that the optimizer chose the plan involuntarily and because of the hint.

For the tests I used Oracle version 19c to be able to use the new hint report function.

I displyed the plans with the following statement:

select * from dbms_xplan.display_cursor(format=>'TYPICAL +hint_report')
;

This was my test statement:

SELECT ename,
       dname,
       sal,
       grade
FROM salgrade s,
    (
        SELECT ename,
               sal,
               dname
          FROM emp    e,
               dept   d
         WHERE e.deptno = d.deptno
           AND ename = 'SCOTT'
    ) de
WHERE de.sal BETWEEN losal AND hisal;

Without further intervention, the optimizer creates the following plan:

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |       |       |     7 (100)|          |
|   1 |  NESTED LOOPS                 |          |     1 |    94 |     7   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |          |     1 |    55 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL          | EMP      |     1 |    33 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    22 |     1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)|          |
|*  6 |   TABLE ACCESS FULL           | SALGRADE |     1 |    39 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ENAME"='SCOTT')
   5 - access("E"."DEPTNO"="D"."DEPTNO")
   6 - filter(("SAL">="LOSAL" AND "SAL"<="HISAL"))

Let’s try a hint now.

In our hint we force the optimizer to start with the table dept, which it would not do voluntarily. (Just consider the cost)

SELECT /*+ leading(d) */ ename,
       dname,
       sal,
       grade
FROM
    salgrade s,
    (
        SELECT ename,
               sal,
               dname
          FROM emp    e,
               dept   d
         WHERE e.deptno = d.deptno
           AND ename = 'SCOTT'
    ) de
WHERE de.sal BETWEEN losal AND hisal;

Against my expectation, the optimizer hint is followed, although it refers to an alias in another query block.

This time there is also a hint report and it looks like the hint is fine.

-------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |       |       |     9 (100)|          |
|   1 |  NESTED LOOPS       |          |     1 |    94 |     9   (0)| 00:00:01 |
|*  2 |   HASH JOIN         |          |     1 |    55 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT     |     4 |    88 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMP      |     1 |    33 |     3   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL | SALGRADE |     1 |    39 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("E"."DEPTNO"="D"."DEPTNO")
   4 - filter("ENAME"='SCOTT')
   5 - filter(("SAL">="LOSAL" AND "SAL"<="HISAL"))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
   1 -  SEL$F5BB74E1
           -  leading(d)

The hint shouldn’t have worked at all.

The hint is in the main query and references an object in another query block.

Thus, why did it still work?

As you can see, the subquery was resolved and merged with the main query. This is called simple view merging.

Simple view merging is a so-called transformation.

The optimizer rewrites the query. Due to the transformation, there is only one query block for the query. That could be the reason that the leading hint works.

Will the hint above still work if we forbit the transformation?

SELECT /*+ leading(d) */ ename,
       dname,
       sal,
       grade
FROM
    salgrade s,
    (
        SELECT /*+ NO_MERGE */  ename,
               sal,
               dname
          FROM emp    e,
               dept   d
         WHERE e.deptno = d.deptno
           AND ename = 'SCOTT'
    ) de
WHERE de.sal BETWEEN losal AND hisal;

In fact, the leading hint is no longer being obeyed. This is typical of Hints not working.

The transformation takes place before the optimization.

This forces two different query blocks.

The direct referencing of another query block that becomes effective after the transformation no longer works.

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |       |       |     7 (100)|          |
|   1 |  NESTED LOOPS                  |          |     1 |    68 |     7   (0)| 00:00:01 |
|   2 |   VIEW                         |          |     1 |    29 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |          |     1 |    55 |     4   (0)| 00:00:01 |
|   4 |     NESTED LOOPS               |          |     1 |    55 |     4   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL         | EMP      |     1 |    33 |     3   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)|          |
|   7 |     TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    22 |     1   (0)| 00:00:01 |
|*  8 |   TABLE ACCESS FULL            | SALGRADE |     1 |    39 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("ENAME"='SCOTT')
   6 - access("E"."DEPTNO"="D"."DEPTNO")
   8 - filter(("DE"."SAL">="LOSAL" AND "DE"."SAL"<="HISAL"))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
---------------------------------------------------------------------------
   1 -  SEL$1
         U -  leading(d)
   3 -  SEL$2
           -  NO_MERGE

Recognize the U? The hint report also shows that the leading hint is not followed.

In  Statspack I saw this type of hint, which was unknown to me:

SELECT /*+ leading(de.d) */ ename,
       dname,
       sal,
       grade
FROM
    salgrade s,
    (
        SELECT /*+ NO_MERGE */  ename,
               sal,
               dname
          FROM emp    e,
               dept   d
         WHERE e.deptno = d.deptno
           AND ename = 'SCOTT'
    ) de
WHERE de.sal BETWEEN losal AND hisal;

Qualifying with the subquery alias shouldn’t work. The alias of the subquery is not a query block name.

Yet it does work:

--------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     9 (100)|          |
|   1 |  NESTED LOOPS        |          |     1 |    68 |     9   (0)| 00:00:01 |
|   2 |   VIEW               |          |     1 |    29 |     6   (0)| 00:00:01 |
|*  3 |    HASH JOIN         |          |     1 |    55 |     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| DEPT     |     4 |    88 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| EMP      |     1 |    33 |     3   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS FULL  | SALGRADE |     1 |    39 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("E"."DEPTNO"="D"."DEPTNO")
   5 - filter("ENAME"='SCOTT')
   6 - filter(("DE"."SAL">="LOSAL" AND "DE"."SAL"<="HISAL"))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
   3 -  SEL$2
           -  leading(de.d)
           -  NO_MERGE

Bottom Line

The ability to reference a subquery with alias makes it simpler to hint complex queries. Based on the hint report, you can see that the hint is correctly understood.

Like so much in the context of hints, this option is not documented.

That leaves open how consistent this kind of hint is. I checked my test case from version 11.2.0.4. to 19.0 and it always worked.

It does also work with a view, not just a subquery in the from clause.

However, again if the view has an alias, the alias must be used in the hint.

As Jonathan Lewis let me know, this way of hinting is officially advised [3].

Strange that I have never come across it in my assignments. It is really useful.

References