Beiträge

Lothar Flatz

Oracle SQL Tuning: Unmögliche Optimizer Hints

/0 Kommentare/in , /von

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

Alexander A.

Oracle 19c DBMS_JOB Konvertierung mit Tücken

/0 Kommentare/in /von

Unlängst machte ich bei einer Datenbank-Migration Bekanntschaft mit einer der vielen Verhaltensänderungen, wie sie bei Versionswechsel immer wieder vorkommen.

Der Kunde hatte sich entschieden seine bereits ans Herz gewachsene Oracle 11.2.0.4er Datenbank auf aktuellen Stand zu bringen und sich folgerichtig für die Oracle 19c Version entschieden, welche ja eine „Long Term Support“ Version ist.

Da die Applikationslogik bereits zuvor in einzelne Schemas aufgeteilt war, wählte ich als Migrationsmethode Datapump Export/Import.

DBMS_JOB konvertiert

Nach dem ersten Test-Import und der für Export/Import obligatorischen Analyse der Importfehler stellte ich mit Überraschung fest, dass die vorhandenen Datenbank Jobs, welche zuvor als klassische DBMS_JOB angelegt waren, nun automatisch in neue Scheduler-Jobs „konvertiert“ wurden.

Dieses Verhalten der automatischen Konvertierung kannte ich bisher nur bei einem klassischen Upgrade der Datenbank und es wird auch erst seit Version 19c so gehandhabt.

Das nun auch automatisch neue Scheduler-Jobs bei einem Datapump Import angelegt wurden, überraschte mich erstmal, war aber bei näherer Betrachtung durchaus logisch.

Wie inzwischen allgemein bekannt sein sollte, ist das bereits in die Jahre gekommene DBMS_JOB Package zur Verwaltung von klassischen Datenbank Jobs bereits seit Version 12.2.0.1 „deprecated“, was in der Praxis bedeutet, dass es zwar noch existiert, jedoch in zukünftigen Versionen jederzeit „desupported“ bzw. auch entfernt werden könnte.

Oracle empfiehlt daher komplett auf den „Scheduler“ (DBMS_SCHEDULER) umzusteigen, was in jedem Fall sinnvoll ist, bietet dieser doch wesentlich mehr Funktionalität.

Mit der Version 19c geht Oracle noch einen Schritt weiter und baut eine automatische Konvertierung für klassischen Jobs ein. Das bedeutet, dass das alte DBMS_JOB API noch wie gewohnt verwendet werden kann, Oracle jedoch im Hintergrund automatisch aus jedem neuen DBMS_JOB in Wirklichkeit einen Scheduler Job erzeugt. Das Package DBMS_JOB dient dabei nur mehr als Wrapper um die Rückwärtskompatibilität zu gewährleisten.

Unter der Haube werkt somit nur mehr der Scheduler (das Mapping zwischen „alten“ und „neuen“ Jobs findet man übrigens in der neuen Dictionary Tabelle: SCHEDULER$_DBMSJOB_MAP).

Damit ist auch das Verhalten während meines Datapump Imports erklärt: Da hier die Jobs ebenfalls neu angelegt wurden, erhalte ich auch sofort die dazugehörenden Scheduler Jobs.

SQL> select job,schema_user,what from dba_jobs where job=844;

JOB SCHEMA_USER WHAT
------------- ----------- -------------------------------------
844 DWH dbms_mview.refresh('"DWH"."MVIEW1"');


SQL> select job_name,owner,job_action from dba_scheduler_jobs 
where job_name like 'DBMS_JOB$_844';

JOB_NAME OWNER JOB_ACTION
------------- ---------- -------------------------------------
DBMS_JOB$_844 DWH dbms_mview.refresh('"DWH"."MVIEW1"');

Das Mapping zwischen „alten“ und „neuen“ Jobs findet man übrigens in der neuen Dictionary Tabelle: SCHEDULER$_DBMSJOB_MAP):

SQL> select * from SCHEDULER$_DBMSJOB_MAP where dbms_job_number=844;

DBMS_JOB_NUMBER JOB_OWNER JOB_NAME
--------------- --------- --------------
844 DWH DBMS_JOB$_844

Allgemeine DBMS_JOB Verhaltensänderungen

Trotz Automatismus dürfen folgende Änderungen nicht unerwähnt bleiben:

Berechtigungen

Da es sich nun immer um Scheduler Jobs handelt (auch wenn diese mittels DBMS_JOB angelegt werden) und damit um Datenbankobjekte, muss der User zwingend die Berechtigung zum Anlegen dieser Objekte über das „CREATE JOB“ Privileg erhalten, um weiterhin Jobs über DBMS_JOB anzulegen und zu verwalten.

Transaktionsverhalten

Verwendet man in einer früheren Version als 19c DBMS_JOB zur Verwaltung der Jobs, muss immer explizit ein „commit“ verwendet werden, um die jeweilige Transaktion abzuschließen.

Ab Oracle 19c ist dies nun nicht mehr notwendig – der Scheduler Job wird implizit angelegt.

Trotzdem bleibt die Transaktionsfunktionalität bei der Verwendung von DBMS_JOB erhalten.

Stolperstein NLS-Einstellungen

Nachdem die Jobs während meines Datapump Imports automatisch in Scheduler Jobs konvertiert wurden, war hier für mich als DBA eigentlich nichts weiter zu tun.

Die Freude währte allerdings nur kurz, denn der Kunde machte mich umgehend auf ein seltsames Phänomen aufmerksam: Einige Spalten in seinen Materialized Views hätten plötzlich seltsame Werte bekommen!

Zahlen, die in VARCHAR2 Feldern als Strings gespeichert waren, hatten nun wie von Geisterhand 6 Nullen als Nachkommastelle bekommen.

Meine erste Reaktion war natürlich sofort das Konstrukt „Zahlen in Strings zu speichern“ in Frage zu stellen, allerdings hatte dieser Umstand leider wie so oft eine historische Berechtigung und mit dem eigentlichen Problem gar nichts zu tun.

Lediglich die Auswirkungen eines ganz anderen Problems kamen aber genau an dieser Stelle glücklicherweise an die Oberfläche: Die Jobs, welche den Refresh dieser Materialized Views durchführten, waren genau jene Jobs welche beim Import automatisch konvertiert wurden.

Analyse

Nach einer kurzen Analyse war klar: Bei der Neuanlage der ursprünglichen Jobs während des Imports wurden offensichtlich geänderte NLS-Settings verwendet!

Durch die besondere Konstellation von mehreren verschachtelten Views, MViews und DB-Links führten diese NLS-Settings (konkret die Einstellung des Parameters NLS_NUMERIC_CHARACTERS) dazu, dass einige Zahlen nun in den MViews falsch dargestellt wurden.

Damit war das Problem erkannt und nach einigen Tests schnell behoben.
Das NLS-Environment für einen bestehenden Scheduler-Job lässt sich übrigens auch im Nachhinein einfach ändern (man muss also nicht den kompletten Job neu erstellen):

begin
sys.dbms_scheduler.set_attribute('"USER1"."DBMS_JOB$_1185"','NLS_ENV',
'NLS_LANGUAGE=''GERMAN'' NLS_TERRITORY=''GERMANY'' NLS_CURRENCY=''€'
'NLS_ISO_CURRENCY=''GERMANY'' NLS_NUMERIC_CHARACTERS='',.'
' NLS_DATE_FORMAT=''DD.MM.RR'' NLS_DATE_LANGUAGE=''GERMAN'
' NLS_SORT=''GERMAN''');
end;
/

Fazit:

  • DBMS_JOBs werden ab Oracle 19c automatisch in Scheduler Jobs „konvertiert“ (sowohl beim Upgrade wie auch beim Import) – das alte DBMS_JOB Interface bleibt vorerst vollständig erhalten.
  • Besser vor einem Upgrade alle DBMS_JOBs überprüfen und kontrolliert durch DBMS_SCHEDULER Jobs ersetzen.
  • Besonders beim Import vorab die NLS-Settings von Jobs überprüfen.

Mein persönlicher Tipp an dieser Stelle:

Auch wenn gute Automatismen vorhanden sind, sollte man sich immer schon im Vorfeld eines geplanten Versionswechsels mit bekannten Verhaltensänderungen auseinandersetzen und diese auch bereits vorab gelöst haben.

Dann gibt es auch keine unliebsamen Überraschungen mit unbekannten „Side-Effects“.