[GOLDILOCKS] ANALYZE 구문으로 옵티마이저 최적화하기

GOLDILOCKS 는 ANALYZE TABLE, ANALYZE SYSTEM 구문으로 OPTIMIZER 가 최적화된 플랜을 만드는데 도움을 줄 수 있습니다.

ANALYZE SYSTEM

시스템의 통계 정보를 분석합니다.

1. CPU 가 초당 처리할 수 있는 오퍼레이션 수

2. NETWORK 가 초당 처리할 수 있는 오퍼레이션 수 ( 클러스터 인 경우 )

를 측정합니다.

ANALYZE SYSTEM 은 장비나, 네트워크가 변경되는 경우 한번만 수행하면 됩니다.

분석된 데이터는 DBA_STAT_SYSTEM 테이블에 저장됩니다.

gSQL> ANALYZE SYSTEM COMPUTE STATISTICS;

analyzed.

gSQL> SELECT * FROM DBA_STAT_SYSTEM;

  CPU_OPS NETWORK_IOPS NETWORK_BUFSIZE LAST_ANALYZED
--------- ------------ --------------- --------------------------
144207812         null            null 2018-06-20 23:43:54.530000

1 row selected.

ANALYZE SYSTEM 구문으로 분석된 데이터를 제거하는 경우 DELETE 구문으로 지울 수 있습니다.

gSQL> ANALYZE SYSTEM DELETE STATISTICS;

analyzed.

gSQL> SELECT * FROM DBA_STAT_SYSTEM;

no rows selected.

ANALYZE TABLE

테이블의 통계 정보를 분석합니다.

1. 전체 데이터 카운트

2. 각 컬럼의 통계정보

3. 인덱스의 통계정보

를 측정합니다.

ANALYZE TABLE 은 처음 수행 이후, 데이터의 유형이 급격하게 변하는 경우 수행하면 됩니다.

분석된 데이터는 USER_TABLES, USER_TAB_COLUMNS, USER_INDEXES 테이블에 저장됩니다.

PF_TEST 테이블의 컬럼정보가 다음과 같을 때, ANALYZE TABLE 구문을 수행하면 분석된 데이터가 아래 테이블에 들어갑니다.

gSQL> DESC PF_TEST

COLUMN_NAME TYPE                           IS_NULLABLE
----------- ------------------------------ -----------
C1          NUMBER(10,0)                   FALSE
C2          NUMBER(10,0)                   TRUE
C3          NUMBER(10,0)                   TRUE
C4          NUMBER(18,0)                   TRUE
C5          NUMBER(18,5)                   TRUE
C6          NUMBER(10,0)                   TRUE
C7          NUMBER(10,0)                   TRUE
C8          NUMBER(10,0)                   TRUE
C9          NUMBER(10,0)                   TRUE
C10         CHARACTER(30)                  TRUE
C11         CHARACTER(30)                  TRUE
C12         CHARACTER(30)                  TRUE
C13         CHARACTER(30)                  TRUE
C14         CHARACTER VARYING(100)         TRUE
C15         CHARACTER VARYING(100)         TRUE
C16         CHARACTER VARYING(4000)        TRUE
C17         CHARACTER VARYING(100)         TRUE
C18         CHARACTER VARYING(500)         TRUE
C19         DATE                           TRUE
C20         TIMESTAMP(6) WITHOUT TIME ZONE TRUE

INDEX_NAME                TABLESPACE_NAME INDEX_TYPE IS_UNIQUE COLUMNS
------------------------- --------------- ---------- --------- -------
PF_TEST_PRIMARY_KEY_INDEX MEM_TEMP_TBS    BTREE      TRUE      C1

CONSTRAINT_NAME     CONSTRAINT_TYPE ASSOCIATED_INDEX          COLUMNS
------------------- --------------- ------------------------- -------
PF_TEST_PRIMARY_KEY PRIMARY KEY     PF_TEST_PRIMARY_KEY_INDEX C1

gSQL> ANALYZE TABLE PF_TEST;

Table analyzed.

gSQL> SELECT TABLE_NAME, NUM_ROWS FROM DICTIONARY_SCHEMA.USER_TABLES WHERE TABLE_NAME = 'PF_TEST';

TABLE_NAME NUM_ROWS
---------- --------
PF_TEST         301

1 row selected.

gSQL> SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, LOW_VALUE, HIGH_VALUE FROM DICTIONARY_SCHEMA.USER_TAB_COLUMNS WHERE TABLE_NAME = 'PF_TEST';

TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS LOW_VALUE                      HIGH_VALUE
---------- ----------- ------------ --------- ------------------------------ ------------------------------
PF_TEST    C1                   301         0 1                              301
PF_TEST    C2                   300         1 1                              300
PF_TEST    C3                   300         1 1                              300
PF_TEST    C4                   300         1 1                              300
PF_TEST    C5                   300         1 1                              300
PF_TEST    C6                   300         1 1                              300
PF_TEST    C7                   300         1 1                              300
PF_TEST    C8                   300         1 1                              300
PF_TEST    C9                   300         1 1                              300
PF_TEST    C10                  301         0 1                              MOZI
PF_TEST    C11                    1         1 JDBC Column 4 Performance TEST JDBC Column 4 Performance TEST
PF_TEST    C12                    1         1 JDBC Column 5 Performance TEST JDBC Column 5 Performance TEST
PF_TEST    C13                    1         1 JDBC Column 6 Performance TEST JDBC Column 6 Performance TEST
PF_TEST    C14                    1         1 null                           null
PF_TEST    C15                    1         1 null                           null
PF_TEST    C16                    1         1 null                           null
PF_TEST    C17                    1         1 null                           null
PF_TEST    C18                    1         1 null                           null
PF_TEST    C19                    1         1 2018-06-20 23:53:33            2018-06-20 23:53:33
PF_TEST    C20                    1         1 2018-06-20 23:53:33.374000     2018-06-20 23:53:33.374000

20 rows selected.

gSQL> SELECT TABLE_NAME, INDEX_NAME, DISTINCT_KEYS FROM DICTIONARY_SCHEMA.USER_INDEXES WHERE TABLE_NAME = 'PF_TEST';

TABLE_NAME INDEX_NAME                DISTINCT_KEYS
---------- ------------------------- -------------
PF_TEST    PF_TEST_PRIMARY_KEY_INDEX           301

1 row selected.

ANALYZE TABLE 의 단점

옵티마이저의 최적화를 위해 사용되지 않는 주로 CHAR, VARCHAR 타입의 데이터들도 통계정보에 들어가게 되므로 불필요한 작업이 발생할 수 있습니다.

ANALYZE TABLE 에 옵션을 부여하여, 위의 문제점을 해결할 수 있습니다.

해결방법 1. 지정한만큼의 샘플데이터를 이용해 통계정보 구축

  지정한 비율만큼 샘플 혹은 지정한 레코드 수만큼 샘플을 사용합니다.

ANALYZE TABLE lineitem ESTIMATE STATISTICS SAMPLE 10 PERCENT;
ANALYZE TABLE lineitem ESTIMATE STATISTICS SAMPLE 100000 ROWS;

해결방법 2. 주요 컬럼과 인덱스에 대해서만 통계정보 구축 (권장)

ANALYZE TABLE lineitem COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
ANALYZE TABLE lineitem COMPUTE STATISTICS FOR ALL INDEXES;