Oracle/Tuning2019. 2. 27. 09:01

CLOB #6 - 블록 I/O

CLOB 타입을 사용한 쿼리는 블록 I/O 분석에 주의가 필요하다. 관련 내용을 살펴보자.


테스트를 위해 아래와 같이 테이블을 생성하자. t1 테이블은 BASICFILE, t2 테이블은 SECUREFILE로 생성했다. t2 테이블만 16블록이고, 나머지 테이블을 8블록이다.

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

CREATE TABLE t1 LOB (c2) STORE AS BASICFILE (DISABLE STORAGE IN ROW)
AS
SELECT ROWNUM AS c1, TO_CLOB ('X') AS c2 FROM XMLTABLE ('1 to 1000');

CREATE TABLE t2 LOB (c2) STORE AS SECUREFILE (DISABLE STORAGE IN ROW)
AS
SELECT * FROM t1;

CREATE TABLE t3 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 200');
CREATE TABLE t4 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 100');

-- 1-2
SELECT segment_name, blocks
  FROM user_segments
 WHERE segment_name IN ('T1', 'T2', 'T3', 'T4');

SEGMENT_NAME BLOCKS
------------ ------
T1                8
T2               16
T3                8
T4                8

4 행이 선택되었습니다.

t1, t2 테이블의 전체 열을 조회하면 행의 건수인 1000개의 블록 I/O가 추가로 발생한다. CLOB 값을 조회하기 위한 랜던 I/O다.
-- 2-1
SET ARRAYSIZE 1000

SELECT * FROM t1;

-----------------------------------------------------
| Id  | Operation         | Name | A-Rows | Buffers |
-----------------------------------------------------
|   0 | SELECT STATEMENT  |      |   1000 |    1008 |
|   1 |  TABLE ACCESS FULL| T1   |   1000 |    1008 | -- !
-----------------------------------------------------

-- 2-2
SELECT * FROM t2;

-----------------------------------------------------
| Id  | Operation         | Name | A-Rows | Buffers |
-----------------------------------------------------
|   0 | SELECT STATEMENT  |      |   1000 |    1012 |
|   1 |  TABLE ACCESS FULL| T1   |   1000 |    1012 | -- !
-----------------------------------------------------

c1 열만 조회하면 두 테이블 모두 추가적인 블록 I/O가 발생하지 않는다.

-- 3-1
SELECT c1 FROM t1;

-----------------------------------------------------
| Id  | Operation         | Name | A-Rows | Buffers |
-----------------------------------------------------
|   0 | SELECT STATEMENT  |      |   1000 |      14 |
|   1 |  TABLE ACCESS FULL| T1   |   1000 |      14 |
-----------------------------------------------------

-- 3-2
SELECT c1 FROM t2;

-----------------------------------------------------
| Id  | Operation         | Name | A-Rows | Buffers |
-----------------------------------------------------
|   0 | SELECT STATEMENT  |      |   1000 |      20 |
|   1 |  TABLE ACCESS FULL| T1   |   1000 |      20 |
-----------------------------------------------------

c2 열에 LENGTH 함수를 사용해도 추가적인 블록 I/O가 발생하지 않는 것을 확인할 수 있다. LOB 로케이터에 길이 값이 저장되어 있기 때문인 것으로 보인다.
-- 4-1
SELECT LENGTH (c2) AS c2 FROM t1;

-----------------------------------------------------
| Id  | Operation         | Name | A-Rows | Buffers |
-----------------------------------------------------
|   0 | SELECT STATEMENT  |      |   1000 |      14 |
|   1 |  TABLE ACCESS FULL| T1   |   1000 |      14 |
-----------------------------------------------------

-- 4-2
SELECT LENGTH (c2) AS c2 FROM t2;

-----------------------------------------------------
| Id  | Operation         | Name | A-Rows | Buffers |
-----------------------------------------------------
|   0 | SELECT STATEMENT  |      |   1000 |      20 |
|   1 |  TABLE ACCESS FULL| T1   |   1000 |      20 |
-----------------------------------------------------

아래 Top-N 쿼리는 추가적인 블록 I/O가 표시되지 않는다.

-- 5-1
SELECT *
  FROM (SELECT * FROM t1 ORDER BY c1)
 WHERE ROWNUM <= 100;

-----------------------------------------------------------
| Id  | Operation               | Name | A-Rows | Buffers |
-----------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    100 |       9 |
|*  1 |  COUNT STOPKEY          |      |    100 |       9 |
|   2 |   VIEW                  |      |    100 |       9 |
|*  3 |    SORT ORDER BY STOPKEY|      |    100 |       9 |
|   4 |     TABLE ACCESS FULL   | T1   |   1000 |       9 |
-----------------------------------------------------------

-- 5-2
SELECT *
  FROM (SELECT * FROM t2 ORDER BY c1)
 WHERE ROWNUM <= 100;
-----------------------------------------------------------
| Id  | Operation               | Name | A-Rows | Buffers |
-----------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    100 |      15 |
|*  1 |  COUNT STOPKEY          |      |    100 |      15 |
|   2 |   VIEW                  |      |    100 |      15 |
|*  3 |    SORT ORDER BY STOPKEY|      |    100 |      15 |
|   4 |     TABLE ACCESS FULL   | T2   |   1000 |      15 |
-----------------------------------------------------------

아래 쿼리처럼 Top-N 처리 후 CLOB 열을 WHERE 절에 사용하면 추가적인 블록 I/O가 표시되는 것을 확인할 수 있다.[각주:1] t1 테이블은 500개, t2 테이블은 200개의 추가적인 블록 I/O가 발생한 것으로 표시되는데 LOBREAD 항목이 포함된 수치로 일반적인 논리 I/O와 다른 동작으로 판단된다.[각주:2]
-- 6-1
SELECT *
  FROM (SELECT * FROM t1 WHERE ROWNUM > 0 ORDER BY c1)
 WHERE ROWNUM <= 100
   AND c2 LIKE '%';

-------------------------------------------------------------------
| Id  | Operation              | Name | A-Rows | Buffers | Reads  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |    100 |     509 |    100 |
|*  1 |  COUNT STOPKEY         |      |    100 |     509 |    100 |
|*  2 |   VIEW                 |      |    100 |     509 |    100 | -- !
|   3 |    SORT ORDER BY       |      |    100 |       9 |      0 |
|   4 |     COUNT              |      |   1000 |       9 |      0 |
|*  5 |      FILTER            |      |   1000 |       9 |      0 |
|   6 |       TABLE ACCESS FULL| T1   |   1000 |       9 |      0 |
-------------------------------------------------------------------

-- 6-2
SELECT *
  FROM (SELECT * FROM t2 WHERE ROWNUM > 0 ORDER BY c1)
 WHERE ROWNUM <= 100
   AND c2 LIKE '%';

-------------------------------------------------------------------
| Id  | Operation              | Name | A-Rows | Buffers | Reads  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |    100 |     215 |    100 |
|*  1 |  COUNT STOPKEY         |      |    100 |     215 |    100 |
|*  2 |   VIEW                 |      |    100 |     215 |    100 | -- !
|   3 |    SORT ORDER BY       |      |    100 |      15 |      0 |
|   4 |     COUNT              |      |   1000 |      15 |      0 |
|*  5 |      FILTER            |      |   1000 |      15 |      0 |
|   6 |       TABLE ACCESS FULL| T2   |   1000 |      15 |      0 |
-------------------------------------------------------------------

아래 쿼리는 테이블 3개를 조인했다. CLOB이 없는 t4 테이블에 추가적인 I/O가 표시된다. 값은 정확히 100개의 블록 I/O다. 조인이 완료된 후에 LOB 로케이터를 통해 CLOB 값을 조회하는 것이다.

-- 7-1
SELECT /*+ ORDERED NO_SWAP_JOIN_INPUTS(C) */
       *
  FROM t1 a, t3 b, t4 c
 WHERE b.c1 = a.c1
   AND c.c1 = b.c1;

-------------------------------------------------------
| Id  | Operation           | Name | A-Rows | Buffers |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    100 |     130 |
|*  1 |  HASH JOIN          |      |    100 |     130 |
|*  2 |   HASH JOIN         |      |    200 |      22 |
|   3 |    TABLE ACCESS FULL| T1   |   1000 |      13 |
|   4 |    TABLE ACCESS FULL| T3   |    200 |       9 |
|   5 |   TABLE ACCESS FULL | T4   |    100 |     108 | -- !
-------------------------------------------------------

-- 7-2
SELECT /*+ ORDERED NO_SWAP_JOIN_INPUTS(C) */
       *
  FROM t2 a, t3 b, t4 c
 WHERE b.c1 = a.c1
   AND c.c1 = b.c1;

-------------------------------------------------------
| Id  | Operation           | Name | A-Rows | Buffers |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    100 |     136 |
|*  1 |  HASH JOIN          |      |    100 |     136 |
|*  2 |   HASH JOIN         |      |    200 |      28 |
|   3 |    TABLE ACCESS FULL| T2   |   1000 |      19 |
|   4 |    TABLE ACCESS FULL| T3   |    200 |       9 |
|   5 |   TABLE ACCESS FULL | T4   |    100 |     108 | -- !
-------------------------------------------------------

아래 쿼리는 집계 함수와 CASE 표현식을 사용했다. SORT AGGREGATE 오퍼레이션에 추가적인 블록 I/O가 표시되는 것을 확인할 수 있다. 6번 쿼리와 동일한 값이 표시된다.

-- 8-1
SELECT /*+ ORDERED NO_SWAP_JOIN_INPUTS(C) */
       COUNT (CASE WHEN a.c2 LIKE '%' THEN 1 END) AS c1
  FROM t1 a, t3 b, t4 c
 WHERE b.c1 = a.c1
   AND c.c1 = b.c1;

-----------------------------------------------------------------
| Id  | Operation            | Name | A-Rows | Buffers | Reads  |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |     531 |    100 |
|   1 |  SORT AGGREGATE      |      |      1 |     531 |    100 | -- !
|*  2 |   HASH JOIN          |      |    100 |      31 |      0 |
|*  3 |    HASH JOIN         |      |    200 |      22 |      0 |
|   4 |     TABLE ACCESS FULL| T1   |   1000 |      13 |      0 |
|   5 |     TABLE ACCESS FULL| T3   |    200 |       9 |      0 |
|   6 |    TABLE ACCESS FULL | T4   |    100 |       9 |      0 |
-----------------------------------------------------------------

-- 8-2
SELECT /*+ ORDERED NO_SWAP_JOIN_INPUTS(C) */
       COUNT (CASE WHEN a.c2 LIKE '%' THEN 1 END) AS c1
  FROM t2 a, t3 b, t4 c
 WHERE b.c1 = a.c1
   AND c.c1 = b.c1;

-----------------------------------------------------------------
| Id  | Operation            | Name | A-Rows | Buffers | Reads  |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |     237 |    100 |
|   1 |  SORT AGGREGATE      |      |      1 |     237 |    100 | -- !
|*  2 |   HASH JOIN          |      |    100 |      37 |      0 |
|*  3 |    HASH JOIN         |      |    200 |      28 |      0 |
|   4 |     TABLE ACCESS FULL| T2   |   1000 |      19 |      0 |
|   5 |     TABLE ACCESS FULL| T3   |    200 |       9 |      0 |
|   6 |    TABLE ACCESS FULL | T4   |    100 |       9 |      0 |
-----------------------------------------------------------------

지금까지 살펴본데로 CLOB의 블록 I/O 표시는 부정확한 면이 있다. 해석에 주의하도록 하자.


ora12cr2_ora_6132_basicfile.trc

ora12cr2_ora_6132_basicfile.prf

ora12cr2_ora_3584_securefile.trc

ora12cr2_ora_3584_securefile.prf


  1. 인라인 뷰 내부의 ROWNUM > 0 조건은 Predicate Pushdown을 방지하기 위해 기술했다. [본문으로]
  2. 상세한 내용은 트레이스 파일과 Tanel Poder의 글(https://blog.tanelpoder.com/2011/03/20/lobread-sql-trace-entry-in-oracle-11-2/)을 참조하자. [본문으로]
저작자표시 비영리 변경금지

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

TIMESTAMP 인덱스 - Right-Growing 경합 #2  (0) 2019.03.06
TIMESTAMP 인덱스 - Right-Growing 경합 #1  (0) 2019.02.28
사용자 정의 함수의 수행 횟수  (0) 2019.01.30
SQL Patch  (0) 2019.01.09
SQL Profile  (0) 2019.01.09
Posted by 정희락_

티스토리툴바