Logo Oracle Deutschland   DBA Community  -  Mai 2012
Real Application Testing Teil 2: SQL Performance Analyzer
von Ulrike Schwinn, Oracle Deutschland B.V. & Co. KG



Real Applicaton Testing steht seit Oracle Datenbank Version 11g als neue Option der Datenbank zur Verfügung. Es handelt sich dabei um 2 Komponenten - Database Replay (kurz DB Replay) und SQL Performance Analyzer (kurz SPA). Wichtig zu wissen ist, daß SPA nicht nur unabhängig von DB Replay nutzbar ist, sondern auch als eine gute Ergänzung bzw. Vorbereitung für den DB Replay Testlauf dienen kann. Database Replay wurde in Teil 1 im ersten Teil des Tipps ausführlich besprochen und demonstriert. In diesem Artikel liegt der Fokus nun auf der Komponente SQL Performance Analyzer.

Wie Database Replay lässt sich der SQL Performance Analyzer einfach über die graphische Oberfläche des Enterprise Managers oder auch im Linemode über Package Zugriffe bzw. SQL Aufrufe verwenden. Auch hier sollte die Umgebung gewissen Voraussetzungen genügen:

  • Bevor man startet, sollte man sich unbedingt die My Oracle Support Note 560977.1 ansehen, um unter Umständen notwendige Patches einzuspielen.
  • Überprüfen Sie, ob Real Application Testing auch wirklich installiert worden ist. Dies geschieht über die View V$OPTION. Der VALUE sollte dabei auf TRUE stehen.
  • Informieren Sie sich über die Lizenzierung des Produkts. Real Application Testing ist eine zusätzliche Option der Datenbank. Möchte man das vollständige Report bzw. Tuning Framework nutzen, ist zusätzlich die Lizenzierung von Diagnostics und Tuning Pack notwendig. Mehr dazu finden Sie auch im Licensing Guide.
Um einen besseren Überblick zu gewährleisten, gliedert sich der Artikel in folgende Abschnitte:

SPA im Überblick

Der Fokus von SQL Performance Analyzer liegt auf der detaillierten Statement Analyse eines definierten SQL Workloads. Ein SQL Workload besteht dabei aus SELECT bzw. DML Statements (nur im Linemode), die über ein SQL Tuning Set (kurz STS) zur Verfügung gestellt werden. Der SQL Workload wird dabei zweimal abgespielt - vor der Veränderung und nach einer Veränderung. Das Ergebnis ist eine detaillierte Vergleichsanalyse - vor und nach der Veränderung - der einzelnen Statements gemessen an verschiedenen Metriken beispielsweise elapsed time. Möchte man nun einen Tuning Prozess anschliessen, ist dies einfach möglich durch die Integration des SQL Tuning Advisors oder durch die Nutzung von SQL Plan Baselines.

Vorteil von SPA ist die einfache Handhabung und die Verfügbarkeit. Da keine DML-Statements die Testumgebung verändern - DML Statements werden automatisch zurück gerollt - ist kein Zurücksetzen nach dem Test erforderlich. Zudem kann die Analyse schon für 9i Datenbanken erfolgen. Der folgende Screenshot zeigt ein Beispiel eines SPA-Reports in Enterprise Manager Database Control.


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

Wie erzeugt man nun SQL Tuning Sets? Da eine gute Integration von SPA in DB Replay existiert, können SQL Tuning Sets zum Beispiel automatisch beim DB Replay Capture (ab Version 11.2.0.2) oder beim Replay erzeugt werden. Folgende Aufrufe zeigen die Verwendung. 
-- STS beim Capture - nur moeglich fuer NON RAC DBs
execute dbms_workload_capture.start_capture (name        => '&capturename', -
                                             dir         => '&dir', -
                                             capture_sts => TRUE);
-- STS beim Replay
execute dbms_workload_replay.prepare_replay(synchronization    => &TRUE_FALSE, -
                                            connect_time_scale => &Wert1_100, -
                                            think_time_scale   => &Wert1_100, -
                                            capture_sts        => TRUE);
Unabhängig davon ist es möglich SQL Tuning Sets über AWRs oder aus dem Cache zu erzeugen und gegebenenfalls über Data Pump Export und Import auf eine andere Datenbank zu verlagern. Wer sich genauer über SQL Tuning Sets informieren möchte, kann folgende Tipps zu rate ziehen. Hinweis: Im Fall von 9i Datenbanken können die SQL Tuning Sets über SQL Traces erzeugt werden.

Wie funktioniert SPA? Nach der Erzeugung eines STS wird SPA für den ersten Testlauf gestartet, danach wird die Umgebung verändert und SPA zum zweiten Mal durchgeführt. Bei beiden Durchläufen werden die entsprechenden Statistiken (wie elapsed time (default), cpu time, disk reads, direct writes, optimizer costs etc.) gesammelt, die Ausführungspläne gespeichert und der Anteil am Workload notiert, um eine Gewichtung des Statements durchzuführen. Um relevante Ergebnisse zu erzielen, erfahren die Statements ab Release 11g Release 2 ein "Warm up" und werden mehrfach ausgeführt. Die Analyse ordnet dabei die Statements nach Relevanz für den Workload (siehe "Net Impact on Workload(%)"), listet die verschiedenen Metriken pro Statement auf, zeigt an, ob Ausführungspläne "gekippt" sind und gibt die Ausführungspläne aus.

SPA im Enterprise Manager

In der Regel ist es sehr einfach SPA über die graphische Oberfläche wie Grid Control, Cloud Control oder Database Control anzuwenden. Um einen kleinen Einblick in die Nutzung zu erhalten, wird in folgendem Abschnitt die Vorgehensweise kurz erläutert. Bevor man starten kann, ist ein STS notwendig, das entweder über PL/SQL Aufrufe oder auch über die graphische Oberfläche (im Performance Bereich) leicht generiert werden kann. Danach navigiert man auf die Startseite von SPA. Je nach Art des Werkzeugs ist dies entweder in Database Control über "Software and Support => SQL Performance Analyzer" oder in Cloud Control 12c über "Performance => SQL => SQL Performance Analyzer" möglich. Die Startseite (siehe folgender Screenshot generiert mit Cloud Control 12c) und das weitere Vorgehen ist danach bei jedem Werkzeug gleich.


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

Die beiden Links "Upgrade from 9i or 10i" und "Upgrade from 10.2 or 11g" unterstützen die Nutzung bei Verwendung in unterschiedlichen Datenbank Versionen im Upgrade Fall. Für 9i und 10.1 Datenbanken müssen dabei zusätzlich aus SQL Traces SQL Tuning Sets erzeugt werden. Der Link "Parameter Change" ermöglicht das einfache Ändern eines einzigen Initialisierungsparameters (Auswahl aus 158 Parametern). Danach wird der Parameter automatisch gesetzt, und die beiden Testläufe werden durchgeführt. Seit 11g gibt es die Möglichkeit Statistiken über die Technologie "Pending Statistics" vor der produktiven Nutzung vorab zu testen (siehe auch Community Tipp ). Auch dies wird in der graphischen Oberfläche im Link "Optimizer Statistics" berücksichtigt. Bei komplexeren Setups sollte der Link "Guided Workflow" verwendet werden. Dieser ermöglicht die Testumgebung zwischen dem ersten und zweiten Lauf selbst zu erstellen - zum Beispiel mehr als einen Initialisierungsparameter zu verändern, das Storage zu ändern usw. Die Liste der Ergebnisse mit verschiedenen Metriken sieht dann wie folgt in Enterprise Manager Database Control aus.


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

Ein Klick auf das Brillensymbol führt dann zu der Ergebnisseite (siehe Screenshot zum Beispiel eines SPA-Reports). Reichen die Metrikvergleiche zur Bewertung nicht aus, kann über den Button "Run SQL Trial Comparison" schnell ein weiterer Report generiert werden.

SPA im Linemode

Steht für das Testsystem keine graphische Oberfläche zur Verfügung, können die entsprechenden PL/SQL Packages wie DBMS_SQLPA und DBMS_SQLTUNE für die Linemode Nutzung verwendet werden. Der folgende Abschnitt demonstriert die Verwendung an einem Beispiel - dabei sollen auch DML Statements berücksichtigt werden. (Zur Erinnerung: DML Statements werden bei Nutzung der graphischen Oberfläche noch nicht unterstützt; es werden nur SELECT Statements bzw SELECT Anteile in Statements berücksichtigt.)

Hinweis: Die Skripte stehen zum Download zur Verfügung. Das Readme führt Sie durch die Anwendung der einzelnen Skripte.

  1. Im ersten Schritt wird eine SQL Tuning Task erzeugt. Für unseren Fall ist schon das SQL Tuning Set SPA_DML1 vorab generiert worden. Zur Kontrolle überprüfen wir die View USER_ADVISOR_TASKS. 
    variable tname varchar2(100)
execute :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'SPA_DML1');

SELECT task_name, status FROM user_advisor_tasks WHERE task_name = :tname;
TASK_NAME                      STATUS
------------------------------ -----------
TASK_21137                     INITIAL
  2. Da alle DML Statements berücksichtigt werden sollen, muss eine zusätzliche Parameter-Einstellung (EXECUTE_FULLDML) vorgenommen werden. Der Default ist übrigens FALSE. Weitere Informationen zu den Einstellungen finden Sie im Handbuch Table 138-10. 
    execute DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name   => 'TASK_21137', -
                                               parameter   => 'EXECUTE_FULLDML', -
                                               value       => 'TRUE');
  3. Nun wird der erste Test durchgeführt. Um ein "Warm Up" des Buffer Caches vorzubereiten und um möglichst gute Run-Time Statistiken zu erhalten, werden die Statements ab 11g Release 2 mehrfach ausgeführt (siehe Parametereinstellung DISABLE_MULTI_EXEC). Die Langläufer unter den Statements werden dabei nur zweimal ausgeführt. 
    execute DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name       => 'TASK_21137', -
                                         execution_type  => 'test execute', -
                                         execution_name  => 'Test1');


    4. Nun kann die Umgebung entsprechend angepaßt werden. Beispiele für Änderungen wären: Einstellungen der Initialisierungsparameter (OPTIMIZER_FEATURES_ENABLE etc.), Änderung der Speicherung (Komprimierung etc.), Hinzufügen von Indizes usw.

  4. Danach erfolgt ein weiterer SPA Testlauf. 
    execute DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (task_name       => 'TASK_21137', - 
                                          execution_type  => 'test execute ', -
                                          execution_name  => 'Test2');
  5. Nun soll die Auswertung erfolgen. Die Metrik buffer_gets ist für die Bewertung in unserem Beispiel ausschlaggebend. Andere mögliche Bewertungsmaßstäbe stehen über die Metrik-Einstellungen cpu_time, disk_reads, optimizer_cost, elapsed_time (default) oder direct_writes zur Verfügung. 
    execute DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (task_name        => 'TASK_21137', -
                                          execution_type   => 'COMPARE PERFORMANCE', -
                                          execution_name   => 'Vergleich1', -
                                   execution_params => dbms_advisor.arglist('comparison_metric', 'buffer_gets'));
  6. Nun werden die zugehörigen Berichte erzeugt. Mögliche Formate sind TEXT, HTML oder XML. Mit dem Parameter LEVEL lassen sich zusätzlich Detail-Reports zu den einzelnen Themenbereichen generieren wie Berichte über Statements mit geänderten Plänen (CHANGED_PLANS), verschlechterte (REGRESSED) oder verbesserte (IMPROVED) Statements. 
    --Ueberblicksreport
set long 1000000 longchunksize 1000000 linesize 200 head off feedback off echo off
variable ergebnis clob

execute :ergebnis := dbms_sqlpa.report_analysis_task(task_name => 'TASK_21137', -
                                                     type      => 'HTML', -
                                                     section   => 'SUMMARY');
/* 
Report fuer Statements mit geaenderten Plaenen
execute :ergebnis:= dbms_sqlpa.report_analysis_task(task_name => 'TASK_21137', -
                                                    type      => 'HTML',-
                                                    level     => 'CHANGED_PLANS',- 
                                                    section   => 'ALL');
Report fuer "regressed statements"
execute :ergebnis:= dbms_sqlpa.report_analysis_task(task_name => 'TASK_21137', -
                                                    type      => 'HTML',-
                                                    level     => 'REGRESSED', -
                                                    section   => 'ALL');
*/
spool report.html
print ergebnis
spool off
    7. Folgender Screenshot zeigt einen Ausschnitt aus dem generierten Bericht report.html.


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

    Das anschliessende Tuning

    Nachdem wir im letzten Schritt einen Überblick über die Performance Metriken und Pläne der Statements erhalten haben, heisst es den Umfang der Performance-Unterschiede abzuschätzen. Bei großen Unterschieden und vielen betroffenen Statements sollte die Ursache in der Gesamtkonfiguration gesucht werden. Falls nur einige Statements betroffen sind, gibt es zwei Optionen wie man mit "regressed statements" umgehen kann. Die erste Option wäre den SQL Tuning Advisor zu nutzen und die Empfehlungen - wie Profiles, Statementänderungen, Statistiken, alternative Pläne usw. - zu implementieren.
    Hinweis: Hierzu muss das Tuning Pack lizenziert sein.

    Der andere Weg wäre mit SQL Plan Baselines zu arbeiten. Die Idee ist, die Statements mit ihren originalen Ausführungsplänen als SQL Plan Baselines festzulegen. Nutzt man die graphische Oberfläche zum Beispiel im Enterprise Manager Database Control, lassen sich beide Möglichkeiten durch einen einfachen Klick auf den Button " Run SQL Tuning Advisor" oder "Create SQL Plan Baselines" implementieren.


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

    Möchte man auch diese Schritte im Linemode durchführen, bietet sich die Nutzung der Packages DBMS_SQLTUNE und DBMS_SPM an. Folgendes Beispiel zeigt die Implementierung des SQL Tuning Advisors im Linemode. Ausgangslage ist das Beispiel aus dem oberen Abschnitt. 
    variable name varchar2(200)
execute :name:= DBMS_SQLTUNE.CREATE_TUNING_TASK(spa_task_name    => 'TASK_21137', -
                                                spa_task_owner   => 'SYS', -
                                                spa_compare_exec => 'Vergleich1',-  
                                                task_name        => 'TUNING_SPA');
execute DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => :name);
    Das Ergebnis wird dann folgendermassen ausgegeben: 
    set long 1000000 longchunksize 1000000 linesize 200 head off feedback off echo off
variable report clob
execute :report := DBMS_SQLTUNE.REPORT_TUNING_TASK(task_name => 'TUNING_SPA',- 
                                                   type      => 'TEXT',-
                                                   level     => 'TYPICAL',- 
                                                   section   => 'ALL');
print report
...
-------------------------------------------------------------------------------
                      Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed                      : 3
Number of SQLs in the Report                 : 3
Number of SQLs with Findings                 : 3
Number of SQLs with Statistic Findings       : 3
Number of SQLs with Alternative Plan Findings: 3
Number of SQLs with Index Findings           : 2
-------------------------------------------------------------------------------
    SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
object ID  SQL ID        statistics profile(benefit) index(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
         3 29d388ssdjrrj          1                          99.34%
         4 9t8bgrfusbfav          1                          99.34%
         2 cyzznbykb509s          1
-------------------------------------------------------------------------------
    Objects with Missing/Stale Statistics (ordered by schema, object, type)
-------------------------------------------------------------------------------
Schema Name                  Object Name                  Type  State   Cascade

---------------------------- ---------------------------- ----- ------- -------
                          SH T                            TABLE STALE   NO
...
    Die Empfehlungen in unserem Beispiel weisen darauf hin, daß Statistiken veraltet sind, Indizes hinzugefügt werden könnten und alternative Pläne existieren.

    Möchte man hingegen SQL Plan Baselines für die Statements mit "gekippten" Ausführungsplänen nutzen, sind folgende Schritte erforderlich. Zuerst muß eine SQL Tuning Set Untermenge (SUB_STS1) für die "schlechten" (regressed) Statements erzeugt werden. 
    execute DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name =>'SUB_STS1', description => 'Regressed Statements');
DECLARE
  sqlset_cur  DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN sqlset_cur FOR
    SELECT value(p)
    FROM table(
      DBMS_SQLTUNE.SELECT_SQLPA_TASK('TASK_21137','Vergleich1','REGRESSED')) p;
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'SUB_STS1', populate_cursor => sqlset_cur);
  CLOSE sqlset_cur;
END;
/
    Dann können die entprechenden Ausführungspläne mit einem einzigen Aufruf als Baselines geladen werden. Fertig! 
    DECLARE
  my_plans PLS_INTEGER;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'SUB_STS1');
END;
/
    Möchten Sie noch weitere Informationen zu Baselines haben, lesen Sie unseren Beitrag in der Community dazu.

    Informationen und Links

    Folgende Tipps und Links können hilfreich 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