개요

방법

A. 예제 스크립트

A.1 파티션 Table의 통계 수집

A.1.1 create_table_partition.sql

다음은 Partition Table을 생성하는 예입니다.

CREATE TABLE DAILY_LOG (
   LOG_DAY VARCHAR2(8),
   C1 NUMBER,
   C2 NUMBER,
   C3 CHAR(2000),
   C4 CHAR(2000),
   C5 CHAR(2000)   )
   PARTITION BY RANGE (LOG_DAY)
   (
     PARTITION P20230101 VALUES LESS THAN ('20230102'),
     PARTITION P20230102 VALUES LESS THAN ('20230103'),
     PARTITION P20230103 VALUES LESS THAN ('20230104'),
     PARTITION P20230104 VALUES LESS THAN ('20230105'),
     PARTITION P20230105 VALUES LESS THAN ('20230106'),
     PARTITION P20230106 VALUES LESS THAN ('20230107'),
     PARTITION P20230107 VALUES LESS THAN ('20230108'),
     PARTITION P20230108 VALUES LESS THAN ('20230109'),
     PARTITION P20230109 VALUES LESS THAN ('20230110'),
     PARTITION P20230110 VALUES LESS THAN ('20230111'),
     PARTITION P20230111 VALUES LESS THAN ('20230112'),
     PARTITION P20230112 VALUES LESS THAN ('20230113'),
     PARTITION P20230113 VALUES LESS THAN ('20230114'),
     PARTITION P20230114 VALUES LESS THAN ('20230115'),
     PARTITION P20230115 VALUES LESS THAN ('20230116'),
     PARTITION P20230116 VALUES LESS THAN ('20230117'),
     PARTITION P20230117 VALUES LESS THAN ('20230118'),
     PARTITION P20230118 VALUES LESS THAN ('20230119'),
     PARTITION P20230119 VALUES LESS THAN ('20230120'),
     PARTITION P20230120 VALUES LESS THAN ('20230121')
   );
 
 BEGIN
   FOR i IN 1..20 LOOP
     INSERT INTO DAILY_LOG
     SELECT '202301'||LPAD(i,2,'0'), LEVEL, LEVEL, LEVEL, LEVEL, LEVEL
     FROM DUAL CONNECT BY LEVEL <= 10000;
     COMMIT;
   END LOOP;
 END;
 /

A.1.2 create_index.sql

다음은 Index를 생성하는 예입니다.

CREATE INDEX DAILY_LOG_IDX_01 ON DAILY_LOG (LOG_DAY) LOCAL;
CREATE INDEX DAILY_LOG_IDX_02 ON DAILY_LOG (LOG_DAY, C1) LOCAL;
CREATE INDEX DAILY_LOG_IDX_03 ON DAILY_LOG (LOG_DAY, C1, C2) LOCAL;
CREATE INDEX DAILY_LOG_IDX_04 ON DAILY_LOG (LOG_DAY, C3) LOCAL;

A.1.3 add_partition.sql

다음은 파티션을 추가하는 예입니다.

ALTER TABLE DAILY_LOG ADD PARTITION P20230121 VALUES LESS THAN ('20250122');
INSERT INTO DAILY_LOG SELECT '20250121',1,1,1,1,1 FROM DUAL CONNECT BY LEVEL<=10000;
COMMIT;

파티션 P20250101 ~ P20250120까지는 통계 정보가 존재하고 P20250121은 통계 수집을 하지 않았습니다. 따라서 LOG_DAY column 통계 정보도 최대값이 '20250120'일 것입니다.

A.1.4 view_partition_plan.sql

다음은 통계 정보가 있는 파티션과 없는 파티션간의 Plan 차이를 보여주는 예입니다.

set autotrace traceonly exp planstat
ELECT * FROM DAILY_LOG WHERE LOG_DAY='20250120';
SELECT * FROM DAILY_LOG WHERE LOG_DAY='20250121';
set autotrace off

파티션 P20250101 ~ P20250120까지는 통계 정보가 존재하고 P20250121은 통계 수집을 하지 않았습니다. 따라서 LOG_DAY column 통계 정보도 최대값이 '20250120'일 것입니다. 이제 위 두 쿼리는 각가 다른 Plan을 만듭니다.

A.1.5 stats_copy.sql

다음은 COPY_TABLE_STATS를 이용하여 통계 정보 복사 및 Plan을 확인하는 예입니다.

CALL DBMS_STATS.COPY_TABLE_STATS (USER, 'DAILY_LOG', 'P20250120', 'P20250121');
ALTER SYSTEM FLUSH PPC;
set autotrace traceonly exp planstat
SELECT * FROM DAILY_LOG WHERE LOG_DAY='20230120';
SELECT * FROM DAILY_LOG WHERE LOG_DAY='20230121';
set autotrace off

COPY_TABLE_STATS를 통해 P20250121 파티션에 통계 정보가 생기고, 히스토그램도 수정되었다. 이제 두 Plan은 동일하게 FULL TABLE SCAN이 나옵니다.

A.2 Column 통계 정보 수집

A.2.1 create_table_for_histogram.sql

다음은 스키마 및 데이터를 생성하는 예입니다.

 CREATE TABLE ARMY (
   ID   
NUMBER(10),
   NAME VARCHAR2(10),
   JOB  VARCHAR2(20),
   SAL  NUMBER,
   DEPT VARCHAR2(20)
   )
 PCTFREE 90;
 
 INSERT INTO ARMY
   SELECT LEVEL,
     CHR(65+MOD(ABS(DBMS_RANDOM.RANDOM),26))
     ||CHR(65+MOD(ABS(DBMS_RANDOM.RANDOM),26))
     ||CHR(65+MOD(ABS(DBMS_RANDOM.RANDOM),26))
     ||CHR(65+MOD(ABS(DBMS_RANDOM.RANDOM),26))
     ||CHR(65+MOD(ABS(DBMS_RANDOM.RANDOM),26)),
     CASE WHEN MOD(LEVEL,100)<80 THEN 'INFANTRY'
     WHEN MOD(LEVEL,100)<90 THEN 'ARCHER'
     WHEN MOD(LEVEL,100)<95 THEN 'CAVALRY'
     WHEN MOD(LEVEL,100)<98 THEN 'WIZARD'
     ELSE 'GENERAL' END,
     (1+MOD(ABS(DBMS_RANDOM.RANDOM),99))*100,
     CASE MOD(LEVEL,8)
     WHEN 0 THEN 'PROMISE'
     WHEN 1 THEN 'PROMISE'
     WHEN 2 THEN 'RETHINKING'
     WHEN 3 THEN 'INFIELD'
     WHEN 4 THEN 'INFIELD'
     WHEN 5 THEN 'DETAIL1'
     WHEN 6 THEN 'ENDLESS'
     ELSE 'ENDLESS' END
 FROM DUAL CONNECT BY LEVEL<=10000;

 COMMIT;
 
 ALTER TABLE ARMY ADD CONSTRAINT ARMY_PK PRIMARY KEY (ID);

A.2.2 create_index_for_histogram.sql

다음은 Index를 생성하는 예입니다.

 CREATE INDEX ARMY_IDX_NAME ON ARMY (NAME);
 CREATE INDEX ARMY_IDX_JOB ON ARMY (JOB);
 CREATE INDEX ARMY_IDX_SAL ON ARMY (SAL);
 CREATE INDEX ARMY_IDX_DEPT ON ARMY (DEPT);

A.2.3 view_histogram.sql

다음은 생성된 히스토그램을 확인하는 예입니다.

 set linesize 300
 set pagesize 50000
 col name format a10
 col end_point_actual format a50
 col low_val format a50
 col high_val format a50
 
 SELECT C.NAME, HH.BUCKET_CNT, HH.NULL_CNT, HH.DISTINCT_CNT,
   HH.LOW_VAL, HH.HIGH_VAL, HH.DENSITY, HH.AVG_COL_SIZE
 FROM SYS._DD_HIST_HEAD HH, SYS._DD_COL C
 WHERE HH.OBJ_ID = C.OBJ_ID
   AND HH.COL_NO = C.COL_NO
   AND C.OBJ_ID = (SELECT OBJ_ID FROM SYS._DD_OBJ WHERE NAME='ARMY' AND TYPE_NO=1)
   AND C.NAME NOT LIKE 'NOTE%'
   ORDER BY C.COL_NO;
 
 SELECT C.NAME, H.* 
 FROM SYS._DD_HISTOGRAM H, SYS._DD_HIST_HEAD HH, SYS._DD_COL C
 WHERE H.HIST_HEAD_ID = HH.HIST_HEAD_ID
   AND HH.OBJ_ID = C.OBJ_ID
   AND HH.COL_NO = C.COL_NO
   AND C.OBJ_ID = (SELECT OBJ_ID FROM SYS._DD_OBJ WHERE NAME='ARMY' AND TYPE_NO=1)
   AND C.NAME NOT LIKE 'NOTE%'
 ORDER BY C.COL_NO, H.BUCKET;

A.2.4 view_plan_detail_for_histogram.sql

다음은 쿼리 Plan을 확인하는 예입니다.

set lines 150
set autot traceonly exp plans 
SELECT * FROM ARMY WHERE ID <= 100;
SELECT * FROM ARMY WHERE SAL >= 5000; 
SELECT * FROM ARMY WHERE DEPT = 'PROMISE'; 
SELECT * FROM ARMY WHERE JOB = 'INFANTRY';
set autot off

A.2.5 set_default_histogram.sql

다음은 Bucket개수를 1로 수집한 예입니다.

CALL DBMS_STATS.GATHER_TABLE_STATS(USER,'ARMY',estimate_percent=100);

A.2.6 set_histogram.sql

다음은 Bucket 개수를 10으로 수집한 예입니다.

CALL DBMS_STATS.GATHER_TABLE_STATS(USER,'ARMY',
  method_opt='FOR ALL COLUMNS SIZE 10',
  estimate_percent=100);