Online Statistics Gathering for Bulk Loads

12.1 버전부터 APPEND 힌트로 direct-path INSERT를 수행하거나, CTAS로 테이블을 생성하면 Online Statistics Gathering for Bulk Loads 기능이 동작한다. 해당 기능은 Bulk Load 시 테이블 통계 정보를 생성한다.

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

1 2 3 4 5 6

-- 1 DROP TABLE t1 PURGE; DROP TABLE t2 PURGE; DROP TABLE t3 PURGE;   CREATE TABLE t1 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 10');

아래 쿼리의 실행 계획에서 OPTIMIZER STATISTICS GATHERING 오퍼레이션을 확인할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

-- 2-1 INSERT /*+ APPEND */ INTO t1 SELECT * FROM t1;   ------------------------------------------------- | Id  | Operation                        | Name | ------------------------------------------------- |   0 | INSERT STATEMENT                 |      | |   1 |  LOAD AS SELECT                  | T1   | |   2 |   OPTIMIZER STATISTICS GATHERING |      | -- ! |   3 |    TABLE ACCESS FULL             | T1   | -------------------------------------------------   -- 2-2 CREATE TABLE t2 AS SELECT * FROM t1;   ------------------------------------------------- | Id  | Operation                        | Name | ------------------------------------------------- |   0 | CREATE TABLE STATEMENT           |      | |   1 |  LOAD AS SELECT                  | T2   | |   2 |   OPTIMIZER STATISTICS GATHERING |      | -- ! |   3 |    TABLE ACCESS FULL             | T1   | -------------------------------------------------

NO_GATHER_OPTIMIZER_STATISTICS 힌트를 사용하거나, _optimizer_gather_stats_on_load 파라미터를 FALSE로 설정하면 기능을 비활성화할 수 있다. 아래 쿼리에서 OPTIMIZER STATISTICS GATHERING 오퍼레이션이 사라진 것을 확인할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

-- 3-1 CREATE TABLE t2 AS SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */ * FROM t1;   --------------------------------------- | Id  | Operation              | Name | --------------------------------------- |   0 | CREATE TABLE STATEMENT |      | |   1 |  LOAD AS SELECT        | T2   | |   2 |   TABLE ACCESS FULL    | T1   | ---------------------------------------   -- 3-2 ALTER SESSION SET "_optimizer_gather_stats_on_load" = FALSE;   세션이 변경되었습니다.   CREATE TABLE t2 AS SELECT * FROM t1;   --------------------------------------- | Id  | Operation              | Name | --------------------------------------- |   0 | CREATE TABLE STATEMENT |      | |   1 |  LOAD AS SELECT        | T2   | |   2 |   TABLE ACCESS FULL    | T1   | ---------------------------------------

힌트와 파라미터 정보는 아래와 같다.

1 2 3 4 5 6 7 8 9 10 11 12 13

-- 4-1 SELECT name, inverse, version FROM v$sql_hint WHERE name = 'GATHER_OPTIMIZER_STATISTICS';   NAME                        INVERSE                        VERSION --------------------------- ------------------------------ -------- GATHER_OPTIMIZER_STATISTICS NO_GATHER_OPTIMIZER_STATISTICS 12.1.0.1   1개의 행이 선택되었습니다.   -- 4-2 NAME                            DEFAULT_VALUE DESCRIPTION ------------------------------- ------------- ------------------------------------------ _optimizer_gather_stats_on_load TRUE          enable/disable online statistics gathering

아래와 같이 테이블을 생성해보자. t2는 테이블 통계 정보가 생성되었고, t3는 NO_GATHER_OPTIMIZER_STATISTICS 힌트로 인해 통계 정보가 생성되지 않았다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

-- 5-1 ALTER SESSION SET "_optimizer_gather_stats_on_load" = TRUE;   세션이 변경되었습니다.   CREATE TABLE t2 AS SELECT * FROM t1; CREATE TABLE t3 AS SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */ * FROM t1;   -- 5-2 SELECT table_name, num_rows, sample_size FROM user_tables WHERE table_name in ('T2', 'T3');   TABLE_NAME NUM_ROWS SAMPLE_SIZE ---------- -------- ----------- T2               10          10 T3   2 행이 선택되었습니다.

다시 Bulk Load를 수행해도 통계 정보가 갱신되지 않는다. 해당 기능은 통계 정보가 없는 빈 세그먼트에 대해서만 동작한다.

1 2 3 4 5 6 7 8 9 10 11 12

-- 6 INSERT /*+ APPEND */ INTO t2 SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 20;   20 행이 생성되었습니다.   SELECT num_rows, sample_size FROM user_tables WHERE table_name = 'T2';   NUM_ROWS SAMPLE_SIZE -------- -----------       10          10   1개의 행이 선택되었습니다.

통계 정보를 삭제하고, 테이블을 TRUNCATE한 후 Bulk Load를 수행해보자. 통계 정보가 수집된 것을 확인할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

-- 7-1 EXEC DBMS_STATS.DELETE_TABLE_STATS ('TUNA', 'T2');   PL/SQL 처리가 정상적으로 완료되었습니다.   TRUNCATE TABLE t2;   테이블이 잘렸습니다.   -- 7-2 INSERT /*+ APPEND */ INTO t2 SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 20;   20 행이 생성되었습니다.   SELECT num_rows, sample_size FROM user_tables WHERE table_name = 'T2';   NUM_ROWS SAMPLE_SIZE -------- -----------       20          20   1개의 행이 선택되었습니다.

파티션 테이블에 대한 해당 기능의 동작을 살펴보자. 테스트를 위해 아래와 같이 테이블을 생성하자.

1 2 3 4 5 6 7 8

-- 8 DROP TABLE t4 PURGE;   CREATE TABLE t4 (c1 NUMBER) PARTITION BY RANGE (c1) (     PARTITION p1 VALUES LESS THAN (11)   , PARTITION p2 VALUES LESS THAN (21)   , PARTITION p3 VALUES LESS THAN (MAXVALUE));

아래와 같이 데이터를 입력해보자. 첫 번째 쿼리는 테이블 레벨에서 p1 파티션에 10행, 두 번째 쿼리는 테이블 레벨에서 p2 파티션에 10행, 세 번째 쿼리는 파티션 레벨에서 p3 파티션에 30행을 입력한다.

1 2 3 4 5 6 7 8 9

-- 9 INSERT /*+ APPEND */ INTO t4 SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10; COMMIT;   INSERT /*+ APPEND */ INTO t4 SELECT LEVEL + 10 FROM DUAL CONNECT BY LEVEL <= 10; COMMIT;   INSERT /*+ APPEND */ INTO t4 PARTITION (p3) SELECT LEVEL + 20 FROM DUAL CONNECT BY LEVEL <= 30; COMMIT;

테이블 통계 정보는 10행, 파티션 통계 정보는 p3 파티션만 30행으로 수집되었다. 테이블 통계 정보는 최초 Bulk Load 시 수집되고, 파티션 통계 정보는 파티션을 지정했을 때만 수집된다는 사실을 유추할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

-- 10-1 SELECT num_rows, sample_size FROM user_tables WHERE table_name = 'T4';   NUM_ROWS SAMPLE_SIZE -------- -----------       10          10   1개의 행이 선택되었습니다.   -- 10-2 SELECT partition_name, num_rows, sample_size FROM user_tab_partitions WHERE table_name = 'T4';   PARTITION_NAME NUM_ROWS SAMPLE_SIZE -------------- -------- ----------- P1 P2 P3                   30          30   3 행이 선택되었습니다.

해당 기능은 인덱스 통계 정보와 히스토그램을 생성하지 않는다. 관련 통계 정보가 필요하다면 아래와 같이 직접 통계 정보를 수집해야 한다.

1 2	-- 11 EXEC DBMS_STATS.GATHER_TABLE_STATS ('TUNA', 'T1', options => 'GATHER AUTO');

배치 프로그램에서 중간 집계 테이블을 사용하는 경우 통계 정보 부재로 인해 비효율적인 실행 계획이 수립되는 경우가 많았다. 해당 기능을 통해 관련 이슈가 해소될 수 있을 것으로 기대된다.

[참조]

Oracle Database SQL Tuning Guide - Online Statistics Gathering for Bulk Loads

ORACLE-BASE - Online Statistics Gathering for Bulk Loads in Oracle Database 12c Release 1 (12.1)