X

Technologie - Trends - Tipps&Tricks
in deutscher Sprache

SPA mal anders - SQL-Tuning mit SQL Performance Analyzer (SPA)

Was tun? Die Performance eines Datenbank Workloads ist nicht (mehr) das was sie verspricht. Häufig hat man SQL Statements in Verdacht, die aus irgendwelchen Gründen nicht mehr performant laufen. Die Ursachen dafür können vielfältig sein - eine Änderung an den Zugriffsobjekten wie Indizes, an Statistiken, an der Datenbankumgebung selbst z.B. bei einer Migration/Upgrade u.v.m. Die betroffenen Statements müssen gefunden, analysiert und getunet werden. Mit einem AWR Report lassen sich beispielsweise die TOP n Statements finden. Ein zusätzliches SQL Tracing und die Analyse von Ausführungsplänen und -Statistiken kann anschliessend beim Tunen helfen. Je nach Anzahl der Statements kann der Tuning Aufwand dabei allerdings entsprechend groß werden.

Der SQL Performance Analyzer (auch kurz SPA) kann hier Abhilfe schaffen! Wie Database Replay ist SPA eine Komponente von Real Application Testing, die in der Enterprise Edition der Oracle Cloud eingeschlossen ist.

Noch einmal zur Erinnerung: Bei Real Application Testing handelt es sich um ein Werkzeug der Datenbank, das einen Workload aufzeichnen und in einer Testumgebung wieder abspielen kann. Keine Installation einer separaten Werkzeug-Software oder spezielles Skripting für Real Application Testing ist dafür nötig. SPA und DB Replay können auch unabhängig voneinander verwendet. Sie sind allerdings auch eine gute Ergänzung. So kann es sinnvoll sein, zuerst die SQL Performance mit SPA zu überprüfen und danach den gesamten Workload mit DB Replay unter Berücksichtigung aller konkurrierenden Zugriffe zu testen. Oder umgekehrt: bei der Aufzeichnung von DB Replay den SQL Workload mitaufzuzeichnen und danach separat mit SPA zu evaluieren und zu tunen.

Interessiert an der Database Replay Komponente? Eine Beschreibung und Anleitung mit Linemode Skripten findet sich in unserem Blogbeitrag Testen mit Database Replay.

Wie funktioniert der SQL Performance Analyzer? Der Fokus von SQL Performance Analyzer liegt auf der detaillierten Statement Analyse eines definierten SQL Workloads. Ein SQL Workload kann dabei aus SELECT bzw. DML Statements bestehen, die über ein SQL Tuning Set (kurz STS) zur Verfügung gestellt werden. Der Workload wird zweimal abgespielt - einmal VOR der Veränderung und dann NACH einer Veränderung. Das Ergebnis ist eine detaillierte Vergleichsanalyse (vor und nach der Veränderung) der einzelnen Statements, gemessen an verschiedenen Metriken wie beispielsweise elapsed time, cpu_time etc. Optional besteht anschliessend die Möglichkeit den Tuning Prozess zu automatisieren - mit dem SQL Tuning Advisor oder über die Verwendung von SQL Plan Baselines.

Zur Ausführung kann ähnlich wie bei Database Replay die graphische Oberfläche über Enterprise Manager Cloud Control oder das PL/SQL Package DBMS_SQLPA sowie die zugehörigen Data Dictionary Views verwendet werden. Die vollständige Funktionalität des Package und der Data Dictionary Views lässt sich im Handbuch in den entsprechenden Kapiteln (siehe unten) nachlesen.

Folgendes Beispiel zeigt einen exemplarischen Ablauf unter Verwendung von Linemode Skripten. Möchte man das Ganze selbst ausprobieren, kann man diese Skripte hier herunterladen. (Ein Readme zur Erläuterung des Ablaufs ist im zip File enthalten :)).

Zusammengefasst sind folgende Schritten notwendig:

  • Generierung eines SQL Tuning Sets
  • Erste Ausführung VOR der Veränderung
  • Zweite Ausführung NACH der Veränderung
  • Auswertung(en) gemäß der gewählten Metrik
  • Generierung von verschiedenen Berichten

 

Der Ablauf

Voraussetzung für SPA ist die Bereitstellung eines SQL Tuning Sets (auch STS). Dazu stehen mehrere Möglichkeiten zur Verfügung. SQL Tuning Sets können beispielsweise aus AWRs oder aus dem Cache erzeugt werden und gegebenenfalls über Data Pump Export und Import auf eine andere Datenbank verlagert werden. Entweder ist dies graphisch über den Enterprise Manager oder im Linemode mit dem Package DBMS_SQLTUNE möglich. Die SQL Tuning Set Funktionalität haben wir in einem vorangegangenen Tipp ausführlich erläutert. Mehr darüber kann man im Blogposting Oracle SQL Tuning Sets - Grundlage zum SQL Tuning nachlesen. Ein paar nützliche Skripte finden sich hier zum Download.

Im folgenden Beispiel generieren wir ein SQL Tuning Set STS1 aus den AWR Reports.

-- STS creation with name STS1
execute dbms_sqltune.create_sqlset(sqlset_name =>'STS1');

-- STS1: SELECT statements in parsing schema DWH_DATA
DECLARE
  cur dbms_sqltune.sqlset_cursor;
BEGIN
   OPEN cur FOR
       SELECT VALUE(P)
       FROM dbms_sqltune.select_workload_repository
        (begin_snap=>5716, end_snap=>5717,  basic_filter => 'upper(sql_text) like ''SELECT%''
                                            and parsing_schema_name = ''DWH_DATA''') P;
       dbms_sqltune.load_sqlset (sqlset_name => 'STS1', populate_cursor => cur);
    END;
/

Monitoren lassen sich die SQL Tunings Sets mit Data Dictionary Views wie DBA_SQLSET oder DBA_SQLSET_STATEMENTS.

select name, owner, statement_count 
from dba_sqlset;

NAME                 OWNER      STATEMENT_COUNT
-------------------- ---------- ---------------
STS1_SUB             US                       5
STS1A                US                       5
STSR                 US                      27
STS2                 US                     283
STS1                 US                      96
STS3                 US                      44
RAt3_r_315627        SYS                      6

Um SPA zu verwenden ist zuerst eine spezielle TASK erforderlich, die mit CREATE TASK des Package DBMS_SQLPA erzeugt wird. Monitoren lässt sich die Task über USER_ADVISOR_TASKS.

 
-- create analysis task
declare
  tname varchar2(100);
begin
   tname :=  DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'STS1');
 end;
 /
PL/SQL procedure successfully completed.

select task_name, status, advisor_name
       from user_advisor_tasks;

TASK_NAME       STATUS      ADVISOR_NAME
--------------- ----------- -------------------------
TASK_115        INITIAL   SQL Performance Analyzer

Danach wird mit EXECUTE_ANALYSIS_TASK ein erster Lauf durchgeführt.

 
-- first run
execute DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name  => 'TASK_115', -
                                         execution_type  => 'test execute', -
                                         execution_name  => 'RUN_BEFORE');

Nun muss die Umgebung entsprechend angepaßt/geändert werden. Beispiele für Änderungen sind Einstellungen der Initialisierungsparameter (OPTIMIZER_FEATURES_ENABLE etc.), Änderung der Speicherung (Komprimierung etc.), Hinzufügen von Indizes usw. Danach erfolgt der zweite Durchlauf.

 
-- Ignore Optimizer Hints
alter session set optimizer_ignore_hints=true;

Session altered.

-- second run
execute DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name  => 'TASK_115', -
                                         execution_type  => 'test execute', -
                                         execution_name  => 'RUN_AFTER');

Nun werden noch Auswertungen nach unterschiedlichen Metriken wie buffer_gets, cpu_time, disk_reads, optimizer_cost, elapsed_time (default), direct_writes etc. berechnet. Dazu verwendet man wieder die Prozedur EXECUTE_ANALYSIS_TASK - dieses Mal allerdings mit EXECUTION_TYPE compare performance. Pro Aufruf kann dabei jeweils eine Metrik zur Bewertung herangezogen werden, die dann unter einem Execution Name abgespeichert wird. Folgende Beispiele führen Bewertungen zu ELAPSED_TIME und CPU_TIME durch.

 
-- metric elasped_time
execute DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (task_name        => 'TASK_115', -
                                          execution_type   => 'COMPARE PERFORMANCE', -
                                          execution_name   => 'ELA', -
                                   execution_params => dbms_advisor.arglist('comparison_metric', 'ELAPSED_TIME'));

--metric cpu_time
execute DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (task_name        => 'TASK_115', -
                                          execution_type   => 'COMPARE PERFORMANCE', -
                                          execution_name   => 'CPU', -
                                   execution_params => dbms_advisor.arglist('comparison_metric', 'CPU_TIME'));

Einen Überblick über die Executions kann man mit folgender Abfrage bekommen.

 
select execution_name, task_name, status, advisor_name 
from user_advisor_executions;

EXECUTION_NAME  TASK_NAME            STATUS      ADVISOR_NAME
--------------- -------------------- ----------- -------------------------
RUN_AFTER       TASK_115             COMPLETED   SQL Performance Analyzer
RUN_BEFORE      TASK_115             COMPLETED   SQL Performance Analyzer
ELA             TASK_115             COMPLETED   SQL Performance Analyzer
CPU             TASK_115             COMPLETED   SQL Performance Analyzer

4 rows selected.

Nun können die zugehörigen Berichte in TEXT-, HTML- oder XML- Format generiert werden. 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. Der folgende Aufruf zeigt die Generierung eines Summary Reports. Verwendet wird dabei die Metrik elasped_time, die unter dem Namen ELA abgespeichert ist.

 
--summary report in HTML for elapsed_time
spool summary.html

set long 1000000 longchunksize 1000000 linesize 200 head off feedback off echo off
select dbms_sqlpa.report_analysis_task('TASK_115', 'HTML', 'TYPICAL','SUMMARY', null, 100, 'ELA')
from dual;

spool off

Im Screenshot ist ein Ausschnitt aus dem Report zu sehen.

Möchte man weitere Details erhalten wie zum Beispiel die einzelnen Ausführungspläne und Execution Statistiken vor und nach der Änderung, kann man mit folgendem Aufruf einen detaillierten HTML Report für die Statements generieren.

-- detail report for all statements 
set long 1000000 longchunksize 1000000 linesize 200 head off feedback off echo off
spool detail.html

select dbms_sqlpa.report_analysis_task('TASK_115', 'HTML', 'ALL', 'ALL', null, 100, 'ELA') detailed_rep 
from dual;

spool off

Im Screenshot ist ein Ausschnitt aus dem Report zu sehen.



Folgende Aufrufe zeigen weitere Beispiele für Berichte - einmal für "improved" und dann für "regressed" Statements.

 
set long 1000000 longchunksize 1000000 linesize 200 head off feedback off echo off
-- only improved statements
spool improved.html

select dbms_sqlpa.report_analysis_task('TASK_115', 'HTML', 'IMPROVED', 'ALL', null, 100, 'ELA') detailed_rep 
from dual;

spool off

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

set long 1000000 longchunksize 1000000 linesize 200 head off feedback off echo off
-- only regressed statements
spool regressed.html

select dbms_sqlpa.report_analysis_task('TASK_115', 'HTML', 'REGRESSED', 'ALL', null, 100, 'ELA') detailed_rep 
from dual;

spool off

Nun kann man weitere Tests durchführen und das Ergebnis mit den entsprechenden Reports dokumentieren.

Tuning mit Advanced Features

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 weitere 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.

Der andere Weg wäre mit SQL Plan Baselines zu arbeiten. Die Idee ist, die Statements mit ihren originalen "besseren" Ausführungsplänen als SQL Plan Baselines festzulegen. 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 von Plan Baselines für die "regressed" Statements aus dem SQL Tuning Set STS1. Zuerst wird eine Untermenge des SQL Tuning Sets erzeugt, das nur die "regressed" Statements enthält. In unserem Fall handelt es sich um 3 Statements, wie wir bei der Überprüfung der Tuning Sets feststellen können.

 
execute DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'STSR', description=> 'Regressed Statements');

-- select STS for 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_115', null,'ELA', 'REGRESSED')) p;
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STSR', populate_cursor => sqlset_cur);
  CLOSE sqlset_cur;
END;
/

select elapsed_time, cpu_time, executions, sql_text
from dba_sqlset_statements
where sqlset_name ='STSR';

ELAPSED_TIME   CPU_TIME EXECUTIONS
------------ ---------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
     1453756    1433161          1
SELECT
    'G' || tt1.PG_FeatureValue_19_1 PG_FeatureValue_19_1,
    'B' || 1010

     3455568    3314237          1
SELECT
    'B' || 1010415 repchannel_1,
    'B' || tt1.domain_productgroup_1 dom

    40194308   33679037        390
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :1))

Jetzt können die entsprechenden Ausführungspläne mit einem einzigen Aufruf als Baselines geladen werden. Fertig!

 
set pause on
set serveroutput on
DECLARE
  new_plans PLS_INTEGER;
BEGIN
  new_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'STSR');
  dbms_output.put_line('######### number of plans for regressed statements = '||new_plans);
END;
/
######### number of plans for regressed statements = 3

 

Fazit

Vorteile von SPA sind neben der einfachen und schnellen Handhabung auch die Verfügbarkeit. DML Statements können ausgeführt werden, verändern aber die Testumgebung nicht, da DML Statements automatisch zurückgerollt werden. So ist kein Zurücksetzen nach dem Test erforderlich. Grundsätzlich erfahren auch alle Statements eine Art "Warm up" und werden mehrfach ausgeführt, so dass ein realistischer Vergleich stattfindet. Ausserdem bieten die SPA Berichte eine gute Dokumentation der einzelnen Tuningschritte. Hat man keinen Enterprise Manager zur Verfügung kann man wie wir im Beispiel gezeigt haben auch den Linemode verwenden.

Um den Einstieg zu erleichern, kann man sich hier die Skripte downloaden.

Weitere Varianten der Skripte lassen sich aus dem Handbuch ableiten. Folgende Links können dazu hilfreich sein.

 

         

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.