오라클 성능 고도화 원리와 해법 I (2016년-2)
01 블록 단위 I/O
- 모든 DBMS에서 I/O는 블록 단위로 이루어진다.
테이블에서 하나의 컬럼만 읽으려고 하더라도 블럭단위로 데이터를 가져온다.
--전체 컬럼을 선택하든
SELECT /*+GATHER_PLAN_STATISTICS*/
*
FROM TABLE_A
WHERE COL2= '2016-12-31'
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 117K|00:00:00.85 | 54592 |
| 1 | TABLE ACCESS BY INDEX ROWID| TABLE_A | 1 | 2247 | 117K|00:00:00.85 | 54592 |
|* 2 | INDEX RANGE SCAN | TABLE_A_IDX22 | 1 | 2247 | 117K|00:00:00.16 | 3630 |
--------------------------------------------------------------------------------------------------------
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 237 1.88 2.04 0 54592 0 117811
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 239 1.88 2.04 0 54592 0 117811
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 38
Rows Row Source Operation
------- ---------------------------------------------------
117811 TABLE ACCESS BY INDEX ROWID TABLE_A (cr=54592 pr=0 pw=0 time=839863 us cost=675 size=1352694 card=2247)
117811 INDEX RANGE SCAN TABLE_A_IDX22 (cr=3630 pr=0 pw=0 time=127203 us cost=24 size=0 card=2247)(object id 1089668)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 237 0.00 0.00
SQL*Net more data to client 3956 0.00 0.24
SQL*Net message from client 237 3.26 7.74
--단일 컬럼을 선택하든
SELECT /*+GATHER_PLAN_STATISTICS*/
COL3
FROM TABLE_A
WHERE COL2= '2016-12-31'
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 117K|00:00:00.41 | 54592 |
| 1 | TABLE ACCESS BY INDEX ROWID| TABLE_A | 1 | 2247 | 117K|00:00:00.41 | 54592 |
|* 2 | INDEX RANGE SCAN | TABLE_A_IDX22 | 1 | 2247 | 117K|00:00:00.14 | 3630 |
--------------------------------------------------------------------------------------------------------
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 237 0.43 0.41 0 54592 0 117811
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 239 0.43 0.41 0 54592 0 117811
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 38
Rows Row Source Operation
------- ---------------------------------------------------
117811 TABLE ACCESS BY INDEX ROWID TABLE_A (cr=54592 pr=0 pw=0 time=393307 us cost=675 size=31458 card=2247)
117811 INDEX RANGE SCAN TABLE_A_IDX22 (cr=3630 pr=0 pw=0 time=111840 us cost=24 size=0 card=2247)(object id 1089668)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 2 0.00 0.00
library cache pin 2 0.00 0.00
SQL*Net message to client 237 0.00 0.00
SQL*Net message from client 236 1.41 2.18
읽어야할 BUFFER량은 동일하다. 다만 오라클서버에서 클라이언트로 전송하는 데이터량(여기서는 size가 1352694<->31458)과
시간(7.74초<->2.18초)에서는 차이가 나므로 필요한 컬럼만 SELECT해야 한다.
- SQL 성능을 좌우하는 가장 중요한 성능지표는 액세스하는 블록의 개수이다.
- 옵티마이저의 판단에 가장 큰 영향을 미치는 것도 액세스해야하는 블록의 개수이다.
- 블록 단위 I/O는 버퍼 캐시와 데이터 파일 I/O 모두에 적용된다.
- 메모리 버퍼 캐시에서 블록일 읽고 쓸 때
- 데이터파일에 저장된 데이터 블록을 직접 읽거나 쓸 때
- 데이터파일에서 DB 버퍼 캐시로 블록을 적재할 때 : SINGLE BLOCK READ 또는 MULTIBLOCK READ 방식을 사용
- 버퍼 캐시에서 변경된 블록을 다시 데이터파일에 저장할 때 : DIRTY 버퍼를 주기적으로 데이터파일에 기록하는 것을 말하며,
DBWR 프로세스에 의해 수행된다. 성능향상을 위해 한 번에 여러 블록씩 처리한다.
- 오라클에서 허용하는 블록크기는 2K,4K,8K(default),16K,32K,64K
(1) Sequential VS. Random 액세스
- SEQUENTIAL 액세스는 레코드간 논리적 또는 물리적 순서를 따라 차례대로 읽어 나가는 방식, 그림에서 5번.
성능향상을 위해 MUTIBLOCK I/O, INDEX PREFETCH 등을 활용 - RANDOM 액세스는 레코드간 논리적, 물리적인 순서를 따리지 않고, 한 건을 읽기 위해 한 블록씩 접근하는 방식,
그림에서 1,2,3,4,6. 인덱스 스켄에서는 대부분 4,6번이 성능저하를 일으킨다.
성능향상을 위해 BUFFER PINNING, TABLE PREFETCH 등을 활용 - I/O 튜닝의 핵심
- SEQUENTIAL 액세스 선택도 높이기
- RANDOM 액세스 발생량 줄이기
(2) Sequential 액세스 선택도 높이기
--테이블 만들기
CREATE TABLE T
AS
SELECT * FROM ALL_OBJECTS
ORDER BY DBMS_RANDOM.VALUE
--총건수 조회
SELECT COUNT(1) FROM T
COUNT(1)
--------
95956
--조회1
SELECT COUNT(1) FROM T
WHERE OWNER LIKE 'SYS%'
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=1338 pr=1004 pw=0 time=51133 us)
32874 TABLE ACCESS FULL T (cr=1338 pr=1004 pw=0 time=52593 us cost=366 size=564723 card=33219)
-> 선택도 32874/95956*100 = 34.26%
-> 읽은 블록수는 1338
-> FULL SCAN으로 양호하다.
--조회2
SELECT COUNT(1) FROM T
WHERE OWNER LIKE 'SYS%'
AND OBJECT_NAME = 'ALL_OBJECTS'
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=1338 pr=0 pw=0 time=15172 us)
1 TABLE ACCESS FULL T (cr=1338 pr=0 pw=0 time=15166 us cost=366 size=1666 card=49)
-> 선택도 1/95956*100 = 0.001%
-> 읽은 블록수 1338
-> FULL SCAN으로 비효율적이다.
--인덱스 생성
CREATE INDEX T_IDX ON T(OWNER, OBJECT_NAME);
SELECT /*+ INDEX(T T_IDX)*/ COUNT(1) FROM T
WHERE OWNER LIKE 'SYS%'
AND OBJECT_NAME = 'ALL_OBJECTS'
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=92 pr=91 pw=0 time=4628 us)
1 INDEX RANGE SCAN T_IDX (cr=92 pr=91 pw=0 time=4622 us cost=181 size=1666 card=49)(object id 368214)
-> 읽은 블록수는 92개로 줄었다. 하지만 INDEX에서 SELECT한 총 건수는
SELECT /*+INDEX(T T_IDX)*/ COUNT(1) FROM T
WHERE OWNER LIKE 'SYS%'
AND ((OWNER = 'SYS' AND OBJECT_NAME >= 'ALL_OBJECTS') OR (OWNER > 'SYS'));
COUNT(1)
--------
17345
-> 선택도 1/17345*100 = 0.005%
-> 여전히 비효율적이다.
--기존 인덱스 제거
DROP INDEX T_IDX;
--순서를 바꾼 인덱스 생성
CREATE INDEX T_IDX ON T(OBJECT_NAME, OWNER);
SELECT /*+ INDEX(T T_IDX) VER2*/ COUNT(1) FROM T
WHERE OWNER LIKE 'SYS%'
AND OBJECT_NAME = 'ALL_OBJECTS'
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=19 us)
1 INDEX RANGE SCAN T_IDX (cr=3 pr=0 pw=0 time=14 us cost=3 size=34 card=1)(object id 368215)
-> 선택도가 좋은 OBJECT_NAME컬럼을 선두 컬럼으로 지정함으로써 읽은 블록수가 3건으로 줄어들었다.
(3) Random 액세스 발생량 줄이기
DROP INDEX T_IDX;
CREATE INDEX T_IDX ON T(OWNER);
SELECT /*+ INDEX(T T_IDX) VER3*/ COUNT(1) FROM T
WHERE OWNER LIKE 'SYS%'
AND OBJECT_NAME = 'ALL_OBJECTS'
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=2149 pr=70 pw=0 time=47634 us)
1 TABLE ACCESS BY INDEX ROWID T (cr=2149 pr=70 pw=0 time=47629 us cost=6412 size=1666 card=49)
32874 INDEX RANGE SCAN T_IDX (cr=71 pr=70 pw=0 time=27985 us cost=79 size=0 card=33219)(object id 368216)
-> 인덱스에서 32874건 출력했다.
-> 원래 32874번 테이블을 RANDOM 액세스해야하지만 BUFFER PINNING 효과에 의해 2149블록만 읽었다.
-> 32874건의 인덱스를 읽고 OBJECT_NAME을 필터링하려고 테이블 T에 접근하는 RANDOM 액세스 반복하여 최종적으로 1개의 ROW만을 가져오는 것은 너무나 비효율적이다.
DROP INDEX T_IDX;
CREATE INDEX T_IDX ON T(OWNER, OBJECT_NAME);
SELECT /*+ INDEX(T T_IDX) VER4*/ COUNT(1) FROM T
WHERE OWNER LIKE 'SYS%'
AND OBJECT_NAME = 'ALL_OBJECTS'
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=92 pr=91 pw=0 time=4630 us)
1 INDEX RANGE SCAN T_IDX (cr=92 pr=91 pw=0 time=4623 us cost=181 size=1666 card=49)(object id 368217)
-> OBJECT_NAME 컬럼을 추가하여 TABLE ACCESS를 하지 않게 하였다. INDEX만 읽고도 실행가능하므로 RANDOM ACCESS가 발생하지 않았으며 블록 READ량이 줄어들었다.
버퍼피닝(BUFFER PINNING) 이해하기
--일반 조회
SELECT /*+GATHER_PLAN_STATISTICS INDEX_RS(A)*/
*
FROM TALBE_A A
WHERE COL1 = '2016-12-31'
AND COL2 LIKE 'O%'
AND COL3 BETWEEN 1 AND 10000
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 17985 |00:00:00.16 | 16309 |
| 1 | TABLE ACCESS BY INDEX ROWID| TALBE_A | 1 | 6 | 17985 |00:00:00.16 | 16309 |
|* 2 | INDEX RANGE SCAN | TALBE_A_IDX1 | 1 | 6 | 17985 |00:00:00.03 | 2314 |
-------------------------------------------------------------------------------------------------------
-> 17985건을 읽기 위해 논리적 READ를 16309회 하였다.
-> 만약 물리적 주소 순서대로 조회하게 되면 과연 논리적 READ량이 줄어들까?
--물리주소 순서로 SORT해서 조회
SELECT /*+GATHER_PLAN_STATISTICS*/
B.*
FROM (SELECT /*+NO_MERGE INDEX_RS(A)*/
ROWID RID
FROM TALBE_A
WHERE COL1 = '2016-12-31'
AND COL2 LIKE 'O%'
AND COL3 BETWEEN 1 AND 10000
ORDER BY ROWID) A, TALBE_A B
WHERE A.RID = B.ROWID
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 17985 |00:00:00.24 | 11811 |
| 1 | NESTED LOOPS | | 1 | 6 | 17985 |00:00:00.24 | 11811 |
| 2 | VIEW | | 1 | 6 | 17985 |00:00:00.09 | 2280 |
| 3 | SORT ORDER BY | | 1 | 6 | 17985 |00:00:00.08 | 2280 |
|* 4 | INDEX RANGE SCAN | TALBE_A_IDX1 | 1 | 6 | 17985 |00:00:00.02 | 2280 |
| 5 | TABLE ACCESS BY USER ROWID| TALBE_A | 17985 | 1 | 17985 |00:00:00.14 | 9531 |
-------------------------------------------------------------------------------------------------------
-> 예상대로 BUFFERS가 16309 -> 11811 줄어든 것을 알 수 있다.
-> 하지만 SORT OPERATION에 의한 PGA 사용량이 증가한다.
"구루비 DB 스터디 모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 I " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3188
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.