Welche Mittel stehen zur Verfügung um SQL Performance bei eine Migration von einer On-Premises Datenbank nach Autonomous Database (genauer ATP) zu überprüfen oder gar zu garantieren? Wie lassen sich einfach Tests – speziell wenn es um SQL Workloads geht – bewerkstelligen? Was kann man tun gegen das “ungewisse” Gefühl, nicht alle Statements beim Testen “erwischt” oder vielleicht wichtige übersehen zu haben? Und wie erhält man eine Gesamt-Aussage über die Performance des Statement Workloads? Diese und andere Fragen erreichen uns immer häufiger zu diesem Thema.

Zwei Technologien in der Oracle Datenbank könnten diese Aufgaben lösen – nämlich SQL Tuning Sets und SQL Performance Analyzer. Beide sind dabei nicht nur in On-Premiese Installationen, sondern auch für Oracle Cloud im speziellen Oracle Autonomous Database verfügbar.

Kurz zur Erinnerung: SQL Tuning Sets gibt es schon seit der Einführung des SQL Tuning Frameworks in der Oracle Datenbank. Ein SQL Tuning Set ist, wie der Name schon andeutet, eine Sammlung von SQL Statements, die unter einem User definierten Namen als Objekt in der Datenbank abgespeichert wird. Das Interessante daran ist, dass nicht nur die SQL Kommandos selbst, sondern auch zugehörige Informationen über die Ausführung mitabgespeichert werden. 
Ein SQL Tuning Set (STS) besteht dabei aus folgenden Komponenten:

  • Ein oder mehrere SQL Statements
  • Kontext zur Ausführung wie Bind Variablen, Parsing Schema usw.
  • Grundlegende Statistiken zur Ausführung wie Elapsed Zeit, CPU Zeit usw.
  • Ausführungspläne

Mehr dazu findet sich im Internet  oder auch in unserem deutschsprachigen Posting “Oracle SQL Tuning Sets – Grundlage zum SQL Tuning“. 

Aber wie kann man danach Tests mit dem STS durchführen? Hier kommt der SQL Performance Analyzer (SPA), eine Komponente von Real Application Testing, zum Einsatz. 
Kurz zur Erinnerung: 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.
Auf diese Weise lassen sich Statements aus dem Workload nicht nur testen und vergleichen, sondern auch tunen, falls dies erforderlich ist. Mehr dazu findet sich auch Posting “SPA mal anders – SQL-Tuning mit SQL Performance Analyzer (SPA)“.

Um das Zusammenspiel beider Techniken zu veranschaulich, werden in den folgenden Abschnitten die notwendigen Schritte im Source System und im Target System (in Autonomous Database) aufgelistet und demonstriert. Um möglichst vielen Oracle Anwender zu erreichen, zeigen wir die Verwendung im Linemode und nutzen die beiden Packages DBMS_SQLTUNE für das SQL Tuning Set und DBMS_SQLPA für SQL Performance Analyzer. Einfacher geht es mit der graphischen Komponente – dem Enterprise Manager Cloud Control, dessen Verwendung sich in diesen Fällen ohne weitere Erklärung von alleine erschliesst. Mehr Informationen und Links zur weiteren Verwendung der Packages finden sich am Ende des Postings.

Source Datenbank

1. Erzeuge das STS 

In unserem Fall nutzen wir die Möglichkeit, ein STS aus AWR-Snapshots zu erstellen. Wir verwenden dabei die Filterkriterien SQL-Text, Parsing-Schema und die Anzahl der Ausführungen. Das folgende Snippet zeigt ein Codebeispiel.

-- check existing STS 
SELECT name, owner, created, statement_count 
FROM dba_sqlset where owner!='SYS'; 

-- drop and create an empty STS 
execute dbms_sqltune.drop_sqlset(sqlset_name=>'STS1', sqlset_owner=>'DWH1'); 
execute dbms_sqltune.create_sqlset(sqlset_name=>'STS1', sqlset_owner=>'DWH1') 

-- create statements from AWR snapshots 
DECLARE cur dbms_sqltune.sqlset_cursor; 
 BEGIN OPEN cur   
   FOR SELECT VALUE(P) FROM dbms_sqltune.select_workload_repository(begin_snap =>235, end_snap=> 236,   
   basic_filter => 'sql_text like ''SELECT%'' and executions<3 and parsing_schema_name = ''DWH1''') P; 
   dbms_sqltune.load_sqlset (sqlset_name => 'STS1', populate_cursor => cur); 
END; 
/ 
-- check statements of the STS 
SELECT executions, cpu_time/1000, elapsed_time/1000, sql_id, substr(sql_text,1,80) 
FROM dba_sqlset_statements 
WHERE sqlset_name='STS1' ORDER BY 3 desc;

Möchte man die Anzahl der Topn SQL Statements in den Snapshots erhöhen – der Default ist 30, kann man DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS dazu verwenden wie z.B.

execute dbms_workload_repository.modify_snapshot_settings(topnsql => 100);

2. STS in einer Tabelle

In folgendem Beispiel werden die Informationen zu dem STS in die mit der Prozedur CREATE_STGTAB_SQLSET erstellte Staging-Tabelle S_TABLE gespeichert. Bitte vermeiden Sie den Tablespace SYSTEM als Speicherort.

drop table staging_table;
-- create empty staging table
execute dbms_sqltune.create_stgtab_sqlset(table_name => 'S_TABLE'); 
-- copy STS information in the staging table
execute dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'STS1', -
                                        sqlset_owner => 'DWH1', -
                                        staging_table_name => 'S_TABLE');

3. Data Pump Export der Staging Tabelle

expdp DWH1/"password"@servicename tables= S_TABLE directory=STSDIR dumpfile=stagingfile.dmp

Autonomous Database ATP

1. Kopie der dmp Datei in den Object Storage


Bild 1: dmp Datei der Staging Table im Object Storage

2. Data Pump Import via SQL Developer, Instant Client, CloudShell etc.


Bild 2: Data Pump Import im SQL Developer

3. STS aus der Staging Tabelle
Diese Prozedur kopiert die Informationen aus der Staging Tabelle S_TABLE in das Schema DWH1 und wandelt sie in ein SQL-Tuning-Set um. Im folgenden Beispiel existiert der User DWH1 auch in der Autononous Database. Möchte man einen anderen Owner verwenden, ist vorab ein Mapping mit DBMS_SQLTUNE.REMAP_STGTAB_SQLSET erforderlich.

execute dbms_sqltune.unpack_stgtab_sqlset(sqlset_name => 'STS1', -
                                          sqlset_owner => 'DWH1', - 
                                          replace => TRUE, -
                                          staging_schema_owner => 'DWH1', - 
                                          staging_table_name => 'S_TABLE');

Danach kann man über die Data Dictionary Views DBA_SQLSET und DBA_SQLSET_STATEMENTS den Erfolg der Aktion überprüfen.

SQL Performance Analyzer
Nun kann das Testen mit SQL Performance Analyzer beginnen. Wie bereits beschrieben, werden dabei die Execution Statistiken aus zwei Testläufen miteinander verglichen. Der erste Testlauf liefert die Informationen zu den Execution Statistiken aus dem Source System, der zweite Testlauf zu denen aus der Autonomous Database.  Darüberhinaus können natürlich weitere beliebige Testläufe durchgeführt werden.

1. Erzeuge eine Analysis Task
Wie bei den meisten Tuning Tasks in der Oracle Datenbank wird zuerst eine Analysis Task benötigt.

-- drop and create an analysis task
execute dbms_sqlpa.drop_analysis_task(task_name => '&taskname');

declare
	  tname varchar2(100);
begin
	   tname :=  dbms_sqlpa.create_analysis_task(sqlset_name => '&STSname');
end;
/
-- check the tasks
SELECT task_name, status, advisor_name 
FROM user_advisor_tasks;

2. Erster Testlauf
Nun werden die SQL- Execution Statistiken aus einem ersten Workload benötigt. Entweder führt man den Workload über Database Links in der Source Datenbank aus oder man benutzt die Möglichkeit die Informationen aus dem importierten STS direkt zu verwenden. Die Einstellung erfolgt über den Parameter EXECUTION_TYPE, wie z.B.

  • [TEST] EXECUTE – führt jede SQL-Anweisung testweise aus und sammelt deren Ausführungspläne und Ausführungsstatistiken. Die resultierenden Pläne und Statistiken werden im Advisor Framework gespeichert. Dies ist die Standardeinstellung.
  • CONVERT SQLSET – wird verwendet, um die in einem SQL Tuning Set erfassten Statistiken zu lesen und sie als Taskausführung zu modellieren. Dies kann verwendet werden, wenn eine erneute Ausführung der SQL Statements auf dem Quellsystem vermieden werden soll, weil bereits gültige Daten für den Test im SQL Tuning Set vorhanden sind.

In unserem Beispiel verwenden wir die Einstellung CONVERT SQLSET, die auf das importierte und entpackte SQL Tuning Set STS1 angewendet wird. Hierbei vergeben wir einen Ausführungsnamen mit EXECUTION_NAME.

-- list the task name
SELECT task_name, status, advisor_name 
FROM user_advisor_tasks;

-- convert STS
execute dbms_sqlpa.execute_analysis_task(task_name => '&taskname', -
	          execution_type => 'convert sqlset', -
	          execution_name => 'first_run', -
	          execution_params => DBMS_ADVISOR.ARGLIST(
	                               'sqlset_name', 'STS1', -
	                               'sqlset_owner', 'DWH1'));

3. Zweiter Testlauf
Jetzt führen wir den zweiten Testlauf mit der Einstellung TEST EXECUTE in der Autonomous Database Umgebung aus. 

execute dbms_sqlpa.execute_analysis_task(task_name => '&taskname', -
                                         execution_type => 'test execute', - 
                                         execution_name => 'second_run');

Natürlich können weitere Testläufe durchgeführt werden. Dazu wird die Einstellung TEST EXECUTE verwendet und ein Namen für die Ausführung vergeben –  zum Beispiel THIRD_RUN etc.

Gut zu wissen: Bei den Ausführungsstatistiken pro Testlauf werden die Statistiken  über mehrere Ausführungen (5 oder gar mehr) gemittelt. Das bedeutet allerdings auch wenn bei der ersten Ausführung I/O stattfindet, wird diese Information mit in die Statistik eingerechnet. Um das auszugleichen, kann es sinnvoll sein mehr als einen SPA TEST EXECUTE Testlauf durchzuführen.

4. Vergleich
Standardmäßig werden immer die SQL Performance Daten der letzten beide Läufe verglichen. SPA verwendet ELAPSED_TIME als Default Metrik für den Vergleich. Mit DBMS_ADVISOR.ARGLIST können Default Metrik und/oder andere Analysis Task Parameter geändert werden.

execute dbms_sqlpa.execute_analysis_task(task_name => '&taskname', -
                                         execution_type => 'compare', -
                                         execution_name => 'comparison', -
                            execution_params => DBMS_ADVISOR.ARGLIST('workload_impact_threshold', 0,- 
                                                                     'sql_impact_threshold', 0));

Will man unterschiedliche Testläufe und nicht nur die letzten beiden miteinander vergleichen, kann man dies mit folgendem Befehl tun:

execute dbms_sqlpa.execute_analysis_task(task_name => '&taskname', -
                                         execution_type => 'compare', - 
                                         execution_params => DBMS_ADVISOR.ARGLIST('execution_name1', 'first run',- 
                                                                                  'execution_name2', 'third_run', - 
                                                                                  'workload_impact_threshold', 0, -
                                                                                    'sql_impact_threshold', 0));

Hier wird der Testlauf FIRST_RUN mit dem Testlauf THIRD_RUN verglichen.

5. Reports
Nun können die zugehörigen Berichte in verschiedenen Formaten 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 in unserem Fall die Metrik ELAPSED_TIME, die der Default ist. Dieser kann jedoch bzgl. anderer Metriken angezeigt werden, indem einfach EXECUTE_ANALYSIS_TASK aus Schritt 4 mit geänderten EXECUTION_PARAMS aufgerufen wird. Der folgenden Code Snippet zeigt die Generierung eines Summary Reports. 

-- summary report
VAR rep CLOB;
execute :rep := dbms_sqlpa.report_analysis_task(task_name => '&taskname', -
                                                type => 'HTML', -
                                                level => 'TYPICAL', - 
                                                section => 'SUMMARY');

set long 1000000 longchunksize 1000000 linesize 200 head off feedback off echo off
spool summary.html
PRINT :rep
spool off

Oder auch mit einem SELECT Statement wie …

set trimspool on trim on pages 0 linesize 1000 long 1000000 longchunksize 1000000
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => '&taskname', level => 'TYPICAL', section => 'SUMMARY')
FROM dual;

Das Ergebnis sieht dann folgendermaßen aus.


Bild 3: Ausschnitt aus dem Summary Report

Falls Sie im Report weitere Details wie einzelne Ausführungspläne und Execution Statistics miteinander vergleichen wollen, können Sie zusätzliche detaillierte HTML Reports generieren. Dafür müssen nur die Parameter LEVEL und SECTION entsprechend geändert werden. Um einen Überblick über alle Einstellungen zu bekommen, kann man im Handbuch die Beschreibung dazu finden.

Folgendes Beispiel zeigt alle Details im Report. Wir verwenden für SECTION und LEVEL den Wert ALL.

VAR rep CLOB;
execute :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => '&taskname', type=>'HTML', level => 'ALL', section => 'ALL');
set long 1000000 LONGCHUNKSIZE 1000000 LINESIZE 200 pages 0
spool allreport.html
PRINT :rep
spool off

Folgendes Beispiel zeigt einen Report nur für die “regressed” Statements. 

-- detail report about regressed statements  
VAR rep CLOB; 
execute :rep := dbms_sqlpa.report_analysis_task(task_name => '&taskname',-   
                                                type => 'text', -
                                                level => 'REGRESSED', -
                                                section => 'ALL'); 

set long 1000000 longchunksize 1000000 linesize 200 head off feedback off echo off 
spool detailregressed.html 
PRINT :rep spool off

Sind viele Statements von einer Performance Regression betroffen, sollte die Ursache zuerst 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 Management zu arbeiten. Die Idee ist dabei, die Statements mit ihren originalen “besseren” Ausführungsplänen als SQL Plan Baselines festzulegen. Möchte man auch diese Schritte im Linemode durchführen, kann man die Packages DBMS_SQLTUNE für SQL Tuning Advisor und DBMS_SPM für SQL Plan Management dazu verwenden.

Fazit
Die Vorteile von SQL Performance Analyzer liegen in der einfachen Handhabung und die breite Palette von Berichten. Außerdem ist kein Zurücksetzen nach den Tests erforderlich, da DML Statements zurückgerollt werden. Die Berichte bieten eine gute Dokumentation der einzelnen Tuningschritte und geben nicht nur einen Gesamtüberblick sondern bieten auch Bewertungsmöglichkeiten nach unterschiedlichen Metriken. Hat man kein Enterprise Manager Cloud Control zur Verfügung, kann man einfache Package Aufrufe im Linemode dazu verwenden. Die Handhabung für Autonomous Database unterscheidet sich nicht von der Verwendung in anderen Oracle Cloud oder On-Premises Umgebungen. SPA ist vorinstalliert und ohne weitere Konfiguration verfügbar. SQL Performance Analyzer kann auf einem Produktionssystem oder einem Testsystem verwendet werden, das der Datenbankumgebung des Produktionssystems so weit wie möglich entsprechen sollte. Wie in unserem Beispiel gezeigt, ist dabei die Verwendung von CONVERT SQLSET für den ersten Testlauf eine einfache Möglichkeit, um die Anzahl der erforderlichen Ausführungen im Source System zu minimieren. Und nicht zu vergessen, was gerade einer meiner Kollegen über seine Erfahrungen mit SQL Performance Analyzer anmerkte: “Was ich toll fand, ist die Möglichkeit, so viele Durchläufe zu machen, wie man möchte, und dabei von einem Durchlauf zum anderen jeweils eine Sache zu ändern. Und man hat die Möglichkeit danach jeden Lauf mit jedem anderen Lauf zu vergleichen.”

Links