트러블슈팅 오라클 퍼포먼스 2판 (2017년)
진단 팩을 이용하지 않는 분석 - Statspack
STATSPACK report for
>>> 1. 인스턴스 및 서버 정보
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1368679494 DCBIS 1 23-Sep-17 03:54 11.2.0.2.0 NO
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
kdcbdb1a AIX-Based Systems (64- 16 4 0 31.0
>>> 2. 스냅샷 정보
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 21 14-Nov-17 16:52:13 203 3.6
End Snap: 22 14-Nov-17 17:13:08 208 3.5
Elapsed: 20.92 (mins) Av Act Sess: 0.1
DB time: 1.88 (mins) DB CPU: 1.15 (mins)
>>> 3.SGA 구성정보
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 304M Std Block Size: 8K
Shared Pool: 576M Log Buffer: 7,216K
>>> 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: 10,188.7 3,837.6
Logical reads: 2,042.3 769.2
Block changes: 60.1 22.6
Physical reads: 243.6 91.8
Physical writes: 4.6 1.7
User calls: 47.3 17.8
Parses: 32.4 12.2
Hard parses: 19.0 7.2
W/A MB processed: 3.4 1.3
Logons: 0.1 0.1
Executes: 632.0 238.0
Rollbacks: 0.0 0.0
Transactions: 2.7
>>> 4. 일련의 비율 정보
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 94.22 Optimal W/A Exec %: 100.00
Library Hit %: 99.81 Soft Parse %: 41.38
Execute to Parse %: 94.87 Latch Hit %: 99.96
Parse CPU to Parse Elapsd %: 9.46 % Non-Parse CPU: 99.27
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 89.64 90.43
% SQL with executions>1: 83.36 85.64
% Memory for SQL w/exec>1: 71.32 73.61
>>> 5. Top 5 이벤트의 리소스 사용율 프로파일
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 57 77.1
log file parallel write 4,554 6 1 7.9
log file sync 3,267 4 1 5.8
os thread startup 51 3 58 4.0
db file parallel write 474 1 3 2.0
-------------------------------------------------------------
>>> 6. CPU 사용율
Host CPU (CPUs: 16 Cores: 4 Sockets: 0)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.78 1.14 0.75 0.42 98.83 0.07 4.04
Instance CPU
~~~~~~~~~~~~ % Time (seconds)
-------- --------------
Host: Total time (s): 20,104.2
Host: Busy CPU time (s): 235.0
% of time Host is Busy: 1.2
Instance: Total CPU time (s): 71.5
% of Busy CPU used for Instance: 30.4
Instance: Total Database time (s): 132.6
%DB time waiting for CPU (Resource Mgr): 0.0
Virtual Memory Paging
~~~~~~~~~~~~~~~~~~~~~
KB paged out per sec: 117.7
KB paged in per sec: 9.0
>>> 7. 메모리 사용율
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 31,744.0 31,744.0
SGA use (MB): 1,449.5 1,449.5
PGA use (MB): 586.4 591.5
% Host Mem used for SGA+PGA: 6.4 6.4
-------------------------------------------------------------
>>> 8. 시간 모델 통계
Time Model System Stats DB/Inst: DCBIS/DCBIS Snaps: 21-22
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 97.6 86.5
DB CPU 69.0 61.2
parse time elapsed 5.9 5.3
hard parse elapsed time 3.4 3.0
PL/SQL execution elapsed time 1.0 .9
connection management call elapsed 0.9 .8
repeated bind elapsed time 0.9 .8
hard parse (sharing criteria) elaps 0.3 .3
hard parse (bind mismatch) elapsed 0.3 .3
PL/SQL compilation elapsed time 0.1 .1
sequence load elapsed time 0.0 .0
DB time 112.8
background elapsed time 19.9
background cpu time 2.5
-------------------------------------------------------------
Foreground Wait Events DB/Inst: DCBIS/DCBIS Snaps: 21-22
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
(중간 생략)
Wait Event Histogram DB/Inst: DCBIS/DCBIS Snaps: 21-22
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
Disk file operations I/O 671 100.0
LGWR wait for redo copy 51 100.0
SQL*Net more data to clien 56 100.0
buffer busy waits 17 94.1 5.9
control file parallel writ 446 26.0 62.8 7.6 2.7 .9
control file sequential re 109K 100.0 .0
db file parallel read 1 100.0
db file parallel write 474 40.9 40.5 10.3 4.9 1.5 .6 1.3
db file scattered read 689 98.4 1.3 .3
db file sequential read 129K 100.0 .0 .0 .0
direct path read 168 99.4 .6
direct path sync 3 100.0
direct path write 34 100.0
direct path write temp 572 99.5 .2 .3
enq: CR - block range reus 1 100.0
enq: TX - row lock content 40 45.0 35.0 20.0
latch free 2 100.0
latch: In memory undo latc 9 100.0
library cache: mutex X 9 100.0
log file parallel write 4554 53.2 36.6 7.3 2.3 .5 .1
log file sync 3267 52.9 36.1 7.9 2.6 .5 .1
os thread startup 51 100.0
reliable message 1 100.0
DIAG idle wait 2508 100.0
SQL*Net message from clien 43K 70.5 10.8 .9 .5 .2 .1 14.9 2.2
SQL*Net message to client 43K 100.0
SQL*Net more data from cli 135 100.0
Space Manager: slave idle 911 .3 .7 1.9 97.1
Streams AQ: qmn coordinato 96 52.1 1.0 46.9
Streams AQ: qmn slave idle 45 100.0
class slave wait 9 100.0
dispatcher timer 21 100.0
jobq slave wait 2647 .1 .0 .0 99.8
pmon timer 418 100.0
rdbms ipc message 10K 7.5 2.4 13.6 7.3 2.9 .9 27.7 37.7
shared server idle wait 42 100.0
smon timer 11 9.1 9.1 27.3 54.5
-------------------------------------------------------------
SQL ordered by CPU DB/Inst: DCBIS/DCBIS Snaps: 21-22
-> Total DB CPU (s): 69
-> Captured SQL accounts for 120.4% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
22.17 42 0.53 32.1 36.52 1,492,948 648130771
Module: JDBC Thin Client
SELECT * FROM (SELECT A.TABLESPACE_NAME, ROUND(A.TOTAL_BYTES/102
4/1024,1) AS TOTAL_MB, ROUND(NVL(B.FREE_BYTES, 0)/1024/1024,1) A
S FREE_MB, ROUND(NVL(B.FREE_BYTES, 0) * 100 / A.TOTAL_BYTES, 1)
AS FREE_PCT FROM (SELECT TABLESPACE_NAME, SUM(BYTES) AS TOTAL_BY
11.05 126 0.09 16.0 18.28 43,653 1563971278
Module: JDBC Thin Client
BEGIN SP_KCBSCR25_PROFG3(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 )
; END;
8.74 730,540 0.00 12.7 14.16 1,577,293 2926525797
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where
ts#=:1 and file#=:2 and block#=:3
(중간 생략)
-------------------------------------------------------------
SQL ordered by Elapsed time for DB: DCBIS Instance: DCBIS Snaps: 21 -22
-> Total DB Time (s): 113
-> Captured SQL accounts for 129.1% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
36.52 42 0.87 32.4 22.17 93,472 648130771
Module: JDBC Thin Client
SELECT * FROM (SELECT A.TABLESPACE_NAME, ROUND(A.TOTAL_BYTES/102
4/1024,1) AS TOTAL_MB, ROUND(NVL(B.FREE_BYTES, 0)/1024/1024,1) A
S FREE_MB, ROUND(NVL(B.FREE_BYTES, 0) * 100 / A.TOTAL_BYTES, 1)
AS FREE_PCT FROM (SELECT TABLESPACE_NAME, SUM(BYTES) AS TOTAL_BY
18.28 126 0.15 16.2 11.05 1,260 1563971278
Module: JDBC Thin Client
BEGIN SP_KCBSCR25_PROFG3(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 )
; END;
14.16 730,540 0.00 12.6 8.74 7 2926525797
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(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/4349
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.