Seit Oracle Database 10g mit der Einführung des Tuning Advisory Frameworks spielen die sogenannten SQL Tuning Sets (auch kurz STS) eine wichtige Rolle. Ein STS besteht dabei nicht alleine aus einer Ansammlung von gespeicherten SQL Texten. Zusätzlich werden z.B. Execution Kontext und Execution Statistiken im Data Dictionary mitabgespeichert.
Ein SQL Tuning Set besteht somit aus folgenden Komponenten:

Erzeugen eines SQL Tuning Sets

Wie eingangs schon erwähnt wurde, können SQL Tuning Sets aus unterschiedlichsten Quellen wie z.B. dem Cursor Cache erzeugt werden. Da die STS zusätzlich Basisstatistiken enthalten, sind diese als mögliche einschränkende Kriterien zugelassen. Die Basisstatistiken, die verwendet werden können sind dabei durch den Objekttyp SQLSET_ROW definiert. Informationen dazu findet man im PL/SQL Packages und Types References Handbuch.
Beispiele für mögliche Statistiken sind:

EXECUTE DBMS_SQLTUNE.CREATE_SQLSET('SQLSET_1');
DECLARE
 cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN cur FOR
   SELECT VALUE(P)
   FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
   'parsing_schema_name = ''SCOTT'' AND elapsed_time >12000000', null, null, 
   null, null, 1, 10, null)) P;
   DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'SQLSET_1', populate_cursor => cur);
  CLOSE cur;
END;
/

EXECUTE DBMS_SQLTUNE.CREATE_SQLSET('SQLSET_2');
DECLARE
  cur sys_refcursor;
BEGIN OPEN cur FOR
SELECT VALUE(P) 
    FROM table(
      DBMS_SQLTUNE.SELECT_CURSOR_CACHE('action = ''TEST_BEREICH''')) P;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'SQLSET_2', populate_cursor => cur);
END;
/

EXECUTE DBMS_SQLTUNE.CREATE_SQLSET('SQL_WORKLOAD1');
EXECUTE DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
                sqlset_name     => 'SQL_WORKLOAD,
                time_limit      => 1800, 
                repeat_interval => 300, 
                basic_filter    => 'parsing_schema_name = ''SCOTT'' AND elapsed_time>12000000'); 

BEGIN 
 DBMS_SQLTUNE.CREATE_SQLSET(
            sqlset_name  =>'&sts_name',
            sqlset_owner =>'&sts_owner');
END;                                                      
/  
BEGIN 
 DBMS_SCHEDULER.CREATE_JOB(
    job_name   => 'LOAD_STS',
    job_type   => 'PLSQL_BLOCK',
    job_action =>
      'BEGIN dbms_sqltune.capture_cursor_cache_sqlset(
             sqlset_name     => ''&sts_name'',
             time_limit      => 25200,
             repeat_interval => 900,
             sqlset_owner    => ''&sts_owner'',
             basic_filter => ''parsing_schema_name NOT IN ''''DBSNMP'''',''''SYS'''')'');
        END;',
    enabled  => TRUE);
END;
/ 
-- Job Abbruch mit 
BEGIN
  DBMS_SCHEDULER.STOP_JOB(job_name => 'LOAD_STS');
END;
/

SQL> SELECT name, statement_count FROM dba_sqlset ORDER BY created;

NAME                           STATEMENT_COUNT
------------------------------ ---------------
rat1_c_245324                              586
rat1_c_2453241375555946                    586
REPLAY_orcl_20101011_r_2236708             457
REPLAY_test_r_4692651                      355
REPLAY_orcl_20101011_r_6480785             676
...

SQL> SELECT sql_text,cpu_time,elapsed_time, executions, buffer_gets 
     FROM dba_sqlset_statements 
     WHERE sqlset_name='SQL_WORKLOAD3';

SQL_TEXT
--------------------------------------------------------------------------------
  CPU_TIME ELAPSED_TIME EXECUTIONS BUFFER_GETS
---------- ------------ ---------- -----------
BEGIN DBMS_OUTPUT.DISABLE; END;
      8218       144234          1          75

select count(*) from zn.zndb_10p
   1112864     34363168          1       65260

select count(*), last_name from hr.bigemp where last_name like 'M%' group by las
     57724        57724          1         217

select count(*) from hr.bigemp where last_name='King'
      7091        69174          2          99

BEGIN :last :='King'; END;

      1567         1567          1           0

select count(*) from hr.bigemp where last_name like 'M%'
    183577       210873          6        1296
 

Transfer von SQL Tuning Sets

Abgesehen von Standardeigenschaften wie das Löschen oder das 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:

EXECUTE DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name => 'STAGING_TABLE');   

EXECUTE DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name        => 'SQL_WORKLOAD3',
			                staging_table_name => 'STAGING_TABLE');

EXECUTE DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(sqlset_name        => '%',
      					  replace            => TRUE,
      			                  staging_table_name => 'STAGING_TABLE'); 

Weitere Anwendungen und Tipps

Mit dem in Oracle Database 11g eingeführten SQL Performance Analyzer können auf diese Art und Weise, Performancevergleiche zwischen unterschiedlichen Releaseständen durchgeführt werden. Die 11g Datenbank kann dabei als Zielrelease genutzt werden oder zu Repository-Zwecke, um z.B. Vergleiche zwischen älteren Releaseständen z.B. 9i und 10g durchzuführen. Da in 9i noch keine SQL Tuning Sets zur Verfügung stehen, ist das SQL Performance Analyzer Interface so erweitert worden, dass Trace Files aus der Oracle 9i Datenbank, zu SQL Tuning Sets konvertiert werden können.

STS können auch parallel während eines DB Replay Captures generiert werden (siehe auch DBMS_WORKLOAD_CAPTURE Package). Vorzuziehen ist allerdings die manelle Methode, da man hier nach Belieben Filter setzen kann.

Ist das STS zu umfangreich, kann man man im Nachhinein das STS verkleinern - entweder über die graphische Oberfläche oder mithilfe des Package DBMS_SQLTUNE.

EXECUTE DBMS_SQLTUNE.CREATE_SQLSET('SUBSET1');

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE (P) 
    FROM table(DBMS_SQLTUNE.SELECT_SQLSET(
         sqlset_name =>'REPLAY_us_r_44488776', 
         basic_filter=>'elapsed_time > 5000000')) P;
 
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'SUBSET1',
                        populate_cursor => cur); 
  CLOSE cur;
END;
/

• SQL Tuning Sets im Performance Handbuch
• DBMS_SQLTUNE

Zurück zur Community-Seite