Oracle 11g Performance Monitoring und Forecast

Performance Tuning mit
Oracle 12c
Agenda
1.
2.
3.
4.
5.
6.
7.
10.11.2013
Adaptive Execution Plans
Adaptive Statistics
SQL Plan-Direktiven
Neuerungen bei Statistiken
Konkurrierendes Sammeln von Statistiken
Private Session-Statistiken
Online-Statistiken
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 2
1
Adaptive Execution Plans
10.11.2013
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 3
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:



10.11.2013
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
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 4
Adaptive Query Optimization
10.11.2013
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 5
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:


10.11.2013
Vereinigungsmethoden
Parallele Ausführungsmethoden
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 6
Adaptive Vereinigungsmethoden





Der Optimizer verfügt über einen initialen Plan (Default Plan)
Während der Ausführung der SQL-Anweisung erhält der StatistikSammler 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 7
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 8
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)
10.11.2013
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 9
Adaptive Vereinigungsmethoden
10.11.2013
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 10
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 11
Verwendete Parameter

Die Optimizer-Parameter befanden sich auf “Standard”:
NAME
-----------------------------------optimizer_adaptive_features
optimizer_dynamic_sampling
optimizer_features_enable
optimizer_adaptive_reporting_only
10.11.2013
TYPE
----------boolean
integer
string
boolean
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
VALUE
---------TRUE
2
12.1.0.1
FALSE
Seite 12
Reporting-Modus

Im Reporting-Modus werden alle Informationen für adaptive
Vereinigungsmethoden gesammelt, allerdings wird der DefaultPlan 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 13
2
Adaptive Statistics
10.11.2013
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 14
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:



10.11.2013
Dynamic Statistics
Automatic Reoptimization
SQL Plan-Direktiven
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 15
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 16
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:


10.11.2013
Statistik Feedback
Performance Feedback
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 17
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 18
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 19
3
SQL Plan-Direktiven
10.11.2013
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 20
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
---DOAG
DOAG
DOAG
DOAG
DOAG
OBJECT_
------ORDERS
ORDERS
ORDERS
ORDERS
ORDERS
10.11.2013
SUBOBJECT_NA
-----------PRODUCT
CNT
AMOUNT
ORDER_TEXT
OBJECT
-----COLUMN
COLUMN
COLUMN
COLUMN
TABLE
TYPE
---------------DYNAMIC_SAMPLING
DYNAMIC_SAMPLING
DYNAMIC_SAMPLING
DYNAMIC_SAMPLING
DYNAMIC_SAMPLING
REASON
-----------------------------------SINGLE TABLE CARDINALITY MISESTIMATE
SINGLE TABLE CARDINALITY MISESTIMATE
SINGLE TABLE CARDINALITY MISESTIMATE
SINGLE TABLE CARDINALITY MISESTIMATE
SINGLE TABLE CARDINALITY MISESTIMATE
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 21
4
Neuerungen bei Statistiken
10.11.2013
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 22
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 23
DBMS_STATS-Berichte


Das Sammeln von Statistiken ist Zeit-kritisch und Ressourcenaufwendig
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;
/
10.11.2013
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 24
DBMS_STATS-Berichte
10.11.2013
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 25
5
Konkurrierendes Sammeln von Statistiken
10.11.2013
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 26
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 27
6
Private Session-Statistiken
10.11.2013
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 28
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 29
7
Online-Statistiken
10.11.2013
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 30
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 */

10.11.2013
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 31
F&A
10.11.2013
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 32
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.
10.11.2013
Performance Tuning mit Oracle 12c - Copyright (c) 2013 by Lutz Fröhlich
Seite 33