Oracle/Administration2018. 6. 23. 15:24

SQL*Plus에서 ARRAYSIZE 설정에 따라 I/O가 변동되는 것은 주지의 사실이다. CLOB 타입은 LONGCHUNKSIZE 설정과 관련이 있다.


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

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 CLOB);

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

COMMIT;


아래는 ARRAYSIZE, LONG, LONGCHUNKSIZE 시스템 변수에 대한 설명이다.

  • ARRAYSIZE : Sets the number of rows, called a batch, that SQL*Plus will fetch from the database at one time.
  • LONG : Sets maximum width (in bytes) for displaying LONG, CLOB, NCLOB and XMLType values; and for copying LONG values.
  • LONGCHUNKSIZE : Sets the size (in bytes) of the increments in which SQL*Plus retrieves a LONG, CLOB, NCLOB or XMLType value.


아래와 같이 LONG, LONGCHUNKSIZE 시스템 변수의 값을 변경하며 AUTOTRACE를 통해 실행 통계 값을 수집하자.[각주:1]

-- 2
SET AUTOTRACE TRACEONLY

-- 2-1
SET LONG 80
SET LONGCHUNKSIZE 80

SELECT * FROM t1;

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

-- 2-2
SET LONG 80
SET LONGCHUNKSIZE 800

/

Statistics
----------------------------------------------------------
        306  consistent gets
        400  physical reads
      48460  bytes sent via SQL*Net to client

-- 2-3
SET LONG 80
SET LONGCHUNKSIZE 8000

/

Statistics
----------------------------------------------------------
        306  consistent gets
        400  physical reads
      48460  bytes sent via SQL*Net to client

-- 2-4
SET LONG 800
SET LONGCHUNKSIZE 80

/

Statistics
----------------------------------------------------------
       2106  consistent gets
       4000  physical reads
     194260  bytes sent via SQL*Net to client

-- 2-5
SET LONG 800
SET LONGCHUNKSIZE 800

/

Statistics
----------------------------------------------------------
        306  consistent gets
        400  physical reads
     192460  bytes sent via SQL*Net to client

-- 2-6
SET LONG 800
SET LONGCHUNKSIZE 8000

/

Statistics
----------------------------------------------------------
        306  consistent gets
        400  physical reads
     192460  bytes sent via SQL*Net to client

-- 2-7
SET LONG 8000
SET LONGCHUNKSIZE 80

/

Statistics
----------------------------------------------------------
      20106  consistent gets
      40200  physical reads
    1652260  bytes sent via SQL*Net to client

-- 2-8
SET LONG 8000
SET LONGCHUNKSIZE 800

/

Statistics
----------------------------------------------------------
       2106  consistent gets
       4200  physical reads
    1634260  bytes sent via SQL*Net to client

-- 2-9
SET LONG 8000
SET LONGCHUNKSIZE 8000

/

Statistics
----------------------------------------------------------
        306  consistent gets
        600  physical reads
    1632460  bytes sent via SQL*Net to client


아래는 수집된 실행 통계 값을 정리한 표다. 가로축이 LONG, 세로축이 LONGCHUNKSIZE 설정값이다. 설정값에 따라 I/O가 변경되는 것을 확인할 수 있다. 실제 저장된 값의 길이를 감안하여 적절한 LONGCHUNKSIZE 값을 설정해야 불필요한 I/O 발생을 방지할 수 있다.

-- 3-1 consistent gets
-----------------------------
|      |  80 |  800 |  8000 |
-----------------------------
|   80 | 306 | 2106 | 20106 |
|  800 | 306 |  306 |  2106 |
| 8000 | 306 |  306 |   306 |
-----------------------------

-- 3-2 physical reads
-----------------------------
|      |  80 |  800 |  8000 |
-----------------------------
|   80 | 400 | 4000 | 40200 |
|  800 | 400 |  400 |  4200 |
| 8000 | 400 |  400 |   600 |
-----------------------------

-- 3-3 bytes sent via SQL*Net to client
------------------------------------
|      |    80 |     800 |    8000 |
------------------------------------
|   80 | 48460 |  194260 | 1652260 |
|  800 | 48460 |  194260 | 1652260 |
| 8000 | 48460 |  194260 | 1652260 |
------------------------------------


아래는 ARRAYSIZE, LONGCHUNKSIZE 시스템 변수에 해당하는 Java 메소드다.

  • ARRAYSIZE ~= setFetchSize
  • LONGCHUNKSIZE ~= defaultLobPrefetchSize, setLobPrefetchSize



  1. LONG 설정 값을 실제로 저장된 값의 평균 길이로 가정하자. [본문으로]

'Oracle > Administration' 카테고리의 다른 글

Online Statistics Gathering for Bulk Loads  (0) 2018.09.29
CLOB #4 - EMPTY_CLOB 함수  (0) 2018.07.12
CLOB #2 - NVL 함수  (0) 2018.06.23
CLOB #1 - BASICFILE vs SECUREFILE  (0) 2018.06.23
UNIQUE 인덱스와 NON-UNIQUE 인덱스  (0) 2018.06.18
Posted by 정희락_