· PDF fileReport einzelner SQL Statements über hash_value ... (kurz AWR) eingesetzt. Per...

www.informatik-aktuell.de

Angelika Gallwitz Statistische Auswertungen in Oracle mit

Statspack und AWR 1 Mittwoch, 16. Dezember 2015

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 2

Mittwoch, 16. Dezember 2015

mailto:[email protected]

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 3 Mittwoch, 16. Dezember 2015

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

4


Oracle mit Statspack und AWR Mittwoch, 16. Dezember 2015

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



Oracle mit Statspack und AWR 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] /* ausschalten mit „BASIC“

Control_management_pack_access=diagnostic/diagnostic+tuning /* 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




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




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);




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




• 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 Statspack-Report analysiert werden



• 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



• $ORACLE_HOME/rdbms/admin/sprepins.sql

Genereller Instance Healthcheck

• $ORACLE_HOME/rdbms/admin/sprepsql.sql

Report einzelner SQL Statements über hash_value Bsp:

• SQL> connect perfstat/perfstat

• SQL> execute statspack.snap

• SQL> execute statspack.snap SQL> 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



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');



Kommt mit der Datenbank seit Oracle 10

Liegt im Schema SYS

Im Tablespace SYSAUX

Ist in jeder Oracle Edition enthalten(SE, EE)



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]



Statspack AWR

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

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;



ADDM

Selbst-Tuning

Komponente

Selbst-Tuning

Komponente

Interne Clients

SGA

In Memory Statistik-Sammlung

SGA

Repository der

AWR u. ADDM

Snapshots

MMON Prozess

V$ DBA_ WR.$

Externe Clients OEM SQLPlus

User-Background Prozesse




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 Statistik-Snapshot der Datenbankauslastung

Diese Snapshots werden per Default 8 Tage aufbewahrt

18



1. Komponenten Überblick

2. Ziel der ADDM Analyse

3. Problemtypen des ADDM

4. Der "Real Time ADDM„

5. ADDM Modi

6. ADDM Views




Datenbank Zeit reduzieren

Wartezeit reduzieren

Durchsatz Erhöhung

Ohne Resourcenänderung







ADDM

Analyse

CPU

Bottlenecks

High-load

SQL

Statements

RAC global

cache

interconnect,

Wartezeit

Concurrency

Gleichzeitig

keit

Hot objects

und Top

SQLs

Memory

Strukturen

I/O

Durchsatz

High-load

PL/SQL

Applikation

Datenbank

Parameter

Diagnostic Connection

◦ Real Time

◦ Emergency

Normal Connection




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




Datenbank langsam oder hängt

Anmeldungist möglich

Emergency Monitoring

Normale Connection

View • ASH data • Hang data

Starte Analyse • Hang Analysis Data • I/O Metriken • Host Metriken

View • Analyse Ergebnis • Empfehlung • Hang Data • Top Activity Snapshot

Empfehlungen folgen oder Datenbank Instanz

runterfahren SR an Oracle?

Blocker session?

Kill session

Andere blockers?

System ok?

Ende

Emergency analysis?

Real-Time ADDM

Yes

Yes No

No

No

Yes

Yes

No

In Anlehnung an Oracle Dokumentation

No




1. Trigger

2. Trigger Control

3. Analyse- MODE

4. Diagnose- MODE

5. Real Time ADDM Report




AWR Tabellen gibt es in 3 verschiedenen Bereichen

◦ Metadata (WRM$ …)

◦ Historische Daten (WRH$ …)

◦ AWR Tabellen der Advisory Funktionen (WRI$ …)



AW$AWREPORT AWRRPT_TEXT_TYPE_TABLE DBMS_FEATURE_AWR

AWRRPT_HTML_TYPE_TABLE DBA_HIST_SNAPSHOT DBMS_WORKLOAD_REPOSITORY

AWRRPT_INSTANCE_LIST_TYPE DBMS_AWR_REPORT_LAYOUT

Ä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



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



?/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



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




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



1. Statistiken sammeln von

Betriebssystem

Datenbank

Anwendung

2. Durchsuche die Daten nach Performance-Problemen

3. Erstelle eine Liste der Probleme und Fehler

4. Bilde ein Konzept oder Modell, was passiert ist, anhand der gesammelten Daten

5. Implementiere anhand der Analyse die Änderungen und monitore diese Änderungen

6. Überprüfe ob die Performanceziele erreicht wurden

7. Wiederhole die Schritte



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




Kann generiert werden im EM Express oder mit folgendem Script:




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



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



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 39



Diagnostics 40



Diagnostics 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



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: 3.70 Recursive Call %: 95.81

Rollback per transaction %: 0.73 Rows per Sort: 27.22

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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



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 ~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time Wait

Class

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

db file sequential read 226,973,391 1,086,316 5 35.6 User I/O

CPU time 680,549 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

latch: cache buffers chains 2,453,426 21,920 9 0.7 Concurrenc



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).




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

Begin End ---------- ----------

Buffer Cache: 10,000M 10,000M

Std Block Size: 16K

Shared Pool Size: 3,008M 3,008

Log Buffer: 276,464K



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



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 Begin End ------ ------

Memory Usage %: 93.75 91.61

% SQL with executions>1: 98.17 78.35

% Memory for SQL w/exec>1: 95.95 85.08



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



Event Maßnahme

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

db file scattered read v$sqlarea, v$filestat full table scans

db file sequential read v$sqlarea, v$filestat , Indexhints

buffer busy waits block contention v$session

free buffer waits write time os statistik prüfen

log buffer space redo buffer allocation retries in v$sysstat

log file sync number of transactions(commit+rollbacks) v$sysstat

enqueue waits v$enqueue_stat locks prüfen

library cache,pin,lock v$sqlarea sql statements mit hoher anzahl von parse calls child cursors(version_count)

Angelika Gallwitz



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



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



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



https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=34405.1

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



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 DB/Inst: OLTP/OLTP Snaps: 13694-13753

-> 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

Avg

%Time Total Wait wait Waits

Wait Class Waits -outs Time (s) (ms) /txn

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

User I/O 301,011,884 .0 1,259,029 4 12.9

Concurrency 8,384,040 5.2 51,800 6 0.4

System I/O 16,813,457 .0 45,012 3 0.7

Configuration 326,248 11.7 17,891 55 0.0

Administrative 2,028,810 .0 9,351 5 0.1

Commit 1,541,707 .0 8,518 6 0.1

Application 523,352 .3 7,745 15 0.0

Other 4,374,811 3.3 6,482 1 0.2

Network 137,115,426 .0 2,030 0 5.9

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



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

%Time Total Wait wait Waits

Event 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

latch: cache buffers chains 2,453,426 .0 21,920 9 0.1

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



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



• SQL Statistics: SQL ordered by Elapsed Time

SQL ordered by CPU Time

SQL ordered by Gets

SQL ordered by Reads

SQL ordered by Executions

SQL ordered by Parse Calls

SQL ordered by Sharable Memory

SQL ordered by Version Count

Complete List of SQL Text kommen dieselben Statements im Top Bereich vor, prüfen ob

Statement Tuning nötig ist.



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




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 ;




START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY

DISKS

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

10-DEZ-015 12:55:13 10-DEZ-015 13:23:28 2445 125 134 21 60


DISKS

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

10-DEZ-015 13:52:41 10-DEZ-015 14:23:14 837 319 181 11 60


DISKS

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

10-DEZ-015 14:30:19 10-DEZ-015 14:57:18 3498 343 136 19 60


DISKS

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

10-DEZ-015 15:22:38 10-DEZ-015 15:36:43 1714 760 152 18 1




Metalink

Segment Statistiks id 762526,1

AWR Performance Statisics ID 1359094.1

Der Oracle DBA Hanser Verlag

OCP 11G Oracle Pub




68



Telefon: +49 172 205 8483

[email protected]

www.gallwitz-it.de



· PDF fileReport einzelner SQL Statements über hash_value ... (kurz AWR) eingesetzt. Per...

Documents

Transcript of · PDF fileReport einzelner SQL Statements über hash_value ... (kurz AWR) eingesetzt. Per...