CLOB #1 - BASICFILE vs SECUREFILE

12.1 버전부터 CLOB 타입을 SECUREFILE로 생성할 수 있다. 블록 I/O 측면에서 BASICFILE과 SECUREFILE의 차이를 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;

CREATE TABLE t1 (c1 NUMBER, c2 VARCHAR2(4000), c3 VARCHAR2(4000));
CREATE TABLE t2 (c1 NUMBER, c2 CLOB) LOB (c2) STORE AS BASICFILE;
CREATE TABLE t3 (c1 NUMBER, c2 CLOB) LOB (c2) STORE AS SECUREFILE;

INSERT INTO t1
SELECT ROWNUM AS c1, LPAD ('X', 4000, 'X'), LPAD ('X', 4000, 'X') AS c2
  FROM XMLTABLE ('1 to 100');

INSERT INTO t2
SELECT ROWNUM AS c1, TO_CLOB (LPAD ('X', 4000, 'X')) || TO_CLOB (LPAD ('X', 4000, 'X')) AS c2
  FROM XMLTABLE ('1 to 100');

INSERT INTO t3
SELECT ROWNUM AS c1, TO_CLOB (LPAD ('X', 4000, 'X')) || TO_CLOB (LPAD ('X', 4000, 'X')) AS c2
  FROM XMLTABLE ('1 to 100');

COMMIT;

아래는 캐싱된 커서의 runtime 통계다. VARCHAR2 타입을 사용한 2-1번 쿼리가 348, CLOB 타입을 사용한 2-2, 2-3번 쿼리가 106의 블록 I/O가 발생하는 것으로 표시된다. 캐싱된 커서의 runtime 통계는 CLOB 타입에 의해 발생한 블록 I/O를 표시하지 못하는 것으로 보인다.

-- 2
SET ARRAYSIZE 15
SET LONG 80
SET LONGCHUNKSIZE 80

-- 2-1
SELECT * FROM t1;

---------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |    100 |00:00:00.01 |     348 |
|   1 |  TABLE ACCESS FULL| T1   |      1 |    100 |00:00:00.01 |     348 |
---------------------------------------------------------------------------

-- 2-2
SELECT * FROM t2;

---------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |    100 |00:00:00.01 |     106 |
|   1 |  TABLE ACCESS FULL| T1   |      1 |    100 |00:00:00.01 |     106 |
---------------------------------------------------------------------------

-- 2-3
SELECT * FROM t3;

---------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |    100 |00:00:00.01 |     106 |
|   1 |  TABLE ACCESS FULL| T2   |      1 |    100 |00:00:00.01 |     106 |
---------------------------------------------------------------------------

AUTOTRACE로 블록 I/O를 확인해보자. consistent gets에서 차이를 확인할 수 있다.^[각주:1] BASICFILE이 SECUREFILE보다 400개의 블록 I/O가 더 발생한 것으로 표시된다. 다른 통계 값의 차이도 확인해보자.^[각주:2]

-- 3
SET AUTOTRACE TRACEONLY

-- 3-1
SELECT * FROM t1;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        348  consistent gets
          0  physical reads
          0  redo size
     805887  bytes sent via SQL*Net to client
        673  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

-- 3-2
SELECT * FROM t2;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        706  consistent gets
        400  physical reads
          0  redo size
     103660  bytes sent via SQL*Net to client
      58708  bytes received via SQL*Net from client
        302  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

-- 3-3
SELECT * FROM t3;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        306  consistent gets
        400  physical reads
          0  redo size
      48460  bytes sent via SQL*Net to client
      58708  bytes received via SQL*Net from client
        102  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

세션 통계를 확인하면 SECUREFILE의 consistent gets direct 항목이 0으로 측정되어 consistent gets가 감소한 것을 확인할 수 있다. consistent gets direct 항목 대신 shared io pool buffer get success 항목이 400으로 측정된다. SECUREFILE을 NOCACHE 옵션으로 생성하면 SHARED I/O POOL을 사용하기 때문에 발생하는 현상이다.

-- 4
NAME                              3-2     3-3
--------------------------------- ------- -------
logical read bytes from cache     2506752 2506752
consistent gets                       706     306
consistent gets direct                400       0 -- !
consistent gets from cache            306     306
shared io pool buffer get success       0     400 -- !
lob reads                             400       0
physical read total bytes         3276800 3276800
physical read total IO requests       400     400
physical reads direct (lob)           400     400
securefile direct read bytes            0 3276800
securefile direct read ops              0     400
SQL*Net roundtrips to/from client     302     102

BASICFILE로 생성된 CLOB 타입을 SECUREFILE로 변경시 I/O가 감소하는 현상은 측정 기준의 상이함으로 발생하는 것으로 판단된다. 반대의 경우 I/O가 증가하는 현상에 민감하게 반응할 필요가 없음을 예상할 수 있다.

1. CLOB을 NOCACHE 옵션으로 생성했기 때문에 여러 번 수행하더라도 physical reads가 발생한다. [본문으로]
2. SQL*Net roundtrips to/from client 항목에서 차이가 발생하는 것을 확인할 수 있다. BASICFILE의 경우 302, SECUREFILE의 경우 102로 200이 감소했다. [본문으로]