Angelika Gallwitz - Informatik Aktuell

www.informatik-aktuell.de
Angelika Gallwitz
Statistische Auswertungen in Oracle mit
Statspack und AWR
Mittwoch, 16. Dezember
2015
1

DBA Datenbankadministrator

Seit 1984 in der IT tätig

Seit 1987 mit Oracle Datenbanken

Als Entwickler und Datenbankadministrator
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
2
1.
Einleitung
2.
Oracle 12c Neuerungen
3.
Systemvorraussetzungen
4.
Perfstat Schema einrichten, Statspack Reports
5.
ADDM und AWR Architektur
6.
ADDM Ablauf
7.
AWR Reports
8.
Leitfaden zur Auswertung
9.
Massnahmen I/O Durchsatz Messung Calibration
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
3


Die Automatischen Features der Performance Diagnose in der Oracle Datenbank 12c.
Statspack-Reports bzw. AWR Reports werden herangezogen, um Performance Engpässe zu erkennen
und Maßnahmen zu ergreifen. Wie erzeuge und interpretiere ich einen Statistik Report. Wie verändere
ich Snap Level und Threshold.

Im AWR Repository werden aktuelle und historischen Performance Statistiken abgespeichert.

Der (ADDM) durchläuft ein Set vordefinierter Kriterien, nach denen die aktuelle Performance der
Datenbank analysiert wird .

Nachdem ein Problem identifiziert und analysiert worden ist, hilft der ADDM selbst Probleme zu
beheben

Mit Statspack ADDM-, ASH- und AWR-Reports können Probleme und Statistiken sofort oder
nachträglich analysiert werden.

Der ADDM analysiert Performance Probleme direkt nach dem Auftreten des Events.

ADDM sollte von DBAs zuerst benutzt werden, wenn ein Performance Problem bemerkt wird.

Es wird anhand der Wait-Events eine Zuordnung zu den Problembereichen vorgenommen und
Empfehlungen zu Tuning Maßnahmen gegeben
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
4

Neue Idle Events in den Reports: LogMiner, PQ, SQL*Net, Capture Reply

In der Performance Analyse und Diagnose durch den "Real Time ADDM".

SQL Monitor-,ASH-, ADDM und AWR – Reports zeigen jetzt Statistiken von „In-Memory“
Operationen

AWR Report hat neue Sektionen - In-Memory Segments Statistics – IO Statistik.

Der ADDM kann in unterschiedlichen Connection Modi gefahren werden.

Der „Real-Time ADDM“ mit dem Emergency Feature hilft die Ursachen und Lösungen zu
finden, wenn die Datenbank hängt.


Performance Hub Active Report

PGA_AGGREGATE_LIMIT kann jetzt gesetzt werden
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
5
ADDM und AWR sind standardmäßig eingeschaltet und werden gesteuert über den Parameter
Control_management_pack_access:
timed_statistics = true
statistics_level = [typical|all]
Control_management_pack_access=diagnostic/diagnostic+tuning
/* ausschalten mit „BASIC“
/* ausschalten mit None
Systemstatistiken müssen vorhanden sein:
dbms_stats.gather_system_stats(gathering_mode=>'start');
Nach 30 Minuten
dbms_stats.gather_system_stats(gather_mode=>'stop');
Für I/O Statistiken z.B. average read time benötigen Sie Ihre Hardwarespezifischen
Antwortzeiten normale Werte liegen zw. 5000 u. 20000 Microsekunden
Execute dbms_advisor.set_default_task_parameter(‚ADDM‘, ‚DBIO_EXPECTED‘, 8000);
Iostat –d 5
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
6
SQL> SELECT statistics_name, activation_level, Session_settable, session_status, statistics_view_name
FROM
v$statistics_level ORDER BY 1
STATISTICS_NAME
ACTIVAT SES SESSION_ STATISTICS_VIEW_NAME
---------------------------------------------------------------- ------- --- -------- -------------------------OLAP row load time precision
TYPICAL YES ENABLED
Automatic DBOP Monitoring
TYPICAL YES ENABLED V$SQL_MONITOR
Active Session History
TYPICAL NO ENABLED V$ACTIVE_SESSION_HISTORY
Adaptive Thresholds Enabled
TYPICAL NO ENABLED
Automated Maintenance Tasks
TYPICAL NO ENABLED
Bind Data Capture
TYPICAL NO ENABLED V$SQL_BIND_CAPTURE
Buffer Cache Advice
TYPICAL NO ENABLED V$DB_CACHE_ADVICE
Global Cache Statistics
TYPICAL NO ENABLED
Longops Statistics
TYPICAL NO ENABLED V$SESSION_LONGOPS
MTTR Advice
TYPICAL NO ENABLED V$MTTR_TARGET_ADVICE
Modification Monitoring
TYPICAL NO ENABLED
PGA Advice
TYPICAL NO ENABLED V$PGA_TARGET_ADVICE
Plan Execution Sampling
TYPICAL YES ENABLED V$ACTIVE_SESSION_HISTORY
Plan Execution Statistics
ALL
YES ENABLED V$SQL_PLAN_STATISTICS
SQL Monitoring
TYPICAL YES ENABLED V$SQL_MONITORING
Segment Level Statistics
TYPICAL NO ENABLED V$SEGSTAT
Shared Pool Advice
TYPICAL NO ENABLED V$SHARED_POOL_ADVICE
Streams Pool Advice
TYPICAL NO ENABLED V$STREAMS_POOL_ADVICE
Threshold-based Alerts
TYPICAL NO ENABLED
Time Model Events
TYPICAL YES ENABLED V$SESS_TIME_MODEL
Timed OS Statistics
ALL
YES ENABLED
Timed Statistics
TYPICAL YES ENABLED
Ultrafast Latch Statistics
TYPICAL NO ENABLED
Undo Advisor, Alerts and Fast Ramp up
TYPICAL NO ENABLED V$UNDOSTAT
V$IOSTAT_* statistics
TYPICAL NO
ENABLED
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
7

SQL> select pname, pval1 from sys.aux_stats$
;
PNAME
PVAL1
------------------------------ ---------STATUS
DSTART
DSTOP
FLAGS
1
CPUSPEEDNW
3161,90476
IOSEEKTIM
10
IOTFRSPEED
4096
SREADTIM
,865
MREADTIM
,161
CPUSPEED
1115
MBRC
0
MAXTHR
1865474048
SLAVETHR
479232
Execute dbms_advisor.set_default_task_parameter(‚ADDM‘, ‚DBIO_EXPECTED‘, 8650);
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
8

Seit Oracle 9.0

sreadtim - single block read time

mreadtim - multiblock read time

mbrc - multi-block read count

cpuspeed - CPU speed

Seit Oracle 9.2

maxthr - maximum I/O throughput

slavethr -average slave throughput

Seit Oracle 10g

cpuspeedNW - Represents noworkload CPU speed

ioseektim - I/O seek time equals seek time + latency time + operating system overhead time.

iotfrspeed - I/O transfer speed is the rate at which an Oracle database can read data in a single read
request

How to Set Different System Statistics for the Instance Doc Id 149560.1
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
9
•
Seit Oracle 8.1.7 bis heute unterstützt
•
Sinnvoll einsetzbar, wenn keine Diagnostik Pack
Lizenz vorhanden ist
•
Ist vergleichbar mit dem AWR Report
•
Speichert die Performance Statistik Daten permanent
in Oracle Tabellen im Schema des Users perfstat
•
Die gesammelten Daten können über den StatspackReport analysiert werden
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
10
•
Schema perfstat anlegen
• ?/rdbms/admin/spcreate.sql
•
Automatisierung
• /rdbms/admin/spauto.sql
•
Report erstellen
• ?/rdbms/admin/spreport.sql
•
Perfstat-User löschen
• ?/rdbms/admin/spdrop.sql
•
Perfstat-Daten löschen
• ?/rdbms/admin/spdrop/sppurge.sql
•
Readme
• ?/rdbms/admin/spdoc.txt
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
11
•
$ORACLE_HOME/rdbms/admin/sprepins.sql
Genereller Instance Healthcheck
•
$ORACLE_HOME/rdbms/admin/sprepsql.sql
Report einzelner SQL Statements über hash_value
Bsp:
•
•
•
•
SQL>
SQL>
SQL>
SQL>
connect perfstat/perfstat
execute statspack.snap
execute statspack.snap
select snap_id,old_hash_value,hash_value from perfstat.stats$sql_summary
order by hash_value;
$ORACLE_HOME/rdbms/admin/sprsqins.sql
Abfrage dbid ,instance, hash_value
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
12

•
•
•

In Tabelle perfstat.stats$statspack_parameter
select * from perfstat.stats$level_description
Levels: 0, 5 ,6 ,7 ,10
Levels = 5 ist der Default Wert
Level >= 7 beinhaltet zus. Segmentstatistiken
SQL> connect perfstat
SQL> execute statspack.snap - (i_snap_level=>7,
i_modify_parameter=>'true');
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
13

Kommt mit der Datenbank seit Oracle 10

Liegt im Schema SYS

Im Tablespace SYSAUX

Ist in jeder Oracle Edition enthalten(SE, EE)
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
14


Snap_level 1 statistics_level = typical
Snap_level 2 statistics_level = all
beinhaltet zusätzlich OS Statistiken

control_management_pack_access = diagnostic oder
tuning+diagnostic

TIMED_STATISTICS = TRUE

ash_enable = [true|false]
awr_restrict_mode = [true|false]
db_cache_advice = [on|off]


Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
15





Auch für Standard Edition ohne
zusätzliche Lizenz
Snap level 1-10
Es muß ein Job eingerichtet
werden, der regelmäßig Statistiken
sammelt
Es muß ein Job eingerichtet
werden, der die gesammelten
Statistiken löschen
Statistiken können adhoc
gesammelt werden
SQL> connect perfstat
SQL> execute statspack.snap
Statspack




Benötigt Diagnostic Pack
Snap Level 1 oder 2
ADDM sammelt automatisch
Statistiken 1 x jede Stunde,
in 11g werden diese 8 Tage
aufbewahrt
Statistiken können adhoc
gesammelt werden
SQL> connect sys / as sysdba
SQL> BEGIN
dbms_workload_repository.create
_snapshot();
END;
AWR
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
16
SelbstTuning
Komponente
ADDM
SelbstTuning
Komponente
Interne Clients
In Memory
StatistikSammlung
SGA
MMON
Prozess
SGA
V$
UserBackgroun
d Prozesse
Externe
Clients
DBA_
OEM
Repository
der
AWR u.
ADDM
Snapshots
WR.$
SQLPlus
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
17




Automatic Database Diagnostic Monitor, läuft direkt nach
jedem AWR Snapshot
Dabei wird im Hintergrund eine eigene Statistiksammlung,
das Automatic Workload Repository (kurz AWR) eingesetzt.
Per Default erzeugt Oracle 11g einmal stündlich ein StatistikSnapshot der Datenbankauslastung
Diese Snapshots werden per Default 8 Tage aufbewahrt
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
18
1.
Komponenten Überblick
2.
Ziel der ADDM Analyse
3.
Problemtypen des ADDM
4.
Der "Real Time ADDM„
5.
ADDM Modi
6.
ADDM Views
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
19
Datenbank Zeit reduzieren
Wartezeit reduzieren
Durchsatz Erhöhung
Ohne Resourcenänderung
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
20
CPU
Bottlenecks
High-load
SQL
Statements
Datenbank
Parameter
RAC global
cache
interconnect,
Wartezeit
Applikation
ADDM
Analyse
Concurrency
High-load
PL/SQL
Gleichzeitig
keit
Hot objects
und Top
SQLs
I/O
Durchsatz
Memory
Strukturen
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
21

Diagnostic Connection
◦ Real Time
◦ Emergency

Normal Connection
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
22

Emergency Monitoring“ und „Spot ADDM“„
◦ Unterstützt bei der Ursachenforschung:
 Datenbank hängt
 Login nicht mehr möglich
◦ Schlägt Lösungen vor ohne Neustart der Datenbank.
◦ Die Analyse Informationen stammen in Echtzeit aus der SGA:






Blockierende Sessions,
Deadlocks und andere Hänger
Shared pool connection,
Object Locks ,
Top Aktivitäten und andere Ausnahmesituationen
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
23
Datenbank langsam
oder hängt
Anmeldung
ist möglich
Emergency
analysis?
Yes
No
Yes
Emergency
Monitoring
Real-Time
ADDM
Andere
blockers?
Blocker
session?
Yes
No
Yes
No
No
System
ok?
Ende
Normale
Connection
Starte Analyse
• Hang Analysis Data
• I/O Metriken
• Host Metriken
View
• ASH data
• Hang data
Kill session
No
SR an Oracle?
View
• Analyse Ergebnis
• Empfehlung
• Hang Data
• Top Activity Snapshot
Empfehlungen folgen
oder Datenbank Instanz
runterfahren
Angelika Gallwitz
Statistische Auswertungen in Mittwoch, 16. Dezember
Oracle mit Statspack
und an
AWR
2015
In Anlehnung
Oracle
Dokumentation
24
1.
Trigger
2.
Trigger Control
3.
Analyse- MODE
4.
Diagnose- MODE
5.
Real Time ADDM Report
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
25

AWR Tabellen gibt es in 3 verschiedenen Bereichen
◦ Metadata (WRM$ …)
◦ Historische Daten (WRH$ …)
◦ AWR Tabellen der Advisory Funktionen (WRI$ …)
AW$AWREPORT
AWRRPT_HTML_TYPE_TABLE
AWRRPT_INSTANCE_LIST_TYPE
AWRRPT_TEXT_TYPE_TABLE
DBA_HIST_SNAPSHOT
DBMS_AWR_REPORT_LAYOUT
DBMS_FEATURE_AWR
DBMS_WORKLOAD_REPOSITORY
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
26

Ändern mit dem DBMS_WORKLOAD_REPOSITORY
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id =>
22, high_snap_id => 32, dbid => 3310949047 )

Änderung des Defaults auf 18 Tage Aufbewahrung und 30 Minuten
Interval
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention
=> 43200, interval => 30, topnsql => 100, dbid => 3310949047);

Setze ich das Interval auf 0 so wird kein Snapshot erzeugt
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
27

Oracle empfiehlt, in Zeiten guter Performance eine Baseline
als Referenz zu ziehen, damit diese in Zeiten schlechter
Performance herangezogen werden kann
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 270,
end_snap_id => 280, baseline_name => 'peak baseline', dbid =>
3310949047, expiration => 30);



Wie groß wird das AWR
?/rdbms/admin/awrinfo.sql
AWR-Daten extrahieren
?/rdbms/admin/awrextr.sql
Import der AWR-Daten in eine andere DATENBANK
?/rdbms/admin/awrload.sql
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
28

?/rdbms/admin/awrrpt.sql – Nonrac

?/rdbms/admin/awrqrpt.sql - RAC

?/rdbms//admin/awrrpti.sql -- RAC 1 Inst

?/rdbms/admin/awrsqrpt.sql – 1 Sqlstat

?/rdbms/awrddrpt.sql – Vergleich
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
29

awrddrpi.sql
Reports on differences between snapshot pairs

awrddrpt.sql
Defaults the dbid and instance number

awrextr.sql
Extracts AWR data into a .DMP file

awrgdrpi.sql
AWR global compare period report

awrgdrpt.sql
AWR global difference report

awrgrpt.sql
AWR global report

awrgrpti.sql
AWR RAC global report

awrinfo.sql
Output general AWR information

awrload.sql
Supports loading DUMP file data into AWR

awrrpt.sql
Defaults to current instance

awrrpti.sql
Select dbid and instance

awrsqrpi.sql
Difference report between two snapshots sql Statements

awrsqrpt.sql
Same as awrsqrpi but defaults dbid & instance

awrupd12.sql
Updates AWR data to version 12c.
Nur die AWR Daten werden geändert, die vorher mit aus einer früheren Version
geladen wurden oder aus einer anderen DB
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
30

Vorbereitung:
◦ Kenne dein System mit normaler Performance
◦ Mache ein Konzept für Performancemessung und Tuning
◦ Habe “normale” AWR/Statspack Snapshots als Referenz
◦ Baselines verschiedener Loads
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
31
1.
Statistiken sammeln von
Betriebssystem
Datenbank
Anwendung
2.
Durchsuche die Daten nach Performance-Problemen
3.
Erstelle eine Liste der Probleme und Fehler
4.
5.
Bilde ein Konzept oder Modell, was passiert ist, anhand der
gesammelten Daten
Implementiere anhand der Analyse die Änderungen und monitore
diese Änderungen
6.
Überprüfe ob die Performanceziele erreicht wurden
7.
Wiederhole die Schritte
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
32

Kann generiert werden im EM Express oder mit folgendem Script:

SQL> @$ORACLE_HOME/rdbms/admin/perfhubrpt.sql






Summary Tab
Activity Tab
Workload Tab
RAC Tab
Monitored SQL Tab
ADDM Tab


The Current ADDM Findings tab
real-time analysis für letzte 5 Minuten

Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
33

Kann generiert werden im EM Express oder mit folgendem Script:


Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
34
Der Einstieg in die Analyse erfolgt über die
DB Time und die Top 5 Timed Wait Events
In Oracle 12c Top 10 Timed Wait Events
1. Report Header mit DB Time anschauen
2. Top-5-Wait-Events
3. Für weitere Analsyse in den Abschnitt
gehen, aus dem die Top-5-WaitEvents
stammen
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
36
Snap Id
Snap Time
Sessions Curs/Sess
--------- ------------------- -------- --------Begin Snap:
13751 18-Jun-10 10:00:42
1,360
137.1
End Snap:
13752 18-Jun-10 11:00:11
1,613
162.9
Elapsed:
59.48 (mins)
DB Time:
863.08 (mins)




DB Time ist die Gesamtzeit aller user Prozesse, die entweder active
arbeiten oder active warten in ihrer Datenbankanfrage .
Average Active Sessions = Total DB time / Wall Clock Elapsed Time
DB Time beinhaltet nur foreground sessions incl. CPU time, IO time und
wait time, excludes idle wait time
In diesem Beispiel haben wir 1360 Sessions in der Sämple Periode
Active sessions werden 1 mal pro Sekunde gesammelt
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
37
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
38
SQL> @?/rdbms/admin/addmrpt.sql
ADDM Reports ansehen
DBMS_ADDM.GET_REPORT function:
DBMS_ADDM.GET_REPORT ( task_name IN VARCHAR2 RETURN CLOB);
SET LONG 1000000 PAGESIZE 0;
SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;
Select
DBMS_ADDM.REAL_TIME_ADDM_REPORT() from dual;
REAL_TIME_ADDM_REPORT
-------------------------------------------------------<report db_version="12.1.0.1.0" inst_count="1" cpu_cores="4"
hyperthread="N" con
Angelika Gallwitz Oracle 12c
Automatic Performance
Diagnostics
Mittwoch, 16. Dezember
2015
39
Angelika Gallwitz Oracle 12c
Automatic Performance
Diagnostics
Mittwoch, 16. Dezember
2015
40
Angelika Gallwitz Oracle 12c
Automatic Performance
Diagnostics
Mittwoch, 16. Dezember
2015
41
3) Load Profile:




Einheit seconds und Transactions per second.
Wichtig um das Verhalten der Instanz zu verstehen
Sollte mit dem Baseline Report verglichen werden
Zum Vergleich mit dem expected load auf der Maschine das Delta zur
schlechten Performance Zeit
Load Profile
Per Second
Per Transaction
---------------
---------------
Redo size:
851,303.02
7,729.46
Logical reads:
146,512.51
1,330.27
Block changes:
5,413.90
49.16
Physical reads:
2,296.44
20.85
Physical writes:
141.63
1.29
User calls:
637.38
5.79
Parses:
403.32
3.66
Hard parses:
11.29
0.10
Sorts:
793.12
7.20
Logons:
3.15
0.03
Executes:
8,291.92
75.29
Transactions:
110.14
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
42




4)Instance Efficiency Percentages (Target 100%):
dient als Indikator
buffer cache hit, library cache hit,parses etc.
diese Werte können hoch oder niedrig sein in Abhängigkeit der Datenbank
Aktivität, muß kein Performance Problem darstellen


. % Blocks changed per Read:

Rollback per transaction %:
3.70
0.73
Recursive Call %:
Rows per Sort:

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
95.81
27.22

Buffer Nowait %:
99.87
Redo NoWait %:
99.98

Buffer
Hit
%:
98.49
In-memory Sort %:
100.00

Library Hit
%:
99.65
Soft Parse %:
97.20

Execute to Parse %:
95.14
Latch Hit %:
99.16

Parse CPU to Parse Elapsd %:
3.59
% Non-Parse CPU:
99.85
Guter Wert
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
44
6) Top 5 Timed Events bis Statspack Oracle 12.1.0.2 :
Wichtigstes Kapitel, zeigt welches Wait Event zu wieviel %
von der DB Zeit gesehen wurde
Top 5 Timed Events
Avg %Total
~~~~~~~~~~~~~~~~~~
Event
Class
Waits
Time (s)
wait
Call
(ms)
Time Wait
---------------------------------------- ----------- ------ ------ ---------db file sequential read
226,973,391
CPU time
1,086,316
5
680,549
35.6
User I/O
22.3
db file scattered read
27,397,666
81,533
3
2.7
User I/O
read by other session
36,385,854
77,520
2
2.5
User I/O
2,453,426
21,920
9
0.7 Concurrenc
latch: cache buffers chains
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
46
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
47

Hier wird der I/O Durchsatz für die Datenbank gezeigt .Die Information im Load Profile Bereich des
AWR Reports sind nicht nachvollziehbare Werte enthalten. Oder untersuchen Sie den Activity Stats
Bereich mit der Sum Total für physical reads und writes (und redo).

Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
48


2) Cache Sizes :
Größe der SGA Region nachdem AMM sie geändert hat
Diese Information kann mit den original init.ora
Parametern am Ende des AWR Reports vgl werden.
Wird mehr shared pool verwendet als buffer cache

Prüfe Bind Variablen ja oder nein
Cache Sizes
Buffer Cache:
Std Block Size:
Shared Pool Size:
Log Buffer:
Begin
End
---------- ---------10,000M
10,000M
16K
3,008M
3,008
276,464K
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
49


Memory sizes wie DB cache size and shared pool
size können auf Probleme hinweisen
Zum Beispiel
◦ shared pool > DB cache size
◦ => Weist auf fehlende Bind Variablen hin
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
50

5) Shared Pool Statistics:
Wie ändert sich der shared pool während der snapshot Periode., 91-93% des Memory wird
vom Shared Pool benutzt.

% SQL with executions>1:Wieviel % der SQL werden mehr als 1 Mal benutzt
Shared Pool Statistics
Memory Usage %:
% SQL with executions>1:
% Memory for SQL w/exec>1:
Begin
End
-----93.75
98.17
95.95
-----91.61
78.35
85.08
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
51
1. OLTP
• Viele kleine Transaktionen, oft hat 1 Query nur 1 Zeile als
• Ergebnis deshalb Effiziente SQL Abfragen
• Read to Write 5:1 bis 10:1
Häufige Benutzung selektiver Indexe
=> Augenmerk auf Redo/Undo und sequential read waits
2. Batch oder Data Warehouse
• Viele Reads DISK I/O Lastig
Wenig Writes (außer Temp wegen Sorts)
Wenig große Transactions
=> Augenmerk auf sort/workarea und scattered read
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
52
Event
Maßnahme
-----------------------------------------------------db file scattered read
db file sequential read
buffer busy waits
free buffer waits
v$sqlarea, v$filestat full table scans
v$sqlarea, v$filestat , Indexhints
block contention v$session
write time os statistik prüfen
log buffer space
log file sync
enqueue waits
library cache,pin,lock
calls
redo buffer allocation retries in v$sysstat
number of transactions(commit+rollbacks) v$sysstat
v$enqueue_stat locks prüfen
v$sqlarea sql statements mit hoher anzahl von parse
child cursors(version_count)
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
53





db file sequential read
Der Server Prozess wartet auf diesen Event nach einer
singleblock I/O Operation
Tritt auf während index unique oder range scans, table access
by rowid , etc
Dieser Event muß nicht unbedingt ein Bottleneck sein, Indexe
werden gelesen ebenso table blocks
Jedoch als Maßnahme kann man die SQL Statements prüfen ,
ob z.B. unnötige „INDEX“ Hints vergeben wurden
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
55




db file scattered read
Wartezeiten im Multiblock Bereich entstehen bei Full table
scan operationen auf Tabellen oder Fast Full Scan Zugriffen
auf Indices
( init.ora Parameter db_file_multiblock_read_count reduziert
I/O Operationen) kann aber auch full tablescans erzwingen
Bsp. db block ist 8k db_file_multiblock_read_count =8
daraus folgt ein single multiblock I/O ist 64K groß dieser
Wert ist abhängig vom Operating System und Storage
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
56

Read By Other Session Wait Event [ID 732891.1]

War früher buffer busy wait




Versuchter Zugriff auf einen buffer im buffer cache , aber der buffer wird gerade von
disk von einem anderen user gelesen, es muß gewartet werden bis der Lesezugriff
beendet ist, bis man wieder darauf zugreifen kann
SELECT p1 "file#", p2 "block#", p3 "class#"
FROM v$session_wait WHERE event = 'read by other session';
Wird hier derselbe block wiederholt angezeigt , so haben wir vermutlich
Einen "hot" block oder object.
SELECT relative_fno, owner, segment_name, segment_type FROM dba_extents
WHERE file_id = &file
AND &block BETWEEN block_id AND block_id + blocks - 1;

Siehe auch NOTE:34405.1 - WAITEVENT: "buffer busy waits" Reference Note
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
57

Könnte auf einen Hot block hinweisen

Grund für diesen Latch kann sein



1. Sequence Number Generation Code um eine Zeile in einer
Tabelle upzudaten
2. Index Leaf Chasing = Viele Prozesse suchen denselben
Index - Applikationsproblem?
Metalink ID 163424.1 how to find the hot block
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
58
9) Wait Class
Welche wait class weist auf Contention hin. Welcher Bereich sollte
untersucht werden network, concurrency, cluster, i/o Application,
configuration etc.
Wait Class
-> s
DB/Inst: OLTP/OLTP
Snaps: 13694-13753
- second
-> cs - centisecond -
100th of a second
-> ms - millisecond -
1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc
Avg
Wait Class
Waits
%Time
Total Wait
wait
Waits
-outs
Time (s)
(ms)
/txn
-------------------- ---------------- ------ ---------------- ------- ---------
User I/O
301,011,884
Concurrency
8,384,040
System I/O
.0
1,259,029
5.2
51,800
16,813,457
.0
326,248
11.7
Administrative
2,028,810
Commit
Configuration
Application
Other
Network
6
0.4
45,012
3
0.7
17,891
55
0.0
.0
9,351
5
0.1
1,541,707
.0
8,518
6
0.1
523,352
.3
7,745
15
0.0
4,374,811
3.3
6,482
1
0.2
137,115,426
.0
2,030
0
5.9
4
12.9
-------------------------------------------------------------
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
59
Haupt wait events in der Datenbank. foreground and background
database wait events as well as time model, operating system, service,
and wait classes statistics.
> s
- second
-> cs - centisecond -
100th of a second-> ms - millisecond -
1000th of a second
-> us - microsecond - 1000000th of a second-> ordered by wait time desc, waits desc (idle events last)
Avg
Event
Waits
%Time
Total Wait
wait
Waits
-outs
Time (s)
(ms)
/txn
---------------------------- -------------- ------ ----------- ------- --------db file sequential read
226,973,391
.0
1,086,316
5
9.7
db file scattered read
27,397,666
.0
81,533
3
1.2
read by other session
36,385,854
.0
77,520
2
1.6
2,453,426
.0
21,920
9
0.1
latch: cache buffers chains
log file parallel write
12,792,937
.0
21,448
2
0.5
log file switch (checkpoint
44,546
19.5
16,044
360
0.0
RMAN backup & recovery I/O
317,245
.0
13,387
42
0.0
buffer busy waits
3,959,062
.1
12,573
3
0.2
Backup: sbtwrite2
2,026,139
.0
8,996
4
0.1
log file sync
1,541,707
.0
8,518
6
0.1
direct path write temp
1,377,867
.0
2,708
2
0.1
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
60





Statspack AWR Beispiel
Reads und Writes beinhalten den größten Teil der Total
database time
Erhöhe RAM: durch Erhöhung der db_cache_size eine
Reduktion des disk I/O
Tune SQL to reduce disk I/O
Faster disk I/O sub-system
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
61
•
SQL Statistics:









SQL
SQL
SQL
SQL
SQL
SQL
SQL
SQL
ordered
ordered
ordered
ordered
ordered
ordered
ordered
ordered
by
by
by
by
by
by
by
by
Elapsed Time
CPU Time
Gets
Reads
Executions
Parse Calls
Sharable Memory
Version Count
Complete List of SQL Text
kommen dieselben Statements im Top Bereich vor, prüfen ob
Statement Tuning nötig ist.
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
62

Um die Kalibrierung mit dem Database Resource Manager nutzen zu können, müssen verschiedene
Vorrausetzungen eingehalten werden:

1. eingesetzte Datenbankversion >=11.1

2. aufrufender Benutzer hat SYSDBA-Privileg

3. asynchrones I/O ist aktiviert (DISC_ASYNCH_IO=TRUE und

4. FILESYSTEMIO_OPTIONS=SETALL)

5. zum Zeitpunkt der Calibration geringe Last auf der Datenbank

Die Datenbank muss zumindest um eine 10er Potenz größer sein wie der CACHE im Storage bzw.
Infrastruktur (zB: IBM SVC,...)

SAS Disken: 150-250 IOPS, 100-200 MBPS, 4-6ms Latency

SATA Disken: 50-150 IOPS, 70-150 MBPS, 8-12ms Latency

SSD: 30.000 - 150.000 IOPS, 250-1200 MBPS, <1ms Latency
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
63

Seit Oracle 11g gibt es im Database Resource Manager die I/O Calibration Utility.

Damit können die Höchstwerte für IOPS und MB/sec ermittelt werden.

DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure

SET SERVEROUTPUT ON

DECLARE
lat INTEGER;
iops INTEGER;


mbps INTEGER;

BEGIN

-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);

DBMS_RESOURCE_MANAGER.CALIBRATE_IO (60, 20, iops, mbps, lat); DBMS_OUTPUT.PUT_LINE
('max_iops = ' || iops); DBMS_OUTPUT.PUT_LINE ('latency = ' || lat); dbms_output.put_line('max_mbps =
' || mbps);

end; /

select * from V$IO_CALIBRATION_STATUS ;
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
64

START_TIME
DISKS
END_TIME

---------------------------- ---------------------------- ---------- ---------- ---------- ---------- ----------

10-DEZ-015 12:55:13

START_TIME
DISKS

---------------------------- ---------------------------- ---------- ---------- ---------- ---------- ----------

10-DEZ-015 13:52:41

START_TIME
DISKS

---------------------------- ---------------------------- ---------- ---------- ---------- ---------- ----------

10-DEZ-015 14:30:19

START_TIME
DISKS

---------------------------- ---------------------------- ---------- ---------- ---------- ---------- ----------

10-DEZ-015 15:22:38
10-DEZ-015 13:23:28
END_TIME
10-DEZ-015 14:23:14
END_TIME
10-DEZ-015 14:57:18
END_TIME
10-DEZ-015 15:36:43
MAX_IOPS MAX_MBPS MAX_PMBPS
2445
125
134
21
MAX_IOPS MAX_MBPS MAX_PMBPS
837
319
343
11
136
60
LATENCY
19
MAX_IOPS MAX_MBPS MAX_PMBPS
1714
760
152
Angelika
Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
60
LATENCY
181
MAX_IOPS MAX_MBPS MAX_PMBPS
3498
LATENCY
60
LATENCY
18
1
Mittwoch, 16. Dezember
2015
65


Metalink

Segment Statistiks id 762526,1

AWR Performance Statisics ID 1359094.1

Der Oracle DBA Hanser Verlag

OCP 11G Oracle Pub
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
66
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
68

Telefon: +49 172 205 8483
 [email protected]
 www.gallwitz-it.de
Angelika Gallwitz
Statistische Auswertungen in
Oracle mit Statspack und AWR
Mittwoch, 16. Dezember
2015
69