Rund um SQL Tuning Sets

Ulrike Schwinn (email: ulrike.schwinn@oracle.com)

Oracle Business Unit Database ORACLE Deutschland B.V. & Co. KG

Motivation

• Sie wollen ... • Mehr über einen Workload erfahren ...

• Ausführungspläne aus einem Workload erhalten ...

• SQL Plan Baselines nutzen bzw. erzeugen ...

• Advisors wie SQL Tuning oder SQL Access verwenden ...

• SQL Performance Analyzer verwenden ...

• Datenbankobjekte, die • SQL Text

• Execution Kontext (Parsing User, Bind Werte, …)

• Execution Statistiken (Buffer Gets, CPU, #Executions, …)

• Plan

• Plan Statistiken usw.

Was sind SQL Tuning Sets?

• Plan Statistiken usw.

beinhalten.

• Persistente Speicherung im SYSAUX Tablespace• Größe abhängig von SQL Text, Unterschiede, Binds etc...

• Geringer Performance Overhead

Hintergrundinfo ... Objekttyp NutzungCREATE TYPE 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, cpu_time NUMBER,

buffer_gets NUMBER,

disk_reads NUMBER,

direct_writes NUMBER,

rows_processed NUMBER, …

optimizer_cost NUMBER, optimizer_env RAW(2000),

first_load_time VARCHAR2(19),

…

plan_hash_value NUMBER,

sql_plan sql_plan_table_type,

bind_list sql_binds)

SQL Tuning Sets einfach selektieren

• Überblick über DBA_SQLSET

NAME CREATED STATEMENT_COUNT

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

REPLAY_us_r_44488776 20-MAR-12 1487

SCOTT_STS 21-MAR-12 19

SH_STS 20-JAN-11 12

SPA_DML 28-MAR-12 12

• Details in DBA_SQLSET_STATEMENTSSQL_TEXT EXECU BUFFER_GETS

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

update emp set empno=1 1 97

insert into test_emp select * from emp 1 55

update test_emp set sal=1 1 37

insert into test_emp values (1,'t', 2 4

't',1,sysdate,1,0,10)

SQL Tuning Sets selektieren im EM

SQL Tuning Sets selektieren im EM

Grundlegendes zu STS

• Seit Oracle 10g automatisch ohne Installation verfügbar

• Integriert in Enterprise Manager und im Package DBMS_SQLTUNE

• Vor. : Privileg ADMINISTER SQL TUNING SET

• Lizenzierung über Tuning Pack• Lizenzierung über Tuning Pack• Ausnahme: enthalten in Lizenz von Real Application Testing

• Parameter: control_management_pack_access

Administration mit DBMS_SQLTUNE

Load

Create• Erzeugen eines neuen leeren STS

• Voraussetzung vor dem Laden

• create_sqlset(sqlset_name, desc,…

• Ändern innerhalb eines STS

execute dbms_sqltune.create_sqlset ('STS1')

Select

Update/Delete

Drop

• Ändern innerhalb eines STS

• update_sqlset(sqlset_name, sql_id,…

• Löschen von Statements eines STS

• delete_sqlset(sqlset_name, filter …

• Löschen des gesamten Sets

• drop_sqlset(sqlset_name)

� STS können …� aus dem Cursor Cache (auch über bestimmten Zeitraum)

� aus AWR Snapshots

� aus SQL

� als Untermenge eines existierenden STS

� während des Captures bzw. während des Replays

� aus 10046 Trace Dateien

Wie werden SQL Tuning Sets befüllt?

� aus 10046 Trace Dateien

erzeugt werden.

� Schnittstellen� EM

� Package DBMS_SQLTUNE

Erste Beispiele

• Aus dem Cursor Cache mit mehr als 500 Buffer Gets

• Aus dem Cursor Cache über eindeutige SQL_ID

SELECT sql_id, sql_text FROM

table(dbms_sqltune.select_cursor_cache('buffer_gets >

500')) ORDER BY sql_id;

• Aus dem Cursor Cache über eindeutige SQL_ID

SELECT sql_id, plan_hash_value FROM

table(dbms_sqltune.select_cursor_cache('sql_id =

''gmnd4yt3c4zyw'''))

ORDER BY sql_id, plan_hash_value;

Mit PL/SQL laden ...

• Aus dem Cursor Cache mit SQL_ID ladenDECLARE

cur sys_refcursor;

BEGIN

open cur for

select value(p) from table(dbms_sqltune.select_cursor_cache

('sql_id = ''gwj1f651t001a''')) p;

dbms_sqltune.load_sqlset(sqlset_name => 'STS_ID',

populate_cursor => cur);

• Aus dem Cursor Cache über eine bestimmte Zeit

populate_cursor => cur);

close cur;

END;

execute dbms_sqltune.capture_cursor_cache_sqlset

(sqlset_name => 'STS_CAPTURE',

time_limit => 3600,

repeat_interval => 600,

basic_filter => 'sql_text like ''%emp%'' and buffer_gets> 1000')

Im Enterprise Manager

• Filter setzen

Im Enterprise Manager

• Über einen bestimmten Zeitraum (auch inkrementell)

... aus Snapshots

• Aus Snapshots mit Filter auf SYSDECLARE

cur sys_refcursor;

BEGIN

open cur for

select value(p) from table(

dbms_sqltune.select_workload_repository

(begin_snap => 20184, (begin_snap => 20184,

end_snap => 20185,

basic_filter => 'parsing_schema_name <> ''SYS''')) p;

dbms_sqltune.load_sqlset('STS_SNAP', cur);

close cur;

END;

/

... aus SQL Traces

• Erzeuge 10046 Trace

• Erzeuge logisches Directory TRACE_DIR

• Lade SQL in STSDECLARE

cur sys_refcursor;

BEGIN BEGIN

open cur for

select value(p) from table(dbms_sqltune.select_sql_trace

(directory => 'TRACE_DIR',

file_name => '%.trc')) p;

dbms_sqltune.load_sqlset('STS_TRACE', cur);

close cur;

END;

/

... aus SQL Traces in andere DB

• Zusätzliche Mapping Tabelle erzeugenCREATE TABLE mapping AS

SELECT object_id id, owner, substr(object_name, 1, 30) name

FROM dba_objects

UNION ALL

SELECT user_id id, username owner, null name

FROM dba_users;

• Mapping Tabelle und Trace Datei wird auf Ziel DB kopieren

• Laden mit

FROM dba_users;

… dbms_sqltune.select_sql_trace(directory=>'TRACE_DIR',

file_name => '%.trc',

mapping_table_name => 'MAPPING',

mapping_table_owner => 'HR'))…

Wenn STS zu groß sind ...

• oder dbms_sqltune.select_sqlset

Welche Filterkriterien gibt es?

•sql_id

•sql_text

•parsing_schema_name

•module

•action

•elapsed_time•elapsed_time

•cpu_time

•buffer_gets

•disk_reads

•direct_writes

•rows_processed

•...

Transportieren mit Export/Import

“Pack”

SYSAUX

ADV

AWR

STS

STGTAB STGTAB SYSAUX

STS

AWR

ADV

“Unpack”Transport

1. Kopiere STS vom SYS Schema in Staging Tabelle (“pack”)

2. Transportiere Staging Tabelle zum Testsystem (Data Pump, db link, etc.)

3. Kopiere STS aus der Staging Tabelle ins SYS Schema (“unpack”)

“Pack” “Unpack”Transport

Im Enterprise Manager

� Über DBA_SQLSET_PLANS

• Über DBMS_XPLAN

Szenario 1: Ausführungspläne aus STS

SELECT operation, options, object_name, object_type, cost

FROM dba_sqlset_plans

WHERE sql_id = '7kg0av34u0r1k';

SELECT * FROM table

(dbms_xplan.display_sqlset(

sqlset_name => 'SQL_WORKLOAD1',

sql_id => '7kg0av34u0r1k',

format => 'ADVANCED',

sqlset_owner => 'US'))

Szenario 1 ... das Ergebnis ...

...

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

ALL_ROWSALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX_RS_ASC(@"SEL$1" "COMP"@"SEL$1" ("COMP"."ORT"))

END_OUTLINE_DATA

*/

Peeked Binds (identified by position):

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

1 - :ALLOW_BINDS (VARCHAR2(30), CSID=873): 'Munich'

• Vorhersehbare und garantierte Planentwicklung

• Identifizierung sich wiederholender Statements => Statement Log

• Speicherung der SQL Pläne => SQL Plan History

• Speicherung der verifizierten/akzeptierten SQL Pläne =>

Szenario 2: SQL Plan Management

Plan history

HJ

HJ

GB

Plan baseline

Statement log

• Speicherung der verifizierten/akzeptierten SQL Pläne => SQL Plan Baseline

• Falls Veränderung und somit neuer Plan eintritt

=> Nutzung der SQL Plan Baseline und Speicherung des neuen Ausführungsplans in SQL Plan History (zur Verifikation)

• Setup über• OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE/FALSE

• OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE/FALSE

� Upgrade von 10gR2 oder 11g

Test Umgebung mit STS

� STS sind getuned

� Transport des STS auf Produktion mit Export/Import

� Generierung von

Szenario 2: SQL Plan Baselines

Plan History

HJ

GB

HJ

Upgrade bzw. ge-tunte Umgebung

Production Database

keine Plan

Regression

HJ

GB

HJ

DBA� Generierung von

SQL Plan Baselines aus STS mit dbms_spm.load_plans_from_sqlset

� Auch möglich: direkter Export/Import von SQL Plan Baselines

HJ

GB

HJ

Development/Test Database

Ge-tuned

HJ

GB

HJ

Staging Tabelle

Plan History

� Generierung von SQL Plan Baselines mit Filterkriterien

• Überprüfung mit DBA_SQL_PLAN_BASELINES

Szenario 2: SQL Plan Baselines

variable pls number;

execute pls := dbms_spm.load_plans_from_sqlset(

sqlset_name => 'STS_WORKLOAD1',

basic_filter => 'sql_text like ''%select%''');

PLAN_NAME ACC ENA FIX

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

SQL_TEXT

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

SQL_PLAN_axcu6w1bk1nyue451c073 YES YES NO

select /*+ use_nl(c) ordered parallel*/ count(*)

from sh.sales s,sh.customers c

• SQL Advisor sind Advisor die bei Tuning von SQL Statements helfen

• 2 Arten: SQL Tuning und SQL Access Advisor

• Nutzbar über Enterprise Manager, teilweise SQL*Developer und Packages DBMS_SQLTUNE/ DBMS_ADVISOR

Szenario 3: SQL Advisor

DBMS_ADVISOR

• SQL Tuning Sets sind Voraussetzung bei der manuellen Nutzung der beiden Advisors

• Voraussetzung: Tuning Pack

• SQL Tuning Advisor

• Manuell bzw. auch automatisch in Automated MaintenanceTasks

• Aufgaben: Überprüfung von Statistiken, Zugriffsstrukturen, SQL und Profiles

• Ergebnis/Findings:

• Erneuerung von Statistiken

• Restrukturierung von SQL

Szenario 3: SQL Advisor Funktionsweise

• Restrukturierung von SQL

• SQL Profiles

• Alternative Pläne

• SQL Access Advisor

• Ermittelt geeignete Zugriffs (Access)- Strukturen wie B*/Bitmap, Function Based Index, Materialized Views, Logs und Partitionen

• Resultat/Empfehlung: DDL-Skript zum Ausführen (CREATE, DROP, DBMS_STATS

Szenario 3: Ergebnis SQL Tuning

Szenario 3: SQL Access Advisor

Bericht anzeigenu.

Implementierung

Tuning Task erzeugen

SQL Tuning Task ausführen

Advisor Framework(dba/user views)

Parametrisierung Bericht erzeugen

Eingabe lesen

Resultat schreiben

DECLARE

t_id number;

t_name varchar2(255);

BEGIN

dbms_advisor.create_task(dba_advisor.sqlaccess_advisor, t_id, t_name);

dbms_advisor.add_sts_ref(t_name,'US','SQL_WORKLOAD1');

dbms_advisor.set_task_parameter(t_name,'ANALYSIS_SCOPE','ALL');

dbms_advisor.execute_task(t_name);

dbms_advisor.create_file(dbms_advisor.get_task_script(t_name),'HOME','s.sql');

END;

/

Szenario 3: Ergebnis SQL AccessRem SQL Access Advisor: Version 11.2.0.3.0 - Production

Rem

Rem Username: SYS

Rem Task: TASK_34528

Rem Execution date:

Rem

CREATE MATERIALIZED VIEW LOG ON

"US"."COMP"

WITH ROWID, SEQUENCE("ORT","JAHR")

INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW "SYS"."MV$$_86E00000"

REFRESH FAST WITH ROWID

ENABLE QUERY REWRITE

AS SELECT US.COMP.ORT C1, MAX("US"."COMP"."JAHR") M1, COUNT(*) M2 FROM US.CO

MP GROUP

BY US.COMP.ORT;

begin

dbms_stats.gather_table_stats('"SYS"','"MV$$_86E00000"',NULL,dbms_stats.auto_s

ample_size);

end;

Szenario 3: Resultat im EM

Szenario 4: SQL Performance Analyzer

• Vor: Erfasse SQL als STS und transportiere STS

SQLTuning

SQL Plan + StatsPre-Change Test

SQL Plans + StatsVergleich der SQL Performance

Ergebnis Report

• Ausführung VORHER mit SPA

• Veränderungen durchführen

• Ausführung NACHHER mit SPA

• Performance Vergleich mit SPA und Tuning

TuningSet

SQL Plans + StatsPost-Change Test

Performance

SPA Report: Ein Beispiel

Report im Enterprise Manager

• optional

Im Detail

Execution Plan 1

Execution Plan 2

� STS Teilmenge bestehend aus „regressed“ Statements

Szenario 5: SQL Plan Baselines und SPA

execute dbms_sqltune.create_sqlset (sqlset_name => 'SUB_STS1',

description => '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_name => 'TASK_21137',

• Laden als SQL Plan Baselines aus STS (siehe Szenario 2)

(task_name => 'TASK_21137',

execution_name => 'Vergleich1',

level_filter => 'REGRESSED')) p;

dbms_sqltune.load_sqlset('SUB_STS1', sqlset_cur);

close sqlset_cur;

end;

/

� Selektive Auswahl von Statements über unterschiedlicheMethoden ( Cache, Snapshots, SQL-Ids, Trace etc.)

� Persistente Speicherung in der Datenbank

� Kann auf andere DBs übertragen werden

� Voraussetzung für unterschiedliche Tuning Tasks z.B. für

Vorteile

� Voraussetzung für unterschiedliche Tuning Tasks z.B. fürSQL Tuning Advisor oder SQL Access Advisor

� Vereinfacht das Tuning von SQL Statements => Generierung von SQL Plan Baselines

• Handbücher

• Performance Guide

• Package Reference Guide

• Deutsche DBA Community

• Aktuelle Informationen

Informationen

• Aktuelle Informationen

• blogs.oracle.com/dbacommunity_deutsch

• Inhaltsverzeichnis

• tinyurl.com/dbacontent

• Beispiele zum Thema

• SQL Tuning Sets im Einsatz Teil 1 und Teil 2