Logo Oracle Deutschland   DBA Community  -  Januar 2012
Ausführungspläne beeinflussen mit SQL Plan Management
von Ulrike Schwinn, Oracle Deutschland B.V. & Co. KG

Beim manuellen Tuning von SQL Statements stellt sich häufig die Frage, wie man die Ausführung von Statements beeinflussen kann. Indirekt ist dies möglich durch Änderung an Optimizereinstellungen in der Umgebung, Änderungen von Statistiken, Änderungen an den Zugriffsstrukturen oder natürlich auch direkt mit Hints. Möchte oder kann man keine Hints in den Applikationen programmieren und trotzdem den Optimizer auf direkte Art und Weise beeinflussen, kann man den Einsatz von SQL Plan Management in Erwägung ziehen. Was ist SQL Plan Management? SQL Plan Management (kurz SPM) ist in der Datenbankversion Oracle 11g eingeführt worden und steht ohne zusätzliche Installation in der Enterprise Edition der Datenbank zur Verfügung. Ziel von SPM ist es, stabile und optimale Performance für ausgewählte SQL Statements zu erlangen. Dabei ist es wichtig, dass nicht nur die Stabilität der Pläne - wie bei Stored Outlines - implementiert ist, sondern eine Anpassung bei Veränderung ermöglicht wird.

Welche Anwendungsfälle gibt es? Statements sollen stabile Performance aufweisen und darüberhinaus einen "guten Ausführungsplan" auch bei Veränderungen beibehalten. Dies ist besonders dann interessant, wenn sich die "SQL Umgebung" für das Statement ändert. Dies kann durch Migration, Änderungen an Initialisierungsparametern, Veränderungen der Statistiken usw. eintreten. Allerdings kann das SQL Plan Management auch eingesetzt werden um Änderungen an den Ausführungsplänen zu erzwingen. Der Tipp demonstriert diesen Anwendungsfall und ist in folgende Abschnitte unterteilt:

Grundsätzliches

Wie funktioniert nun SQL Plan Management? Voraussetzung für das SQL Plan Management ist das Aufzeichnen von SQL Plänen sich wiederholender Statements im SYSAUX Tablespace. Nach wiederholter Ausführung des Statements wird der SQL Plan in eine sogenannte SQL Plan Baseline aufgenommen und gilt somit als Massstab für die folgenden Ausführungen. Ändert sich beim nächsten Parsen der Ausführungsplan wird dieser neue SQL Plan nicht genutzt, sondern in der sogenannten SQL Plan Historie abgespeichert. Der initiale SQL Plan, die SQL Plan Baseline, kommt zur Ausführung. So werden zu jedem Statement die SQL Pläne in der SQL Plan Historie zusätzlich zu den SQL Plan Baselines gespeichert. SQL Pläne aus der Historie kommen erst dann zum Einsatz, wenn sie für den Optimizer "akzeptabel" und "enabled" sind und zur SQL Plan Baseline gehören. Ein zusätzlicher Prozess der Verifikation hilft, den SQL Plan auf seine Güte hin zu testen und dann zur Baseline als akzeptierten Plan hinzuzufügen.

Wie kann nun eine SQL Baseline erzeugt werden? Folgende vier Methoden ermöglichen das Erzeugen von SQL Baselines:
  1. aus einem existierenden SQL Tuning Set (auch STS)
  2. aus dem Cursor Cache
  3. aus einer Staging Tabelle - nach einem Export und Import von existierenden Baselines aus einem System
  4. automatisch über Initialisierungsparameter
Die Fälle 1 bis 3 können unter Zuhilfenahme des Package DBMS_SPM implementiert werden (siehe auch Kapitel Informationen). Fall 4, die automatische Erzeugung mit Initialisierungsparameter, sowie das Monitoring und die Evolution von SQL Plänen wird ausführlich in Tipp SQL Pläne in 11g und das SQL Plan Management Feature beschrieben.

Hinweis: Systemweites bzw. permanentes Setzen von OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES auf den Wert TRUE hat allerdings eine hohe Speicherverwendung im SYSAUX Tablespace zur Folge. Alle sich wiederholenden Statements würden eine SQL Plan Baseline erhalten - rekursive Statements wie auch Abfragen wie "select sysdate from dual". Daher wird empfohlen diese Methode nur für bestimmte Anwendungen über eine definierte Zeit zu verwenden oder die Baselines manuell aus dem Cursor Cache oder einem SQL Tuning Set zu generieren.

Ausführungspläne beeinflussen

Nehmen wir in unserem Beispiel an, Sie können einen Ausführungsplan nur durch Verwendung von Hints oder durch andere Veränderungen beeinflussen. Das Einfügen von Hints in die Statements ist allerdings in der Porduktionsumgebung nicht gewährleistet. Die Idee ist nun, mit Plan Management eine Statementversion mit Hint als SQL Plan Baseline zu definieren und mit derjenigen Statementversion ohne Hint zu verbinden. Dieses Verfahren wird in den folgenden Schritten demonstriert.

1. Schritt
In einer SQL*Plus Session wird zuerst das SQL Statement ohne Hint - das "schlechte" Statement - ausgeführt, um mit dem SQL Plan Baseline Capture zu beginnen. 
SQL> SELECT PROD_NAME, SUM(AMOUNT_SOLD)
     from Sales s, Products p
     where s.prod_id=p.prod_id  and PROD_CATEGORY like '&prod_name'
     group by PROD_NAME;

PROD_NAME                                          SUM(AMOUNT_SOLD)
-------------------------------------------------- ----------------
Envoy External 6X CD-ROM                                  645586.12
Model SM26273 Black Ink Cartridge                         617732.28
Model K8822S Cordless Phone Battery                       582640.54
Bounce                                                    244595.65
Smash up Boxing                                           260436.75
...
Das Statement verwendet leider keinen Index bei der Ausführung. Dies soll nun durch SQL Plan Management erreicht werden.

2. Schritt
Im nächsten Schritt wird die SQL ID des Statements abgefragt und eine Baseline aus dem Cursor Cache unter Zuhilfenahme des DBMS_SPM Package erzeugt. 
SQL> SELECT sql_id, sql_fulltext 
     FROM v$sql WHERE sql_text LIKE '%from Sales s, Products p%';
SQL_ID        SQL_FULLTEXT
------------- ----------------------------------------
accd372bxtnm0 SELECT PROD_NAME, SUM(AMOUNT_SOLD)
              from Sales s, Products p
              where s.prod_id=p.pr
SQL> variable cnt number;
SQL> execute :cnt :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'accd372bxtnm0');


3. Schritt
Das Ganze wird über die View DBA_SQL_PLAN_BASELINES kontrolliert. Die SQL Plan Baseline ist enabled. 
SQL> SELECT sql_handle, sql_text, plan_name, enabled 
     FROM dba_sql_plan_baselines WHERE sql_text LIKE '%from Sales s, Products p%';

SQL_HANDLE                     SQL_TEXT
------------------------------ ----------------------------------------
PLAN_NAME                      ENA
------------------------------ ---
SQL_31575736896b20e3           SELECT PROD_NAME, SUM(AMOUNT_SOLD)
                               from Sales s, Products p
                               where s.prod_id=p.pr
SQL_PLAN_32pur6u4qq87342949306 YES
4. Schritt
Nun wird die Baseline ausgeschaltet - auch dieses lässt sich mit dem Package DBMS_SPM einfach bewerkstelligen. ATTRIBUTE_VALUE muss dabei auf NO gesetzt werden. 
SQL> variable cnt number;
SQL> execute :cnt:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (-
              SQL_HANDLE     =>'SQL_31575736896b20e3', -
              PLAN_NAME      =>'SQL_PLAN_32pur6u4qq87342949306', -
              ATTRIBUTE_NAME =>'enabled', -
              ATTRIBUTE_VALUE=>'NO'); 

SQL> SELECT sql_handle, plan_name, enabled, accepted, sql_text 
     FROM dba_sql_plan_baselines WHERE sql_text LIKE '%from Sales s, Products p%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SQL_TEXT
----------------------------------------
SQL_31575736896b20e3           SQL_PLAN_32pur6u4qq87342949306 NO  YES
SELECT PROD_NAME, SUM(AMOUNT_SOLD)
from Sales s, Products p
where s.prod_id=p.pr
Die Kontrollabfrage auf DBS_SQL_PLAN_BASELINE bestätigt die Durchführung.

5. Schritt
Nun wird das Statement unter Nutzung eines Index Hints ausgeführt. Die SQL_ID und der PLAN_HASH_VALUE sind für das Erzeugen des akzeptierten Plans notwendig. 
SQL> SELECT /*+ INDEX(p PRODUCTS_PROD_CAT_IX) */ PROD_NAME, SUM(AMOUNT_SOLD)
     from Sales s, Products p
     where s.prod_id=p.prod_id
     and PROD_CATEGORY like '&prod_name' 
     group by PROD_NAME;
...

SQL> SELECT sql_id, plan_hash_value, sql_fulltext 
     FROM v$SQL WHERE sql_text LIKE '%SELECT /*+ INDEX(p%';
SQL_ID        PLAN_HASH_VALUE SQL_FULLTEXT
------------- --------------- ----------------------------------------
9b3zvw95b85k2      2270238699 SELECT /*+ INDEX(p PRODUCTS_PROD_CAT_IX)
                               */ PROD_NAME, SUM(AMOUNT_SOLD)
                              from Sal
6. Schritt
Im letzten Schritt wird der neue akzeptierte Plan mit "SQL_ID 9b3zvw95b85k2" und PLAN_HASH_VALUE "2270238699" mit dem originalen SQL Statement und dessen "SQL_HANDLE SQL_31575736896b20e" verbunden (siehe auch Schritt 4). 
SQL> execute :cnt:=dbms_spm.load_plans_from_cursor_cache(-
                   sql_id          =>'9b3zvw95b85k2', -
                   plan_hash_value => 2270238699, -
                   sql_handle      => 'SQL_31575736896b20e3');
Die Kontrollabfrage zeigt, dass wir nun zwei verschiedene Pläne mit einem einzigen SQL_HANDLE besitzen. Ein Plan ist dabei enabled, der andere disabled. 
SQL> SELECT sql_handle, plan_name, enabled, accepted, sql_text 
     FROM dba_sql_plan_baselines WHERE sql_text LIKE '%from Sales s, Products p%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SQL_TEXT
----------------------------------------
SQL_31575736896b20e3           SQL_PLAN_32pur6u4qq87342949306 NO  YES
SELECT PROD_NAME, SUM(AMOUNT_SOLD)
from Sales s, Products p
where s.prod_id=p.pr

SQL_31575736896b20e3           SQL_PLAN_32pur6u4qq873f1c7133b YES YES
SELECT PROD_NAME, SUM(AMOUNT_SOLD)
from Sales s, Products p
where s.prod_id=p.pr
Nun können wir das Statement noch einmal ohne Verwendung eines Hints ausführen und den Ausführungsplan abfragen. Wir sehen, dass die Baseline "SQL_PLAN_32pur6u4qq873f1c7133b" Verwendung findet und somit der Hint die Ausführung beeinflusst. 
SQL> set autotrace on explain

SQL> SELECT PROD_NAME, SUM(AMOUNT_SOLD)
     from Sales s, Products p
     where s.prod_id=p.prod_id  and PROD_CATEGORY like '&prod_name'
     group by PROD_NAME;

Execution Plan
----------------------------------------------------------
Plan hash value: 2270238699
--------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | C
ost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------
--------------------------------------

|   0 | SELECT STATEMENT              |                      |    71 |  3976 |
 379  (19)| 00:00:05 |       |       |

|   1 |  HASH GROUP BY                |                      |    71 |  3976 |
 379  (19)| 00:00:05 |       |       |

|*  2 |   HASH JOIN                   |                      |   918K|    49M|
 329   (7)| 00:00:04 |       |       |

|   3 |    TABLE ACCESS BY INDEX ROWID| PRODUCTS             |    72 |  3384 |
   9   (0)| 00:00:01 |       |       |

|*  4 |     INDEX FULL SCAN           | PRODUCTS_PROD_CAT_IX |    72 |       |
   1   (0)| 00:00:01 |       |       |

|   5 |    PARTITION RANGE ALL        |                      |   918K|  8075K|
 314   (5)| 00:00:04 |     1 |    28 |

|   6 |     TABLE ACCESS FULL         | SALES                |   918K|  8075K|
 314   (5)| 00:00:04 |     1 |    28 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("S"."PROD_ID"="P"."PROD_ID")
   4 - filter("PROD_CATEGORY" LIKE '%')

Note
-----
   - SQL plan baseline "SQL_PLAN_32pur6u4qq873f1c7133b" used for this statement

Informationen und hilfreiche Links

Folgende Handbucheinträge und Links können nützlich sein: Mehr zu diesem Thema bzw. zu weiteren Themen rund um die Datenbank lesen Sie in den nächsten Ausgaben ...

Zurück zur Community-Seite