Online Statistics Gathering for Bulk Loads

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

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

-- 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 오퍼레이션을 확인할 수 있다.

-- 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 오퍼레이션이 사라진 것을 확인할 수 있다.

-- 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   |
---------------------------------------

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

-- 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 힌트로 인해 통계 정보가 생성되지 않았다.

-- 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를 수행해도 통계 정보가 갱신되지 않는다. 해당 기능은 통계 정보가 없는 빈 세그먼트에 대해서만 동작한다.

-- 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를 수행해보자. 통계 정보가 수집된 것을 확인할 수 있다.

-- 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개의 행이 선택되었습니다.

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

-- 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행을 입력한다.

-- 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 시 수집되고, 파티션 통계 정보는 파티션을 지정했을 때만 수집된다는 사실을 유추할 수 있다.

-- 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 행이 선택되었습니다.

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

-- 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)