SQL Tuning Sets im Einsatz - Teil2
von Ralf Durben, ORACLE Deutschland GmbH

In einem frühen Tipp zu SQL Tuning Sets im Einsatz wurde gezeigt, wie Sie SQL Tuning Sets verwenden können. Es wurde dabei beschrieben, wie SQL Tuning Sets von einer Datenbank zu einer anderen Datenbank transferiert werden können. Auch wird dort gezeigt, wie einfach man die laufende Last (SQL-Statements) in inkrementellen Schritten oder die SQL-Statements, die noch im Cursor-Cache gespeichert sind, in ein SQL Tuning Set übernehmen kann. Leider ist dieses in der Praxis oft nicht verwendbar. In diesem Update zeige ich weitere Möglichkeiten zum Laden von SQL-Statements und des Transfers.

Jeder DBA kennt die Situation, dass die Performance in der jüngeren Vergangenheit beklagt wird, nach dem Motto "Vor 30 Minuten gab es ein Performance-Problem". Nun wird der DBA sicherlich nicht auf Verdacht ständig die komplette Last in einem SQL Tuning Set erfassen und auch der Cursor-Cache beinhaltet oft die benötigten Informationen nicht mehr. 


Füllen eines SQL Tuning Sets mit Daten aus AWR Snapshots
Alternativ lassen sich aber auch die durch AWR bzw. ASH gesammelten Daten im Workload Repository nutzen. Diese Daten werden ja ständig gesammelt und per Default 8 Tage (konfigurierbar) in der Datenbank gespeichert. Da Oracle sekündlich die Top25 SQL-Statements sammelt, werden Sie bei einem realen Performance-Problem das verursachende SQL-Statement auch hier finden. In der oben beschriebenen Situation kann ein DBA also die Snapshots identifizieren, zwischen denen das "Problem" bestanden haben soll: 

SELECT snap_id , begin_interval_time , end_interval_time 

 FROM  dba_hist_snapshot

 ORDER BY snap_id;
Sie brauchen nur zwei verschiedene Snapshot-IDs um alle aufgezeichneten SQL-Statements zu diesem Zeitraum in ein SQL Tuning Set übernehmen zu können: 

DECLARE 

 sqlset_cur dbms_sqltune.sqlset_cursor; 

BEGIN 

 dbms_sqltune.create_sqlset('testset'); 

 OPEN sqlset_cur FOR 

  SELECT VALUE(P) FROM TABLE( 

    dbms_sqltune.select_workload_repository

     (2031,2033, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL) 

   ) P; 

  dbms_sqltune.load_sqlset

   (sqlset_name => 'testset', populate_cursor => sqlset_cur); 

END; 

/
In diesem Beispiel wurden die beiden Snapshots 2031 und 2033 verwendet. Sie können auch noch Filter einsetzen, um die aufzunehmenden SQL-Statements einzugrenzen. Dazu werfen wir einen Blick in die Definition der Funktion SELECT_WORKLOAD_REPOSITORY: 

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (

  begin_snap        IN NUMBER,

  end_snap          IN NUMBER,

  basic_filter      IN VARCHAR2 := NULL,

  object_filter     IN VARCHAR2 := NULL,

  ranking_measure1  IN VARCHAR2 := NULL,

  ranking_measure2  IN VARCHAR2 := NULL,

  ranking_measure3  IN VARCHAR2 := NULL,

  result_percentage IN NUMBER   := 1,

  result_limit      IN NUMBER   := NULL

  attribute_list    IN VARCHAR2 := NULL)

 RETURN sys.sqlset PIPELINED;
Mit dem Parameter object_filter können Sie festlegen, auf welche Datenbankobjekte die aufzunehmenden SQL-Statements zugegriffen haben sollen: 

DECLARE 

 sqlset_cur dbms_sqltune.sqlset_cursor; 

BEGIN 

 dbms_sqltune.create_sqlset('testset'); 

 OPEN sqlset_cur FOR 

  SELECT VALUE(P) FROM TABLE( 

    dbms_sqltune.select_workload_repository

     (2031,2033, NULL, 'SCOTT.EMP', NULL, NULL, NULL, 1, NULL, NULL) 

   ) P; 

  dbms_sqltune.load_sqlset

   (sqlset_name => 'testset', populate_cursor => sqlset_cur); 

END; 

/
Mit dem Parameter basic_filter können Sie eine WHERE-Klausel festlegen und dabei auf Eigenschaften zugreifen, die in dem Datentyp DBMS_SQLTUNE.SQLSET_ROW zur Verfügung stehen: 

sqlset_row AS object (

  sql_id                   VARCHAR(13),

  force_matching_signature NUMBER,

  sql_text                 CLOB,

  object_list              sql_objects,

  bind_data                RAW(2000),

  parsing_schema_name      VARCHAR2(30),

  module                   VARCHAR2(48),

  action                   VARCHAR2(32),

  elapsed_time             NUMBER,

  cpu_time                 NUMBER,

  buffer_gets              NUMBER,

  disk_reads               NUMBER,

  direct_writes            NUMBER,

  rows_processed           NUMBER,

  fetches                  NUMBER,

  executions               NUMBER,

  end_of_fetch_count       NUMBER,

  optimizer_cost           NUMBER,

  optimizer_env            RAW(2000),

  priority                 NUMBER,

  command_type             NUMBER,

  first_load_time          VARCHAR2(19),

  stat_period              NUMBER,

  active_stat_period       NUMBER,

  other                    CLOB,

  plan_hash_value          NUMBER,

  sql_plan                 sql_plan_table_type,

  bind_list                sql_binds)
Zum Beispiel können alle SQL-Statements erfasst werden, die im SQL-Text den Hint "use_nl" enthalten: 

DECLARE 

 sqlset_cur dbms_sqltune.sqlset_cursor; 

BEGIN 

 dbms_sqltune.create_sqlset('testset'); 

 OPEN sqlset_cur FOR 

  SELECT VALUE(P) FROM TABLE( 

    dbms_sqltune.select_workload_repository

     (2031,2033, 'sql_text like ''*+ use_nl'' ', NULL, NULL, NULL, NULL, 1, NULL, NULL) 

   ) P; 

  dbms_sqltune.load_sqlset

   (sqlset_name => 'testset', populate_cursor => sqlset_cur); 

END;
Auch in Oracle Enterprise Manager Grid Control stehen Ihnen diese Möglichkeiten zur Verfügung und die Handhabung ist denkbar einfach: Navigieren Sie von der Datenbank-Homepage auf den Reiter "Server" und klicken Sie dann auf "Automatic Workload Repository". Hier klicken Sie auf die Anzahl vorhandener Snapshots und bekommen eine Auflistung aller vorhandenen Snapshots.

Bild in voller Größe

Hier wählen Sie den ersten (also den älteren) Snapshot aus und wählen als Aktion "Create SQL Tuning Set" aus. Klicken Sie dann auf "Go".

Bild in voller Größe

Sie wählen den zweiten Snapshot aus und geben dem neuen SQL Tuning Set einen Namen. Anschließend klicken Sie auf "OK". Über diese Methode können Sie auch ganz einfach mal alle SQL-Statements eines Datenbank-Benutzers über einen definierten Zeitraum auflisten: Erstellen Sie das SQL Tuning Set wie oben beschrieben und klicken dann auf das SQL Tuning Set in der erscheinenden Seite. Sie bekommen alle aufgenommenen SQL-Statements angezeigt. Jetzt können Sie einfach nach der Spalte "Parsing Schema" sortieren:

Bild in voller Größe

In diesem Beispiel hat der Datenbank-Benutzer LBACSYS ein Kommando verwendet. 

Füllen eines SQL Tuning Sets mit Daten aus AWR Baselines
AWR Baselines sind vorgegebene Zeiträume, für die dann die AWR Daten vorliegen. Per Default gibt es die Baseline "SYSTEM_MOVING_WINDOW", in der die letzten 8 Tage abgebildet sind. Auch auf Basis einer Baseline können Sie ein SQL Tuning Set erstellen: 

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (

  baseline_name     IN VARCHAR2,

  basic_filter      IN VARCHAR2 := NULL,

  object_filter     IN VARCHAR2 := NULL,

  ranking_measure1  IN VARCHAR2 := NULL,

  ranking_measure2  IN VARCHAR2 := NULL,

  ranking_measure3  IN VARCHAR2 := NULL,

  result_percentage IN NUMBER   := 1,

  result_limit      IN NUMBER   := NULL)

  attribute_list    IN VARCHAR2 := NULL)

 RETURN sys.sqlset PIPELINED;
Statt der beiden Snapshots-IDs geben Sie also einfach nur den Namen der Baseline an. In Grid Control navigieren Sie zu den Baselines (Datenbank-Homepage->Server->AWR Baselines) und verfahren wie bei den AWR Snapshots. 

Füllen eines SQL Tuning Sets mit Daten aus Top Activity
Auch aus dem Anzeige-Bereich des Online Performance Monitorings in Grid Control können Sie sehr leicht ein SQL Tuning Set erstellen. Der Bereich "Top Activity" (Navigation über Datenbank-Homepage->Performance->Top Activity) zeigt ja die CPU-Nutzung der jeweiligen Datenbanksitzungen in einem ausgewählten Zeitraum (schraffierter Bereich). Desweiteren sehen Sie dort die SQL-Statements mit der höchsten Aktivität.

Bild in voller Größe

Sie können nun mehrere SQL-Statements auswählen und mit der Aktion "Create SQL Tuning Set" das SQL Tuning Set erstellen. 

Transfer von SQL Tuning Sets
Der erste Tipp zu SQL Tuning Sets zeigt, dass diese mit PL/SQL-Kommandos von Datenbank zu Datenbank transportiert werden können. Damit können zum Beispiel in einer Test-Datenbank identifizierte Tuning-Kandidaten auch als solche in einer Produktions-Datenbank optimiert werden.

Dieser Transfer funktioniert aber auch sehr einfach in Grid Control. Navigieren Sie dazu zu den SQL Tuning Sets einer Datenbank (z.B. Datenbank-Homepage->Server->SQL Tuning Sets). Wählen Sie ein Tuning Set aus und nutzen Sie den Button "Copy To A Database". Achtung: Dieses funktioniert nicht, wenn Sie aus Grid Control mit dem Benutzer SYS an die Datenbank angemeldet sind, da der Vorgang Objekte anlegt, die nicht unter SYS angelegt werden können!

Bild in voller Größe

Wählen Sie eine Zieldatenbank aus und achten auf korrekte Credentials. Auch hier darf für die Zieldatenbank als Datenbank-Credential nicht der Benutzer SYS verwendet werden! Klicken Sie auf "OK" und der entsprechende Job wird gestartet. 

Zusammenfassung
SQL Tuning Sets können auch sehr leicht mit SQL-Statements aus der Vergangenheit gefüllt werden. Auch der Transfer ist mit Grid Control sehr leicht durchzuführen. 

Lizenzhinweis:
Lizenzhinweis: SQL Tuning Sets sind Bestandteil des Tuning Packs, welches wiederum das Diagnostics Pack verwendet und voraussetzt. AWR und ASH wiederum sind Bestandteil dieses Diagnostics Packs 

Versionshinweis:
Die Navigationsangaben beziehen sich auf Grid Control 10.2.0.5 und kann bei früheren oder späteren Versionen variieren.

Zurück zur Community-Seite