Performance by Example (für DBAs): SQL Patch(ing)

Die Oracle Datenbank kennt (für DBAs) verschiedene Methoden, um den Ausführungplan eines Statements zu beeinflussen. Es handelt sich dabei um SQL Baseline Management, SQL Profiles sowie SQL Patches. Alle anderen Themen, wie aktuelle Statistiken oder Instance Parameter lassen wir für diesen Artikel außen vor.

Ein Teil dieses Artikels ist auch in der Oracle Database Kolumne im Artikel Oracle-Ausführungsplan-Managementtools im Überblick und SQL Patch im Detail erschienen.

Oracle Datenbanken und abgespeicherte Ausführungspläne (Stored Executionplans)

SQL Baseline Management

SQL Baseline Management gibt es grundsätzlich für alle Datenbank Editionen. Allerdings ist der Funktionsumfang bei der Oracle Standard Edition so eingeschränkt, dass es oft nicht sinnvoll eingesetzt werden kann. Die Funktionalität besteht darin, dass man zuerst die aktuellen (hoffentlich guten) Ausführungspläne persistiert und sie dann aktiviert. Findet der Optimizer einen neuen Ausführungsplan, wird dieser auch weiterhin gespeichert, aber nicht aktiv genutzt. Mittels ENVOLVE wird die Laufzeit verifiziert. Ist die Laufzeit besser, wird der neue Ausführungsplan freigeschaltet und darf in Zukunft genutzt werden.

SQL Profiles

SQL Profiles setzen neben der Oracle Enterprise Edition auch die kostenpflichtigen Diagnostic und Tuning Packs voraus. Hier kann man mit Hilfe des SQL Tuning Advisors alternative Ausführungspläne für Statements erzeugen und für die Nutzung hinterlegen.

SQL Patch

Die SQL Patches dienen dazu, einem SQL Statement manuell einen anderen Ausführungsplan beizubringen, wenn der Ausführungsplan in einen Oracle Fehler läuft bzw. wenn der aktuelle Ausführungsplan zu langsam ist. SQL Patches sind in allen Oracle Editionen erlaubt. Grundsätzlich sollten Entwickler lieber ihre Statements direkt optimieren und nicht auf SQL Patches setzen. DBAs haben oft keine Möglichkeit, ein SQL Statement zu beeinflussen – hier kann SQL Patch die Lösung sein.

Nachdem SQL Patch in allen Datenbanken Editionen zur Verfügung steht, ist es sinnvoll sich diese Funktionalität genauer anzusehen.

SQL Patch im Detail

Für unsere Experimente nutzen wir das HR Demo Schema von Oracle. Damit der Benutzer HR die nötigen Rechte für AUTOTRACE und V$-Views hat, kann man wie im Artikel Berechtigungen für SQL Tuning beschrieben vorgehen.

select /* STMT */ first_name, salary 
  from employees 
 where salary > 5000 
   and First_name like '%eter';

Im Ausführungsplan sehen wir, dass ein Full Table Scan genutzt wird.

-------------------------------------------------------...
| Id  | Operation         | Name      | Rows  | Bytes |...
-------------------------------------------------------...
|   0 | SELECT STATEMENT  |           |     3 |    33 |...
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     3 |    33 |...
-------------------------------------------------------...

Das ist für die EMPLOYEES Tabelle sicher das Optimale, da sie sehr klein ist, Wir wollen trotzdem mit dem SQL Patch dafür sorgen, dass der Index EMP_NAME_IX (LAST_NAME, FIRST_NAME) genutzt wird.

Damit wir die nötigen Daten für den SQL Patch sammeln können, brauchen wir einige Informationen – beginnend mit einem ausführlichen Execution Plan. Dafür besorgen wir uns zuerst die SQL_ID des Statements

select sql_id, substr(sql_text,1,40) 
  from v$sqlarea 
 where sql_text like 'select /* STMT */ first_name, salary %';

SQL_ID        SUBSTR(SQL_TEXT,1,40)
------------- -------------------------------------
g6pauu7gt9h8b select /* STMT */ first_name, salary

Mit der SQL_ID können wir uns mittels dem Package DBMS_XPLAN genauere Informationen zum Ausführungsplan heraussuchen.

select * from table(dbms_xplan.display_cursor('g6pauu7gt9h8b',null,'ALL'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID  g6pauu7gt9h8b, child number 0
-------------------------------------
select /* STMT */ first_name, salary   from employees  where salary
> 5000    and First_name like '%eter'

Plan hash value: 1445457117

-------------------------------------------------------...
| Id  | Operation         | Name      | Rows  | Bytes |...
-------------------------------------------------------...
|   0 | SELECT STATEMENT  |           |       |       |...
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     5 |    55 |...
-------------------------------------------------------...

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMPLOYEES@SEL$1

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

   1 - filter(("FIRST_NAME" LIKE '%eter' AND "SALARY">5000 AND
              "FIRST_NAME" IS NOT NULL))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "FIRST_NAME"[VARCHAR2,20], "SALARY"[NUMBER,22]

Wir sehen wieder den Ausführungsplan mit dem Full Table Scan. Darunter steht die Liste der Query Block Names. In unserem Beispiel ist das nur einer – und genau diesen brauchen wir für den nächsten Schritt.

SQL Patch anlegen

Fassen wir zusammen. Wir wollen für das Statement mit der SQL_ID=g6pauu7gt9h8b für den Query Block SEL$1 der Tabelle EMPLOYEES (bzw. Aliasname sofern genutzt) einen INDEX Hint zur Nutzung des Index EMP_NAME_IX einbauen.

Dazu nutzen wir das Package DBMS_SQLDIAG, um einen SQL Patch anzulegen. Der PL/SQL Block versucht den SQL Patch zuerst zu löschen, sofern dieser schon einmal angelegt wurde, und dann wird dieser neu erzeugt:

declare
  v_name varchar2(128);
begin
  begin
    /* optional: Patch droppen, sofern dieser schon existiert */
    dbms_sqldiag.drop_sql_patch(name=>'DBM_TUNING_SQL_PATCH_1');
    exception when others then dbms_output.put_line(sqlerrm);
  end;
  v_name := dbms_sqldiag.create_sql_patch( -
       sql_id=>'g6pauu7gt9h8b', -
       hint_text=>'INDEX(@SEL$1 EMPLOYEES EMP_NAME_IX)', -
       name=>'DBM_TUNING_SQL_PATCH_1');
end;
/

Kontrolle ob es funktioniert hat:
Wichtig: das SQL Statement muss 100% ident sein, sonst bekommt es eine andere SQL_ID!

SET AUTOTRACE ON

select /* STMT */ first_name, salary 
  from employees 
 where salary > 5000 
   and First_name like '%eter';

FIRST_NAME               SALARY
-------------------- ----------
Peter                      9000
Peter                     10000

2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2898859329

-----------------------------------------------------------...
| Id  | Operation                           | Name        |...
-----------------------------------------------------------...
|   0 | SELECT STATEMENT                    |             |...
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES   |...
|*  2 |   INDEX FULL SCAN                   | EMP_NAME_IX |...
-----------------------------------------------------------...

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

   1 - filter("SALARY">5000)
   2 - filter("FIRST_NAME" LIKE '%eter' AND "FIRST_NAME" IS NOT NULL)

Note
-----
   - SQL patch "DBM_TUNING_SQL_PATCH_1" used for this statement
...

Es hat wunderbar geklappt. Statt dem Full Table Scan wird jetzt der Index genutzt und in den Notes (werden in älteren Oracle Versionen beim Autotrace leider noch nicht angezeigt) findet man die Information, dass der SQL Patch genutzt wird. Perfekt, oder?

Suchen wir einfach nach allen Mitarbeitern, deren Vorname auf „a“ endet und die mehr als 5000 Gehalt haben.

select /* STMT */ first_name, salary 
  from employees 
 where salary > 5000 
   and First_name like '%a';

FIRST_NAME               SALARY
-------------------- ----------
Neena                     17000
Shanta                     6500
Clara                     10500
Mattea                     7200
Lisa                      11500
Sundita                    6100
Alyssa                     8800

7 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

---------------------------------------...
| Id  | Operation         | Name      |...
---------------------------------------...
|   0 | SELECT STATEMENT  |           |...
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |...
---------------------------------------...

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

   1 - filter("FIRST_NAME" LIKE '%a' AND "SALARY">5000 AND "FIRST_NAME"
              IS NOT NULL)
...

Und schon wieder wird ein Full Table Scan gemacht. Warum bloß?

Das Problem liegt daran, dass jede kleine Änderung an einem Statement zu einer neuen SQL_ID führt!

SET AUTOTRACE OFF
select sql_id, sql_text from v$sqlarea where sql_text like 'select /* STMT */ first_name, salary%';

SQL_ID        SQL_TEXT
------------- ---------------------------------------------------------
g6pauu7gt9h8b select /* STMT */ first_name, salary from employees where salary > 5000 and First_name like '%eter'
b1r15hk28un25 select /* STMT */ first_name, salary from employees where salary > 5000 and First_name like '%a'

Schauen wir uns genauer an, was man bei DBMS_SQLDIAG.CREATE_SQL_PATCH angeben kann:

DBMS_SQLDIAG.CREATE_SQL_PATCH (
    sql_text        IN   CLOB,
    hint_text       IN   CLOB,
    name            IN   VARCHAR2   := NULL,
    description     IN   VARCHAR2   := NULL,
    category        IN   VARCHAR2   := NULL,
    validate        IN   BOOLEAN    := TRUE)
RETURN VARCHAR2; 

DBMS_SQLDIAG.CREATE_SQL_PATCH (
    sql_id          IN   VARCHAR2,
    hint_text       IN   CLOB,
    name            IN   VARCHAR2   := NULL,
    description     IN   VARCHAR2   := NULL,
    category        IN   VARCHAR2   := NULL,
    validate        IN   BOOLEAN    := TRUE)
RETURN VARCHAR2; 

Leider gibt es hier nur die Möglichkeit, entweder die SQL_ID oder den SQL_TEXT mitzugeben. Eine „FORCE MATCHING“ Option, wie es für SQL Profiles gibt, steht nicht zur Verfügung. SQL Patches sind seitens Oracle nicht als Tuning Methode geplant, sondern nur um einen problematischen Execution Plan zu modifizieren.

Damit haben wir nur die Möglichkeit, einen SQL Patch pro SQL_ID zu erzeugen. Ist das überhaupt eine realistisch nutzbare Methode?

Jein.

Wenn die Entwickler konsequent auf Bind Variablen setzen, haben wir schon gewonnen. Der Inhalt der Variablen hat keinen Einfluss auf die SQL_ID, somit bezieht sich der SQL Patch immer auf das Statement an sich.

Werden keine Bind Variablen genutzt, kann man diese mittels CURSOR_SHARING=FORCE erzwingen. Für OLTP Applikationen wird dies nur in seltenen Fällen zu (Performance)problemen führen. In DWH/DSS Applikationen sollte man CURSOR_SHARING auf dem Default EXACT belassen.

Die Einstellung für CURSOR_SHARING kann sowohl in der Session als auch auf der Instanz gesetzt werden. Sofern mehrere Applikationsschemata oder Benutzer mit der Datenbank arbeiten, kann man die Einstellung für die Session auch in einen Logon Trigger anpassen.

Versuchen wir es einmal mit CURSOR_SHARING in der Session.

Cursor Sharing Force in Session

Cursor Sharing für die Session einschalten:

alter session set cursor_sharing = force;

Das Statement einmal laufen lassen (bitte beachten, der Kommentar lautet jetzt STMT2, damit Oracle neu parsem muss):

select /* STMT2 */ first_name, salary 
  from employees 
 where salary > 5000 
   and First_name like '%a';

und in V$SQLAREA das Ergebnis ansehen

select sql_id, sql_text 
  from v$sqlarea 
 where sql_text like 'select /* STMT2%';

SQL_ID	      SQL_TEXT
------------- -------------------------------------------------------------
dut5pw7kn5gy1 select /* STMT2 */ first_name, salary from employees where salary > :"SYS_B_0" and First_name like :"SYS_B_1"

Wie man hier sehen kann, wurden die Konstanten/Literale durch Bind Variablen SYS_B% ersetzt. Damit wird die SQL_ID gleich bleiben, solange sich nur die Inhalte der Konstanten/Literale ändern.

Passen wir unseren SQL Patch entsprechend an:

declare
  v_name varchar2(128);
begin
  begin
    /* optional: Patch droppen, sofern dieser schon existiert */
    dbms_sqldiag.drop_sql_patch(name=>'DBM_TUNING_SQL_PATCH_1');
    exception when others then dbms_output.put_line(sqlerrm);
  end;
  v_name := dbms_sqldiag.create_sql_patch( -
       sql_id=>'dut5pw7kn5gy1', -
       hint_text=>'INDEX(@SEL$1 EMPLOYEES EMP_NAME_IX)', -
       name=>'DBM_TUNING_SQL_PATCH_1');
end;
/

und probieren es mit einer Änderung beim Gehalt (nur den Wert ändern, sonst nichts am Statement ändern!):

select /* STMT2 */ first_name, salary 
  from employees 
 where salary > 666 
   and First_name like '%a';

Kontrolle in V$SQLAREA, ob es keine neue SQL_ID gibt:

select sql_id, sql_text 
  from v$sqlarea 
 where sql_text like 'select /* STMT2%';

SQL_ID	      SQL_TEXT
------------- -------------------------------------------------------------
dut5pw7kn5gy1 select /* STMT2 */ first_name, salary from employees where salary > :"SYS_B_0" and First_name like :"SYS_B_1"

Perfekt. Jetzt überprüfen wir den Ausführungsplan:

select * from table(dbms_xplan.display_cursor('dut5pw7kn5gy1',null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID	dut5pw7kn5gy1, child number 0
-------------------------------------
select /* STMT2 */ first_name, salary   from employees  where salary
> :"SYS_B_0"    and First_name like :"SYS_B_1"

Plan hash value: 612698390

-----------------------------------------------------------...
| Id  | Operation                           | Name        |...
-----------------------------------------------------------...
|   0 | SELECT STATEMENT                    |             |...
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES   |...
|*  2 |   INDEX SKIP SCAN                   | EMP_NAME_IX |...
-----------------------------------------------------------...

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMPLOYEES@SEL$1
   2 - SEL$1 / EMPLOYEES@SEL$1

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

   1 - filter("SALARY">:SYS_B_0)
   2 - access("FIRST_NAME" LIKE :SYS_B_1)
       filter("FIRST_NAME" LIKE :SYS_B_1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "FIRST_NAME"[VARCHAR2,20], "SALARY"[NUMBER,22]
   2 - "EMPLOYEES".ROWID[ROWID,10], "FIRST_NAME"[VARCHAR2,20]

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   1 -  SEL$1 / EMPLOYEES@SEL$1
           -  INDEX(@SEL$1 EMPLOYEES EMP_NAME_IX)

Note
-----
   - SQL patch "DBM_TUNING_SQL_PATCH_1" used for this statement

Das Ergebnis ist wie erwartet. Es wird der Index genutzt und in der Note wird bestätigt, dass der SQL Patch zur Verwendung gekommen ist.

Zusammenfassung

SQL Patch bietet die Möglichkeit, den Ausführungplan von Statements als DBA zu beeinflussen. In Kombination mit CURSOR_SHARING=FORCE hat man eine wirkliche Alternative zu SQL Profiles. Das ist vor allem für jene interessant, die auf die Enterprise Edition mit Diagnostic und Tuning Pack verzichten müssen.

Wenn ein Statement aus mehreren Query Blöcken besteht (Subselects, Inline Views, With clause,…), ist es manchmal schwierig den richtigen Select Block zu identifizieren.

Schreibe einen Kommentar

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.