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 사용량이 증가한다.