트러블슈팅 오라클 퍼포먼스 2판 (2017년)
진단 팩을 이용한 분석 - EM(Performance 페이지), AWR
WORKLOAD REPOSITORY report for
>>> 1. 인스턴스 및 서버 정보
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
DCBIS 1368679494 DCBIS 1 23-Sep-17 03:54 11.2.0.2.0 NO
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
kdcbdb1a AIX-Based Systems (64-bit) 16 4 31.00
>>> 2. 스냅샷 정보
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 57618 14-Nov-17 16:00:38 219 3.4
End Snap: 57619 14-Nov-17 17:00:39 208 3.5
Elapsed: 60.02 (mins)
DB Time: 5.35 (mins)
>>> 3. SGA 구성정보
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 304M 304M Std Block Size: 8K
Shared Pool Size: 576M 576M Log Buffer: 7,548K
>>> 3. 메트릭 정보
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 0.1 0.0 0.00 0.00
DB CPU(s): 0.1 0.0 0.00 0.00
Redo size: 7,755.8 2,989.2
Logical reads: 2,021.7 779.2
Block changes: 53.4 20.6
Physical reads: 198.3 76.4
Physical writes: 3.9 1.5
User calls: 51.4 19.8
Parses: 34.0 13.1
Hard parses: 22.2 8.6
W/A MB processed: 3.3 1.3
Logons: 0.1 0.1
Executes: 653.0 251.7
Rollbacks: 0.0 0.0
Transactions: 2.6
>>> 4. 일련의 비율 정보
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 94.35 In-memory Sort %: 100.00
Library Hit %: 99.88 Soft Parse %: 34.78
Execute to Parse %: 94.79 Latch Hit %: 99.96
Parse CPU to Parse Elapsd %: 8.53 % Non-Parse CPU: 99.37
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 88.87 90.30
% SQL with executions>1: 77.57 87.26
% Memory for SQL w/exec>1: 45.71 85.51
>>> 5. Top 5 이벤트의 리소스 사용율 프로파일
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 193 60.2
log file sync 9,230 11 1 3.6 Commit
direct path read 4,755 6 1 1.8 User I/O
db file sequential read 374,373 3 0 .9 User I/O
control file sequential read 172,899 1 0 .2 System I/O
>>> 6. CPU 사용율
Host CPU (CPUs: 16 Cores: 4 Sockets: )
~~~~~~~~ Load Average
Begin End %User %System %WIO %Idle
--------- --------- --------- --------- --------- ---------
0.68 0.88 0.8 0.4 0.1 98.8
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 0.3
% of busy CPU for Instance: 29.4
%DB time waiting for CPU - Resource Mgr: 0.0
>>> 7. 메모리 사용율
Memory Statistics
~~~~~~~~~~~~~~~~~ Begin End
Host Mem (MB): 31,744.0 31,744.0
SGA use (MB): 960.0 960.0
PGA use (MB): 589.6 591.7
% Host Mem used for SGA+PGA: 4.88 4.89
>>> 8. 시간 모델 통계
Time Model Statistics DB/Inst: DCBIS/DCBIS Snaps: 57618-57619
-> Total time in database user-calls (DB Time): 320.8s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 274.0 85.4
DB CPU 193.2 60.2
parse time elapsed 20.1 6.3
hard parse elapsed time 12.0 3.7
PL/SQL execution elapsed time 2.9 .9
connection management call elapsed time 2.7 .9
repeated bind elapsed time 2.5 .8
hard parse (sharing criteria) elapsed time 0.9 .3
hard parse (bind mismatch) elapsed time 0.9 .3
failed parse elapsed time 0.2 .0
PL/SQL compilation elapsed time 0.1 .0
sequence load elapsed time 0.1 .0
DB time 320.8
background elapsed time 51.0
background cpu time 5.7
-------------------------------------------------------------
(중간 생략)
Wait Event Histogram DB/Inst: DCBIS/DCBIS Snaps: 57618-57619
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> % of Waits: value of .0 indicates value was <.05%; value of null is truly 0
-> % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
-> Ordered by Event (idle events last)
% of Waits
-----------------------------------------------
Total
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
ADR block file read 16 100.0
ADR block file write 5 60.0 40.0
ADR file lock 6 100.0
Disk file operations I/O 1224 100.0
LGWR wait for redo copy 115 100.0
SQL*Net break/reset to cli 46 73.9 19.6 2.2 4.3
SQL*Net message to client 134.3 100.0
SQL*Net more data from cli 406 100.0
SQL*Net more data to clien 659 97.3 .2 .5 .3 1.4 .3 .2
buffer busy waits 52 100.0
control file parallel writ 1280 15.9 72.0 7.7 3.3 1.1
control file sequential re 177.1 100.0
cursor: pin S 1 100.0
db file parallel read 3 100.0
db file parallel write 1236 42.6 40.0 11.6 4.5 .6 .2 .6
db file scattered read 1293 100.0
db file sequential read 376.4 100.0 .0 .0 .0
direct path read 4755 75.0 12.4 7.0 4.5 .8 .3 .1
direct path sync 11 63.6 18.2 18.2
direct path write 72 100.0
direct path write temp 1652 99.3 .2 .4 .1
enq: CR - block range reus 5 20.0 80.0
enq: TX - row lock content 118 32.2 33.9 25.4 6.8 1.7
latch free 5 80.0 20.0
latch: In memory undo latc 18 100.0
(중간 생략)
SQL ordered by Elapsed Time DB/Inst: DCBIS/DCBIS Snaps: 57618-57619
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
-> %Total - Elapsed Time as a percentage of Total DB time
-> %CPU - CPU Time as a percentage of Elapsed Time
-> %IO - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for 89.8% of Total DB Time (s): 321
-> Captured PL/SQL account for 23.5% of Total DB Time (s): 321
Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
104.3 120 0.87 32.5 60.6 1.7 968q4f33a89pd
Module: JDBC Thin Client
SELECT * FROM (SELECT A.TABLESPACE_NAME, ROUND(A.TOTAL_BYTES/1024/1024,1) AS TOT
AL_MB, ROUND(NVL(B.FREE_BYTES, 0)/1024/1024,1) AS FREE_MB, ROUND(NVL(B.FREE_BYTE
S, 0) * 100 / A.TOTAL_BYTES, 1) AS FREE_PCT FROM (SELECT TABLESPACE_NAME, SUM(BY
TES) AS TOTAL_BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, (SELECT TAB
52.3 359 0.15 16.3 60.0 .1 5tftvawtbsjjx
Module: JDBC Thin Client
BEGIN SP_KCBSCR25_PROFG3(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ) ; END;
41.9 2,163,921 0.00 13.1 60.8 .0 9tgj4g8y4rwy8
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis
ts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL
(bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
(중간 생략)
SQL ordered by CPU Time DB/Inst: DCBIS/DCBIS Snaps: 57618-57619
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> %Total - CPU Time as a percentage of Total DB CPU
-> %CPU - CPU Time as a percentage of Elapsed Time
-> %IO - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for 85.9% of Total CPU Time (s): 193
-> Captured PL/SQL account for 21.1% of Total CPU Time (s): 193
CPU CPU per Elapsed
Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id
---------- ------------ ---------- ------ ---------- ------ ------ -------------
63.2 120 0.53 32.7 104.3 60.6 1.7 968q4f33a89pd
Module: JDBC Thin Client
SELECT * FROM (SELECT A.TABLESPACE_NAME, ROUND(A.TOTAL_BYTES/1024/1024,1) AS TOT
AL_MB, ROUND(NVL(B.FREE_BYTES, 0)/1024/1024,1) AS FREE_MB, ROUND(NVL(B.FREE_BYTE
S, 0) * 100 / A.TOTAL_BYTES, 1) AS FREE_PCT FROM (SELECT TABLESPACE_NAME, SUM(BY
TES) AS TOTAL_BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, (SELECT TAB
31.4 359 0.09 16.2 52.3 60.0 .1 5tftvawtbsjjx
Module: JDBC Thin Client
BEGIN SP_KCBSCR25_PROFG3(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ) ; END;
25.5 2,163,921 0.00 13.2 41.9 60.8 .0 9tgj4g8y4rwy8
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis
ts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL
(bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
(이하 생략)
"데이터베이스 스터디모임" 에서 2017년에 "전문가를 위한 트러블슈팅 오라클 퍼포먼스(Second Edition)
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4348
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.