SQL Pläne in 11<i>g</i> und das SQL Plan Management Feature

DBA Community - November 2008 (zuletzt ergänzt Januar 2012)

SQL Pläne in 11g und das SQL Plan Management Feature

von Ulrike Schwinn, ORACLE Deutschland GmbH

Jede Umgebung unterliegt Veränderungen. Upgrades, Einführung zusätzlicher Zugriffsstrukturen, Einführung oder Änderungen von Datenbankparametern stellen nur einige Beispiele dar. Dies kann auch bei optimal getunten Applikationen zu Änderungen des Ausführungsplans (SQL Plans) und somit zu veränderter Performance und im schlechtesten Fall zu längeren Ausführungszeiten führen. Wünschenswert wäre den "Status Quo" zu erhalten und die "alten" Ausführungspläne beibehalten zu können. Der Einsatz von Statement Hints oder die Nutzung von Stored Outlines sind mögliche Lösungsansätze. Nachteil dieser Methoden ist allerdings die fehlende Flexibilität und Weiterentwicklung der Pläne: neue Ausführungspläne, die zu einer Verbesserung führen könnten, werden nicht mehr in Betracht kommen. Diese Problematik wird flexibel durch das sogenannte SQL Plan Management (auch SQL Plan Control oder kurz SPM) gelöst.

Hinweis: SQL Plan Management steht ohne zusätzliche Installation in der Enterprise Edition der Datenbank zur Verfügung.

In folgendem Tipp wird der Einsatz von SPM an einem Beispiel demonstriert. Die gewählte Methode um eine automatische Generierung zu erreichen, ist die Nutzung von speziellen Initialisierungsparametern.

Wie funktioniert nun SQL Plan Management? Voraussetzung für das SQL Plan Management ist die Tatsache, dass SQL Pläne von sich wiederholenden Statements aufgezeichnet werden und im SYSAUX Tablespace in der sogenannten SQL Base gespeichert werden. Nach wiederholter Ausführung eines 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" 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.

Um diese Funktionen zu demonstrieren, stellen wir zuerst sicher, dass das Aufzeichnen (auch Capture) von SQL Plan Baselines eingeschaltet ist. Dazu müssen wir den Parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES auf TRUE (Default FALSE) setzen.

ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;

bzw. auch systemweit mit

ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE SCOPE=MEMORY;

Nun führen wir folgende Abfrage auf die Tabelle SH.SALES mehrfach durch:

SQL> SELECT * FROM sales WHERE amount_sold > 30 ORDER BY prod_id;

Als Nächstes wird überprüft, ob eine Baseline erzeugt worden ist. Dies kann anhand des Ausführungsplans oder über die View DBA_SQL_PLAN_BASELINES erfolgen:

SQL> SELECT sql_handle,sql_text, plan_name, origin, enabled, accepted, 
     TO_CHAR(last_verified,'dd.mm.yyyy hh24:mi') verification
     FROM dba_sql_plan_baselines
     WHERE sql_text LIKE '%sales%'; 

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME                      ORIGIN         ENA ACC VERIFICATION
------------------------------ -------------- --- --- ----------------

SYS_SQL_0b7958c381e9005a

SELECT * FROM sales WHERE amount_sold > 30 ORDER BY prod_id

SYS_SQL_PLAN_81e9005a54bc8843  AUTO-CAPTURE   YES YES

Der SQL Plan "SYS_SQL_PLAN_81e9005a54bc8843" stellt die SQL Plan Baseline für das Statement "SYS_SQL_0b7958c381e9005a" dar. Der Plan ist eingeschaltet (siehe ENABLED) und akzeptiert (siehe ACCEPTED), allerdings nicht zusätzlich verifiziert (siehe VERIFICATION) worden.

Der folgende Screenshot zeigt die Implementierung dieser Funktionalität im Enterprise Manager:

Für eine größere Ansicht auf das Bild klicken.

Um sicherzustellen, dass SQL Plan Baselines verwendet werden, muss überprüft werden, ob der Parameter OPTIMIZER_USE_SQL_PLAN_BASELINES den Wert TRUE besitzt. Dies ist übrigens auch die Standardeinstellung.

SQL> show parameter optimizer_use_sql_plan_baselines

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_sql_plan_baselines     boolean     TRUE

Um im nächsten Schritt die Nutzung der SQL Plan Baseline zu überprüfen, wird der Ausführungsplan des Statements ausgewertet. Neu in 11g ist die Möglichkeit mit DBMS_XPLAN die SQL Plan Baselines anzuzeigen. Das folgende Beispiel zeigt die Verwendung:

SQL> set linesize 130
SQL> SELECT * FROM TABLE(dbms_xplan.display_sql_plan_baseline(SQL_HANDLE=>'SYS_SQL_0b7958c381e9005a'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
SQL handle: SYS_SQL_0b7958c381e9005a
SQL text: SELECT * FROM sales WHERE amount_sold > 30 ORDER BY prod_id
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_81e9005a54bc8843
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 3803407550
------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |   906K|    25M|       |  7751   (2)| 00:01:34 |       |       |
|   1 |  SORT ORDER BY       |       |   906K|    25M|    83M|  7751   (2)| 00:01:34 |       |       |
|   2 |   PARTITION RANGE ALL|       |   906K|    25M|       |   501   (4)| 00:00:07 |     1 |    28 |
|*  3 |    TABLE ACCESS FULL | SALES |   906K|    25M|       |   501   (4)| 00:00:07 |     1 |    28 |
------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("AMOUNT_SOLD">30)
26 rows selected.

Um in unserem Fall eine Änderung des Ausführungsplan zu bewirken, setzen wir beispielsweise den Parameter OPTIMIZER_INDEX_COST_ADJ von 100 (Default) auf den Wert 1. Damit wird der Optimizer bei der Wahl des Zugriffspfads beeinflusst und wählt eine "indexfreundliche" Variante aus. Danach üerprüfen wir erneut den Ausführungsplan und müssen feststellen, dass keine Veränderung eingetreten ist.

Es wird mit dem SQL Plan Management gearbeitet, daher wird die Baseline automatisch genutzt:

SQL> ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=1;
Session altered.

SQL> set autotrace traceonly explain

SQL> SELECT * FROM sales WHERE amount_sold > 30 ORDER BY prod_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 3803407550
------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |   906K|    25M|       |  7751   (2)| 00:01:34 |       |       |
|   1 |  SORT ORDER BY       |       |   906K|    25M|    83M|  7751   (2)| 00:01:34 |       |       |
|   2 |   PARTITION RANGE ALL|       |   906K|    25M|       |   501   (4)| 00:00:07 |     1 |    28 |
|*  3 |    TABLE ACCESS FULL | SALES |   906K|    25M|       |   501   (4)| 00:00:07 |     1 |    28 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("AMOUNT_SOLD">30)
Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_81e9005a54bc8843" used for this statement

Setzen wir den Parameter OPTIMIZER_USE_SQL_PLAN_BASELINES auf FALSE, ändert sich der SQL Plan. Nun werden keine SQL Plan Baselines mehr genutzt.

Die Änderung des Plans sieht dann folgendermassen aus:

SQL> ALTER SESSION SET OPTIMIZER_USE_SQL_PLAN_BASELINES=FALSE;
Session altered.

SQL> SELECT * FROM sales WHERE amount_sold > 30 ORDER BY prod_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 899219946
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          |Rows|Bytes|TempSpc|Cost(%CPU)|Time    |Pstart|Pstop|
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |906K|  25M|       | 7305  (2)|00:01:28|      |     |
|   1 |  SORT ORDER BY                      |               |906K|  25M|    83M| 7305  (2)|00:01:28|      |     |
|   2 |   PARTITION RANGE ALL               |               |906K|  25M|       |   55 (48)|00:00:01|     1|   28|
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID|SALES          |906K|  25M|       |   55 (48)|00:00:01|     1|   28|
|   4 |     BITMAP CONVERSION TO ROWIDS     |               |    |     |       |          |        |      |     |
|   5 |      BITMAP INDEX FULL SCAN         |SALES_PROMO_BIX|    |     |       |          |        |     1|   28|
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("AMOUNT_SOLD">30)

Die erneute Ausführung zeigt, dass nun ein weiterer Ausführungsplan Verwendung findet. Die Baseline wird wie vorgesehen nicht verwendet wird.

Überprüfen wir nun die View DBA_SQL_PLAN_BASELINES können wir feststellen, dass nun zwei SQL Pläne aufgelistet sind. Der initiale Baseline Plan "SYS_SQL_PLAN_81e9005a54bc8843" hat dabei den Status "akzeptiert" (siehe ACCEPTED), der neue Plan "SYS_SQL_PLAN_81e9005a11df68d0" den Status "nicht akzeptiert".

SQL> SELECT sql_handle,sql_text, plan_name, origin, enabled, accepted,
     TO_CHAR(last_verified,'dd.mm.yyyy hh24:mi') verification
     FROM dba_sql_plan_baselines
     WHERE sql_text LIKE '%amount_sold%';

SQL_HANDLE
------------------------------
SQL_TEXT
----------------------------------------------------------------------
PLAN_NAME                      ORIGIN         ENA ACC VERIFICATION
------------------------------ -------------- --- --- ----------------

SYS_SQL_0b7958c381e9005a
SELECT * FROM sales WHERE amount_sold > 30 ORDER BY prod_id
SYS_SQL_PLAN_81e9005a11df68d0  AUTO-CAPTURE   YES NO

SYS_SQL_0b7958c381e9005a
SELECT * FROM sales WHERE amount_sold > 30 ORDER BY prod_id
SYS_SQL_PLAN_81e9005a54bc8843  AUTO-CAPTURE   YES YES

Der folgende Screenshot zeigt die Implementierung dieser Funktionalität im Enterprise Manager:

Für eine größere Ansicht auf das Bild klicken.

Die Baseline kann auch explizit ausgeschaltet werden, entweder im Enterprise Manager oder mit dem Package DBMS_SPM. Folgendes Beispiel zeigt das Ausschalten eines Baseline über das Packages DBMS_SPM:

SQL> variable v1 varchar2(30); 
SQL> exec :v1:= dbms_spm.alter_sql_plan_baseline(SQL_HANDLE      =>'SYS_SQL_0b7958c381e9005a',-
                                                 PLAN_NAME       =>'SYS_SQL_PLAN_81e9005a54bc8843',-
                                                 ATTRIBUTE_NAME  =>'accepted',-
                                                 ATTRIBUTE_VALUE =>'NO');

SQL Pläne lassen sich nicht nur ein- und ausschalten, sondern auch vollständig löschen und natürlich auch laden bzw entladen. Diese Funktionen sind wie gerade am Beispiel gezeigt mit dem Package DBMS_SPM oder graphisch mit dem Enterprise Manager durchzuführen.

Manuell akzeptieren sollte man allerdings nur verifizierte Pläne, um vor Performanceeinbussen sicher zu sein. SQL Pläne, die vom Optimizer zur Historie hinzugefügt werden, können mit einem speziellen Mechnismus (auch evolve genannt) verifiziert werden. D.h. es wird geprüft, ob ein neuer Plan bessere Performance aufweist als ein ausgewählter Plan aus der Baseline. Ist dies der Fall, dann wird der Plan zur Baseline hinzugefügt.

Zum Abschluss soll dieser Evolve Mechanismus an unserem Beispiel demonstriert werden. Dazu steht entweder die Funktion EVOLVE_SQL_PLAN_BASELINE des Package DBMS_SPM oder die graphische Oberfläche des Enterprise Managers zur Verfügung.

Folgendes Beispiel zeigt eine Validierung mit dem Package DBMS_SPM. Dabei sollen alle nicht akzeptierten Pläne des Statements "SYS_SQL_0b7958c381e9005a" überprüft werden. In unserem Fall handelt es sich dabei allerdings genau um einen Plan.

SQL> variable vclob clob
SQL> execute :vclob:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_0b7958c381e9005a')
PL/SQL procedure successfully completed.

SQL> print vclob
VCLOB
-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------

  SQL_HANDLE = SYS_SQL_0b7958c381e9005a
  PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SYS_SQL_PLAN_81e9005a11df68d0
-----------------------------------

  Plan was verified: Time used 1.54 seconds.
  Failed performance criterion: Compound improvement ratio <= .67.



                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:            507466         507466
  Elapsed Time(ms):             459            928               .49
  CPU Time(ms):                 456            682               .67
  Buffer Gets:                 4667           4574              1.02
  Disk Reads:                     0            126                 0
  Direct Writes:                  0              0
  Fetches:                        0             28                 0
  Executions:                     1              1'
-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.

Offensichtlich erfüllt der getestete Plan nicht die erforderlichen Kriterien und bietet keine bessere Performance als die vorgegebene SQL Plan Baseline. Darüberhinaus ermöglicht die Funktion EVOLVE_SQL_PLAN_BASELINE auch einzelne Pläne oder eine Liste von Plänen zur Valdierung mitanzugeben. Das Handbuch Oracle Database PL/SQL Packages and Types Reference 11g Release 1 (11.1) liefert eine vollständige Beschreibung des Package DBMS_SPM.

Überprüft man nun im letzten Schritt die View DBA_SQL_PLAN_BASELINES, zeigt der Status dieses Plans weiterhin den Wert "nicht akzeptiert" an. Zusätzlich dazu wird die Uhrzeit der Verifikation mitangegeben.

SQL> SELECT sql_handle,sql_text, plan_name, origin, enabled, accepted,
     TO_CHAR(last_verified,'dd.mm.yyyy hh24:mi') verification
     FROM dba_sql_plan_baselines
     WHERE sql_text LIKE '%amount_sold%';

SQL_HANDLE
------------------------------
SQL_TEXT
----------------------------------------------------------------------
PLAN_NAME                      ORIGIN         ENA ACC VERIFICATION
------------------------------ -------------- --- --- ----------------

SYS_SQL_0b7958c381e9005a
SELECT * FROM sales WHERE amount_sold > 30 ORDER BY prod_id
SYS_SQL_PLAN_81e9005a11df68d0  AUTO-CAPTURE   YES NO  30.10.2008 14:51

SYS_SQL_0b7958c381e9005a
SELECT * FROM sales WHERE amount_sold > 30 ORDER BY prod_id
SYS_SQL_PLAN_81e9005a54bc8843  AUTO-CAPTURE   YES YES

Fazit

Die Nutzung von Initialisierungsparameter zur Erzeugung von SQL Plan Baselines ist eine Methode, die einfach anzuwenden ist. 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 Baselines erhalten - rekursive Statements und auch Abfragen wie "select sysdate from dual". Daher wird empfohlen diese Methode nur für bestimmte Anwendungen über eine definierte Zeitdauer zu verwenden oder die Baselines manuell aus dem Cursor Cache oder einem SQL Tuning Set zu generieren.

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