[Oracle] array size

Oracle fetch array size의 적정크기는 얼마일까.

보통 100 ~ 500을 추천하는데 이유가 뭘까. array size를 늘리면 늘릴수록 I/O가 덜 발생해서 성능에 좋은것 아닌가?

궁금하다.

테스트를 해보자.

테스트 방법 참고자료: https://gerardnico.com/lang/sqlplus/arraysize

테스트 환경.

DB: Oracle 12c

Server: Linux CentOS 7

Tool: SQL Plus

SQL Plus 접속

oracle@ps1team:/home/oracle> sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 2 22:17:55 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

테스트 테이블 생성

SQL> create table t_test  as select * from all_objects;

Table created.

쿼리 결과대신 trace 결과만 출력하도록 셋팅

SQL> set autotrace TRACEONLY

array size 2

SQL> set arraysize 2   

SQL> select * from t_test;

72098 rows selected.

Execution Plan

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

Plan hash value: 2796558804

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

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |        | 78434 |    35M|   385   (1)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T_TEST | 78434 |    35M|   385   (1)| 00:00:01 |

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

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

Statistics

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

          0  recursive calls

          5  db block gets

      36762  consistent gets

          0  physical reads

          0  redo size

   17090506  bytes sent via SQL*Net to client

     397136  bytes received via SQL*Net from client

      36050  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      72098  rows processed

SQL> select * from t_test;

72098 rows selected.

array size를 2로 했을때 발생한 block I/O (db block gets + consistent gets) 는 36762 건으로, fetch 건수(SQL*NET roundtrips) 36050 과 거의 동일하다.

array size를 점점 늘려가며 테스트를 해보자.

SQL> set arraysize 5

SQL> select * from t_test;

72098 rows selected.

Execution Plan

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

Plan hash value: 2796558804

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

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |        | 78434 |    35M|   385   (1)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T_TEST | 78434 |    35M|   385   (1)| 00:00:01 |

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

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

Statistics

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

          0  recursive calls

          5  db block gets

      15553  consistent gets

          0  physical reads

          0  redo size

   12894480  bytes sent via SQL*Net to client

     159217  bytes received via SQL*Net from client

      14421  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      72098  rows processed

array size 100

SQL> set arraysize 100

SQL> select * from t_test;

72098 rows selected.

Execution Plan

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

Plan hash value: 2796558804

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

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |        | 78434 |    35M|   385   (1)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T_TEST | 78434 |    35M|   385   (1)| 00:00:01 |

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

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

Statistics

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

          0  recursive calls

          5  db block gets

       2132  consistent gets

          0  physical reads

          0  redo size

   10236874  bytes sent via SQL*Net to client

       8528  bytes received via SQL*Net from client

        722  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      72098  rows processed

...

...

array size 5000

SQL> set arraysize 5000

SQL> select * from t_test;

72098 rows selected.

Execution Plan

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

Plan hash value: 2796558804

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

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |        | 78434 |    35M|   385   (1)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T_TEST | 78434 |    35M|   385   (1)| 00:00:01 |

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

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

Statistics

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

          0  recursive calls

          5  db block gets

       1439  consistent gets

          0  physical reads

          0  redo size

   10099910  bytes sent via SQL*Net to client

        762  bytes received via SQL*Net from client

         16  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      72098  rows processed

다음과 같이 array size를 조절하며 테스트를 해봤다.

테스트 결과에서 알 수 있듯이

array size를 무작정 늘린다고 해서 block I/O 횟수가 같은 비율로 줄어들지 않는다.

위 테스트 데이터의 경우 array size 100이 넘어갈 경우 급격하게 효율이 떨어지는 것을 알수있다.

array size를 크게 잡을 경우 어플리케이션단 메모리에 부담이 된다.

늘어나는 메모리에 비해 성능차이가 거의 없으므로 100 이 적당한 것 같다.

다만 무작정 100으로 size를 잡는것이 아니라

온라인 서비스용과 배치용, 파일에 내려받는 용 등의 용도에 따라 적절한 size 조절이 필요할듯 하다. 

fetch 횟수와 block I/O가 다른 이유 및 전반적인 튜닝 정보는 다음 링크를 참고한다.

데이터 전문가 지식포털 - 데이터베이스 Call과 네트워크 부하

한국데이터 진흥원 -  Oracle Optimizer의 원리 이해 및 SQL & 애플리케이션의 튜닝(하): 오라클의 튜닝 기법의 100% 활용