Oracle SQL Tuning: Unmögliche Optimizer Hints

Englisch Version here: https://www.dbconcepts.at/oracle-sql-tuning-impossible-optimizer-hints/

Query Blöcke

Ich war bis jetzt der Meinung, dass ein Optimizer Hint nur innerhalb seines Query Blocks wirkt, außer man qualifiziert den Hint mit dem Query Block Namen [1].

Man kann beispielsweise Viewnamen in einem Hint nicht wirksam angeben.

In meiner Untersuchung zur Verbesserung des Statspack [2] habe ich gesehen, dass das nicht immer stimmt und wollte diese genauer untersuchen.

Ausgangslage

Meine Testbeispiele erstelle ich im Scott Schema, so dass jeder meinen Test nachvollziehen kann.

Mittels des Optimizer Hints werde ich einen schlechten Plan erzwingen, damit eindeutig ist, dass der Optimizer den Plan unfreiwillig und durch den Hint gewählt hat.

Für die Tests habe ich Oracle Version 19c verwendet, um gleich die neue Hint Report Funktion nutzen zu können.

Die Pläne habe ich mit dem folgenden Statement angezeigt:

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

Hier ist unser Beispiel 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;

Ohne weitere Angabe erzeugt der Optimizer den folgenden 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"))

Versuchen wir es nun mit einem Hint.
In unserem Hint erzwingen wir, dass der Optimizer mit der Tabelle dept beginnt, was er freiwillig nicht machen würde.

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;

Gegen meine Erwartung wird der Hint befolgt, obwohl er sich auf einen Alias in einem anderen Query Block bezieht.

Diesmal ist auch ein Hint Report dabei, da es einen Hint gibt.

-------------------------------------------------------------------------------
| 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)

Eigentlich hätte der Hint gar nicht funktionieren dürfen.

Der Hint befindet sich in der Hauptabfrage und referenziert ein Objekt in einem anderen Query Block.

Weshalb geht es wohl doch?

Wie man sieht wurde aber die Unterabfrage aufgelöst und mit der Hauptabfrage verschmolzen. Man spricht hier von einem Simple View Merging.

Ein Simple View Merging ist eine sogenannte Transformation. Der Optimizer schreibt die Abfrage um.

Durch die Transformation gibt es nur noch einen Query Block für die Abfrage.

Das könnte der Grund sein, dass der Leading Hint funktioniert.
Wird der obige Hint noch wirken, wenn man die Transformation verbietet?

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 der Tat wird der Leading Hint jetzt nicht mehr befolgt.

Das ist typisch für das nicht funktionieren von Hints. Die Transformation erfolgt vor der Optimierung.

Dadurch werden zwei unterschiedliche Query Blocks erzwungen.
Das direkte Referenzieren eines anderen Query Blocks funktioniert dann nicht mehr.

-------------------------------------------------------------------------------------------
| 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

Der Hint Report zeigt ebenfalls an, dass der Leading Hint nicht befolgt wird.

Im Statspack sah ich aber diese Variante des Hints:

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;

Das Qualifizieren mit dem Alias der Unterabfrage sollte eigentlich nicht funktionieren.

Der Alias der Unterabfrage ist kein Query Block Name.

Jedoch, es geht trotzdem:

--------------------------------------------------------------------------------
| 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

Fazit

Die Möglichkeit mit dem Alias einer Subquery zu Hinten erleichtert das Hinten von komplexen Abfragen.

Aufgrund des Hint Reports kann man sehen, dass der Hint korrekt verstanden wird.

Wie so vieles im Kontext von Hints ist auch diese Option nicht dokumentiert.

Ich habe die Abfrage von Version 11.2.0.4. bis 19.0 getestet und sie funktionierte immer.

Statt mit einer Unterabfrage in der From Klausel funktioniert es auch mit einer View.

Jedoch muss auch hier, wenn die View einen Alias bekommt, der Alias im Hint verwendet werden.

Jonathan Lewis hat mich darauf hingewiesen, dass die Art von Hints offiziell erlaubt ist.

Es handelt sich um global table hints [3].

Diese besonders nützliche Art des Hints ist leider noch zu wenig bekannt. Jedenfalls habe ich sie in meiner Praxis noch nicht angetroffen.

Quellen

0 Kommentare

Dein Kommentar

An Diskussion beteiligen?
Hinterlasse uns Deinen Kommentar!

Schreiben Sie einen Kommentar

Ihre E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Time limit is exhausted. Please reload CAPTCHA.