performance tuning mit oracle 12c - doag.org · title: oracle 11g performance monitoring und...
TRANSCRIPT
Performance Tuning mit
Oracle 12c
Agenda
1. Adaptive Execution Plans
2. Adaptive Statistics
3. SQL Plan-Direktiven
4. Neuerungen bei Statistiken
5. Konkurrierendes Sammeln von Statistiken
6. Private Session-Statistiken
7. Online-Statistiken
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 2
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 3
1 Adaptive Execution Plans
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 4
Adaptive Query Optimization
Der 12c Optimizer ist in der Lage, zur Laufzeit
zusätzliche Informationen zu verarbeiten und
Anpassungen am Ausführungsplan vorzunehmen, um
einen besseren Plan zu erzielen
Damit werden folgende Lücken geschlossen, die zu
nicht-optimalen Plänen führen:
Statistiken werden häufig nur wöchentlich gesammelt und sind
damit selten aktuell
Datenbanken mit starken Schwankungen in den
Tabelleninhalten (Staging Tables, Data Warehouse)
Statistiken wurden nicht optimal erstellt, sind veraltet oder
wurden gar nicht erstellt
Adaptive Query Optimization
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 5
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 6
Adaptive Pläne
Verschieben die entgültige Enscheidung über den
optimalen Plan auf den Ausführungszeitpunkt
Der Optimizer prüft, ob die Schätzung der Cardinality
von der aktuellen Anzahl von Sätzen abweicht
Wird ein signifikanter Unterschied festgestellt, wird der
Plan (oder ein Teil des Plans) bei der erstmaligen
Ausführung der SQL-Anweisung angepasst
Aktuell zwei Methoden:
Vereinigungsmethoden
Parallele Ausführungsmethoden
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 7
Adaptive Vereinigungsmethoden
Der Optimizer verfügt über einen initialen Plan (Default Plan)
Während der Ausführung der SQL-Anweisung erhält der Statistik-
Sammler Informationen über die tatsächliche Anzahl vom Sätzen
Basierend auf diesen Informationen trifft der Optimizer die finale
Entscheidung über die Vereinigungsmethode
Aktuell ist der Optimizer in der Lage zwischen einem Nested Loop
Join und einem Hash Join hin und her zu wechseln
Adaptive Vereinigungsmethoden sind in Oracle 12c standardmäßig
implementiert
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 8
SQL Erstausführung CUST_Z COUNT(*)
------ ----------
68736 1
SQL_ID 4u2cw9xyp9wk3, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ c.cust_zip,count(*) FROM customers
c, orders o WHERE o.product = 'Smart Phone' AND o.cust_id = c.cust_id
GROUP BY c.cust_zip
Plan hash value: 3920006230
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21157 (100)| |
| 1 | HASH GROUP BY | | 1 | 24 | 21157 (1)| 00:00:04 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 24 | 21157 (1)| 00:00:04 |
|* 4 | TABLE ACCESS FULL | ORDERS | 1 | 14 | 21156 (1)| 00:00:04 |
|* 5 | INDEX UNIQUE SCAN | CUSTOMERS_I1 | 1 | | 0 (0)| |
| 6 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 10 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Note
-----
- this is an adaptive plan
Änderung in den Daten, signifikant mehr Orders für “Smart Phone”
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 9
SQL – Nach Veränderung der Daten CUST_Z COUNT(*)
------ ----------
68736 1000000
SELECT * FROM TABLE(dbms_xplan.display_cursor(format=>'+adaptive'));
SQL_ID 4u2cw9xyp9wk3, child number 0
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21157 (100)| |
| 1 | HASH GROUP BY | | 1 | 24 | 21157 (1)| 00:00:04 |
| * 2 | HASH JOIN | | 1 | 24 | 21157 (1)| 00:00:04 |
|- 3 | NESTED LOOPS | | | | | |
|- 4 | NESTED LOOPS | | 1 | 24 | 21157 (1)| 00:00:04 |
|- 5 | STATISTICS COLLECTOR | | | | | |
| * 6 | TABLE ACCESS FULL | ORDERS | 1 | 14 | 21156 (1)| 00:00:04 |
|- * 7 | INDEX UNIQUE SCAN | CUSTOMERS_I1 | 1 | | 0 (0)| |
|- 8 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 10 | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | CUSTOMERS | 1 | 10 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
Adaptive Vereinigungsmethoden
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 10
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 11
IS_RESOLVED_ADAPTIVE_PLAN
Neue Spalte in V$SQL
‘Y’ … Der Plan ist “adaptiv” und der finale Plan wurde ausgewählt
‘N ‘ … Der Plan ist “adaptiv”, der finale Plan wurde jedoch noch nicht ausgwählt
NULL … Der Plan ist nicht “adaptiv”
SELECT sql_id,child_number,is_resolved_adaptive_plan
FROM v$sql
WHERE sql_id = '4u2cw9xyp9wk3';
SQL_ID CHILD_NUMBER I
------------- ------------ -
4u2cw9xyp9wk3 0 Y
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 12
Verwendete Parameter
Die Optimizer-Parameter befanden sich auf “Standard”:
NAME TYPE VALUE
------------------------------------ ----------- ----------
optimizer_adaptive_features boolean TRUE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 12.1.0.1
optimizer_adaptive_reporting_only boolean FALSE
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 13
Reporting-Modus
Im Reporting-Modus werden alle Informationen für adaptive
Vereinigungsmethoden gesammelt, allerdings wird der Default-
Plan nicht verändert
ALTER SESSION SET optimizer_adaptive_reporting_only = TRUE;
SELECT * FROM TABLE(dbms_xplan.display_cursor(format=>'+report'));
Adaptive plan:
-------------
This cursor has an adaptive plan, but adaptive plans are enabled for
reporting mode only. The plan that would be executed if adaptive plans
were enabled is displayed below.
Plan hash value: 3920006230
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21157 (100)| |
| 1 | HASH GROUP BY | | 1 | 24 | 21157 (1)| 00:00:04 |
|* 2 | HASH JOIN | | 1 | 24 | 21157 (1)| 00:00:04 |
|* 3 | TABLE ACCESS FULL| ORDERS | 1 | 14 | 21156 (1)| 00:00:04 |
| 4 | TABLE ACCESS FULL| CUSTOMERS | 1 | 10 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
optimizer_dynamic_sampling =
11
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 14
2 Adaptive Statistics
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 15
Adaptive Statistiken
Der neue Ansatz, Ausführungspläne an die aktuellen
Datenbestand anzupassen, erfordert, das Statistiken dynamisch
angepasst werden
Dazu wurden in 12c folgende Features eingeführt:
Dynamic Statistics
Automatic Reoptimization
SQL Plan-Direktiven
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 16
Beispiel Dynamic Statistics
ALTER SESSION SET optimizer_dynamic_sampling = 11;
SELECT * FROM TABLE(dbms_xplan.display_cursor(format=>'+adaptive'));
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21211 (100)| |
| 1 | HASH GROUP BY | | 1 | 27 | 21211 (1)| 00:00:04 |
| * 2 | HASH JOIN | | 1 | 27 | 21211 (1)| 00:00:04 |
|- 3 | NESTED LOOPS | | | | | |
|- 4 | NESTED LOOPS | | 1 | 27 | 21211 (1)| 00:00:04 |
|- 5 | STATISTICS COLLECTOR | | | | | |
| 6 | VIEW | VW_GBF_7 | 1 | 17 | 21210 (1)| 00:00:04 |
| 7 | HASH GROUP BY | | 1 | 14 | 21210 (1)| 00:00:04 |
| * 8 | TABLE ACCESS FULL | ORDERS | 1542K| 20M| 21156 (1)| 00:00:04 |
|- * 9 | INDEX UNIQUE SCAN | CUSTOMERS_I1 | 1 | | 0 (0)| |
|- 10 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 10 | 1 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 10 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
- this is an adaptive plan (rows marked '-' are inactive)
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 17
Automatic Reoptimization
Der Optimizer verwendet die Informationen, die während der
Ausführung einer SQL-Anweisung entstehen
Weicht die Ausführung signifikant von den Schätzungen des
Optimizers ab, wird eine Anpassung des Ausführungsplans bei der
nächsten Ausführung in Betracht gezogen
Folgende Features unterstützen die Reoptimization:
Statistik Feedback
Performance Feedback
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 18
Statistik Feedback
Mit der erstmaligen Ausführung einer SQL-Anweisung entscheidet
der Optimizer, ob ein Monitoring für das Statistik Feedback aktiviert
werden soll
Die Aktivierung erfolgt nach folgenden Kriterien:
Tabellen ohne Statistiken existieren
Mehrere Filter existieren an einer Tabelle
Prädikate mit komplexen Operatoren sind vorhanden
SELECT sql_id,child_number,is_reoptimizable
FROM v$sql WHERE sql_id='c2vq4sacjkh8k';
SQL_ID CHILD_NUMBER I
------------- ------------ -
c2vq4sacjkh8k 0 Y
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 19
Beispiel Statistik Feedback
CUST_Z COUNT(*)
------ ----------
68736 901000
SQL_ID c2vq4sacjkh8k, child number 1
-------------------------------------
SELECT /*+ gather_plan_statistics */ c.cust_zip,count(*) FROM customers
c, orders o WHERE o.product = 'Smart Phone' AND o.cust_id = c.cust_id
AND o.cnt > 1 AND o.amount > 300 AND o.order_text = 'Internet order'
GROUP BY c.cust_zip
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21229 (100)| |
| 1 | HASH GROUP BY | | 1 | 48 | 21229 (1)| 00:00:04 |
|* 2 | HASH JOIN | | 901K| 41M| 21199 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL| CUSTOMERS | 10000 | 97K| 17 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| ORDERS | 901K| 32M| 21179 (1)| 00:00:04 |
---------------------------------------------------------------------------------
Note
-----
- statistics feedback used for this statement
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 20
3 SQL Plan-Direktiven
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 21
SQL Plan-Direktiven
SQL Plan-Direktiven werden automatisch angelegt und mit den aus
der SQL Reoptimization gewonnen Erkenntnissen gefüttert
Mit diesen zusätzlichen Informationen ist der Optimizer besser in der
Lage, den optimalen Plan zu bestimmen
SELECT o.owner,o.object_name,o.subobject_name,o.object_type,
d.type,d.reason
FROM dba_sql_plan_dir_objects o, dba_sql_plan_directives d
WHERE o.directive_id = d.directive_id
AND o.owner = 'DOAG';
OWNE OBJECT_ SUBOBJECT_NA OBJECT TYPE REASON
---- ------- ------------ ------ ---------------- ------------------------------------
DOAG ORDERS PRODUCT COLUMN DYNAMIC_SAMPLING SINGLE TABLE CARDINALITY MISESTIMATE
DOAG ORDERS CNT COLUMN DYNAMIC_SAMPLING SINGLE TABLE CARDINALITY MISESTIMATE
DOAG ORDERS AMOUNT COLUMN DYNAMIC_SAMPLING SINGLE TABLE CARDINALITY MISESTIMATE
DOAG ORDERS ORDER_TEXT COLUMN DYNAMIC_SAMPLING SINGLE TABLE CARDINALITY MISESTIMATE
DOAG ORDERS TABLE DYNAMIC_SAMPLING SINGLE TABLE CARDINALITY MISESTIMATE
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 22
4 Neuerungen bei Statistiken
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 23
Top Frequency-Histogramme
Hat eine Spalte mehr als 254 verschiedene Werte, dann wurde
automatisch ein “Height-balanced Histogram” erstellt
Problem: 99% der Zeilen hat weniger als 254 verschiedene Werte
Risiko: Die meißten populären Werte werden nicht richtig
repräsentiert
Es wäre besser, auf den populären Werten ein „Frequency
Histogram“ zu erstellen und die nicht-populären Werte zu ignorieren
Dies wird durch ein Top Frequency-Histogramm realisiert
Top Frequency-Histogramme werden erstellt, wenn:
Statistiksammlung mit “AUTO_SAMPLE_SIZE”
99,6% der Werte haben <= 254 Werte
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 24
DBMS_STATS-Berichte
Das Sammeln von Statistiken ist Zeit-kritisch und Ressourcen-
aufwendig
Informationen über Zeiten und Verlauf sind deshalb für die Planung
sehr wichtig
BEGIN
:my_report := DBMS_STATS.REPORT_STATS_OPERATIONS(
since => SYSTIMESTAMP -1, until => SYSTIMESTAMP,
detail_level => 'TYPICAL', format => 'HTML');
END;
/
DBMS_STATS-Berichte
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 25
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 26
5 Konkurrierendes Sammeln von Statistiken
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 27
Konkurrierendes Sammeln
Mehrer Tabellen in einem Schema (Datenbank) oder Patitionen einer
Tabelle parallel bearbeiten
Oracle erstellt einen Job für jede Tabelle und Partition
Die Datenbank führ so viele Jobs wie möglich parallel aus, die
übrigen gehen in eine Warteschlange:
job_queue_processes
Oracle AQ
SQL> BEGIN
2 DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');
3 END;
4 /
Verbesserung in 12c:
Ist eine Tabelle oder Partition sehr klein oder leer, dann wird diese automatisch in
einen Batch mit anderen kleinen Objekten gestellt
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 28
6 Private Session-Statistiken
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 29
Statistiken für Global Temporary Tables
Eine GTT wird Datenbank-weit benutzt
Die Daten in einer GTT sind der jeweiligen Session zugeordnet
Mit 12c ist es möglich, Session-bezogene Statistiken für GGT zu
erstellen
SELECT dbms_stats.get_prefs('GLOBAL_TEMP_TABLE_STATS','DOAG') FROM dual;
DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS','DOAG')
------------------------------------------------------
SESSION
CREATE GLOBAL TEMPORARY TABLE temp_table(text VARCHAR2(200));
INSERT INTO temp_table SELECT product FROM doag.orders;
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 30
7 Online-Statistiken
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 31
Online-Statistiken
Statistiken werden automatische angelegt, wenn:
Ein Index erstellt wird
CREATE TABLE AS SELECT …
INSERT INTO … AS SELECT …
Online-Statistiken für Tabellen erstellen keine Histogramme und
Index-Statistiken
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 10M| 486M| 34091 (1)| 00:00:05 |
| 1 | LOAD AS SELECT | ORDERS_COPY | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 10M| 486M| 21160 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL | ORDERS | 10M| 486M| 21160 (1)| 00:00:04 |
------------------------------------------------------------------------------------------------
Ausschalten:
/*+ NO_GATHER_OPTIMIZER_STATISTICS */
F&A
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 32
10.11.2013 Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich Seite 33
Vielen Dank für Ihre Aufmerksamkeit !
Performance Tuning
mit Oracle 12c
DOAG-Konferenz 2013 Nürnberg
Copyright © 2013 by Lutz Fröhlich
Alle Rechte vorbehalten.