오라클 성능 트러블슈팅의 기초 (2012년)
AWR(Automatic Workload Repository)
- 과거 특정 구간의 성능 문제를 가장 종합적으로 분석할 수 있는 툴
- 특정 시점의 DB 주요 성능 지표인 AWR 스냅샷(1시간에 한번)을 기반하여 리포트 제공
- 오라클이 기본적으로 제공하는 세가지 AWR 관련 리포트
- AWR 리포트
- AWR Diff 리포트
- AWR SQL 리포트
AWR 리포트
- 두 스냅샷간의 차이 값을 리포트
- 단일 데이터베이스에서 특정 구간의 성능 문제를 분석하기 위한 용도로 사용
- 오라클에서 추출할 수 있는 가장 상세한 데이터 제공
AWR을 사용하는 간단한 예제
***** 테이블 생성후 1 row 추가한다.
SQL> create table t1(c1 number);
SQL> insert into t1 values(1);
SQL> commit;
***** TEMP.SQL을 작성한다.
begin
for idx in 1..100 loop
update t1 set c1 = 1;
dbms_lock.sleep(0.1);
commit;
for r in (select * from user_objects) loop
null;
end loop;
end loop;
end;
/
***** 첫번째 스냅샷을 생성한다.
col begin_snap new_value begin_snap;
col db_id new_value db_id;
col inst_num new_value inst_num;
select dbid as db_id from v$database;
DB_ID
----------
3588319577
select instance_number as inst_num from v$instance;
INST_NUM
----------
1
select dbms_workload_repository.create_snapshot as begin_snap from dual;
BEGIN_SNAP
----------
14739
***** 5개의 세션에서 동시에 TEMP.SQL 파일을 실행한다.
***** 5개의 세션에서 작업이 끝난 후 두번째 스냅샷을 생성한다.
col end_snap new_value end_snap;
select dbms_workload_repository.create_snapshot as end_snap from dual;
END_SNAP
----------
14740
***** DBMS_WORKLAD_REPOSITORY.AWR_REPORT_TEXT 함수를 이용해 첫번째 스냅샷(14739)과 두번째 스냅샷(14740)간의 차이에 대한 리포트를 만든다.
select * from table (
dbms_workload_repository.awr_report_text (
&db_id,
&inst_num,
&begin_snap,
&end_snap)
);
OR
@?/rdbms/admin/awrrpt 스크립트 이용
Report Summary
- 리포트를 생성한 구간(스냅샷)에 대한 정보
- AWR 스냅샷 생성 주기는 디폴트 1시간이나 시스템의 성능이 허락한다면 30분/15분이 적당
- Elapsed: 인스턴스 차원에서 실제로 흐른 시간
- DB Time: 각 세션들이 실제로 작업을 수행한 시간(세션수에 비례해서 증가)
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
ELEVEN 3588319577 ELEVEN 1 28-Feb-11 17:52 11.2.0.1.0 NO
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
oranewdb Linux x86 64-bit 8 8 2 7.79
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 14739 14-Mar-12 15:22:14 25 1.4
End Snap: 14740 14-Mar-12 15:26:02 27 1.4
Elapsed: 3.79 (mins)
DB Time: 3.38 (mins)
- 구간 내에서의 전반적인 성능 지표(통계) 제공
- CPU 사용시간, Redo 생성량, Logical Reads, Physical Reads/Writes, Parse, Executes등 가장 기본적이고 중요한 데이터
- 여러가지 Ratio 정보들과 Shared Pool 사용 비율도 제공
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 448M 448M Std Block Size: 8K
Shared Pool Size: 784M 784M Log Buffer: 4,848K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 0.9 0.4 0.09 8.45
DB CPU(s): 0.7 0.3 0.07 6.71
Redo size: 7,700.2 3,505.7
Logical reads: 3,386.3 1,541.6
Block changes: 24.1 11.0
Physical reads: 0.2 0.1
Physical writes: 6.9 3.2
User calls: 0.1 0.1
Parses: 3.2 1.5
Hard parses: 0.3 0.2
W/A MB processed: 2.6 1.2
Logons: 0.0 0.0
Executes: 10.1 4.6
Rollbacks: 0.0 0.0
Transactions: 2.2
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 92.99 Soft Parse %: 89.97
Execute to Parse %: 67.79 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 68.75 % Non-Parse CPU: 99.93
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 78.22 78.42
% SQL with executions>1: 91.16 94.41
% Memory for SQL w/exec>1: 80.79 91.74
- Top 5 대기 이벤트
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 161 79.4
enq: TX - row lock contention 480 41 86 20.4 Applicatio
Disk file operations I/O 117 0 2 .1 User I/O
db file sequential read 164 0 1 .1 User I/O
log file sync 2 0 15 .0 Commit
- 오라클 데이터베이스의 성능을 한눈에 알 수 있는 타임 모델
- 전체 DB 시간(DB time, 202.7초)중, SQL을 실행(sql execute elapsed time, 202.6초)하는데 대부분의 시간을 보냄
- 각 항목의 상관 관계를 통해 값의 의미 파악(책 참고)
Time Model Statistics DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> Total time in database user-calls (DB Time): 202.7s
-> 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 202.6 100.0
DB CPU 160.9 79.4
PL/SQL execution elapsed time 2.5 1.2
parse time elapsed 0.3 .2
hard parse elapsed time 0.3 .1
hard parse (sharing criteria) elapsed time 0.3 .1
PL/SQL compilation elapsed time 0.0 .0
connection management call elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0
DB time 202.7
background elapsed time 19.7
background cpu time 0.2
-------------------------------------------------------------
- OS 레벨의 성능 지표 제공
- SAR 같은 외부 툴을 이용하지 않아도 OS의 전반적인 상태를 알 수 있다.
Operating System Statistics DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Statistic Value End Value
------------------------- ---------------------- ----------------
BUSY_TIME 16,542
IDLE_TIME 165,157
IOWAIT_TIME 2,884
NICE_TIME 0
SYS_TIME 173
USER_TIME 16,359
LOAD 0 1
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 8,360,480,768
NUM_CPUS 8
NUM_CPU_CORES 8
NUM_CPU_SOCKETS 2
Operating System Statistics - DetailDB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Snap Time Load %busy %user %sys %idle %iowait
--------------- -------- -------- -------- -------- -------- --------
14-Mar 15:22:14 0.2 N/A N/A N/A N/A N/A
14-Mar 15:26:02 1.4 9.1 9.0 0.1 90.9 1.6
-------------------------------------------------------------
Wait Event Statistics
- 대기 클래스 정보
- 대기 클래스: 개별 대기 이벤트가 어떤 "종류"에 속하는가를 의미
- enq: TX - row lock contention 대기 이벤트는 Applicattion 클래스에 속함
- db file sequential read 대기 이벤트는 User I/O 클래스에 속함
- 아래에서는 Application이 20.4%로 높은 비율을 차지하고 있다.
Foreground Wait Class DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Avg
%Time Total Wait wait
Wait Class Waits -outs Time (s) (ms) %DB time
-------------------- ---------------- ----- ---------------- -------- ---------
DB CPU 161 79.4
Application 480 0 41 86 20.4
User I/O 288 0 0 1 0.2
Commit 2 0 0 15 0.0
System I/O 867 0 0 0 0.0
Other 294 100 0 0 0.0
Concurrency 8 0 0 0 0.0
Network 19 0 0 0 0.0
-------------------------------------------------------------
- 모든 개별 대기 이벤트에 대한 상세한 정보 제공
Foreground Wait Events DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
enq: TX - row lock content 480 0 41 86 1.0 20.4
Disk file operations I/O 117 0 0 2 0.2 .1
db file sequential read 164 0 0 1 0.3 .1
log file sync 2 0 0 15 0.0 .0
direct path sync 1 0 0 23 0.0 .0
...
-------------------------------------------------------------
Background Wait Events DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Avg
%Time Total Wait wait Waits % bg
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
db file async I/O submit 193 0 12 61 0.4 59.7
log file parallel write 1,054 0 3 3 2.1 15.5
...
- 대기 이벤트 히스토그램 정보
- 대기 시간을 구간별로 나누어서 각 구간에 해당하는 대기가 몇 회 발생했는지에 대한 정보
- 0~1ms 사이의 대기를 1000회하는 것보다 1s의 대기를 1회하는 것이 시스템에 더 치명적일 수 있기 때문에 대기 이벤트 시간 분포 패턴이 중요
- 아래 정보를 보면 대부분 1초 미만의 대기 시간의 패턴을 보이고 있기 때문에 특정 대기 이벤트가 지나치게 오래 대기하는 현상은 없는 것으로 판단
- Wait Event Histogram Detail을 제공하여 64ms 이상 1시간 이하의 시간을 대기하는 대기 이벤트에 대해 상세한 대기 이벤트 히스토그램을 제공
- 만약 대기가 지나치게 오래 지속되면(가령 2초) Hang Analyze와 같은 트러블슈팅 툴을 이용해서 좀 더 깊이 있는 분석 필요
Wait Event Histogram DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
% of Waits
-----------------------------------------------
Total
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
Disk file operations I/O 6 100.0
LGWR wait for redo copy 4 100.0
SQL*Net message to client 29 100.0
asynch descriptor resize 319 100.0
buffer busy waits 8 100.0
control file parallel writ 114 2.6 87.7 9.6
control file sequential re 1144 100.0
db file async I/O submit 193 2.1 1.6 6.2 90.2
db file sequential read 269 92.2 3.0 4.5 .4
db file single write 114 2.6 76.3 21.1
direct path sync 1 100.0
direct path write 6 100.0
enq: TX - row lock content 480 .6 .6 .2 1.0 .6 96.9
log file parallel write 1054 50.0 .3 12.0 31.8 5.4 .3 .3
log file sync 2 100.0
....
Wait Event Histogram Detail (64 msec to 2 sec)DB/Inst: ELEVEN/ELEVEN Snaps:
Event to 2s <32ms <64ms <1/8s <1/4s <1/2s <1s <2s >=2s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
control file parallel writ 11 90.4 8.8 .9
db file async I/O submit 174 9.8 48.2 41.5 .5
enq: TX - row lock content 465 3.1 .6 96.3
log file parallel write 3 99.7 .2 .1
-------------------------------------------------------------
Wait Event Histogram Detail (4 sec to 2 min)DB/Inst: ELEVEN/ELEVEN Snaps: 14
No data exists for this section of the report.
-------------------------------------------------------------
....
Top SQL Summary
- Elapsed Time, CPU Time, Logical Reads, Executions등 다양한 관점에 대해 Top SQL 추출
- 오라클은 수집기준(대략 15개) 별로 최대 30개의 Top SQL만을 AWR에 저장(변경도 가능하지만 그만큼 부하)
Colored SQL
- DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL 프로시저를 이용해 특정 SQL을 색칠해 두면 해당 SQL은 비록 Top SQL에 속하지 않더라도 항상 AWR에 저장
- 특정 SQL을 항상 추적하고 싶을 경우 유용한 기능
SQL ordered by Elapsed Time DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
201.0 5 40.19 99.1 79.3 .0 gx6mfa8zvs1n1
Module: sqlplus@oranewdb (TNS V1-V3)
begin for idx in 1..100 loop update t1 set c1 = 1; dbms_lock.sleep(0.1
); commit; for r in (select * from user_objects) loop null; e
nd loop; end loop; end;
155.1 500 0.31 76.5 99.8 .0 7dazh7hup4yq3
Module: sqlplus@oranewdb (TNS V1-V3)
SELECT * FROM USER_OBJECTS
.....
SQL ordered by CPU Time DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
SQL ordered by User I/O Wait Time DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
SQL ordered by Gets DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
SQL ordered by Reads DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
SQL ordered by Physical Reads (UnOptimized)DB/Inst: ELEVEN/ELEVEN Snaps: 147
SQL ordered by Executions DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
SQL ordered by Parse Calls DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Instance Activity Statistics
- 성능 통계 값이 제공
Instance Activity Stats DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> Ordered by statistic name
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
....
buffer is not pinned count 141,402 621.2 282.8
buffer is pinned count 16,230,862 71,302.8 32,461.7
bytes received via SQL*Net from 2,756 12.1 5.5
bytes sent via SQL*Net to client 6,516 28.6 13.0
calls to get snapshot scn: kcmgs 2,851 12.5 5.7
calls to kcmgas 1,358 6.0 2.7
calls to kcmgcs 5,013 22.0 10.0
cell physical IO interconnect by 42,452,480 186,495.3 84,905.0
....
user calls 24 0.1 0.1
session logical reads 770,822 3,386.3 1,541.6
redo size 1,752,824 7,700.2 3,505.7
physical reads 43 0.2 0.1
Instance Activity Stats - Absolute ValuesDB/Inst: ELEVEN/ELEVEN Snaps: 14739
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
-------------------------------- --------------- ---------------
opened cursors current 36 38
logons current 25 27
session uga memory max 6.705085227E+11 6.705168584E+11
session pga memory 2.529616695E+11 2.529617689E+11
session pga memory max 5.829489299E+11 5.829629230E+11
session cursor cache count 4,650,564 4,650,601
session uga memory 8.301075209E+14 8.301075220E+14
-------------------------------------------------------------
Instance Activity Stats - Thread ActivityDB/Inst: ELEVEN/ELEVEN Snaps: 14739-
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
-------------------------------- ------------------ ---------
log switches (derived) 0 .00
-------------------------------------------------------------
IO Statistics
- 디스크 I/O는 오라클의 모든 오퍼레이션들 중 가장 느리므로 패턴 분석이 중요
- IO 함수 유형, 파일 유형, 테이블스페이스별로 I/O 성능 데이터를 제공
IOStat by Function summary DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Reads: Reqs Data Writes: Reqs Data Waits: Avg
Function Name Data per sec per sec Data per sec per sec Count Tm(ms)
--------------- ------- ------- ------- ------- ------- ------- ------- -------
Others 19M 6.0 .083467 6M 2.0 .026358 1719 4.2
DBWR 0M 0.0 0M 12M 5.7 .052716 193 60.5
LGWR 0M 0.0 0M 2M 2.3 .008786 527 5.3
Buffer Cache Re 0M 0.2 0M 0M 0.0 0M 37 1.9
Direct Writes 0M 0.0 0M 0M 0.0 0M 6 0.0
TOTAL: 19M 6.2 .083467 20M 10.0 .087860 2482 8.8
-------------------------------------------------------------
IOStat by Filetype summary DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Reads: Reqs Data Writes: Reqs Data Small Large
Filetype Name Data per sec per sec Data per sec per sec Read Read
--------------- ------- ------- ------- ------- ------- ------- ------- -------
Control File 18M 5.0 .079074 6M 1.5 .026358 0.0 N/A
Data File 2M 1.2 .008786 21M 6.2 .092253 0.3 N/A
Log File 0M 0.0 0M 2M 2.3 .008786 N/A N/A
TOTAL: 20M 6.2 .087860 29M 10.0 .127398 0.1 N/A
-------------------------------------------------------------
IOStat by Function/Filetype summary DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Reads: Reqs Data Writes: Reqs Data Waits: Avg
Data per sec per sec Data per sec per sec Count Tm(ms)
------- ------- ------- ------- ------- ------- ------- -------
Others
19M 6.0 .083467 6M 2.0 .026358 1491 1.0
Others (Control File)
18M 5.0 .079074 5M 1.5 .021965 1144 0.0
Others (Data File)
1M 1.0 .004393 1M 0.5 .004393 347 4.3
DBWR
0M 0.0 0M 12M 5.7 .052716 0 N/A
DBWR (Data File)
0M 0.0 0M 12M 5.7 .052716 0 N/A
LGWR
0M 0.0 0M 2M 2.3 .008786 0 N/A
LGWR (Log File)
0M 0.0 0M 2M 2.3 .008786 0 N/A
Direct Writes
0M 0.0 0M 0M 0.0 0M 0 N/A
Direct Writes (Data File)
0M 0.0 0M 0M 0.0 0M 0 N/A
Buffer Cache Reads
0M 0.2 0M 0M 0.0 0M 37 1.9
Buffer Cache Reads (Data File)
0M 0.2 0M 0M 0.0 0M 37 1.9
TOTAL:
19M 6.2 .083467 20M 10.0 .087860 1528 1.0
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------- ------- ------------ -------- ---------- -------
KIDS2_DATA
0 0 0.0 .0 1,282 6 0 0.0
SYSAUX
41 0 2.0 1.0 6 0 0 0.0
UNDOTBS1
0 0 0.0 .0 13 0 0 0.0
SYSTEM
5 0 2.0 1.0 2 0 8 1.3
-------------------------------------------------------------
File IO Stats DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------- ------- ------------ -------- ---------- -------
KIDS2_DATA /data03/oradata/ELEVEN/kids2_data01.dbf
0 0 N/A N/A 258 1 0 0.0
KIDS2_DATA /data03/oradata/ELEVEN/kids2_data02.dbf
0 0 N/A N/A 316 1 0 0.0
KIDS2_DATA /data03/oradata/ELEVEN/kids2_data03.dbf
0 0 N/A N/A 391 2 0 0.0
KIDS2_DATA /data03/oradata/ELEVEN/kids2_data04.dbf
0 0 N/A N/A 317 1 0 0.0
SYSAUX /data01/oradata/ELEVEN/sysaux01.dbf
41 0 2.0 1.0 6 0 0 0.0
SYSTEM /data01/oradata/ELEVEN/system01.dbf
5 0 2.0 1.0 2 0 8 1.3
UNDOTBS1 /data01/oradata/ELEVEN/undotbs01.dbf
0 0 N/A N/A 13 0 0 0.0
-------------------------------------------------------------
Advisory Statistics
- SGA Advisory
- SGA의 효율성을 직관적으로 알 수 있도록 도와준다.
- Buffer Pool, Shared Pool, Streams Pool, Java Pool, SGA Target 등에 대한 어드바이저리 정보를 제공하며 이정보를 이용해 각 메모리 영역의 크기를 조정하는 경우 성능에 어떤 변화가 있을지 추측 가능
- 아래 데이터에서 버퍼풀의 크기를 현재 469M에서 640M로 변경해도 Physical Reads에는 그다지 큰 이득이 없음을 알 수 있다.
- 하지만 오라클의 내부 룰에 의한 예상일 뿐 보장할 수는 없다.
Buffer Pool Advisory DB/Inst: ELEVEN/ELEVEN Snap: 14740
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys %DBtime
P Est (M) Factor (thousands) Factor (thousands) Read Time for Rds
--- -------- ------ ------------ ------ -------------- ------------ -------
D 448 1.0 55 1.0 145,989 1 5.3E+04
D 480 1.1 59 1.0 144,799 1 5.1E+04
D 512 1.1 63 1.0 143,851 1 5.0E+04
D 544 1.2 67 1.0 142,927 1 4.9E+04
D 576 1.3 71 1.0 142,025 1 4.7E+04
D 608 1.4 75 1.0 141,095 1 4.6E+04
D 640 1.4 79 1.0 140,124 1 4.5E+04
Shared Pool Advisory DB/Inst: ELEVEN/ELEVEN Snap: 14740
Est LC Est LC Est LC Est LC
Shared SP Est LC Time Time Load Load Est LC
Pool Size Size Est LC Saved Saved Time Time Mem Obj
Size(M) Factr (M) Mem Obj (s) Factr (s) Factr Hits (K)
-------- ----- -------- ------------ -------- ------ ------- ------ ------------
544 .7 26 1,127 395,539 .9 42,177 2.8 18,917
624 .8 107 4,205 399,224 .9 38,492 2.6 58,071
704 .9 186 6,908 410,782 1.0 26,934 1.8 59,403
784 1.0 267 9,998 422,764 1.0 14,952 1.0 60,704
864 1.1 348 12,785 432,060 1.0 5,656 .4 61,750
SGA Target Advisory DB/Inst: ELEVEN/ELEVEN Snap: 14740
SGA Target SGA Size Est DB Est Physical
Size (M) Factor Time (s) Reads
---------- ---------- ------------ ----------------
672 0.5 1,037,899 540,832,598
1,008 0.8 495,523 145,989,472
1,344 1.0 458,436 145,989,472
1,680 1.3 443,904 139,332,352
2,016 1.5 442,207 138,368,822
- PGA 성능정보
- 서버 프로세스에 의한 정렬이나 해시 조인과 같은 작업의 성능 분석시 사용
- 시작/종료 시점에 따른 PGA 크기 변화 정보 제공
- Optimal 소트, One Pass 소트, Multi Pass 소트의 사용 빈도 제공
Optimal 소트: PGA의 작업 영역내에서 정렬이 완료
One Pass 소트: 디스크를 통해 1회의 병합을 거친 후 정렬이 완료
Multi Pass 소트: 디스크를 통해 다단계로 수 차례의 병합을 거친 후 정렬이 완료
- PGA 크기에 대해서도 어드바이저리 정보가 제공(PGA의 크기를 704M->352M로 감소시키면 대략 190M의 디스크 I/O 발생, 오라클 내부룰에 의한 계산이며 실제로 그렇게 된다는 보장은 없다.)
PGA Aggr Summary DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ------------------ --------------------------
100.0 596 0
-------------------------------------------------------------
PGA Aggr Target Stats DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
PGA Aggr Auto PGA PGA Mem W/A PGA %PGA W/A %Auto W/A %Man W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- ----------- ---------- --------- -------- ---------- ---------- -------- ------------
B 488 410 39.7 0.0 .0 .0 .0 99,942
E 488 396 57.2 0.0 .0 .0 .0 99,942
------------------------------------------------------------
PGA Aggr Target Histogram DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- -------------- ------------ ------------
2K 4K 105 105 0 0
64K 128K 6 6 0 0
512K 1024K 24 24 0 0
1M 2M 504 504 0 0
4M 8M 2 2 0 0
-------------------------------------------------------------
PGA Memory Advisory DB/Inst: ELEVEN/ELEVEN Snap: 14740
PGA Target Size W/A MB W/A MB Read/ Cache Overallo Estd
Est (MB) Factr Processed Written to Disk Hit % Count Time
---------- ------- ---------------- ---------------- ------ -------- -------
88 0.1 1,189,011.0 563,408.7 68.0 535 1.5E+10
176 0.3 1,189,011.0 470,950.8 72.0 52 1.4E+10
352 0.5 1,189,011.0 260,464.2 82.0 0 1.2E+10
528 0.8 1,189,011.0 111,743.9 91.0 0 1.1E+10
704 1.0 1,189,011.0 70,549.6 94.0 0 1.1E+10
....
Latch & Mutex Statistics
- 래치 활동 정보
- 오라클이 필요한 자원을 획득하기 위해 내부적으로 어떤 작업을 하는지 알 수 있는 중요한 정보 제공(버퍼, SQL커서, 딕셔너리 오브젝트등)
- 아래 데이터는 Cache Buffers chains Latch에 대한 획득이 가장 빈번하며 이는 버퍼캐시에서 버퍼를 액세스하기 위해 획득해야 하는 래치이므로 버퍼에 대한 액세스가 왕성한 시스템이라는 것을 알 수 있다.
Latch Activity DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Name Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
....
cache buffer handles 48 0.0 0 0 N/A
cache buffers chains 1,545,866 0.0 0.0 0 181 0.0
cache buffers lru chain 1,680 0.0 0 2,704 0.0
call allocation 246 0.0 0 0 N/A
cas latch 1 0.0 0 0 N/A
change notification clie 1 0.0 0 0 N/A
channel handle pool latc 5 0.0 0 0 N/A
channel operations paren 1,062 0.0 0 0 N/A
checkpoint queue latch 19,515 0.0 0 461 0.0
....
- Latch Sleep Breakdown(래치 미스에 대한 정보)
- Latch Miss: 래치 획득에 실패(래치 미스가 많이 발생한다는 것은 그만큼 동일한 자원에 대한 경쟁이 심하다는 의미)
- Latch Miss Sources
- 오라클 커널에서 구체적으로 어떤 오퍼레이션을 수행하다가 래치 미스가 발생했는지 확인
- WHERE 항목: 오라클 커널 함수 이름(5장 콜 트리 분석에서 다룰 예정 혹은 메타 링크에서 확인)
- 뮤텍스 정보
- 오라클 최신버전에서는 SQL 커서나 라이브러리 캐시를 탐색하는 과정에서 더이상 래치가 아닌 뮤텍스를 사용
Top Segment Statistics
- 시스템에서 가장 빈번하게 사용되며 가장 많은 문제를 일으키는 세그먼트를 분석
- 예를 들어 특정 세그먼트가 Table Full Scan 방식으로 주로 읽히는데 Physical Reads가 지나치게 많이 발생한다면 해당 세그먼트를 KEEP 버퍼 풀에 위치시켜 줄이는 방법
Segments by Logical Reads DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Tablespace Subobject Obj. Logical
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS SYSTEM OBJ$ TABLE 595,216 77.22
SYS SYSTEM IND$ TABLE 120,800 15.67
SYS SYSTEM I_IND1 INDEX 38,752 5.03
SYS SYSAUX WRH$_SEG_STAT_OBJ_PK INDEX 2,864 .37
SYS SYSTEM T1 TABLE 2,256 .29
-------------------------------------------------------------
Segments by Physical Reads DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Segments by Physical Read Requests DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Segments by UnOptimized Reads DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Segments by Physical Writes DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Segments by Table Scans DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Segments by DB Blocks Changes DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Segments by Row Lock Waits DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
....
Dictionary Cache Statistics
- 어떤 유형의 딕셔너리 오브젝트를(테이블, 인덱스, 히스토그램, 세그먼트, 유저등) 가장 많이 액세스했는지 알 수 있다.
- 가량 오라클의 버그 등으로 인해 특정 딕셔너리 오브젝트에 대해 지나치게 빈번하게 액세스하는 현상 시 유용하게 사용되는 정보
Dictionary Cache Stats DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> "Pct Misses" should be very low (< 2% in most cases)
-> "Final Usage" is the number of cache entries being used
Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_awr_control 8 0.0 0 N/A 2 1
dc_global_oids 7 0.0 0 N/A 0 31
dc_histogram_data 514 0.0 0 N/A 0 4,728
dc_histogram_defs 2,020 12.4 0 N/A 0 3,435
dc_objects 656 0.3 0 N/A 0 1,685
dc_rollback_segments 45 0.0 0 N/A 0 23
dc_segments 312 6.1 0 N/A 7 894
dc_tablespaces 1,392 0.0 0 N/A 0 16
dc_users 1,259 0.0 0 N/A 0 158
global database name 155 0.0 0 N/A 0 1
-------------------------------------------------------------
Library cache Statistics
- 하나의 SQL 문장을 수행하기 위해서 많은 유형의 라이브러리 캐시 오브젝트를 액세스하는데 이 패턴을 분석하여 라이브러리 캐시 성능 문제를 분석
- LCO의 유형별로 액세스 회수, mISS 회수, Invalidation 회수 정보 제공
Library Cache Activity DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> "Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 9 0.0 12 0.0 0 0
CLUSTER 2 0.0 1 0.0 0 0
DBLINK 3 0.0 0 N/A 0 0
EDITION 3 0.0 3 0.0 0 0
INDEX 11 0.0 11 0.0 0 0
SCHEMA 60 0.0 0 N/A 0 0
SQL AREA 440 17.0 3,059 6.1 57 0
TABLE/PROCEDURE 490 0.0 1,023 9.8 60 0
-------------------------------------------------------------
Memory Statistics
- 동적인 메모리 크기의 변화를 추적하여 SGA의 성능 문제를 정확하게 분석(SGA의 크기를 동적으로 관리하는 10g 이상 버전)
- 동적 SGA 크기 관리: 매우 유용한 기능이지만 래치 경합/뮤텍스 경합과 같은 성능 문제를 일으킬 수 있어 정적인 SGA 크기 관리 기법을 권장
- SGA를 구성하는 각 영역(Database Buffers, Fixed Size, Redo Buffers, Variable Size)의 크기 변화를 분석(ORA-04031과 같은 메모리 부족 에러 발생시 이 정보 활용 가능)
- AWR 리포트에서의 메모리 관련 데이터는 매우 개략적이며 정확한 원인을 찾기 위해서는 제 4장 힙 메모리 분석을 활용
Memory Dynamic Components DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> Min/Max sizes since instance startup
-> Oper Types/Modes: INItializing,GROw,SHRink,STAtic/IMMediate,DEFerred
-> ordered by Component
Begin Snap Current Min Max Oper Last Op
Component Size (Mb) Size (Mb) Size (Mb) Size (Mb) Count Typ/Mod
--------------- ----------- ----------- ----------- ----------- ------ -------
....
DEFAULT buffer 448.00 448.00 448.00 560.00 0 SHR/DEF
KEEP buffer cac .00 .00 .00 .00 0 STA/
PGA Target 704.00 704.00 704.00 704.00 0 STA/
RECYCLE buffer .00 .00 .00 .00 0 STA/
SGA Target 1,344.00 1,344.00 1,344.00 1,344.00 0 STA/
-------------------------------------------------------------
SGA Memory Summary DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ ------------------- -------------------
Database Buffers 469,762,048
Fixed Size 2,215,064
Redo Buffers 4,964,352
Variable Size 1,660,945,256
-------------------
sum 2,137,886,720
-------------------------------------------------------------
SGA breakdown difference DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> ordered by Pool, Name
-> N/A value for Begin MB or End MB indicates the size of that Pool/Name was
insignificant, or zero in that snapshot
Pool Name Begin MB End MB % Diff
------ ------------------------------ -------------- -------------- -------
java free memory 16.0 16.0 0.00
large PX msg pool 7.8 7.8 0.00
large free memory 8.2 8.2 0.00
shared ASH buffers 15.5 15.5 0.00
....
-------------------------------------------------------------
INIT.ORA Parameters
- 초기화 파라미터의 변화에 의해 예상치 못한 성능 문제가 야기되는 경우가 있어 파라미터의 변화에 대한 정보를 제공
- 세션 레벨이나 SQL 레벨에서 변경된 파라미터는 추적이 불가능
init.ora Parameters DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> if IP/Public/Source at End snap is different a '*' is displayed
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
_nlj_batching_misses_enabled 0
audit_file_dest /oracle/eleven/admin/ELEVEN/adump
audit_trail DB
compatible 11.2.0.0.0
control_files /data01/oradata/ELEVEN/control01.
db_block_size 8192
db_domain
db_name ELEVEN
....
기타 정보들
- Buffer Pool Statistics
Buffer Pool Statistics DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free Writ Buffer
Number of Pool Buffer Physical Physical Buff Comp Busy
P Buffers Hit% Gets Reads Writes Wait Wait Waits
--- ---------- ---- ------------ ------------ ----------- ------ ------ --------
D 55,109 100 770,869 47 1,574 0 0 8
-------------------------------------------------------------
- Undo Statistics
Undo Segment Stats DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/
End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- ------------
14-Mar 15:22 436 259 513 3 24 0/0 0/0/0/0/0/0
-------------------------------------------------------------
- Streams Statistics
- Resource Limit Statistics
- RAC Remastering Statistics
AWR Diff 리포트
성능 문제를 파악하는 가장 직관적인 방법은 차이를 보는 것
- 스냅샷1~스냅샷2와 스냅샷3~스냅샷4의 차이 값을 비교(Diff)
- 단일 혹은 두개의 데이터베이스간 동일구간 혹은 다른 구간의 성능을 비교하기 위한 용도로 사용
- 예: 오늘 오전 10~11시에 성능 저하 현상이 발생한 경우, 어제의 동일한 구간(오전 10시~11시)과 비교해서 어떤 차이가 있는지를 비교할 때 사용
AWR Diff를 사용하는 간단한 예제
***** 첫번째 스냅샷을 생성한다.
col begin_snap new_value begin_snap;
col db_id new_value db_id;
col inst_num new_value inst_num;
select dbid as db_id from v$database;
DB_ID
----------
3588319577
select instance_number as inst_num from v$instance;
INST_NUM
----------
1
select dbms_workload_repository.create_snapshot as begin_snap2 from dual;
BEGIN_SNAP
----------
14791
***** 10개의 세션에서 동시에 TEMP.SQL 파일을 실행한다.
***** 10개의 세션에서 작업이 끝난 후 두번째 스냅샷을 생성한다.
col end_snap new_value end_snap;
select dbms_workload_repository.create_snapshot as end_snap2 from dual;
END_SNAP
----------
14792
***** DBMS_WORKLAD_REPOSITORY.AWR_DIFF_REPORT_TEXT 함수를 이용해 생성
select * from table (
dbms_workload_repository.awr_diff_report_text (
&db_id,
&inst_num,
&begin_snap,
&end_snap,
&db_id,
&inst_num,
&begin_snap2,
&end_snap2)
);
- 위의 5개의 세션에서 temp.sql을 돌린 구간 1과 후의 10개의 세션에서 temp.sql을 돌린 구간 2를 비교하여 어느 정도의 차이가 존재하는지 확인(구간 2가 성능 면에서 나쁜 것으로 예측)
- 제공되는 데이터는 AWR 리포트와 동일
- Top 대기 이벤트에 대한 AWR Diff 리포트
Snapshot Set Begin Snap Id Begin Snap Time End Snap Id End Snap Time Avg Active Users Elapsed Time (min) DB time (min)
------------ -------------- ------------------------- ------------ ------------------------- -------------------------- -------------------------- --------------------------
1st 14739 14-Mar-12 15:22:14 (Wed) 14740 14-Mar-12 15:26:02 (Wed) 0.89 3.79 3.38
2nd 14791 16-Mar-12 17:49:29 (Fri) 14792 16-Mar-12 17:52:14 (Fri) 3.66 5.76 13.88
~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~
%Diff: 466.29% 151.97%% 310.89%
1st
---------------------------------------------------------------------------
Event Wait Class Waits Time(s) Avg Time(ms) %DB time
------------------------------ ------------- ------------ ------------ ------
CPU time N/A 160.9 N/A 79.40
enq: TX - row lock contention Application 480 41.3 86.1 20.40
db file async I/O submit System I/O 193 11.8 61.0 5.81
log file parallel write System I/O 1,052 3.1 2.9 1.51
control file parallel write System I/O 114 2.9 25.6 1.44
-db file sequential read User I/O 278 0.1 0.4 0.06
-----------------------------------------------------------
2nd
---------------------------------------------------------------------------
Event Wait Class Waits Time(s) Avg Time(ms) %DB time
------------------------------ ------------- ------------ ------------ -----
enq: TX - row lock contention Application 5,135 509.0 99.1 61.12
CPU time N/A 322.6 N/A 38.74
log file parallel write System I/O 2,034 5.5 2.7 0.66
control file parallel write System I/O 78 1.8 22.6 0.21
db file sequential read User I/O 190 0.2 0.9 0.02
- N/A N/A N/A N/A
---------------------------------------------------------------------------
- 구간 2가 구간 1보다 로우 레벨 락 경합(enq: TX - row lock contention 대기 이벤트)이 더 빈번하게 발생
- Elapsed Time은 약 2배(151.97%) 증가했지만 로우 레벨 락 경합에 의한 대기 시간은 약 10배로 증가함을 확인
AWR SQL 리포트
- 특정 Top SQL에 대한 정보를 좀 더 상세하게 분석하고 싶은 경우에 사용
- DBMS_WORKLOAD_REPOSITORY.AWR_SQL_REPORT_TEXT 함수를 이용
AWR SQL 리포트 사용 예제
-- 우선 SQL ID를 알아내야 합니다.
col sql_id new_value sql_id
select sql_id, sql_text from v$sqlarea where sql_text like 'SELECT * FROM USER_OBJECTS%';
SQL_ID SQL_TEXT
--------------------------------------- --------------------------------------------------
7dazh7hup4yq3 SELECT * FROM USER_OBJECTS
select * from table (
dbms_workload_repository.awr_sql_report_text (
&db_id,
&inst_num,
&begin_snap,
&end_snap2,
'&sql_id')
);
OR
@?/rdbms/admin/awrsqlrpt 이용
AWR SQL 리포트가 제공하는 데이터
- SQL을 정보를 추출한 스냅샷 구간에 대한 정보
- 구간 내에 몇개의 자식 커서(Child Cursors)가 존재하는지 정보
- SQL 텍스트는 동일하지만 여러 개의 실행계획을 가지는 경우가 있기 때문에 SQL 커서를 부모(SQL 텍스트)와 자식(실행계획) 관계로 나누어 관리
- 새로 자식 커서가 등록되면 일단 새로운 자식 커서를 만들고 실행 계획을 새로 만들고 기존 자식 커서들과 실행계획이 같으면 Plan Hash Value 값을 동일하게, 다르다면 다른 값을 가진다.(V$SQL 뷰의 PLAN_HASH_VALUE)
- Plan Hash Value 별로 SQL의 일량과 실제 실행 계획 정보
Top SQL에 속하지 않은 SQL문을 "색깔을 칠하여" AWR/AWR SQL 리포트에 포함
exec dbms_workload_repository.add_colored_sql('&sql_id');
exec dbms_workload_repository.remove_colored_sql('&sql_id');
"데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 트러블슈팅의 기초
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4106
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.