TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션 #2

지난 글에서 TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션의 동작 원리에 대해 살펴봤다. 이번 글에서는 성능 개선 효과와 관련된 내용을 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자. t1_x1 인덱스는 CF가 좋고, t1_x2 인덱스는 CF가 나쁘다.  

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT CEIL (ROWNUM / 1000) AS c1, MOD (ROWNUM, 1000) AS c2, LPAD ('X', 100, 'X') AS c3
  FROM XMLTABLE('1 to 1000000');

CREATE INDEX t1_x1 ON t1 (c1);
CREATE INDEX t1_x2 ON t1 (c2);

아래 쿼리는 CF가 좋은 t1_x1 인덱스로 테이블을 액세스했다. CF가 좋은 경우 2-1번 쿼리(TABLE ACCESS BY INDEX ROWID)와 2-2번 쿼리(TABLE ACCESS BY INDEX ROWID BATCHED)의 수행 시간 차이가 크지 않음을 알 수 있다.

-- 2-1
ALTER SYSTEM FLUSH BUFFER_CACHE;

SELECT /*+ INDEX(T1) NO_BATCH_TABLE_ACCESS_BY_ROWID(T1) */ COUNT (c3) AS cn FROM t1 WHERE c1 >= 0;

------------------------------------------------------------------------------
| Id  | Operation                    | Name  |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |00:00:02.23 |   17917 |  17916 |
|   1 |  SORT AGGREGATE              |       |00:00:02.23 |   17917 |  17916 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |00:00:02.18 |   17917 |  17916 |
|*  3 |    INDEX RANGE SCAN          | T1_X1 |00:00:00.48 |    2079 |   2079 |
------------------------------------------------------------------------------

-- 2-2
ALTER SYSTEM FLUSH BUFFER_CACHE;

SELECT /*+ INDEX(T1) BATCH_TABLE_ACCESS_BY_ROWID(T1) */ COUNT (c3) AS cn FROM t1 WHERE c1 >= 0;

--------------------------------------------------------------------------------------
| Id  | Operation                            | Name  |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |00:00:02.07 |   17917 |  17916 |
|   1 |  SORT AGGREGATE                      |       |00:00:02.07 |   17917 |  17916 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    |00:00:02.02 |   17917 |  17916 |
|*  3 |    INDEX RANGE SCAN                  | T1_X1 |00:00:00.49 |    2079 |   2079 |
--------------------------------------------------------------------------------------

아래 쿼리는 CF가 나쁜 t1_x2 인덱스로 테이블을 액세스했다. 3-1번 쿼리가 3-2번 쿼리보다 오래 수행된 것을 확인할 수 있다. TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션은 CF가 나쁜 경우에 큰 효과를 발휘하는 것을 알 수 있다. 

-- 3-1
ALTER SYSTEM FLUSH BUFFER_CACHE;

SELECT /*+ INDEX(T1) NO_BATCH_TABLE_ACCESS_BY_ROWID(T1) */ COUNT (c3) AS cn FROM t1 WHERE c2 >= 0;

------------------------------------------------------------------------------
| Id  | Operation                    | Name  |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |00:00:04.47 |    1002K|  17916 |
|   1 |  SORT AGGREGATE              |       |00:00:04.47 |    1002K|  17916 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |00:00:04.42 |    1002K|  17916 |
|*  3 |    INDEX RANGE SCAN          | T1_X2 |00:00:00.48 |    2079 |   2079 |
------------------------------------------------------------------------------

-- 3-2
ALTER SYSTEM FLUSH BUFFER_CACHE;

SELECT /*+ INDEX(T1) BATCH_TABLE_ACCESS_BY_ROWID(T1) */ COUNT (c3) AS cn FROM t1 WHERE c2 >= 0;

--------------------------------------------------------------------------------------
| Id  | Operation                            | Name  |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |00:00:02.39 |     986K|  17916 |
|   1 |  SORT AGGREGATE                      |       |00:00:02.39 |     986K|  17916 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    |00:00:02.34 |     986K|  17916 |
|*  3 |    INDEX RANGE SCAN                  | T1_X2 |00:00:00.47 |    2079 |   2079 |
--------------------------------------------------------------------------------------

아래의 세션 통계 값으로 Batched I/O의 동작 여부를 판단할 수 있다.

-- 4
NAME                            3-1   3-2
------------------------------- --- -----
Batched IO zero block count       0     0
Batched IO vector read count      0   318
Batched IO vector block count     0 15835
Batched IO slow jump count        0     0
Batched IO single block count     0     0
Batched IO same unit count        0 15293
Batched IO double miss count      0     0
Batched IO buffer defrag count    0     0
Batched IO block miss count       0 31120
Batched IO (space) vector count   0     0
Batched IO (full) vector count    0     8
Batched IO (bound) vector count   0   310