SQL Tuning Advisor

Heinz-Wilhelm Fabry
BU Database Technologies
ORACLE Deutschland GmbH
Ist
Ihre Datenbank
auch schnell
(genug)?
4 / 114
Tuning nur mit dem richtigen Werkzeug ...
5 / 114
Agenda
Objekt-Statistiken
Tuning Konzept in 10g
Advisors
10gR2 - Noch schneller
6 / 114
Was erwartet mich in 10g?
Gute Statistiken = Effiziente Optimierung
Die Datenbank sorgt automatisch dafür, dass
aktuelle Statistiken auf Tabellen/Indizes liegen
Warum?
Rule Based Optimizer wird nicht mehr unterstützt
Code noch vorhanden, aber bei Fehlern mit RBO keine
Support-Unterstützung mehr
RBO Desupport
Migration to the CBO
Note: 189702.1
Note: 222627.1
Auch das Dictionary wird ab 10g analysiert
7 / 114
Automatische Statistik-Erzeugung?
Vorteile
Datenbank bestimmt,
ob Statisiken überhaupt neu erzeugt werden müssen
welches das ideale Sample-Size ist
wo Histogramme sinnvoll und hilfreich sind
Risiken und Nebenwirkungen
Objekte, die häufigen oder
extremen Schwankungen unterliegen (v.a. im OLTP Umfeld)
8 / 114
Default Jobs in 10gR2
Automatisch installierte Jobs
9 / 114
GATHER_STATS_JOB
Monitoring ist per Default für alle Tabellen
eingeschaltet
DML-Veränderungen werden im Hintergrund
aufgezeichnet
Sammelt keine Statistiken für
Globale temporäre Objekte
Materialized View Logs
Objekte mit gesperrten Statistiken
Anmerkung
Wenn Statistiken manuell erzeugt werden, dann
mit dem Package DBMS_STATS
und das ANAYLZE-Kommando nicht verwenden
10 / 114
GATHER_STATS_JOB
TabellenMonitoring ON
SMON
alle 3h
manuell:
SQL> exec DBMS_STATS.
FLUSH_DATABASE_MONITORING_INFO();
Tabellenänderungen in: USER_TAB_MODIFICATIONS:
ΠTabellen ohne Statisiken:
Statistiken ð EMPTY
• Tabelleninhalt >10% verändert:
Statistiken ð STALE
Automatischer Job - einmal pro Tag:
manuell:
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
11 / 114
GATHER_STATS_JOB
Objekte:
Analyse-Reihenfolge:
hoch
27%
54%
27%
Priorität
54%
Statistiken: EMPTY
Statistiken: STALE
Statistiken: OK
niedrig
12 / 114
Optimizer ohne Statistiken
Was passiert, wenn der CBO keine Statistiken
vorfindet?
Ÿ
Ÿ
Der Optimizer schätzt zur Laufzeit (beim Parsen)
Statistiken für Tabellen und Indizes
Vorraussetzung
OPTIMIZER_DYNAMIC_SAMPLING=n (n > 1)
Level 0 - Dynamic Sampling ausschalten (~OLTP)
Level 2 - Optimizer berücksichtigt ersten 64 Blöcke
Mehr Info: Performance Tuning Guide - Kap. 14 S. 15
13 / 114
Objekte ausnehmen oder Locken
14 / 114
Objekte ausnehmen oder Locken
15 / 114
Konfiguration
Job vollständig ausschalten
execute
execute DBMS_SCHEDULER.DISABLE('<Jobname>');
DBMS_SCHEDULER.DISABLE('<Jobname>');
Löschen und Sperren von Statistiken
execute
execute
DBMS_STATS.DELETE_TABLE_STATS('SH','INT_T');
DBMS_STATS.DELETE_TABLE_STATS('SH','INT_T');
execute
execute
DBMS_STATS.LOCK_TABLE_STATS('SH','INT_T');
DBMS_STATS.LOCK_TABLE_STATS('SH','INT_T');
16 / 114
Agenda
Objekt-Statistiken
Tuning Konzept in 10g
Advisors
10gR2 - Noch schneller
17 / 114
Warum ist Tuning in 10g anders?
Problematik vor 10g
Auswertungen spiegeln nur das Mittel in einem
bestimmten Intervall wider
Wenig Aussagekraft über den Tellerrand hinaus
Wenig Möglichkeiten zur Analyse der Client-Perf.
Aufwändige Diagnose von Problemfällen
18 / 114
Warum ist Tuning in 10g so einfach?
Neu ab 10g
Punktgenaue Analyse
Statistiken werden automatisch gesammelt und im
AWR (Automatic Workload Repository) abgelegt
ASH (Active Session History) gibt Infos über Sessions
ADDM (Automatic Database Diagnostic Monitor)
wertet all das aus und gibt Empfehlungen
19 / 114
Vorraussetzungen fürs Tuning
STATISTICS_LEVEL=TYPICAL [default]
Alternative Settings
BASIC
ALL
TYPICAL bewirkt
Automatic SGA Tuning möglich
Systemstatistiken werden alle 60 Minuten gesammelt
Oracle
BASIC
Oracle Advisory
Advisory
BASIC TYPICAL
TYPICAL ALL
ALL
--------------------------------------------------------------------------------------Buffer
XX
XX
Buffer Cache
Cache Advice
Advice
MTTR
XX
XX
MTTR Advice
Advice
Shared
XX
XX
Shared Pool
Pool Advice
Advice
Segment
Level
Statistics
X
XX
Segment Level Statistics
X
PGA
XX
XX
PGA Advice
Advice
Timed
Statistics
X
X
Timed Statistics
X
X
Timed
XX
Timed OS
OS Statistics
Statistics
Plan
XX
Plan Execution
Execution Statistics
Statistics
20 / 114
Vorraussetzungen fürs Tuning
Warum nicht STATISTICS_LEVEL=BASIC ?
Tabellen-Monitoring ist abgestellt
Automatic-SGA-Management geht nicht
SGA und PGA-Advisors funktionieren nicht
Wer Funktionalität gezielt abschalten will, stellt:
das Statistik-Intervall ab
kann die ASH abschalten
kann den Job abstellen
21 / 114
HIDDEN
Vorraussetzungen fürs Tuning
22 / 114
Tuning in 10g konzeptionell
SGA
Statistiken
MMON*
AWR
Snapshots
Alerts
ADDM**
Proaktiv
ADDM
Ergebnisse
DBA
Reaktiv
*Memory Monitor / **Automatic DB Diagnostic Manager
23 / 114
Handwerkszeug fürs Tuning
AWR (Automatic Workload Repository)
Liegt im Tablespace SYSAUX
Enthält
In-Memory-Statistiken - einsehbar in V$-Views
AWR Snapshots (aktuell und historisiert)
Verwaltet sich selbst
Daten im AWR werden vom MMON gesammelt
Alle 60 Minuten [default] automatisch
Eigene Snapshots können angelegt werden
EXEC dbms_workload_repository.create_snapshot();
Historisiert gespeichert [default: 7 Tage]
Das Löschen bestimmter Snapshots (Baseline) kann für
spätere Analysen unterbunden werden
24 / 114
Handwerkszeug fürs Tuning
AWR (Automatic Workload Repository)
Viele Vorteile gegenüber STATSPACK
Wenig Systembelastung, da weniger SQL Statements
Wie groß wird das AWR?
?/rdbms/admin/utlsyxsz.sql
Daten stehen in WRH$-Tabellen und sind über die
DBA_HIST-Views einsehbar
Einen AWR-Report (ohne EM) erzeugen:
?/rdbms/admin/awrrpt.sql
25 / 114
Handwerkszeug fürs Tuning
AWR
Vorhaltezeit und Intervall verändern:
26 / 114
Handwerkszeug fürs Tuning
AWR
Vorhaltezeit und Intervall verändern:
27 / 114
HIDDEN
Handwerkszeug fürs Tuning
AWR transportieren
Extrahieren:
DBMS_SWRF_INTERNAL.AWR_EXTRACT(
dmpfile => 'awr_data.dmp', dmpdir => 'TMP_DIR',
bid => 302, eid => 305);
Laden in ein Pufferschema:
DBMS_SWRF_INTERNAL.AWR_LOAD(
SCHNAME => 'AWRTEMP',
dmpfile => 'awr_data', dmpdir => 'TMP_DIR' );
Verschieben ins AWR der Zieldatenbank (SYS):
DBMS_SWRF_INTERNAL.MOVE_TO_AWR(
SCHNAME => 'AWRTEMP_USER');
Warum?
Auswertungen außerhalb des Produktivsystems
Zentrales Repository
28 / 114
Handwerkszeug fürs Tuning
Ein AWR Größenbeispiel
Oracle Global Single Instance
60 verschiedene Oracle Applications Module
4 Knoten
á 36 Dual Core CPUs
á 144 GB RAM
10000 Concurrent User
100 aktive Sessions parallel
AWR Daten:
60 Minuten Intervall
30 Tage Vorhaltezeit
Größe: 13 GB
29 / 114
Handwerkszeug fürs Tuning
ASH (Active Session History)
Ringpuffer in der SGA
Feste Größe: 2MB x #CPUs (max. 5% der SGA bzw. <30MB)
Enthält Informationen über aktive Sessions
Wird periodisch auszugsweise (ca. 1/10) ins AWR
geschrieben (Prozess: MMNL = Memory Monitor Light)
View: V$ACTIVE_SESSION_HISTORY
Genaue Beschreibung in Note:243132.1
30 / 114
Handwerkszeug fürs Tuning
ASH - Berichtsintervall definieren
31 / 114
Handwerkszeug fürs Tuning
ASH - Report 1/3
32 / 114
Handwerkszeug fürs Tuning
ASH - Report 2/3
33 / 114
Handwerkszeug fürs Tuning
ASH - Report 3/3
34 / 114
Agenda
Objekt-Statistiken
Tuning Konzept in 10g
Advisors
10gR2 - Noch schneller
35 / 114
Advisors im Überblick
SQL Tuning
SQL Access
ADDM
Segment
MTTR
Space
Undo
SGA Advisor
Memory
PGA Advisor
36 / 114
Handwerkszeug fürs Tuning - der ADDM
SQL Tuning
SQL Access
ADDM
Segment
MTTR
Space
Undo
SGA Advisor
Memory
PGA Advisor
37 / 114
Handwerkszeug fürs Tuning
ADDM (Automatic Database Diagnostic Monitor)
Wissensbasis für Performance-Anforderungen
Liefert detaillierte Empfehlungen
ADDM ausführbar über Kommandozeile oder im EM
Läuft jede Stunde (AWR-Snapshot) oder manuell mit:
SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql
SQL>@$ORACLE_HOME/rdbms/admin/addmrpti.sql (RAC)
Empfiehlt ggf. den Aufruf anderer Advisors
38 / 114
Handwerkszeug fürs Tuning
ADDM: Analysezeitraum festlegen
39 / 114
Handwerkszeug fürs Tuning
ADDM läuft im Hintergrund und analysiert
40 / 114
Handwerkszeug fürs Tuning
ADDM: Ergebnisse
41 / 114
Handwerkszeug fürs Tuning
ADDM: Ergebnisse im Detail
42 / 114
Handwerkszeug fürs Tuning
ADDM: Ergebnisse im Detail
43 / 114
Handwerkszeug fürs Tuning
ADDM: Ergebnisse im Detail
44 / 114
Handwerkszeug fürs Tuning
ADDM: Ergebnisse im Detail
45 / 114
Memory Tuning
SQL Tuning
SQL Access
ADDM
Segment
MTTR
Space
Undo
SGA Advisor
Memory
PGA Advisor
46 / 114
Memory Tuning - SGA & PGA
À
¹
Batch
OLTP
Buffer Cache
Buffer Cache
Large Pool
Large Pool
SQL Cache
SQL Cache
Java Pool
Java Pool
sort
sort
SGA
PGA
47 / 114
Automatic Shared Memory Management
SGA_TARGET und STATISTICS_LEVEL=TYPICAL
müssen gesetzt sein
Auto Tuned
Database
buffer cache
Streams pool
Redo log
buffer
Shared pool
Large pool
Fixed SGA
Java pool
Gesamte SGA
SGA_MAX_SIZE=...
48 / 114
Automatic Shared Memory Management
Im Hintergrund vom MMAN (Memory Manager)
gesteuert
MMAN
Koordiniert
Größe der
einzelnen SGAKomponenten
Hintergrund:
SGA Memory
Broker
Überwacht
Komponenten und
koordiniert
Resize-Operation
49 / 114
SGA Advisor
SGA historisiert im EM
50 / 114
SGA Advisor
SGA Advisor:
auch:
V$SGA_TARGET_ADVICE
51 / 114
SGA Advisor
SGA Advisor - Empfehlung anwenden
52 / 114
PGA Advisor
PGA Advisor
PGA_AGGREGATE_TARGET muß gesetzt sein
Achtung ab 10g
Wenn PGA_AGGREGATE_TARGET nicht gesetzt wird, werden 20% der
SGA für die PGA reserviert
53 / 114
PGA Advisor
PGA Advisor
54 / 114
PGA Advisor
PGA Advisor
55 / 114
SQL Tuning
SQL Tuning
SQL Access
ADDM
Segment
MTTR
Space
Undo
SGA Advisor
Memory
PGA Advisor
56 / 114
SQL Tuning-Schritte in 10g
1. Monitoring
2. Statements identifizieren
3. Tuning Advisors benutzen
SQL Access Advisor
Indizes
Materialized Views
Indizes auf Materialized Views
Empfehlungen basierend auf einem Workload
SQL Tuning Advisor
Optimizer im Tuning Mode
Profile für Statements erstellen
Empfehlungen in Bezug auf Einzelstatements
4. Empfehlungen implementieren
57 / 114
Monitoring - EM Performance Tab
58 / 114
Monitoring - EM Performance Tab
~Echtzeit - refresh alle 15 Sekunden
Interaktive Graphiken Adobe SVG
59 / 114
Monitoring - EM Performance Tab
60 / 114
Monitoring - EM Performance Tab
61 / 114
SQL Tuning Advisor
Nutzung im EM oder mit DBMS_SQLTUNE
Es existieren verschiedene Tuning Modes
Comprehensive mode durchläuft alle möglichen Analysestufen
Limited mode generiert keine SQL Profile
SQL Profile sind gespeicherte zusätzliche Informationen, die zu einem besseren Ausführungsplan führen OHNE die Applikation zu verändern
Diverse Optimizer-Einstellungen
Korrekturen für fehlende oder "stale" Statistiken
Korrekturen für falsche Abschätzungen
Ist persistent und verändert das Statement NICHT
Exportierbar/importierbar (ab 10gR2)
62 / 114
SQL Tuning
Automatische Auswahl
AWR
ADDM
SQL Quellen
High-load SQL
Manuelle Auswahl
AWR
Cursor Cache
Filter / Rank
SQL Tuning Set
(STS)
SQL Tuning
Advisor
User-defined
63 / 114
SQL Tuning
SQL Tuning - Einstiegspunkt
64 / 114
SQL Tuning
65 / 114
SQL Tuning
66 / 114
SQL Tuning
67 / 114
SQL Tuning
68 / 114
SQL Tuning
69 / 114
SQL Tuning
70 / 114
SQL Tuning
71 / 114
SQL Tuning
72 / 114
SQL Tuning
73 / 114
SQL Tuning
74 / 114
SQL Tuning mit DBMS_SQLTUNE
Übersicht
Tuning Task Management
o
o
o
o
SQL Profile Management
CREATE_TUNING_TASK
EXECUTE_TUNING_TASK
REPORT_TUNING_TASK
DROP_TUNING_TASK
o
o
o
ACCEPT_SQL_PROFILE
DROP_SQL_PROFILE
ALTER_SQL_PROFILE
SQL Tuning Set Management
o
o
o
o
CREATE_SQLSET
CAPTURE_CURSOR_CACHE_SQLSET
SELECT_SQLSET
DROP_SQLSET
75 / 114
SQL Tuning mit DBMS_SQLTUNE
4 Schritte zum SQL Profile
DBMS_SQLTUNE.CREATE_TUNING_TASK
DBMS_SQLTUNE.EXECUTE_TUNING_TASK
DBMS_SQLTUNE.REPORT_TUNING_TASK
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
76 / 114
SQL Profile mit Literalen
exec
exec
:p_name:=dbms_sqltune.accept_sql_profile
:p_name:=dbms_sqltune.accept_sql_profile
(task_name=>'SIG',name=>'SIG_PROFILE',
(task_name=>'SIG',name=>'SIG_PROFILE', FORCE_MATCH=>TRUE);
FORCE_MATCH=>TRUE);
SQL> select name, status, force_matching, sql_text
from dba_sql_profiles;
NAME
STATUS
FOR SQL_TEXT
------------ -------- ---- ------------------------------SIG_PROFILE ENABLED YES select /*+ use_nl(c) ordered */
time_id, QUANTITY_SOLD,
AMOUNT_SOLD from sh.sales s,
sh.customers c
where c.cust_id = s.cust_id
and CUST_FIRST_NAME = 'Dina'
77 / 114
SQL Profiling Effektivität
Workload eines großen (deutschen) Kunden 73 problematische Queries:
Vorher
Nachher
Time (s)
Time (s)
10000
10 0 0 0
1000
10 0 0
100
10 0
10
10
1
1
1
5
9
13 17
21 25
29 33 37
41 45 49
Queries
53 57
61 65 69
1
5
9 13 17 21 25 29 33 37 41 45 49 53 57 61 65 69
Queries
78 / 114
Performance Evaluierung - Ausgangslage
Durchschnittliche AntwortZeit
Ohne Tuning
817s
Schlechteste
AntwortZeit
5.751s
Kumulierte
AntwortZeiten
58.821s
79 / 114
Performance Evaluierung - Manuelles Tuning
73 Statements - Tuning durch den Kunden
Durchschnittliche AntwortZeit
Ohne Tuning
Tuning manuell
Schlechteste
AntwortZeit
Kumulierte
AntwortZeiten
817s
5.751s
58.821s
30s
275s
2.131s
80 / 114
Performance Evaluierung - Tuning Advisor
Automatisches Tuning der 73 Statements
Durchschnittliche AntwortZeit
Ohne Tuning
Schlechteste
AntwortZeit
Kumulierte
AntwortZeiten
817s
5.751s
58.821s
Tuning manuell
30s
275s
2.131s
Automatisches
Tuning (Profile)
13s
59s
929s
81 / 114
Performance Evaluierung - Zeitbedarf
Optimierungsdauer für alle 73 Statements
insgesamt ca. 1½ Stunden
Time (s)
10 0 0
10 0
10
1
1
5
9
13 17 21 25 29 33 37 41 45 49 53 57 61
65 69
Queries
82 / 114
Space Advisors
SQL Tuning
SQL Access
ADDM
Segment
MTTR
Space
Undo
SGA Advisor
Memory
PGA Advisor
83 / 114
Default Jobs in 10gR2
AUTO_SPACE_ADVISOR_JOB:
84 / 114
Tabellen verkleinern
High
Watermark
Tabelle kopieren
Zusätzlicher
Speicherplatz
Alte Tabelle löschen
Indexpflege
High
Watermark
85 / 114
Tabellen verkleinern
High
Watermark
High
Watermark
Daten
verschieben
Online
Indizes werden gepflegt
Trigger zünden nicht
ALTER
ALTER TABLE
TABLE <tabellenname>
<tabellenname> SHRINK
SHRINK SPACE;
SPACE;
86 / 114
Tabellen verkleinern im EM
Zentrales Advisory ==> Segment Advisor
Alternativ:
DBMS_SPACE Package
87 / 114
Tabellen verkleinern im EM
88 / 114
Tabellen verkleinern im EM
89 / 114
Tabellen verkleinern im EM
Der Unterschied besteht darin, dass bei der Freigabe des
Speicherplatzes eine kurzzeitige Sperre auf das gesamte Objekt
gehalten wird, um die High Water Mark zurückzusetzen
90 / 114
Tabellen verkleinern im EM
91 / 114
HIDDEN
Tabellen verkleinern im EM
92 / 114
HIDDEN
Tabellen verkleinern im EM
93 / 114
Undo Advisor
Zentrales Advisory ==> Undo Management
94 / 114
Undo Advisor
95 / 114
Undo Advisor
96 / 114
Und wenn irgendwo etwas hängt?
Klassisch
Systemstate-Dump erzeugen und Support
einschalten
Im EM ab 10gR2
Hängen-Analyse starten
Bei Bedarf vorher
Umschalten des Monitoring Modes auf direkten
Speicherzugriff
Sinnvollerweise aus dem GridControl
97 / 114
Erkennen einer Blockade-Situation
98 / 114
Analyse der Blockade
Bei Bedarf den Monitoring Modus ändern
99 / 114
Blockade- / Hängen-Analyse starten
100 / 114
Blockade- / Hängen-Analyse
101 / 114
Blockade- / Hängen-Analyse
Session detailliert ansehen
102 / 114
Blockade- / Hängen-Analyse
Blockade-Baum hierarchisch
103 / 114
Blockade- / Hängen-Analyse
SQL-Details
104 / 114
Agenda
Objekt-Statistiken
Tuning Konzept in 10g
Advisors
10gR2 - Noch schneller
105 / 114
Steuerbares COMMIT-Verhalten
COMMIT kann angepaßt werden
Nicht für verteilte Transaktionen
Implementierung
Verfahren und Zeitpunkt des Schreibens
Neuer Initialisierungs-Parameter
COMMIT_WRITE = '{IMMEDIATE | BATCH},
{WAIT | NOWAIT}'
Kann dynamisch angepaßt werden
Neue WRITE-Klausel für COMMIT
COMMIT WRITE NOWAIT;
Möglichen Datenverlust berücksichtigen !
106 / 114
Self-Tuning Multi-Block Reads
Oracle selbst wählt den Idealwert für
DB_FILE_MULTIBLOCK_READ_COUNT
Der Default korrespondiert mit dem maximal
sinnvollen I/O-Size des Betriebssystems
107 / 114
Autotrace-Format
Autotrace benutzt automatisch DBMS_XPLAN
108 / 114
CPU + IO Kostenmodell
Berücksichtigung der CPU-Leistung beim CBO
Stark verbessert in 10gR2
Beispiel SALES Tabelle mit 150 Mio Rows
Time in s
180
120
IO Modell
CPU+IO Modell
60
0
Join Query
Re-ordering
predicates
109 / 114
Full Table Scan
Full Table Scan, keine Indizes
2,8 GB Daten (~120 Mio Rows)
Time in s
200
150
9i R2
10g
100
50
0
select_1
select_2
select_1: select * from sales where company_id !=2;
select_2: select * from sales where amount_sold >14965 and company_id =2;
110 / 114
Neuer In-Memory Sort Algorithmus
Neue verbesserte SORT-Implementierung
Hash-based
Dramatische Performance-Verbesserungen
Nutzt großen Hauptspeicher effizient aus
Sortier-Operation können bis zu 5x schneller sein
111 / 114
TPC-H Benchmark - Vergleich zu 10gR1
10gR2 ist 27% schneller beim TPC-H Benchmark
auf dem identischen System
8,604
6,795
R2
Quelle: http://www.tpc.org - 8.Juli 2005
112 / 114
Wichtige Quellen
Database Performance with Oracle Database 10g Release 2
http://www.oracle.com/technology/deploy/performance/pdf/twp_perf_database performance with oracle10gr2.pdf
Sort performance improvements in Oracle Database 10g Release 2
http://www.oracle.com/technology/deploy/performance/pdf/twp_general_sort_performance_10gr2_0605.pdf
Optimizing the Optimizer: Essential SQL Tuning Tips and Techniques
http://www.oracle.com/technology/products/manageability/database/pdf/ow05/PS_S997_273997_106-1_FIN_v2.pdf
113 / 114