X

Technologie - Trends - Tipps&Tricks
in deutscher Sprache

Oracle SQL Tuning Sets - Grundlage zum SQL Tuning

Die wenigstens Kunden kennen Oracle SQL Tuning Sets! Das war die Aussage meines Kollegen Mike Dietrich (Master Product Manager bei Oracle für Migrationen und Upgrade)  in seinem letzten Webcast  zum Thema Upgrade und Performance Stability.

Dabei gibt es einige sehr interessante Anwendungsszenarien - besonders im Tuning Umfeld. Mit SQL Tuning Sets auch kurz STS kann man beispielsweise die relevanten Statements für (wiederkehrende) Tuningaufgaben auflisten. Schnell und einfach lassen sich Aussagen über die Performance von Statements treffen - ohne beispielsweise einen AWR Report zu generieren. Ausserdem liefern sie die Grundlage für den SQL Tuning Advisor und SQL Access Advisor und sind Basis für die Verwendung des SQL Performance Advisors. Möchte man auf einem Remote System testen, ist auch dies mit SQL Tuning Sets möglich, da diese mit Datapump Export bzw. Import auf ein Remote Datenbank System kopiert werden können. 

Die SQL Tuning Set Funktionalität steht übrigens in der Enterprise Edition der Oracle Datenbank - On-premises oder in der Cloud - zur Verfügung - ohne zusätzliche Lizenzierung eines Management Packs oder einer Option. Informationen dazu finden sich im Oracle Licensing Guide

SQL Tuning Sets gibt es schon seit langer Zeit in der Oracle Datenbank. Sie wurden im Zusammenhang mit der Einführung des SQL Tuning Frameworks in der Version 10 der Oracle Datenbank eingeführt. Ein STS ist, wie der Name schon andeutet, eine Sammlung von SQL Statements, die unter einem User definierten Namen in der Datenbank abgespeichert werden. 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 besteht 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

Wie lassen sich SQL Tuning Sets erzeugen? Ein STS kann aus verschiedenen Quellen erzeugt werden wie z.B. Automatic Workload Repository (AWR), dem Cursor Cache oder mithilfe von Real Application Testing Database Replay (mehr dazu auch im Tipp Testen mit Oracle Database Replay ). Um beispielsweise möglichst viele Statements einfach und schnell im STS zu speichern, eignet sich das Capture aus dem  Cursor Cache. Sollen nur die Top Statements berücksichtigt werden, kann man sich auf die AWR Methode beschränken.

Zur Erzeugung eines STS kann entweder die graphische Oberfläche über Enterprise Manager Cloud Control, die eine sehr einfache und gute Oberfläche dazu anbietet oder der Linemode verwendet werden.  Da nicht jeder die Cloud Control Oberfläche zur Verfügung hat, werden die Beispiele in folgenden Abschnitten im Linemode durchgeführt.

Die Infrastruktur für SQL Tuning Sets besteht dabei hauptsächlich aus dem Package DBMS_SQLTUNE sowie den zugehörigen Data Dictionary Views DBA_SQLSET und DBA_SQLSET_STATEMENTS. Die vollständige Funktionalität des Packages und der Data Dictionary Views lässt sich im Handbuch (hier in der Version 19c) in den entsprechenden Kapiteln nachlesen unter:

Folgende Beispiele demonstrieren die Verwendung im Linemode. Skripte zum Löschen, Erzeugen von SQL Tuning Sets und Subsets wie auch zum Monitoren kann man sich hier laden. 

Erzeugen eines STS: Cursor Cache, AWR, inkrementell

Wichtig zu wissen ist, die Statements nach bestimmten Kriterien gefiltert und ausgegeben werden können, bevor sie in der Datenbank unter einem STS Namen gespeichert werden. Dazu steht intern der Objekttyp SQLSET_ROW zur Verfügung, der beispielsweise folgende Basis-Filterkriterien zulässt:

  • SQL_TEXT für den SQL Text
  • PARSING_SCHEMA_NAME für das User Schema
  • MODULE bei Verwendung von DBMS_APPLICATION_INFO
  • ACTION bei Verwendung von DBMS_APPLICATION_INFO
  • ELAPSED_TIME
  • CPU_TIME
  • BUFFER_GETS
  • DISK_READS
  • ROWS_PROCESS
  • EXECUTIONS

Mit der Funktion SELECT_CURSOR_CACHE kann man die Statements aus dem Cursor Cache selektieren, die bestimmte Kriterien erfüllen. Im folgendem Beispiel werden maximal 10 SELECT Statements aus dem Schema DWH_DATA gefiltert und nach Elapsed Time geordnet ausgegeben. Dazu verwendet man im Parameter BASIC_FILTER die Einstellungen PARSING_SCHEMA_NAME für das User Schema, SQL_TEXT für die Verwendung von SELECT Statements, RESULT_LIMIT für die Begrenzung auf 10 und RANKING_MEASURE1 für das Sortieren nach Elapsed Time.

set linesize window

SELECT last_exec_start_time, elapsed_time/1000/1000 elapsed_sec, sql_id, sql_text
FROM dbms_sqltune.select_cursor_cache (
basic_filter =>'parsing_schema_name = ''DWH_DATA'' and sql_text like ''SELECT%''', 
result_limit => 100, 
ranking_measure1 =>'elapsed_time')
/

LAST_EXEC_START_TIM ELAPSED_SEC SQL_ID
------------------- ----------- -------------
SQL_TEXT
--------------------------------------------------------------------------------
2020-05-13/12:15:35   93.161427 917fgnxfc9du7
SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ extractvalue(xmlv

2020-05-13/12:15:35   10.061682 gghk7k6vt9gzq
SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ extract(xmlval, '

2020-05-13/12:15:35    9.018263 f931zc8qhhfaa
SELECT ST.* FROM XMLTABLE('/hint_usage/s/h' PASSING XMLTYPE(:B1 ) COLUMNS O VARC

2020-05-13/12:15:35    8.998409 fg4skgcja2cyj
SELECT EXTRACTVALUE(VALUE(D), '/row/@op'), EXTRACTVALUE(VALUE(D), '/row/@dis'),

2020-05-13/12:12:10    5.365492 2s7u2gqad8vm2
SELECT
    'B' || 1010280 repchannel_1,
    'B' || tt1.domain_productgroup_1 dom

2020-05-13/12:13:42    5.324177 6w3jxyn7373bn
SELECT
    'B' || tt1.PG_FeatureValue_03_1 PG_FeatureValue_03_1,
    'B' || 1010
...

Es lassen sich beliebige Filterkriterien miteinander kombinieren und damit die Ausgabe beeinflussen. So können SQL Tuning Sets erzeugt werden, die bestimmten Performance- oder Umgebungs-Anforderungen wie z.B. Modulenangabe durch DBMS_APPLICATION_INFO genügen. Weitere Möglichkeiten findet man im Handbuch im Bereich DBMS_SQLTUNE.SELECT_CURSOR_CACHE.

Wie kann man diese Statements in die Datenbank in einem bestimmten STS speichern? Um ein STS in der Datenbank zu erzeugen, wird im ersten Schritt ein Objekt mit einem bestimmten Namen festgelegt. Dies geschieht mit der Prozedur CREATE_STS.

-- Loeschen mit DROP_SQLSET
execute dbms_sqltune.drop_sqlset(sqlset_name=>'STS1', sqlset_owner=>'US')
/

-- Erzeugen mit CREATE_SQLSET
execute dbms_sqltune.create_sqlset(sqlset_name=>'STS1', sqlset_owner=>'US')
/

Möchte man Statements inkrementell über eine bestimmte Zeit aus dem Cursor Cache in einem SQL Tuning Set ansammeln, ist dies mit der Prozedur CAPTURE_CURSOR_CACHE_SQLSET möglich. Das folgende Beispiel pollt über einen Zeitraum von 30 Minuten (1800 Sekunden) alle 5 Minuten die Statements aus dem Cursor Cache. Hier wurde als Einschränkung das Userschema SCOTT verwendet und die Elapsed Zeit (mehr als 5 Sekunden) als Untergrenze festgelegt.

execute dbms_sqltune.capture_cursor_cache_sqlset(-
   sqlset_name     => 'STS1',-
   time_limit      => 1800, -
   repeat_interval => 300, -
   basic_filter    => 'parsing_schema_name = ''DWH_DATA'' AND elapsed_time>5000000'); 

Hinweis: Dabei dauert die Ausführung der Prozedur CAPTURE_CURSOR_CACHE_SQLSET so lange wie der gegebene Zeitraum vorgibt - in unserem Fall 30 Minuten. Verwendet man noch zusätzlich den DBMS_SCHEDULER um solche Statements inkrementell zu sammeln, lässt sich der Prozess mit DBMS_SCHEDULER.STOP_JOB auch vorzeitig beenden.

Sollen nicht inkrementell sondern alle Statements auf einmal aus dem aktuellen Cursor Cache geladen werden, kann man folgendes PL/SQL Programm dazu verwenden.

execute dbms_sqltune.drop_sqlset(sqlset_name=>'STS2', sqlset_owner=>'US')
/
execute dbms_sqltune.create_sqlset(sqlset_name=>'STS2', sqlset_owner=>'US')
/

DECLARE
 cur dbms_sqltune.SQLSET_CURSOR;
BEGIN
 OPEN cur FOR
   SELECT VALUE(P)
   FROM dbms_sqltune.select_cursor_cache(basic_filter => 
   'parsing_schema_name = ''DWH_DATA'' and sql_text like ''SELECT%''') P;
   dbms_sqltune.load_sqlset(sqlset_name => 'STS2', populate_cursor => cur);
  CLOSE cur;
END;
/

Hinweis: Die Funktion VALUE liefert den Wert eines Objekts zurück.

Alternativ lassen sich die durch AWR bzw. ASH gesammelten Daten aus dem Workload Repository nutzen. Diese Daten werden ständig gesammelt und per Default 8 Tage (konfigurierbar) in der Datenbank gespeichert. Da dabei auch einmal pro Sekunde die Top SQL-Statements von der Datenbank gesammelt werden, können bei einem Performance-Problem die verursachenden SQL-Statements über die zugehörigen Snapshots gefunden werden. Statt der Funktion SELECT_CURSOR_CACHE wird in diesem Fall die Funktion SELECT_WORKLOAD_REPOSITORY verwendet. Sie beinhaltet die beiden IDs der Snapshots, um den Anfang und das Ende der Periode anzugeben. Zusätzlich ist, wie im vorangegangenen Beispiel schon erklärt, die Angabe von Filterkriterien möglich.

Zuerst suchen wir die entsprechenden Snapshots mithilfe der Data Dictionary View DBA_HIST_SNAPSHOT.

-- im Root Container
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI';

SELECT snap_id, begin_interval_time begin_snap, end_interval_time end_snap
FROM dba_hist_snapshot
ORDER BY snap_id;

   SNAP_ID BEGIN_SNAP                     END_SNAP
---------- ------------------------------ ------------------------------
      5389 13-MAY-20 06.01.00.650 AM      13-MAY-20 07.00.04.058 AM
      5390 13-MAY-20 07.00.04.058 AM      13-MAY-20 08.00.07.496 AM
      5391 13-MAY-20 08.00.07.496 AM      13-MAY-20 09.00.10.896 AM
      5392 13-MAY-20 09.00.10.896 AM      13-MAY-20 10.00.14.188 AM
      5393 13-MAY-20 10.00.14.188 AM      13-MAY-20 11.00.17.593 AM
      5394 13-MAY-20 11.00.17.593 AM      13-MAY-20 11.13.08.982 AM
      5395 13-MAY-20 11.13.08.982 AM      13-MAY-20 12.00.21.159 PM
      5396 13-MAY-20 12.00.21.159 PM      13-MAY-20 01.00.24.978 PM
      5397 13-MAY-20 01.00.24.978 PM      13-MAY-20 01.13.03.918 PM

Im Beispiel werden die beiden Snapshots 5396 und 5397 verwendet. Zusätzlich können noch weitere Filterkriterien wie in den anderen Beispielen mitangegeben werden.

execute dbms_sqltune.drop_sqlset(sqlset_name=>'STS3', sqlset_owner=>'US')
/
execute dbms_sqltune.create_sqlset(sqlset_name=>'STS3', sqlset_owner=>'US')
/

DECLARE
 cur dbms_sqltune.sqlset_cursor;
BEGIN
 OPEN cur FOR
  SELECT VALUE(P) 
  FROM dbms_sqltune.select_workload_repository
   (begin_snap=>5396, end_snap=> 5397, basic_filter => 'sql_text like ''SELECT%''   
   and parsing_schema_name = ''DWH_DATA''' ) P; 
  dbms_sqltune.load_sqlset (sqlset_name => 'STS3', populate_cursor => cur); 
END;
/

Hinweis: Die im AWR gelisteten Statements sind auf eien eine Zahl begrenzt, da man nur die Top Statements listen möchte. Möchte man die Zahl verändern, kann dies mit der Funktion DBMS_WORKLOAD_REPOSITORY. MODIFY_SNAPSHOT_SETTINGS tun.

Ist das STS zu groß bzw. möchte man das STS in mehrere Sets unterteilen, lässt sich dazu die Funktion SELECT_SQLSET in Verbindung mit verschiedenen Filterkriterien verwenden. Folgendes Beispiel erzeugt das Subset STS1A. Es enthält 10 Statements aus dem SQL Tuning Set STS1 geordnet nach der verbrauchten Elapsed Time.

execute dbms_sqltune.drop_sqlset(sqlset_name=>'STS1A', sqlset_owner=>'US') 
/ 
execute dbms_sqltune.create_sqlset(sqlset_name=>'STS1A', sqlset_owner=>'US') 
/

DECLARE cur dbms_sqltune.sqlset_cursor; 
BEGIN OPEN cur FOR
     SELECT VALUE (P)
     FROM table(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name =>'STS1',    
            ranking_measure1 =>'ELAPSED_TIME', result_limit=>&limit)) P;
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS1A', populate_cursor => cur);
CLOSE cur;
END;
/
Enter value for limit: 10
old   7:          sqlset_name =>'STS1', ranking_measure1 =>'ELAPSED_TIME', result_limit=>&limit)) P;
new   7:          sqlset_name =>'STS1', ranking_measure1 =>'ELAPSED_TIME', result_limit=>10)) P;

PL/SQL procedure successfully completed.

Monitoren von STS

Möchte man einen Überblick über alle STS des Owners US bekommen, kann man folgende Abfrage verwenden.

SELECT name, created, statement_count
FROM dba_sqlset 
WHERE owner='US'; 

NAME                 CREATED          STATEMENT_COUNT
-------------------- ---------------- ---------------
STS1                 13.05.2020 12:42              15
STS2                 13.05.2020 12:44             283
STS3                 13.05.2020 13:36              44
STS1A                15.05.2020 15:45              10

Auskunft über den genauen Inhalt der SQL Tuning Sets erhält man dann über die Data Dictionary View DBA_SQLSET_STATEMENTS. Hier hat man nun die Möglichkeit nach unterschiedlichen Kriterien - vergleichbar auch mit V$SQL - zu sortieren und zu filtern. Auf diese Art und Weise bekommt man dann schnell einen Überblick über Statements, die den Anforderungen nicht genügen und muss dazu nicht unbedingt einen AWR Report generieren.

SELECT elapsed_time, sql_text 
FROM DBA_SQLSET_STATEMENTS where sqlset_name='STS1' 
ORDER BY 1;

ELAPSED_TIME
------------
SQL_TEXT
--------------------------------------------------------------------------
        4002
SELECT  /*# 49435 #*/ DISTINCT
    t1.domain_productgroup_id domain_productgroup

       25233
select /*+ opt_param('parallel_execution_enabled',


       67140
SELECT /*+ USE_HASH(t1) */ /*# 49435 #*/ DISTINCT
    'B' || t1.period_id period

      344994
...


Transfer von SQL Tuning Sets

Abgesehen von Standardeigenschaften wie das Löschen oder nachträgliche Ändern, gibt es auch die Möglichkeit, SQL Tuning Sets in andere Datenbanken zu transportieren. Dies ist besonders dann sinnvoll, wenn z.B. der Tuning Prozess ausgelagert werden soll. Folgender Ablauf beschreibt das Vorgehen.

1) Anlegen einer Staging Tabelle

execute dbms_sqltune.create_stgtab_sqlset(table_name => 'STAGING_TABLE'); 

Hinweis: Die Nutzung von Datapump darf dabei NICHT als SYSDBA erfolgen.

2) Laden der Staging Tabelle mit Daten aus einem oder mehreren SQL Tuning Sets

execute dbms_sqltune.pack_stgtab_sqlset(-
                           sqlset_name        => 'STS2', -
                           sqlset_owner       => 'US', -
                           staging_table_name => 'STAGING_TABLE');

3) Datapump Export und Import der Tabelle STAGING_TABLE in die Test Umgebung

4) Entpacken der SQL Tuning Sets aus der Staging Tabelle

execute dbms_sqltune.unpack_stgtab_sqlset(-
                                    sqlset_name        => '%', -
                                    sqlset_owner       => 'US',-
                                    replace            => TRUE,
                                    staging_table_name => 'STAGING_TABLE'); 

Nun kann das SQL Tuning Set bzw. die SQL Tunings Sets in der neuen Umgebung, die z.B. als Testumgebung dient, zum Einsatz kommen. Sogar die Releasestände der Umgebungen können dabei unterschiedlich sein. So kann ein SQL Tuning Set aus einer Datenbank Umgebung mit einem weniger aktuellen Releasestand in eine Testumgebung exportiert bzw. importiert werden, um das Verhalten in der neuen Umgebung zu testen.
 

Weitere Informationen

 

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.