같은 테이블을 두 번 읽었지만 일량이 틀려요

select /*+ full(a) full(b)  leading(a) use_hash(b) */

a.col1, b.col2

  from tab1 a,

       tab1 b

  where a.col1 = b.col2 ;

 

오해와 현실

“위의 SQL을 보면 from 절의 두 테이블은 동일하다. 그리고 건수가 많아서 힌트를 주었으므로, 둘 다 full table scan을 할 것이다. 따라서 위의 SQL을 실행하고 결과를 본다면, a 와 b의 일량(block I/O량)은 동일하다.”라고 알고 있는 사람이 많이 있다. 즉 a를 읽었더니 block I/O 량이 1000 블럭이라면 b를 읽을 때도 1000 블럭이 나올 것이라는 이야기다. 이런 주장이 사실일까? 결론부터 말하자면 사실이 아니다. b쪽이 더 많은 블럭을 scan 해야 한다. 그래서 b쪽을 scan할 때 더 느리다. b쪽에 더 많은 일량이 나온다면 버그라고 생각하는 사람도 있지만, 버그가 아니라 정상적인 결과이다.

 

이 글의 목적

위의 결론에 따르면 후행테이블을 scan 할 때 심각한 성능저하가 발생 할 수 있다. 이런 현상을 주위의 지인들에게 질문한 결과 적절한 이유나 원인을 말하는 사람은 거의 없었다. 성능문제의 원인을 모르면 튜닝을 할 수 없다. 그러므로 이 글에서는 성능이 저하되는 이유를 독자에게 제시하고, 비효율을 해결 할 수 있는 방법을 설명한다. 또한 이런 문제가 발생하지 않는 예외적인 경우도 살펴본다.

 

이제 테스트를 진행하기 위해 테이블을 하나 만들자.

 

create table test1 as

select lpad(level, 5, '0') as num,

       lpad(level, 60, '0') as num_txt

  from dual

connect by level <= 50000 ;

 

인덱스가 없음으로 앞으로 모든 실행계획은 full table scan이 될 것이다. 정확한 분석을 위해 test1 테이블의 full table scan 일량(logical reads)을 알아보자.

 

select count(*)

  from test1;

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name  | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |      1 |      1 |00:00:00.01 |     504 |

|   1 |  SORT AGGREGATE    |       |      1 |      1 |00:00:00.01 |     504 |

|   2 |   TABLE ACCESS FULL| TEST1 |      1 |  50000 |00:00:00.06 |     504 |

----------------------------------------------------------------------------- 

 

full table scan의 결과 일량은 504 블럭이다. 따라서 test1 테이블의 데이터가 변경되지 않는다면 항상 504 블럭이 나와야 한다. 정말 그렇게 될까?

 

아래 SQL의 조인 순서는 a--> b 이다.

 

select /*+ leading(a b) */ a.num

  from test1 a,

       test1 b

  where a.num = b.num

    and a.num > '00100'

    and substr(b.num_txt,  -5) > '00100'; --> substr의 인자 -5는 마지막 다섯 자리라는 뜻이다.

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name  | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |      1 |  49900 |00:00:00.45 |    5998 |

|*  1 |  HASH JOIN         |       |      1 |  49900 |00:00:00.45 |    5998 |

|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.06 |     504 |

|*  3 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.15 |    5494 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NUM"="B"."NUM")

   2 - filter("A"."NUM">'00100')

   3 - filter((SUBSTR("B"."NUM_TXT",(-5))>'00100' AND "B"."NUM">'00100'))

 

무려 11배나 차이가 난다

선행테이블은 정상적으로 504블록이 나왔다. 하지만 이상하게도 선행테이블과 동일한 테이블인 후행테이블( b )의 일량이 약 11배나 많다. 수행시간도 후행테이블이 더 느리다. 같은 테이블을 동일한 방법으로 scan 했는데 왜 Block I/O 수가 11배나 차이가 날까?

 

힌트를 주어 조인 순서를 바꿔보자.

 

select /*+ leading(b a) */ a.num

  from test1 a,

       test1 b

  where a.num = b.num

    and a.num > '00100'

    and substr(b.num_txt,  -5) > '00100';

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name  | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |      1 |  49900 |00:00:00.34 |    5998 |

|*  1 |  HASH JOIN         |       |      1 |  49900 |00:00:00.34 |    5998 |

|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.11 |     504 |

|*  3 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.06 |    5494 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NUM"="B"."NUM")

   2 - filter((SUBSTR("B"."NUM_TXT",(-5))>'00100' AND "B"."NUM">'00100'))

   3 - filter("A"."NUM">'00100')

  

array size가 원인이다

이번에는 반대로 a의 일량이 b보다 11배 많게 나왔다. 즉 일관성 있게 후행테이블의 일량이 11배가 많다. 그 이유는 툴(오렌지)의 array size가 10 으로 되어있었기 때문이다. 다른 말로 바꾸면 array size가 10 이기 때문에 49900건을 모두 출력하려면 4990번 fetch 해야 한다. 즉 위의 일량 5494는 원래의 블록 수인 504에 fetch 회수(4990 블럭)을 더한 것이다. 여기까지는 이해가 될 것인데 문제는 “왜 fetch 할 때마다 한 블록을 더 읽어야 하는가?” 이다.

 

Fetch 할 때마다 이전에 읽었던 1블럭을 더 읽어야 한다

한 블록에 20건이 들어있다고 가정하고, Array size가 10 이라고 치자. 그러면 한 블럭의 데이터(20건)를 모두 출력 하려면 동일한 블럭을 반복적으로 두 번 fetch 해야 한다. 바로 이것이 fetch 할 때마다 이미 읽었던 블럭(직전에 fetch 했던 block중 마지막 block)을 다시 Scan 할 수 밖에 없는 이유이다.

 

비효율을 없애려면 array size를 적정 수준으로 늘려라

 

set arraysize 100 --array size 100으로 변경

 

select /*+ leading(a b) */ a.num

  from test1 a,

       test1 b

  where a.num = b.num

    and a.num > '00100'

    and substr(b.num_txt,  -5) > '00100';

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name  | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |      1 |  49900 |00:00:00.38 |    1507 |

|*  1 |  HASH JOIN         |       |      1 |  49900 |00:00:00.38 |    1507 |

|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.06 |     504 |

|*  3 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.11 |    1003 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NUM"="B"."NUM")

   2 - filter("A"."NUM">'00100')

   3 - filter((SUBSTR("B"."NUM_TXT",(-5))>'00100' AND "B"."NUM">'00100'))

  

array size를 올리자 logical read가 5494 에서 1003 으로 변경되었다. 5배 이상 일량(logical reads 량)이 줄어들었다. 하지만 아직도 원래의 블록 수인 504 보다 두 배정도 많다. 

 

set arraysize 1000 --array size 1000으로 변경

 

select /*+ leading(a b) */ a.num

  from test1 a,

       test1 b

  where a.num = b.num

    and a.num > '00100'

    and substr(b.num_txt,  -5) > '00100';

 

-----------------------------------------------------------------------------

| Id  | Operation          | Name  | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |      1 |  49900 |00:00:00.34 |    1058 |

|*  1 |  HASH JOIN         |       |      1 |  49900 |00:00:00.34 |    1058 |

|*  2 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.06 |     504 |

|*  3 |   TABLE ACCESS FULL| TEST1 |      1 |  49900 |00:00:00.09 |     554 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("A"."NUM"="B"."NUM")

   2 - filter("A"."NUM">'00100')

   3 - filter((SUBSTR("B"."NUM_TXT",(-5))>'00100' AND "B"."NUM">'00100'))

 

array size를 1000으로 올리자 logical read가 1003 에서 554로 변경되었다. 이 정도면 원래의 블럭수인 504와 비슷하다. 554와 504의 차이는 50 블럭이므로 fetch를 50번 했다는 것을 알 수 있다.

 

해결방법
테스트의 결과는 fetch가 발생할 때마다 직전 블럭을 읽어야 함을 알 수 있다. 따라서 array size를 적절히 늘리면 fetch 회수가 줄어들므로 이전 블럭을 읽는 횟수도 같이 줄어든다. 이에 따라 성능도 향상된다. 하지만 array size를 늘려도 선행테이블은 logical read의 변화가 없다. 왜냐하면 선행테이블은 fetch에 영향을 끼치지 못하며, 후행 테이블이 scan 되어 조인에 성공될 때만 데이터가 client로 전송(fetch) 되기 때문이다.

조인이 없을 때도 비효율은 발생한다
이런 현상은 full table scan과 해시조인의 조합에서만 발생하는 것은 아니다. 조인 없이 from 절에 테이블이 하나뿐일 때도 동일하게 발생한다. 아래의 SQL이 전형적인 예제이다.

 

array  size가 10일 때       

 

select num

  from test1;

 

Trace Version   : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Environment     : Array Size = 10

                  Long  Size = 80

********************************************************************************

 

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Parse        1    0.000        0.000          0          0          0          0

Execute      1    0.000        0.000          0          0          0          0

Fetch     5001    0.328        0.219          0       5504          0      50000

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Total     5003    0.328        0.219          0       5504          0      50000

 

Misses in library cache during parse: 0

Optimizer goal: ALL_ROWS

Parsing user: SYS (ID=0)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  STATEMENT

  50000   TABLE ACCESS FULL TEST1 (cr=5504 pr=0 pw=0 time=67049 us cost=143 size=300000 card=50000)

 

fetch를 5001 번 했기 때문에 원래의 블럭수( 504 )에 비해 logical read량도 약 5000 블럭이 늘었다. 
 

array  size가 100일 때

 

Trace Version   : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Environment     : Array Size = 100

                  Long  Size = 80

********************************************************************************

 

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Parse        1    0.000        0.000          0          0          0          0

Execute      1    0.000        0.000          0          0          0          0

Fetch      501    0.063        0.041          0       1004          0      50000

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Total      503    0.063        0.041          0       1004          0      50000

 

Misses in library cache during parse: 1

Optimizer goal: ALL_ROWS

Parsing user: SYS (ID=0)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  STATEMENT

  50000   TABLE ACCESS FULL TEST1 (cr=1004 pr=0 pw=0 time=75254 us cost=143 size=300000 card=50000)

 

Array size가 10인 경우(5504)에 비해 일량이 약 5배 정도 감소했다. 그 이유는 fetch 회수가 10배로 줄어들었기 때문이다.

 

array  size가 1000 일 때

 

Trace Version   : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Environment     : Array Size = 1000

                  Long  Size = 80

 

********************************************************************************

 

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Parse        1    0.000        0.000          0          0          0          0

Execute      1    0.000        0.000          0          0          0          0

Fetch       51    0.031        0.016          0        554          0      50000

------- ------ -------- ------------ ---------- ---------- ---------- ----------

Total       53    0.031        0.017          0        554          0      50000

 

Misses in library cache during parse: 1

Optimizer goal: ALL_ROWS

Parsing user: SYS (ID=0)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  STATEMENT

  50000   TABLE ACCESS FULL TEST1 (cr=554 pr=0 pw=0 time=50383 us cost=143 size=300000 card=50000)        

 

무작정 크게 한다고 좋아지지 않는다

array size를 1000으로 변경하니 array size가 10인 경우(5504 블럭)에 비해 일량이 약 10배 정도 감소했다. 하지만 array size가 100 인 경우와 비교해 보면 일량이 고작 2배 정도만 줄어들었다. 다시 말해 여기서 array size를 더 크게 하더라도 얻는 이익은 별로 없다는 것이다. 따라서 무작정 array size를 늘려서는 안 된다. 메모리에 부하를 줄 뿐만 아니라 한번에 많은 데이터가 client로 전송되므로 네트웍 I/O가 과도 하게 늘어날 수 있다. 따라서 client로 fetch 할 건수가 많고, 네트웍 망의 성능이 좋다면 1000~ 2000 정도를 유지하는 것이 적당하다. 물론 조회 프로그램에서는 페이징 처리를 하는 것이 가장 좋지만, 업무적으로 전체 건을 볼 수 밖에 없는 경우는 array size를 적절히 조절하는 것이 대안이 될 수 있다.

성능문제의 발생조건 
fetch의 비효율은 select문에서만 발생한다. 즉 insert–select 나 CTAS(create table as select) 그리고 merge 문 등에서는 이런 종류의 성능저하가 발생하지 않는다. 왜냐하면 DML문은 select문과 달리 조회(데이터를 client로 fetch) 할 필요가 없고, commit이 되면 바로 종료되기 때문이다.

모든 규칙에 예외는 있다

full table scan + sort merge join 의 조합에서는 fetch의 비효율이 발생하지 않는다. 왜냐하면 full table scan + sort merge join 조합은 hash join의 조합과 달라서 모든 데이터를 sort 해야하기 때문이다. 모든 데이터를 sort하려면 어차피 모든 블럭을 scan해야 하므로 fetch를 여러번 해야만 하는 array size를 사용할 필요가 없는 것이다.  그리고 fetch를 여러번 하지 않기 때문에 항상 일량이 일정하다.

또 다른 예외의 경우는 1 블럭에 1 row만 저장되는 경우이다. 이런 경우는 블럭을 한번만 엑세스 해도 그 블럭의 모든 데이터를 한번에 fetch 할 수 있으므로, 같은 블록을 반복해서 읽을 필요가 없다. 따라서 array size를 변경해도 일량이 달라지지 않는다.

 

호기심이 있는 독자는 아래의 테이블을 만들고 위의 테스트를 똑같이 진행 해보기 바란다. 위의 test 결과와는 다를 것이다.

 

drop table test1 ;

 

create table test1 as

select lpad(level, 5, '0') as num,

       lpad(level, 7000, '0') as num_txt

  from dual

connect by level <= 50000 ;

 
array size 항상 나쁜가?
우리는 array size가 있음으로 해서 부분범위처리를 할 수있다. full table scan을 동반하는 해시조인의 경우에도 중간에 효율적으로 멈출 수 있다. 예를 들어 결과건수가 1억건이며, 만건을 먼저 조회한 후에 다음 만건을 보고 싶다고 할때, 운반단위(array size)가 1000 이라면 10번 fetch 하면 멈출 수 있다. 반면에 array size가 없다면 중간에 멈출 수 없으므로 1억건을 모두 fetch 한후에나 결과를 화면에서 볼 수 있다.

결론

같은 테이블을 두 번 full table scan 하고, 그 둘을 해시조인하면 대부분의 경우 후행 테이블의 I/O량이 더 많다. 그래서 후행테이블을 scan 할 때가 더 느리다. 왜냐하면 직전 fetch 때에 이미 읽었던 block의 데이터가 모두 fetch 되지 않을 수 있으므로 그 블럭을 한번 더 읽어보아야 확인 할 수 있기 때문이다. 이런 비효율이 많이 발생하는 경우는 array size가 작기 때문이다. 따라서 적절한 array size로 늘려주면 성능문제를 해결 할 수 있다. 

fetch의 비효율은 full table scan이나 full table scan + hash join 조합을 사용할 때만 발생하는 것은 아니다. index scan을 할때도 똑같이 비효율이 발생한다.(주1)  즉 fetch의 비효율 문제는 인덱스를 사용할때나 테이블을 scan할때를 가리지 않고 모두 발생한다. 이런 사실들로 미루어 볼때, 위에서 언급한 몇가지의 예외를 제외한다면, 우리는 다음과 같은 결말을 낼 수 있다.

"select문의 결과건수가 많음에도 불구하고, 페이징 처리가 되지 않고, array size가 작은 조회용 프로그램이라면 fetch의 비효율은 존재한다."



주1 : 인덱스 사용시 fetch의 비효율 문제는 이미 책으로 정리가 되어 있으므로 필자가 언급하지 않는다. 이 문제에  관심이 있는 사람은 조동욱 님의 책 Optimizing Oracle Optimizer를 참조하기 바란다.