오라클 성능 트러블슈팅의 기초 (2012년)
I/O 관련 대기 이벤트
- I/O 관련 대기이벤트들의 공통적인 특징은 'P1, P2'가 '파일번호, 블록번호' 정보를 제공함.
- P1이 'file#'이고, P2가 'block#'인 대기 이벤트 목록은 다음과 같음
소스코드
col NAME format a60
SELECT NAME
FROM V$EVENT_NAME
WHERE PARAMETER1 = 'file#'
AND PARAMETER2 = 'block#'
;
수행내역
SQL>
1 col NAME format a60
SQL>
1 SELECT NAME
2 FROM V$EVENT_NAME
3 WHERE PARAMETER1 = 'file#'
4 AND PARAMETER2 = 'block#'
5 ;
NAME
------------------------------------------------------------
control file sequential read
control file single write
free buffer waits
local write wait
write complete waits
write complete waits: flash cache
buffer read retry
buffer busy waits
gc buffer busy acquire
gc buffer busy release
read by other session
pi renounce write complete
db file sequential read
db file scattered read
db file single write
gc current request
gc cr request
gc cr disk request
gc cr multi block request
gc current multi block request
gc block recovery request
gc remaster
gc domain validation
gc recovery quiesce
buffer exterminate
cr request retry
recovery buffer pinned
Auto BMR completion
28 개의 행이 선택되었습니다.
- 파일번호, 블로번호를 가지고 어떤 종류의 블록인지, 어떤 오브젝트에 속하는지 추가적인 정보 획득하는 방법을 알아봄
소스코드
-- 0. SPID 확인
@mysess
-- 1. 테이블 생성
CREATE TABLE T1 AS
SELECT LEVEL AS C1,
RPAD('X', 100) AS C2
FROM DUAL
CONNECT BY LEVEL <= 100
;
-- 2. 통계정보 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1');
-- 3. Buffer Cache Flush
ALTER SYSTEM FLUSH BUFFER_CACHE;
-- 4. 트레이스 활성화
ALTER SESSION SET EVENTS 'sql_trace wait=true';
-- 5. SQL 수행
SELECT COUNT(*)
FROM T1
;
-- 6. 트레이스 비 활성화
ALTER SESSION SET EVENTS 'sql_trace off';
수행내역
SQL>
1 @mysess
MY_SESSION_INFO
-------------------------------------------------------------------------------
DB Name : xsoftdb
Sid, Serial# : 137, 110
OS Process : 3060:716 (CPID), 3396 (SPID)
DB User : SYSTEM
OS User : ken-zo
Module Info : 01@ mysess.sql
Program Info : 01@ mysess.sql
Machine[Term] : WORKGROUP\KENZO [KENZO]
1 개의 행이 선택되었습니다.
SQL>
1 CREATE TABLE T1 AS
2 SELECT LEVEL AS C1,
3 RPAD('X', 100) AS C2
4 FROM DUAL
5 CONNECT BY LEVEL <= 100
6 ;
테이블이 생성되었습니다.
SQL>
1 EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
1 ALTER SYSTEM FLUSH BUFFER_CACHE;
시스템이 변경되었습니다.
SQL>
1 ALTER SESSION SET EVENTS 'sql_trace wait=true';
세션이 변경되었습니다.
SQL>
1 SELECT COUNT(*)
2 FROM T1
3 ;
COUNT(*)
----------
100
1 개의 행이 선택되었습니다.
SQL>
1 ALTER SESSION SET EVENTS 'sql_trace off';
세션이 변경되었습니다.
- 해당 트레이스 파일을 열어보면 아래와 같다.
PARSING IN CURSOR #10 len=26 dep=0 uid=5 oct=3 lid=5 tim=1724632090 hv=3296225404 ad='33bd66d4' sqlid='dx4udkv27hu3w'
SELECT COUNT(*)
FROM T1
END OF STMT
PARSE #10:c=0,e=73707,p=26,cr=5,cu=0,mis=1,r=0,dep=0,og=1,plh=3724264953,tim=1724632086
EXEC #10:c=0,e=65,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3724264953,tim=1724632298
WAIT #10: nam='SQL*Net message to client' ela= 12 driver id=1413697536 #bytes=1 p3=0 obj#=0 tim=1724632426
-----------------------------------------------------------------------------------------------------------
-- file#, block# 항목
WAIT #10: nam='db file sequential read' ela= 5794 file#=1 block#=86576 blocks=1 obj#=74630 tim=1724638340
WAIT #10: nam='db file scattered read' ela= 437 file#=1 block#=86577 blocks=2 obj#=74630 tim=1724638987
-----------------------------------------------------------------------------------------------------------
FETCH #10:c=0,e=6723,p=3,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=3724264953,tim=1724639216
STAT #10 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=4 pr=3 pw=0 time=0 us)'
STAT #10 id=2 cnt=100 pid=1 pos=1 obj=74630 op='TABLE ACCESS FULL T1 (cr=4 pr=3 pw=0 time=297 us cost=2 size=0 card=100)'
WAIT #10: nam='SQL*Net message from client' ela= 432 driver id=1413697536 #bytes=1 p3=0 obj#=74630 tim=1724639876
FETCH #10:c=0,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3724264953,tim=1724639958
WAIT #10: nam='SQL*Net message to client' ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=74630 tim=1724640017
*** 2012-04-01 16:20:50.203
WAIT #10: nam='SQL*Net message from client' ela= 41276 driver id=1413697536 #bytes=1 p3=0 obj#=74630 tim=1724681337
- 위에서 얻은 file#, block# 값을 가지고 데이터 오브젝트 아이디 확인 가능
소스코드
-- 1. OBJD 수집
SELECT FILE#,
BLOCK#,
CLASS#,
OBJD AS DATA_OBJ_ID,
STATUS
FROM V$BH
WHERE ((FILE# = 1 AND BLOCK# = 86576) OR
(FILE# = 1 AND BLOCK# = 86577))
--AND STATUS = 'xcur'
ORDER BY BLOCK#
;
-- 2. OBJECT 확인
SELECT OWNER,
OBJECT_NAME,
OBJECT_TYPE
FROM ALL_OBJECTS
WHERE DATA_OBJECT_ID IN (SELECT OBJD AS DATA_OBJ_ID
FROM V$BH
WHERE ((FILE# = 1 AND BLOCK# = 86576) OR
(FILE# = 1 AND BLOCK# = 86577))
--AND STATUS = 'xcur'
)
;
수행내역
SQL>
1 SELECT FILE#,
2 BLOCK#,
3 CLASS#,
4 OBJD AS DATA_OBJ_ID,
5 STATUS
6 FROM V$BH
7 WHERE ((FILE# = 1 AND BLOCK# = 86576) OR
8 (FILE# = 1 AND BLOCK# = 86577))
9 --AND STATUS = 'xcur'
10 ORDER BY BLOCK#
11 ;
FILE# BLOCK# CLASS# DATA_OBJ_ID STATUS
---------- ---------- ---------- ----------- ----------
1 86576 4 74625 free
1 86576 4 74630 free
1 86576 4 74629 free
1 86576 4 74629 free
1 86576 4 74625 free
1 86576 4 74630 free
1 86576 4 74629 free
1 86577 1 74630 free
1 86577 1 74630 free
1 86577 1 74629 free
1 86577 1 74629 free
11 개의 행이 선택되었습니다.
SQL>
1 SELECT OWNER,
2 OBJECT_NAME,
3 OBJECT_TYPE
4 FROM ALL_OBJECTS
5 WHERE DATA_OBJECT_ID IN (SELECT OBJD AS DATA_OBJ_ID
6 FROM V$BH
7 WHERE ((FILE# = 1 AND BLOCK# = 86576) OR
8 (FILE# = 1 AND BLOCK# = 86577))
9 --AND STATUS = 'xcur'
10 )
11 ;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SYSTEM T1 TABLE
1 개의 행이 선택되었습니다.
- 책에서는 V$BH 뷰에서 STATUS값을 'xcur'(Exclusive)로 검색했지만, 테스트 시에는 'free'(Not currently in use) 상태여서 조건에서 제거함
- 위의 결과처럼 '파일번호, 블록번호'에 해당하는 블록이 버퍼 캐시에 존재한다면, V$BH 뷰에서 블록의 종류(CLASS#)와 데이터 오브젝트 아이디(OBJD) 값을 얻을 수 있음
- OBJD 값을 얻었다면, 이를 이용해 ALL_OBJECTS 뷰에서 검색하여, OWNER, OBJECT_NAME, OBJECT_TYPE 확인이 가능
- 또한 블록의 종류(CLASS#)를 나타내는 4와 1은 각각 세그먼트 헤더(4), 데이터블록(1)에 해당되므로, 세그먼트 헤더를 읽은 후에 데이터 블록을 읽는 것이 확인 가능함
소스코드
SELECT ROWNUM,
CLASS
FROM V$WAITSTAT
;
수행내역
SQL>
1 SELECT ROWNUM,
2 CLASS
3 FROM V$WAITSTAT
4 ;
ROWNUM CLASS
---------- ------------------
1 data block
2 sort block
3 save undo block
4 segment header
5 save undo header
6 free list
7 extent map
8 1st level bmb
9 2nd level bmb
10 3rd level bmb
11 bitmap block
12 bitmap index block
13 file header block
14 unused
15 system undo header
16 system undo block
17 undo header
18 undo block
18 개의 행이 선택되었습니다.
- V$Bh 뷰 정의(REFERENCE site : http://docs.oracle.com/cd/B13789_01/server.101/b10755/dynviews_1035.htm)
| COLUMN | DATATYPE | DESCRIPTION |
|---|---|---|
| FILE# | NUMBER | Datafile identifier number (to find the filename, query DBA_DATA_FILES or V$DBFILE) |
| BLOCK# | NUMBER | Block NUMBER |
| CLASS# | NUMBER | Class number |
| STATUS | VARCHAR2(6) | Status of the buffer: => free - Not currently in use => xcur - Exclusive => scur - Shared current => cr - Consistent read => read - Being read from disk => mrec - In media recovery mode => irec - In instance recovery MODE |
| XNC | NUMBER | Number of PCM x to null lock conversions due to contention with another instance. This column is obsolete and maintained for backward compatibility. |
| LOCK_ELEMENT_ADDR | RAW(4,8) | Address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock. |
| LOCK_ELEMENT_NAME | NUMBER | The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock. |
| LOCK_ELEMENT_CLASS | NUMBER | The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock. |
| FORCED_READS | NUMBER | Number of times the block had to be reread from the cache because another instance has forced it out of this instances cache by requesting the lock on the block in exclusive mode |
| FORCED_WRITES | NUMBER | Number of times GCS had to write this block to cache because this instance had used the block and another instance had requested the lock on the block in a conflicting mode |
| DIRTY | VARCHAR2(1) | Y - block modified |
| TEMP | VARCHAR2(1) | Y - temporary block |
| PING | VARCHAR2(1) | Y - block pinged |
| STALE | VARCHAR2(1) | Y - block is stale |
| DIRECT | VARCHAR2(1) | Y - direct block |
| NEW | VARCHAR2(1) | Always set to N. This column is obsolete and maintained for backward compatibility. |
| OBJD | NUMBER | Database object number of the block that the buffer represents |
| TS# | NUMBER | Tablespace number of block |
- 만약, 해당하는 블록이 버퍼 캐시에서 이미 사라졌다면, V$BH 뷰로부터 정보를 얻는 것이 불가능함
- 이런 경우 블록 덤프(Block Dump) 기능을 이용해서 블록 정보를 트레이스 파일에 기록하고, 이 정보를 이용할 수 있음
소스코드
ALTER SYSTEM DUMP DATAFILE 1 BLOCK 86576;
수행내역
SQL>
1 ALTER SYSTEM DUMP DATAFILE 1 BLOCK 86576;
시스템이 변경되었습니다.
- BLOCK DUMP 확인
Trace file c:\app\ken-zo\diag\rdbms\xsoftdb\xsoftdb\trace\xsoftdb_ora_4048.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows XP Version V5.1 Service Pack 3
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x0x00000000
Memory (Avail/Total): Ph:1576M/3069M, Ph+PgF:3158M/4960M, VA:943M/2047M
Instance name: xsoftdb
Redo thread mounted by this instance: 1
Oracle process number: 41
Windows thread id: 4048, image: ORACLE.EXE (SHAD)
*** 2012-04-01 17:29:28.187
*** SESSION ID:(146.360) 2012-04-01 17:29:28.187
*** CLIENT ID:() 2012-04-01 17:29:28.187
*** SERVICE NAME:(xsoftdb) 2012-04-01 17:29:28.187
*** MODULE NAME:(SQL*Plus) 2012-04-01 17:29:28.187
*** ACTION NAME:() 2012-04-01 17:29:28.187
Start dump data blocks tsn: 0 file#:1 minblk 86576 maxblk 86576
Block dump from cache:
Dump of buffer cache at level 4 for tsn=0, rdba=4280880
BH (0x297E4E24) file#: 1 rdba: 0x00415230 (1/86576) class: 4 ba: 0x2945E000
set: 6 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74630 objn: 74630 tsn: 0 afn: 1 hint: f
hash: [0x297D131C,0x3D7A9D60] lru: [0x2AFDA014,0x257EB15C]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x297D12A0) file#: 1 rdba: 0x00415230 (1/86576) class: 4 ba: 0x29164000
set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74630 objn: 74630 tsn: 0 afn: 1 hint: f
hash: [0x26FE9BA4,0x297E4EA0] lru: [0x26FE440C,0x267DBD10]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x26FE9B28) file#: 1 rdba: 0x00415230 (1/86576) class: 4 ba: 0x26D18000
set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74629 objn: 0 tsn: 0 afn: 1 hint: f
hash: [0x25FF9340,0x297D131C] lru: [0x25FF9370,0x297D1C68]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x25FF92C4) file#: 1 rdba: 0x00415230 (1/86576) class: 4 ba: 0x25F6E000
set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74629 objn: 74629 tsn: 0 afn: 1 hint: f
hash: [0x25FCFF3C,0x26FE9BA4] lru: [0x25FE2B34,0x26FE9BD4]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x25FCFEC0) file#: 1 rdba: 0x00415230 (1/86576) class: 4 ba: 0x25934000
set: 6 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74629 objn: 74629 tsn: 0 afn: 1 hint: f
hash: [0x25FDBF5C,0x25FF9340] lru: [0x25FD0CAC,0x2A7E6D74]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x25FDBEE0) file#: 1 rdba: 0x00415230 (1/86576) class: 4 ba: 0x25B04000
set: 6 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74625 objn: 0 tsn: 0 afn: 1 hint: f
hash: [0x25FF4070,0x25FCFF3C] lru: [0x117E1D20,0x27FDFE7C]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x25FF3FF4) file#: 1 rdba: 0x00415230 (1/86576) class: 4 ba: 0x25EA6000
set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74625 objn: 0 tsn: 0 afn: 1 hint: f
hash: [0x3D7A9D60,0x25FDBF5C] lru: [0x117C80B8,0x2D7D8170]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 0 rdba: 0x00415230 (1/86576)
scn: 0x0000.0014d8b1 seq: 0x01 flg: 0x04 tail: 0xd8b11001
frmt: 0x02 chkval: 0x97d6 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x40B40200 to 0x40B42200
40B40200 0000A210 00415230 0014D8B1 04010000 [....0RA.........]
40B40210 000097D6 00000000 00000000 00000000 [................]
40B40220 00000000 00000001 00000007 00001020 [............ ...]
40B40230 00000000 00000002 00000007 00415233 [............3RA.]
40B40240 00000000 00000000 00000000 00000002 [................]
40B40250 00000000 00000000 00000000 00000001 [................]
40B40260 00000000 00012386 40000000 00415231 [.....#.....@1RA.]
40B40270 00000007 00000000 00000000 00000000 [................]
40B40280 00000000 00000000 00000000 00000000 [................]
Repeat 250 times
40B41230 00000000 00010000 00010001 00000000 [................]
40B41240 00000000 00000000 00000000 00000000 [................]
Repeat 250 times
40B421F0 00000000 00000000 00000000 D8B11001 [................]
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 7
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x00415233 ext#: 0 blk#: 2 ext size: 7
#blocks in seg. hdrs freelists: 0
#blocks below: 2
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 1 obj#: 74630 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00415231 length: 7
nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 0
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
End dump data blocks tsn: 0 file#: 1 minblk 86576 maxblk 86576
- 'PAGETABLE SEGMENT HEADER' 값은 블록 종류가 세그먼트 헤더 블록임을 알 수 있는 값이며 책에서는 확인이 됐지만
테스트 시에는 확인을 못함.
- 파일번호 1에 블록번호 86577인 블록도 확인 가능
소스코드
ALTER SYSTEM DUMP DATAFILE 1 BLOCK 86577;
수행내역
SQL>
1 ALTER SYSTEM DUMP DATAFILE 1 BLOCK 86577;
시스템이 변경되었습니다.
- 블록 덤프 확인
Block header dump: 0x00415231
Object id on Block? Y
seg/obj: 0x12386 csc: 0x00.14d8ac itc: 3 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.0014d8ac
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
- 여기서 'typ: 1 - DATA'는 블록 종류가 데이터 블록임을 알 수 있으며, 데이터 블록이란 테이블이나 인덱스에서 실제 데이터를 저장하고 있는 블록을 의미함.
- 'seg/obj: 0x12386'에서 세그먼트의 데이터 오브젝트 아이디가 0x12386(16진수)임을 알 수 있으며, 십진수로 변환하면 74630이어서 V$BH에서 얻은 값과 일치함
소스코드
SELECT TO_DEC('12386') FROM DUAL;
수행내역
SQL>
1 SELECT TO_DEC('12386') FROM DUAL;
TO_DEC('12386')
---------------
74630
1 개의 행이 선택되었습니다.
"데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 트러블슈팅의 기초
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4300
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.