XVIII KONFERENCJA STOWARZYSZENIA POLSKIEJ GRUPY UŻYTKOWNIKÓW SYSTEMU ORACLE
Oracle Automatic Workload Repository (AWR) 12c
Marcin Przepiórowski
• • • • @pioro
Principal Oracle DBA Geek Blogger RAC Attack Ninja http://oracleprof.blogspot.ie/
• AWR - co to jest • AWR - jak używać
• AWR - przykład • AWR a SQL
@pioro
http://oracleprof.blogspot.ie/
DB Optimizer UTLBSTAT/UTLESTAT
StatsPack
Spotlight SQL scripts
Toad V$ views @pioro
http://oracleprof.blogspot.ie/
DBA badający wolno działająca baze danych
@pioro
http://oracleprof.blogspot.ie/
Automatic Repository Workload John Beresniewicz (JB)
Graham Wood Kyle Hailey @pioro
http://oracleprof.blogspot.ie/
• Automatyczny machanizm zbierania statystyk – „always on” • Dostępny w wersji EE
• Wymaga Diagnostic Pack
@pioro
http://oracleprof.blogspot.ie/
• Pre-konfigurowany przez Oracle
• Może być wykorzystany do analizy obecnego stanu bazy danych jak i trendow historycznych
@pioro
http://oracleprof.blogspot.ie/
MMON
Widoki V$
DBA_HIST_....
SYSAUX WR..........
@pioro
http://oracleprof.blogspot.ie/
AWR - dane • mechanizm zapisu statystyk jest oparty o
snapshoty • V$ zawierają dane od momentu startu instancji • delta / czas – może pokazać więcej
@pioro
http://oracleprof.blogspot.ie/
Niebezpieczeństwo średnich 10 odczytów po 1000 ms 200 odczytów po 5 ms średnia = 9.95 ms na odczyt !!!!
Histogram, mediana, percentyl
@pioro
http://oracleprof.blogspot.ie/
AWR a ASH • V$ACTIVE_SESSION_HISTORY Częstotliwość samplowania 1 sek
• DBA_HIST_ACTIVE_SESS_HISTORY generowany na podstawie V$ - częstotliwość samplowania 10 sek
@pioro
http://oracleprof.blogspot.ie/
Nowe sekcje raportu 12c
• Replication Statistics (Golden Gate XStream) • ADDM Reports
• Nowe widoki i kolumny w każdej wersji @pioro
http://oracleprof.blogspot.ie/
10g SQL> select count(*) from dba_views where view_name like 'DBA_HIST%'; COUNT(*) ---------78
12c SQL> select count(*) from dba_views where view_name like 'DBA_HIST%'; COUNT(*) ---------121
@pioro
http://oracleprof.blogspot.ie/
AWR
- jak używać
@pioro
http://oracleprof.blogspot.ie/
@pioro
http://en.wikipedia.org/wiki/Meadow http://oracleprof.blogspot.ie/
@pioro
http://commons.wikimedia.org/wiki/File:Ant_on_leaf.jpg http://oracleprof.blogspot.ie/
http://commons.wikimedia.org @pioro
http://oracleprof.blogspot.ie/
http://pixabay.com @pioro
http://oracleprof.blogspot.ie/
WORKLOAD REPOSITORY report for
DB Name
DB Id
Instance
Inst Num Startup Time
Release
RAC
------------ ----------- ------------ -------- --------------- ----------- --TESTRAC
171991306 testrac1
Host Name
1 16-Sep-13 10:38 11.2.0.3.0
Platform
YES
CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ---------rac1.localdomain Linux x86 64-bit
Snap Id
1
Snap Time
1
1
2.95
Sessions Curs/Sess
--------- ------------------- -------- --------Begin Snap:
485 16-Sep-13 10:49:36
56
.9
End Snap:
486 16-Sep-13 11:53:47
58
.9
Elapsed:
64.19 (mins)
DB Time:
1.48 (mins)
Cache Sizes
Begin
~~~~~~~~~~~
@pioro
End
---------- ---------Buffer Cache:
1,152M
1,152M
Std Block Size:
8K
Shared Pool Size:
400M
400M
Log Buffer:
6,880K
http://oracleprof.blogspot.ie/
20
@pioro
http://oracleprof.blogspot.ie/
DB TIME • Oracle doc “Database time represents the total time spent in database
calls,
and is an indicator of the total instance workload”
• ASH samples counts = DB Time in seconds (proof in DB Time Oracle Performance Tuning: Theory and Practice by Graham Wood, John Beresniewicz)
@pioro
http://oracleprof.blogspot.ie/
Average Active Sessions AAS = ∆DB Time / Elapsed clock time
Average Active Session using ASH samples
AAS = ∑ ASH samples (∆ t=1s)/ Elapsed clock time
“Average active sessions: the magic metric ?” - John Beresniewicz @pioro
http://oracleprof.blogspot.ie/
AWR AWR
AWR SQL
ASH
ASH sesja / sql
Trace 10046
awrsqrpt.sql awrrpt.sql ashrpt.sql dbms_monitor / event 10046
tkprof vs raw file vs TRCA 3rd party – Mr Trace
@pioro
http://oracleprof.blogspot.ie/
Zbieranie faktów
Lokalizacja problemu Metryki
@pioro
http://oracleprof.blogspot.ie/
@pioro
http://oracleprof.blogspot.ie/
@pioro
http://oracleprof.blogspot.ie/
@pioro
http://oracleprof.blogspot.ie/
WORKLOAD REPOSITORY report for DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- -----------IRIS 1390336933 IRIS 1 10.2.0.4.0 NO hprod Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------Begin Snap: 109710 01-Apr-14 18:00:45 72 6.6 End Snap: 109718 01-Apr-14 22:00:51 67 9.4 Elapsed: 240.10 (mins) DB Time: 4,722.66 (mins)
Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Cache Sizes ~~~~~~~~~~~ Begin End Waits Event Time (s) (ms) Time Wait Class ------------------------------------------------ ------------ ----------- ------ ------ ---------Buffer (checkpoint Cache: 13,056M Std Block Size: log file switch in 13,056M 276,536 185,014 8K 669 65.3 Configurat Shared Pool Size: 2,512M 2,512M Log Buffer: 14,336K buffer busy waits 25,969 24,741 953 8.7 Concurrenc enq: SQ - contention 6,820 19,699 2888 7.0 Configurat Load Profile db file sequential read 2,229,562Per Transaction 18,030 8 6.4 User I/O ~~~~~~~~~~~~ Per Second CPU time 14,209 5.0 ----------------------------Redo size: Logical reads: Block changes: Physical reads: Physical writes: User calls: Parses: Hard parses: Sorts: Logons: Executes: Transactions:
@pioro
1,317,404.09 94,852.50 6,715.29 485.31 126.01 20.49 46.85 2.79 118.90 1.04 3,226.89 29.61
44,496.70 3,203.74 226.82 16.39 4.26 0.69 1.58 0.09 4.02 0.04 108.99
http://oracleprof.blogspot.ie/
WORKLOAD REPOSITORY report for DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- -----------IRIS 1390336933 IRIS 1 10.2.0.4.0 NO hprod Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------Begin Snap: 109692 01-Apr-14 09:00:03 67 6.9 End Snap: 109740 02-Apr-14 09:00:48 68 8.9 Elapsed: 1,440.76 (mins) DB Time: 10,553.04 (mins) Top 5 Timed Events
Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Cache Sizes Event Waits Time (s) (ms) Time Wait Class ~~~~~~~~~~~ Begin End ----------------------------------------- ----------- ------ ------ ------------------- ---------log file switch in 13,056M 276,536 185,014 8K 669 29.2 Configurat Buffer (checkpoint Cache: 13,056M Std Block Size: CPU time Shared Pool Size: 114,473 18.1 2,512M 2,512M Log Buffer: 14,336K db file sequential read 17,330,032 104,546 6 16.5 User I/O Loaddone Profile io 11,144,219 26,295 2 4.2 System I/O ~~~~~~~~~~~~ Per Second Per Transaction buffer busy waits 29,933 24,789 828 3.9 Concurrenc ----------------------------------------------------------------------------------------Redo size: Logical reads: Block changes: Physical reads: Physical writes: User calls: Parses: Hard parses: Sorts: Logons: Executes: Transactions:
@pioro
571,656.32 74,246.98 2,981.94 2,287.05 94.85 37.45 82.39 2.27 219.20 2.44 2,208.62 54.70
10,449.80 1,357.22 54.51 41.81 1.73 0.68 1.51 0.04 4.01 0.04 40.37
http://oracleprof.blogspot.ie/
AWR
a SQL
@pioro
http://oracleprof.blogspot.ie/
DBA_HIST_WR_CONTROL
SQL> col RETENTION format a25 SQL> col SNAP_INTERVAL format a25 SQL> select SNAP_INTERVAL, RETENTION, TOPNSQL from DBA_HIST_WR_CONTROL;
SNAP_INTERVAL
RETENTION
TOPNSQL
------------------------- ------------------------- ---------+00000 01:00:00.0
@pioro
+00008 00:00:00.0
DEFAULT
http://oracleprof.blogspot.ie/
32
DBMS_WORKLOAD_REPOSITORY MODIFY_SNAPSHOT_SETTINGS SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (RETENTION=>30*24*60, INTERVAL=>15);
SQL> select SNAP_INTERVAL, RETENTION, TOPNSQL from DBA_HIST_WR_CONTROL;
SNAP_INTERVAL
RETENTION
TOPNSQL
------------------------- ------------------------- ---------+00000 00:15:00.0 @pioro
+00030 00:00:00.0
DEFAULT http://oracleprof.blogspot.ie/
33
DBMS_WORKLOAD_REPOSITORY ADD_COLORED_SQL SQL> exec DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(SQL_ID=>'xxxxxxxxxxxx x');
SQL> exec DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL(SQL_ID=>'xxxxxxxxx xxxx');
@pioro
http://oracleprof.blogspot.ie/
34
select begin_interval_time, pr/sec "phy read / sec", lr/sec "log read / sec" from ()
BEGIN_INTERVAL_TIME ------------------17-SEP-13 12.00.44
@pioro
phy read / sec log read / sec -------------- -------------1169.87 71716.33
http://oracleprof.blogspot.ie/
35
https://github.com/pioro/ashmasters
load_awr.sql
@pioro
http://oracleprof.blogspot.ie/
36
DBWR checkpoint bytes 800000
700000
600000
500000
400000 DBWR checkpoint bytes 300000
200000
100000
0
@pioro
http://oracleprof.blogspot.ie/
"Anomalies bug me"
@pioro
http://oracleprof.blogspot.ie/
38
select sql_id, round(pct*100,2), nvl("'ON CPU'",0) "CPU“ from () pivot ( sum(round(cnt/totalsum*100,2)) for (wait_class) in (‘..’) order by 2 desc SQL_ID PCT CPU SCHEDULER User I/O ------------- ------ ------ ---------- ---------1vur9dhsf7whh 30.79 30.34 0 .45 0rrrjbqmjhc87 10.79 10.79 0 0 82y81yfy7pu8z 9.66 9.66 0 0 9dqca4uwg467x 6.07 5.84 0 .22 0n2ms1wttb1dh 2.7 .45 0 2.02
@pioro
http://oracleprof.blogspot.ie/
39
SQL_ID PCT CPU SCHEDULER User I/O ------------- ------ ------ ---------- ---------1vur9dhsf7whh 30.79 30.34 0 .45
@pioro
http://oracleprof.blogspot.ie/
https://github.com/pioro/ashmasters
topsql.sql
@pioro
http://oracleprof.blogspot.ie/
41
• Używanie właściwej perspektywy w lokalizowaniu problemu
• Dopasowanie AWR do potrzeb – częstotliwość / retencja
• Szukanie anomalii / trendów za pomocą widoków DBA_HIST @pioro
http://oracleprof.blogspot.ie/
• 3rd party products • Free solution – Simulating-ASH https://github.com/pioro/orasash
• Scripts Snapper, MOATS, TopAAS, OraLatencyMap
@pioro
http://oracleprof.blogspot.ie/
43
Q&A oracleprof.blogspot.com @pioro
http://oracleprof.blogspot.ie/
44
