by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

7. GATHERING AND PUBLISHING STATISTICS INDEPENDENTLY




목차

I. OVERVIEW

II. Pending Statistics

III. 실습 예제

IV. 결론

I. OVERVIEW

1. 10g 이하 통계정보 관리

  • Oracle 10g 이하에서는 통계정보가 수집이 되면 SQL Plan이 변경될 수 있음.
    • DBMS_STAT 패키지의 'INVALIDATE' 파라미터 값이 'Y'면 즉시 Cursor가 Invalidate 되며, 'N'이면 '_optimizer_invalidation_period' 값에 의해 임의 시점에 적용됨.
    • SQL Plan 변경은 통계정보 수집이나 Index 추가에 의해 영향을 받음.
  • 하지만 통계정보 수집이 항상 이전보다 SQL Plan을 좋게 만들지 못하기 때문에, 테스트 DB에서 검증을 하고 이를 운영 DB에 적용을 하는 것이 일반적인 워크로드이나, 테스트 DB가 운영DB와 환경이 100% 일치하지 않기 때문에 완벽한 사전 테스트는 힘듬.

2. 10g에서 통계정보 Export/Import

  • 그리고 통계정보를 운영 시에 새롭게 갱신하게 될 경우 여러 Risk가 있기 때문에, 가급적 Down-time에 갱신을 하여 Risk를 최소화 하는 것이 일반적임.
  • 그러나, 테이블 사이즈가 클 경우 Sampling을 10%로 하여도 수행시간이 오래 걸려, Down-time을 넘기는 경우가 있어 서비스 시간이 지연되는 경우가 있는데, 이를 피하기 위해 통계정보 Export/Import 작업을 통해 해결을 함.
    
    -- 1. 통계정보 담는 테이블 생성 
    EXEC DBMS_STATS.CREATE_STAT_TABLE('OWNER', 'STAT_BACKUP_XSOFT', 'SYSTEM');
    
    -- 2. 통계정보 export 스크립트 
    exec dbms_stats.export_table_stats(ownname=>'OWNER',tabname=>'XSOFT_T',statid=>'XSOFT_T',statown=>'OWNER',stattab=>'STAT_BACKUP_XSOFT',cascade=>true);
    
    -- 3. 통계정보 파일 export
    exp userid=id/password file=stat_backup_xsoft.dmp tables=stat_backup_xsoft
    
    -- 4. 통계정보 파일 import
    imp userid=id/password file=stat_backup_xsoft.dmp tables=stat_backup_xsoft
    
    -- 5. 통계정보 import 스크립트
    EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME=>'OWNER',TABNAME=>'XSOFT_T',STATID=>'XSOFT_T',CASCADE=>TRUE,STATOWN=>'OWNER',STATTAB=>'STAT_BACKUP_XSOFT');
    
    -- 6. 통계정보 담는 테이블 삭제
    DROP TABLE KIC.STAT_BACKUP_XSOFT;
    

3. 11g 통계정보 관리

  • Oracle 11g에서는 'Pending Statistics'라는 통계정보 관리방법을 이용하여, 새로운 통계정보 검증 작업을 운영 DB에 영향을 미치지 않으면서도, 운영 DB에서에서 테스트가 가능함.
  • 통계정보 수집에는 두 가지 옵션이 존재함.
    • 기존 pre-11g 방식처럼 통계정보가 수집되지마자 바로 Plan이 update되어 SQL Plan에 영향을 주는 방식.
    • 통계정보가 수집되자마자 바로 SQL Plan에 반영하지 않고 'Pending Statistics'로 저장하는 방식.
  • 11g에서는 통계정보의 'Gathering'과 'Publish'를 분리할 수 있게 되어, 검증된 통계정보만 'Publish'되도록 하여 어플리케이션 성능을 보장받을 수 있음.

II. Pending Statistics

1. 통계정보 Gathering 및 Publish 절차

  • 통계정보를 'Pending Statistics'로 관리하고 'Publish' 되는 과정
    1) 'Pending Statistics'가 생성되도록 'Attribute' 변경
    2) 통계정보 수집하여 'Pending Statistics'로 저장
    3) 'Pending Statistics'로 저장된 통계정보를 이용한 SQL Plan 검증
    4) 'Pending Statistics'를 Publish하여 'Publish Statistics'로 대체
    

2. Pending Statistics 장점

  • 운영에서 테이블에 대한 통계정보를 미리 테스트 할 수 있음.
  • 통계정보에 대한 검증작업에 대한 신뢰도 향상.
  • 검증된 통계정보만 Publish 함으로써 Application의 성능을 유지 및 개선할 수 있음.

3. 통계정보 Gathering 및 Publish Commands

3-1) Publish Attribute 확인방법

-- 1. 오라클 버전 확인
SELECT *
FROM   v$version;

BANNER                                                                      
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production                                      

-- 2. GET_PREFS 값 확인
SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISH
FROM   DUAL;

PUBLISH      
--------
TRUE         

-- TRUE  : 통계정보가 수집되자마자 바로 Publish
-- FALSE : 수집된 통계정보는 Publish 되지 않고 Pending 
  • DBMS_STATS.GET_PREFS 펑션
    DBMS_STATS.GET_PREFS (pname     IN   VARCHAR2,
                          ownname   IN   VARCHAR2 DEFAULT NULL,
                          tabname   IN   VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2;
    
  • DBMS_STATS.GET_PREFS 펑션 파라미터 설명
    parameter values description
    pname CASCADE he value determines whether or not index statistics are collected as part of gathering table statistics.
      DEGREE The value determines degree of parallelism used for gathering statistics.
      ESTIMATE_PERCENT The value determines the percentage of rows to estimate. The valid range is '0.000001,100'. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.
      METHOD_OPT The value controls column statistics collection and histogram creation. It accepts either of the following options, or both in combination:
    *. FOR ALL 'INDEXED / HIDDEN' COLUMNS 'size_clause'
    *. FOR COLUMNS 'size clause' column/attribute 'size_clause' ',column/attribute 'size_clause'...' size_clause is defined as size_clause := SIZE 'integer / REPEAT / AUTO / SKEWONLY' column is defined as column := column_name / (extension)
      NO_INVALIDATE he value controls the invalidation of dependent cursors of the tables for which statistics are being gathered. Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE to have Oracle decide when to invalidate dependent cursors. This is the default.
      GRANULARITY The value determines granularity of statistics to collect (only pertinent if the table is partitioned).
    *. 'ALL' - gathers all (subpartition, partition, and global) statistics
    *. 'AUTO'- determines the granularity based on the partitioning type. This is the default value.
    *. 'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.
    *. 'GLOBAL' - gathers global statistics
    *. 'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.
    *. 'PARTITION '- gathers partition-level statistics
    *. 'SUBPARTITION' - gathers subpartition-level statistics.
      PUBLISH This value determines whether or not newly gathered statistics will be published once the gather job has completed. Prior to Prior to Oracle Database 11g, Release 1 (11.1), once a statistic gathering job completed the new statistics were automatically published into the dictionary tables. The user now has the ability to gather statistics but not publish them immediately. This allows the DBA to test the new statistics before publishing them.
      INCREMENTAL his value determines whether or not the global statistics of a partitioned table will be maintained without doing a full table scan. With partitioned tables it is very common to load new data into a new partition. As new partitions are added and data loaded, the global table statistics need to be kept up to date. Oracle will update the global table statistics by scanning only the partitions that have been changed instead of the entire table if the following conditions hold:
    *. the INCREMENTAL value for the partitioned table is set to TRUE;
    *. the PUBLISH value for the partitioned table is set to TRUE;
    *. the user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.
      STALE_PERCENT This value determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. The default value is 10%.
    ownname   Owner name
    tabname   Table name
  • 각 속성별 데이터 확인 뷰
    • USER_TAB_STATISTICS, USER_IND_STATISTICS 뷰는 10g에도 존재함
    • USER_TAB_PENDING_STATS, USER_IND_PENDING_STATS 뷰는 11g new feature
      Publish 된 통계정보 저장 테이블 Pending 된 통계정보 저장 테이블
      USER_TAB_STATISTICS USER_TAB_PENDING_STATS
      USER_IND_STATISTICS USER_IND_PENDING_STATS
  • 4개 뷰 데이터 확인하는 스크립트
    
    -- 1. TABLE
    CREATE TABLE XSOFT_T (VAL NUMBER);
    
    INSERT INTO XSOFT_T
    SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 1000;
    
    COMMIT;
    
    -- 2. INDEX
    CREATE INDEX XSOFT_T_N1 ON XSOFT_T(VAL);
    
    -- 3. 통계정보 생성
    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER, TABNAME=>'XSOFT_T') ;
    
    -- 4. USER_TAB_STATISTICS 확인
    SELECT *
    FROM   USER_TAB_STATISTICS
    WHERE  TABLE_NAME = 'XSOFT_T'
    ;
    
    COLUMN                         VALUE                            
    ------------------------------ ------------------------------   
    TABLE_NAME                     XSOFT_T                          
    PARTITION_NAME                                                  
    PARTITION_POSITION                                              
    SUBPARTITION_NAME                                               
    SUBPARTITION_POSITION                                           
    OBJECT_TYPE                    TABLE                            
    NUM_ROWS                       1000                             
    BLOCKS                         5                                
    EMPTY_BLOCKS                   0                                
    AVG_SPACE                      0                                
    CHAIN_CNT                      0                                
    AVG_ROW_LEN                    4                                
    AVG_SPACE_FREELIST_BLOCKS      0                                
    NUM_FREELIST_BLOCKS            0                                
    AVG_CACHED_BLOCKS                                               
    AVG_CACHE_HIT_RATIO                                             
    SAMPLE_SIZE                    1000                             
    LAST_ANALYZED                  2011-10-12 오후 6:28:33          
    GLOBAL_STATS                   YES                              
    USER_STATS                     NO                               
    STATTYPE_LOCKED                                                 
    STALE_STATS                    NO                               
    ;
    
    -- 5. USER_IND_STATISTICS 확인
    SELECT *
    FROM   USER_IND_STATISTICS
    WHERE  TABLE_NAME = 'XSOFT_T'
    ;
    
    COLUMN                         VALUE                            
    ------------------------------ -----------------------------
    INDEX_NAME                     XSOFT_N1                     
    TABLE_OWNER                    USER                      
    TABLE_NAME                     XSOFT_T                      
    PARTITION_NAME                                              
    PARTITION_POSITION                                          
    SUBPARTITION_NAME                                           
    SUBPARTITION_POSITION                                       
    OBJECT_TYPE                    INDEX                        
    BLEVEL                         1                            
    LEAF_BLOCKS                    2                            
    DISTINCT_KEYS                  1000                         
    AVG_LEAF_BLOCKS_PER_KEY        1                            
    AVG_DATA_BLOCKS_PER_KEY        1                            
    CLUSTERING_FACTOR              2                            
    NUM_ROWS                       1000                         
    AVG_CACHED_BLOCKS                                           
    AVG_CACHE_HIT_RATIO                                         
    SAMPLE_SIZE                    1000                         
    LAST_ANALYZED                  2011-10-12 오후 6:28:33      
    GLOBAL_STATS                   YES                          
    USER_STATS                     NO                           
    STATTYPE_LOCKED                                             
    STALE_STATS                    NO                           
    ;
    
    -- 6. USER_TAB_PENDING_STATS 확인
    SELECT *
    FROM   USER_TAB_PENDING_STATS
    WHERE  TABLE_NAME = 'XSOFT_T'
    ;
    
    COLUMN                         VALUE                        
    ------------------------------ ------------------------------ 
    TABLE_NAME                                                    
    PARTITION_NAME                                                
    SUBPARTITION_NAME                                             
    NUM_ROWS                                                      
    BLOCKS                                                        
    AVG_ROW_LEN                                                   
    SAMPLE_SIZE                                                   
    LAST_ANALYZED                                                                                        
    ;
    
    -- 7. USER_IND_PENDING_STATS 확인
    SELECT *
    FROM   USER_IND_PENDING_STATS
    WHERE  TABLE_NAME = 'XSOFT_T'
    ;
    
    COLUMN                         VALUE                       
    ------------------------------ ------------------------------
    INDEX_NAME                                                   
    TABLE_OWNER                                                  
    TABLE_NAME                                                   
    PARTITION_NAME                                               
    SUBPARTITION_NAME                                            
    BLEVEL                                                       
    LEAF_BLOCKS                                                  
    DISTINCT_KEYS                                                
    AVG_LEAF_BLOCKS_PER_KEY                                      
    AVG_DATA_BLOCKS_PER_KEY                                      
    CLUSTERING_FACTOR                                            
    NUM_ROWS                                                     
    SAMPLE_SIZE                                                  
    LAST_ANALYZED                                                
    
3-2) Publish Attribute 설정을 변경하는 방법
  • Publish 속성변경은 Schema Level 또는 Table Level에서 변경 가능.
  • 아래 스크립트는 SH.CUSTOMERS의 Publish 속성을 false로 변경하여 이후 통계정보가 수집되더라도 publish되지 않고 user_tab_pending_stats에 저장.
    EXEC DBMS_STATS.SET_TABLE_PREFS('SH', 'CUSTOMERS', 'PUBLISH', 'FALSE');
    
  • DBMS_STATS.SET_TABLE_PREFS 프로시저
    DBMS_STATS.SET_TABLE_PREFS (ownname    IN  VARCHAR2,
                                tabname    IN  VARCHAR2,
                                pname      IN  VARCHAR2,
                                pvalue     IN  VARCHAR2);
    
  • DBMS_STATS.SET_TABLE_PREFS 프로시저 파라미터 설명
    parameter values description
    ownname   Owner name
    tabname   Table name
    pname CASCADE he value determines whether or not index statistics are collected as part of gathering table statistics.
      DEGREE The value determines degree of parallelism used for gathering statistics.
      ESTIMATE_PERCENT The value determines the percentage of rows to estimate. The valid range is '0.000001,100'. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.
      METHOD_OPT The value controls column statistics collection and histogram creation. It accepts either of the following options, or both in combination:
    *. FOR ALL 'INDEXED / HIDDEN' COLUMNS 'size_clause'
    *. FOR COLUMNS 'size clause' column/attribute 'size_clause' ',column/attribute 'size_clause'...' size_clause is defined as size_clause := SIZE 'integer / REPEAT / AUTO / SKEWONLY' column is defined as column := column_name / (extension)
      NO_INVALIDATE he value controls the invalidation of dependent cursors of the tables for which statistics are being gathered. Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE to have Oracle decide when to invalidate dependent cursors. This is the default.
      GRANULARITY The value determines granularity of statistics to collect (only pertinent if the table is partitioned).
    *. 'ALL' - gathers all (subpartition, partition, and global) statistics
    *. 'AUTO'- determines the granularity based on the partitioning type. This is the default value.
    *. 'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.
    *. 'GLOBAL' - gathers global statistics
    *. 'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.
    *. 'PARTITION '- gathers partition-level statistics
    *. 'SUBPARTITION' - gathers subpartition-level statistics.
      PUBLISH This value determines whether or not newly gathered statistics will be published once the gather job has completed. Prior to Prior to Oracle Database 11g, Release 1 (11.1), once a statistic gathering job completed the new statistics were automatically published into the dictionary tables. The user now has the ability to gather statistics but not publish them immediately. This allows the DBA to test the new statistics before publishing them.
      INCREMENTAL his value determines whether or not the global statistics of a partitioned table will be maintained without doing a full table scan. With partitioned tables it is very common to load new data into a new partition. As new partitions are added and data loaded, the global table statistics need to be kept up to date. Oracle will update the global table statistics by scanning only the partitions that have been changed instead of the entire table if the following conditions hold:
    *. the INCREMENTAL value for the partitioned table is set to TRUE;
    *. the PUBLISH value for the partitioned table is set to TRUE;
    *. the user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.
      STALE_PERCENT This value determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. The default value is 10%.
    pvalue   Preference value. If NULL is specified, it will set the Oracle default value.
  • Examples
    DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'CASCADE',          'DBMS_STATS.AUTO_CASCADE');
    DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'ESTIMATE_PERCENT', '9');
    DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'DEGREE',           '99');
    
  • 테스트
    
    -- 1. XSOFT_T 테이블 PUBILSH -> PENDING 변경
    EXEC DBMS_STATS.SET_TABLE_PREFS(USER, 'XSOFT_T', 'PUBLISH', 'FALSE');
    
    -- 2. 통계정보 재생성
    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER, TABNAME=>'XSOFT_T') ;
    
    -- 3. USER_TAB_PENDING_STATS 확인
    SELECT *
    FROM   USER_TAB_PENDING_STATS
    WHERE  TABLE_NAME = 'XSOFT_T'
    ;
    
    COLUMN                         VALUE                
    ------------------------------ ------------------------
    TABLE_NAME                     XSOFT_T                 
    PARTITION_NAME                                         
    SUBPARTITION_NAME                                      
    NUM_ROWS                       1000                    
    BLOCKS                         5                       
    AVG_ROW_LEN                    4                       
    SAMPLE_SIZE                    1000                    
    LAST_ANALYZED                  2011-10-12 오후 7:02:13 
    ;
    
    -- 4. USER_IND_PENDING_STATS 확인
    SELECT *
    FROM   USER_IND_PENDING_STATS
    WHERE  TABLE_NAME = 'XSOFT_T'
    ;
    
    COLUMN                         VALUE                     
    ------------------------------ ----------------------------
    INDEX_NAME                     XSOFT_N1                    
    TABLE_OWNER                    USER                     
    TABLE_NAME                     XSOFT_T                     
    PARTITION_NAME                                             
    SUBPARTITION_NAME                                          
    BLEVEL                                                     
    LEAF_BLOCKS                    2                           
    DISTINCT_KEYS                  1000                        
    AVG_LEAF_BLOCKS_PER_KEY        1                           
    AVG_DATA_BLOCKS_PER_KEY        1                           
    CLUSTERING_FACTOR              2                           
    NUM_ROWS                       1000                        
    SAMPLE_SIZE                    1000                        
    LAST_ANALYZED                  2011-10-12 오후 7:02:13     
    ;
    
3-3) Pending 통계정보를 사용하는 방법
  • 옵티마이져는 default로 Publish된 통계정보를 사용.
  • 인위적으로 pending 통계정보를 사용토록 하기 위한 방법
    • init파일에 OPTIMIZER_PENDING_STATISTICS=TRUE 으로 설정
    • session level에서 다음과 같이 변경.
      alter session set optimizer_pending_statistis = TRUE;
      
3-4) Pending 통계정보를 Publish 하는 방법
  • 모든 valid pending 통계정보를 Publish
    EXEC DBMS_STATS.PUBLISH_PENDING_STATS(NULL, NULL);
    
  • PUBLISH_PENDING_STATS 프로시저
    DBMS_STATS.PUBLISH_PENDING_STATS (ownname         IN  VARCHAR2 DEFAULT USER,
                                      tabname         IN  VARCHAR2,
                                      no_invalidate   BOOLEAN DEFAULT to_no_invalidate_type(get_param('NO_INVALIDATE')),
                                      force           IN  BOOLEAN DEFAULT FALSE);
    
  • PUBLISH_PENDING_STATS 프로시저 파라미터 설명
    parameter description
    ownname Owner name
    tabname Table name
    no_invalidate Do not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
    force If TRUE, will override the lock
  • Examples
    DBMS_STATS.PUBLISH_PENDING_STATS ('SH', null);
    
  • 특정 object에 대한 pending 통계정보를 Publish
    EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SH', 'CUSTOMERS');
    
  • 참고로, pending 통계정보는 dbms_stats.export_pending_stats 함수를 사용하여, export하여 test 서버에 import하여 test를 수행할 수도 있다.

III. 실습 예제

1. Changing Global and Table Statistic Preferences

1-1) STALE_PERCENT 소개
  • DBMS_STAT.SET_PREFS 프로시저의 pname 파라미터 값 중 하나로, 이 값은 테이블의 통계정보를 재 수집해야 하는 rows 변경량(%)을 지정함.
  • rows변경량이 STALE_PERCENT 이상이면 테이블의 통계정보 상태는 STALE이 되어 통계정보 재수집 대상이 됨.
1-2) STALE_PERCENT 기본 값 확인
SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', USER, 'XSOFT_T') STALE_PERCENT
FROM   DUAL
;

STALE_PERCENT  
---------------
10             
  • DEFAULT 값이 10이 설정되어 있음.
1-3) GLOBAL_STALE_PERCENT 값 변경

-- 1. GLOBAL_STALE_PERCENT 값 변경
execute dbms_stats.set_global_prefs('STALE_PERCENT', '13');

-- 2. 데이터 확인
SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', USER, 'XSOFT_T') STALE_PERCENT
FROM   DUAL
;

STALE_PERCENT  
---------------
13         
1-4) LOCAL_STALE_PERCENT 값 변경

-- 1. LOCAL_STALE_PERCENT 값 변경
execute dbms_stats.set_table_prefs(USER, 'XSOFT_T', 'STALE_PERCENT', '65');

-- 2. 데이터 확인
SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', USER, 'XSOFT_T') STALE_PERCENT
FROM   DUAL
;

STALE_PERCENT  
---------------
65         
1-5) LOCAL_STALE_PERCENT 값 제거

-- 1. LOCAL_STALE_PERCENT 값 제거
execute dbms_stats.delete_table_prefs(USER, 'XSOFT_T', 'STALE_PERCENT');

-- 2. 데이터 확인
SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', USER, 'XSOFT_T') STALE_PERCENT
FROM   DUAL
;

STALE_PERCENT  
---------------
13         
1-6) GLOBAL_STALE_PERCENT 값 제거

-- 1. GLOBAL_STALE_PERCENT 값 제거
execute dbms_stats.set_global_prefs('STALE_PERCENT', null);

-- 2. 데이터 확인
SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', USER, 'XSOFT_T') STALE_PERCENT
FROM   DUAL
;

STALE_PERCENT  
---------------
10         
1-7) STALE_PERCENT 테스트
  • STALE_PERCENT 값이 10%이므로 10% 이상의 데이터를 INSERT하고 통계정보가 자동으로 갱신되는지 확인

-- 1. XSOFT_T 테이블 PENDING -> PUBILSH 변경
EXEC DBMS_STATS.SET_TABLE_PREFS(USER, 'XSOFT_T', 'PUBLISH', 'TRUE');

-- 2. XSOFT_T 테이블 PUBILSH 여부 확인
SELECT DBMS_STATS.GET_PREFS('PUBLISH', USER, 'XSOFT_T') PUBLISH
FROM   DUAL;

PUBLISH 
--------
TRUE    

-- 3. XSOFT_T 테이블 PUBILSH 통계정보 확인
SELECT *
FROM   USER_TAB_STATISTICS
WHERE  TABLE_NAME = 'XSOFT_T'
;

COLUMN                         VALUE                    
------------------------------ ---------------------------
TABLE_NAME                     XSOFT_T                    
PARTITION_NAME                                            
PARTITION_POSITION                                        
SUBPARTITION_NAME                                         
SUBPARTITION_POSITION                                     
OBJECT_TYPE                    TABLE                      
NUM_ROWS                       1000                       
BLOCKS                         5                          
EMPTY_BLOCKS                   0                          
AVG_SPACE                      0                          
CHAIN_CNT                      0                          
AVG_ROW_LEN                    4                          
AVG_SPACE_FREELIST_BLOCKS      0                          
NUM_FREELIST_BLOCKS            0                          
AVG_CACHED_BLOCKS                                         
AVG_CACHE_HIT_RATIO                                       
SAMPLE_SIZE                    1000                       
LAST_ANALYZED                  2011-10-12 오후 6:28:33    
GLOBAL_STATS                   YES                        
USER_STATS                     NO                         
STATTYPE_LOCKED                                           
STALE_STATS                    NO                         
;

-- 4. 데이터 INSERT(100% 증가)
INSERT INTO XSOFT_T
SELECT LEVEL + 1000 
FROM   DUAL
CONNECT BY LEVEL <= 1000;

COMMIT;

-- 5. XSOFT_T 테이블 PUBILSH 통계정보 재 확인(통계정보 자동갱신이 설정되어 있지 않아 갱신 안됨)
SELECT *
FROM   USER_TAB_STATISTICS
WHERE  TABLE_NAME = 'XSOFT_T'
;

COLUMN                         VALUE                    
------------------------------ ---------------------------
TABLE_NAME                     XSOFT_T                    
PARTITION_NAME                                            
PARTITION_POSITION                                        
SUBPARTITION_NAME                                         
SUBPARTITION_POSITION                                     
OBJECT_TYPE                    TABLE                      
NUM_ROWS                       1000                       
BLOCKS                         5                          
EMPTY_BLOCKS                   0                          
AVG_SPACE                      0                          
CHAIN_CNT                      0                          
AVG_ROW_LEN                    4                          
AVG_SPACE_FREELIST_BLOCKS      0                          
NUM_FREELIST_BLOCKS            0                          
AVG_CACHED_BLOCKS                                         
AVG_CACHE_HIT_RATIO                                       
SAMPLE_SIZE                    1000                       
LAST_ANALYZED                  2011-10-12 오후 6:28:33    
GLOBAL_STATS                   YES                        
USER_STATS                     NO                         
STATTYPE_LOCKED                                           
STALE_STATS                    NO                         
;

2. Gathering Pending STATISTICS

2-1) 테스트

-- 1. 테이블 재생성
DROP TABLE XSOFT_T;

CREATE TABLE XSOFT_T (VAL_1 NUMBER,
                      VAL_2 NUMBER,
                      VAL_3 NUMBER,
                      VAL_4 NUMBER);

-- 2. 데이터 생성(분포도를 위해 NDV를 각각 10000, 1000, 100, 10으로 생성)
INSERT INTO XSOFT_T
SELECT LEVEL,
       MOD(LEVEL, 10),
       MOD(LEVEL, 100),
       MOD(LEVEL, 1000)
FROM   DUAL
CONNECT BY LEVEL <= 10000;

COMMIT;

-- 3. INDEX 생성
CREATE INDEX XSOFT_T_N1 ON XSOFT_T(VAL_1);
CREATE INDEX XSOFT_T_N2 ON XSOFT_T(VAL_2);
CREATE INDEX XSOFT_T_N3 ON XSOFT_T(VAL_3);
CREATE INDEX XSOFT_T_N4 ON XSOFT_T(VAL_4);

-- 4. global publish 확인
-- (현재 이 시스템에서는 Default가 PUBLISH 상태)
SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISH
FROM   DUAL
;

PUBLISH
-------
TRUE   
;

-- 5. local publish 확인
-- (Default가 PUBLISH 이므로 테이블도 Default로 PUBLISH)
SELECT DBMS_STATS.GET_PREFS('PUBLISH', USER, 'XSOFT_T') PUBLISH
FROM   DUAL
;

PUBLISH
-------
TRUE   
;

-- 6. XSOFT_T 테이블의 publish 통계정보 확인
-- 6-1) table
SELECT TABLE_NAME,
       LAST_ANALYZED ANALYZE_TIME,
       NUM_ROWS,
       BLOCKS,
       AVG_ROW_LEN
FROM   USER_TABLES
WHERE  TABLE_NAME = 'XSOFT_T'
;

TABLE_NAME   ANALYZE_TIME   NUM_ROWS     BLOCKS AVG_ROW_LEN
------------ ------------ ---------- ---------- -----------
XSOFT_T                                                    
;

-- 6-2) indexes
SELECT INDEX_NAME,
       LAST_ANALYZED ANALYZE_TIME,
       NUM_ROWS,
       LEAF_BLOCKS,
       DISTINCT_KEYS
FROM   USER_INDEXES
WHERE  TABLE_NAME = 'XSOFT_T'
ORDER  BY INDEX_NAME
;

INDEX_NAME   ANALYZE_TIME   NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
------------ ------------ ---------- ----------- -------------
XSOFT_T_N1   
XSOFT_T_N2   
XSOFT_T_N3   
XSOFT_T_N4   
;

-- 6-3) columns
SELECT COLUMN_NAME,
       LAST_ANALYZED ANALYZE_TIME,
       NUM_DISTINCT,
       NUM_NULLS,
       DENSITY
FROM   USER_TAB_COLUMNS
WHERE  TABLE_NAME = 'XSOFT_T'
ORDER  BY COLUMN_NAME
;

COLUMN_NAME  ANALYZE_TIME NUM_DISTINCT  NUM_NULLS    DENSITY
------------ ------------ ------------ ---------- ----------
VAL_1                                                       
VAL_2                                                       
VAL_3                                                       
VAL_4                                                       
;

-- 7. XSOFT_T 테이블의 pending 통계정보 확인
-- 7-1) tables
SELECT TABLE_NAME,
       LAST_ANALYZED "analyze time",
       NUM_ROWS,
       BLOCKS,
       AVG_ROW_LEN
FROM   USER_TAB_PENDING_STATS
WHERE  TABLE_NAME = 'XSOFT_T'
AND    PARTITION_NAME IS NULL
;

no rows selected
;

-- 7-2) indexes
SELECT INDEX_NAME,
       LAST_ANALYZED "analyze time",
       NUM_ROWS,
       LEAF_BLOCKS,
       DISTINCT_KEYS
FROM   USER_IND_PENDING_STATS
WHERE  TABLE_NAME = 'XSOFT_T'
AND    PARTITION_NAME IS NULL
ORDER  BY INDEX_NAME
;

no rows selected
;

-- 7-3) columns
SELECT COLUMN_NAME,
       LAST_ANALYZED "analyze time",
       NUM_DISTINCT,
       NUM_NULLS,
       DENSITY
FROM   USER_COL_PENDING_STATS
WHERE  TABLE_NAME = 'XSOFT_T'
AND    PARTITION_NAME IS NULL
ORDER  BY COLUMN_NAME
;

no rows selected
;

-- 8. XSOFT_T 테이블 통계정보 publish -> pending 변경
EXEC DBMS_STATS.SET_TABLE_PREFS(USER, 'XSOFT_T', 'PUBLISH', 'FALSE');

-- 9. XSOFT_T 테이블 pending 확인
SELECT DBMS_STATS.GET_PREFS('PUBLISH', USER, 'XSOFT_T') PUBLISH
FROM   DUAL
;

PUBLISH
-------
FALSE  
;

-- 10. XSOFT_T 테이블 통계정보 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER, TABNAME=>'XSOFT_T');

-- 11. XSOFT_T 테이블 publish 통계정보 확인
-- (XSOFT_T 테이블을 PENDING으로 변경했으므로 PUBLISH에서는 갱신 안됨)
-- 11-1) table
SELECT TABLE_NAME,
       LAST_ANALYZED ANALYZE_TIME,
       NUM_ROWS,
       BLOCKS,
       AVG_ROW_LEN
FROM   USER_TABLES
WHERE  TABLE_NAME = 'XSOFT_T'
;

TABLE_NAME   ANALYZE_TIME   NUM_ROWS     BLOCKS AVG_ROW_LEN
------------ ------------ ---------- ---------- -----------
XSOFT_T                                                    
;

-- 11-2) indexes
SELECT INDEX_NAME,
       LAST_ANALYZED ANALYZE_TIME,
       NUM_ROWS,
       LEAF_BLOCKS,
       DISTINCT_KEYS
FROM   USER_INDEXES
WHERE  TABLE_NAME = 'XSOFT_T'
ORDER  BY INDEX_NAME
;

INDEX_NAME   ANALYZE_TIME   NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
------------ ------------ ---------- ----------- -------------
XSOFT_T_N1   
XSOFT_T_N2   
XSOFT_T_N3   
XSOFT_T_N4   
;

-- 11-3) columns
SELECT COLUMN_NAME,
       LAST_ANALYZED ANALYZE_TIME,
       NUM_DISTINCT,
       NUM_NULLS,
       DENSITY
FROM   USER_TAB_COLUMNS
WHERE  TABLE_NAME = 'XSOFT_T'
ORDER  BY COLUMN_NAME
;

COLUMN_NAME  ANALYZE_TIME NUM_DISTINCT  NUM_NULLS    DENSITY
------------ ------------ ------------ ---------- ----------
VAL_1                                                       
VAL_2                                                       
VAL_3                                                       
VAL_4                                                       
;

-- 12. XSOFT_T 테이블 pending 통계정보 확인
-- (XSOFT_T 테이블을 PENDING이므로 확인 가능)
-- 12-1) tables
SELECT TABLE_NAME,
       LAST_ANALYZED "analyze time",
       NUM_ROWS,
       BLOCKS,
       AVG_ROW_LEN
FROM   USER_TAB_PENDING_STATS
WHERE  TABLE_NAME = 'XSOFT_T'
AND    PARTITION_NAME IS NULL
;

TABLE_NAME    analyze time   NUM_ROWS     BLOCKS AVG_ROW_LEN
------------- ------------ ---------- ---------- -----------
XSOFT_T       13-OCT-11         10000         28          14
;

-- 12-2) indexes
SELECT INDEX_NAME,
       LAST_ANALYZED "analyze time",
       NUM_ROWS,
       LEAF_BLOCKS,
       DISTINCT_KEYS
FROM   USER_IND_PENDING_STATS
WHERE  TABLE_NAME = 'XSOFT_T'
AND    PARTITION_NAME IS NULL
ORDER  BY INDEX_NAME
;

INDEX_NAME    analyze time   NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
------------- ------------ ---------- ----------- -------------
XSOFT_T_N1    13-OCT-11         10000          21         10000
XSOFT_T_N2    13-OCT-11         10000          20            10
XSOFT_T_N3    13-OCT-11         10000          20           100
XSOFT_T_N4    13-OCT-11         10000          21          1000
;

-- 12-3) columns
SELECT COLUMN_NAME,
       LAST_ANALYZED "analyze time",
       NUM_DISTINCT,
       NUM_NULLS,
       DENSITY
FROM   USER_COL_PENDING_STATS
WHERE  TABLE_NAME = 'XSOFT_T'
AND    PARTITION_NAME IS NULL
ORDER  BY COLUMN_NAME
;

COLUMN_NAME   analyze time NUM_DISTINCT  NUM_NULLS    DENSITY
------------- ------------ ------------ ---------- ----------
VAL_1         13-OCT-11           10000          0      .0001
VAL_2         13-OCT-11              10          0         .1
VAL_3         13-OCT-11             100          0        .01
VAL_4         13-OCT-11            1000          0       .001

3. Testing Pending Statistics

  • XSOFT_T 테이블에 대한 모든 통계정보는 pending 상태이며, Pending statistics를 export하여 test 장비에서 테스트를 수행할 수 있다
  • 그리고 만약 테스트한 pending statistics가 만족스럽지 않다면(성능향상이 없다면), pending statistics를 지워버릴 수 있다.
3-1) Sessin level에서 pending statistics를 사용하지 않는 방법
alter session set optimizer_use_pending_statistics = false;
alter session set optimizer_dynamic_sampling = 0;
3-2) 테스트
  • NDV 값이 10인 VAL_2 조건을 사용할 경우, 통계정보를 이용하게 되면 TABLE FULL SCAN을 할 것이고, 아니면 INDEX를 사용하게 될 것임.
  • 테스트 방식은, pending 통계정보도 사용하지 않고, dynamic sampling도 하지 않은 다음, VAL_2 조건일 때 어떤 실행계획이 나오는지 체크함.
  • 이후 pending 통계정보를 활성화 하여 어떻게 실행계획이 변하는지 확인

-- 1. VAL_2 조건 사용한 실행계획 확인
-- (dynamic sampling을 사용 안하기 때문에, 예상 rows는 1건)
-- (그리고 NDV 값을 모르기 때문에 FULL TABLE SCAN이 더 좋음에도 INDEX를 사용함)
EXPLAIN PLAN FOR
SELECT *
FROM   XSOFT_T
WHERE  VAL_2 = 1
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    52 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| XSOFT_T    |     1 |    52 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | XSOFT_T_N2 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
                                                                                          
Predicate Information (identified by operation id):                                       
---------------------------------------------------                                       
                                                                                          
   2 - access("VAL_2"=1)                                                                  
;

-- 2. pending 통계정보 사용
alter session set optimizer_use_pending_statistics = true;

-- 3. 실행계획 다시 확인
EXPLAIN PLAN FOR
SELECT *
FROM   XSOFT_T
WHERE  VAL_2 = 1
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

----------------------------------------------------------------------------- 
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |         |  1000 | 14000 |     9   (0)| 00:00:01 | 
|*  1 |  TABLE ACCESS FULL| XSOFT_T |  1000 | 14000 |     9   (0)| 00:00:01 | 
----------------------------------------------------------------------------- 
                                                                              
Predicate Information (identified by operation id):                           
---------------------------------------------------                           
                                                                              
   1 - filter("VAL_2"=1)                                                      

4. Publishing Pending Statistics

4-1) pending 통계정보를 publish로 변경
  • 만일 Pending 통계정보가 충분히 입증 되었다면, 아래와 같이 Publish 함
    EXEC DBMS_STATS.PUBLISH_PENDING_STATS(USER, 'XSOFT_T');
    
4-2) pending 통계정보 이용한 테스트

-- 1. publish 통계정보 확인
-- 1-1) table
SELECT TABLE_NAME,
       LAST_ANALYZED ANALYZE_TIME,
       NUM_ROWS,
       BLOCKS,
       AVG_ROW_LEN
FROM   USER_TABLES
WHERE  TABLE_NAME = 'XSOFT_T'
;

TABLE_NAME    ANALYZE_TIME   NUM_ROWS     BLOCKS AVG_ROW_LEN          
------------- ------------ ---------- ---------- -----------          
XSOFT_T       13-OCT-11         10000         28          14 
;

-- 1-2) indexes
SELECT INDEX_NAME,
       LAST_ANALYZED ANALYZE_TIME,
       NUM_ROWS,
       LEAF_BLOCKS,
       DISTINCT_KEYS
FROM   USER_INDEXES
WHERE  TABLE_NAME = 'XSOFT_T'
ORDER  BY INDEX_NAME
;

INDEX_NAME    ANALYZE_TIME   NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
------------- ------------ ---------- ----------- -------------
XSOFT_T_N1    13-OCT-11         10000          21         10000
XSOFT_T_N2    13-OCT-11         10000          20            10
XSOFT_T_N3    13-OCT-11         10000          20           100
XSOFT_T_N4    13-OCT-11         10000          21          1000
;

-- 1-3) columns
SELECT COLUMN_NAME,
       LAST_ANALYZED ANALYZE_TIME,
       NUM_DISTINCT,
       NUM_NULLS,
       DENSITY
FROM   USER_TAB_COLUMNS
WHERE  TABLE_NAME = 'XSOFT_T'
ORDER  BY COLUMN_NAME
;

COLUMN_NAME   ANALYZE_TIME NUM_DISTINCT  NUM_NULLS    DENSITY
------------- ------------ ------------ ---------- ----------
VAL_1         13-OCT-11           10000          0      .0001
VAL_2         13-OCT-11              10          0         .1
VAL_3         13-OCT-11             100          0        .01
VAL_4         13-OCT-11            1000          0       .001

-- 2. pending 통계정보 확인
-- (pending -> publish로 변경하면 자동으로 pending 데이터는 삭제됨)
-- 2-1) tables
SELECT TABLE_NAME,
       LAST_ANALYZED "analyze time",
       NUM_ROWS,
       BLOCKS,
       AVG_ROW_LEN
FROM   USER_TAB_PENDING_STATS
WHERE  TABLE_NAME = 'XSOFT_T'
AND    PARTITION_NAME IS NULL
;

no rows selected
;

-- 2-2) indexes
SELECT INDEX_NAME,
       LAST_ANALYZED "analyze time",
       NUM_ROWS,
       LEAF_BLOCKS,
       DISTINCT_KEYS
FROM   USER_IND_PENDING_STATS
WHERE  TABLE_NAME = 'XSOFT_T'
AND    PARTITION_NAME IS NULL
ORDER  BY INDEX_NAME
;

no rows selected
;

-- 2-3) columns
SELECT COLUMN_NAME,
       LAST_ANALYZED "analyze time",
       NUM_DISTINCT,
       NUM_NULLS,
       DENSITY
FROM   USER_COL_PENDING_STATS
WHERE  TABLE_NAME = 'XSOFT_T'
AND    PARTITION_NAME IS NULL
ORDER  BY COLUMN_NAME
;

no rows selected

5. Resetting Statistics and Preferences

  • 기존 통계정보 삭제 및 pending 통계정보로 전환
5-1) 테스트

-- 1. 통계정보 삭제
exec dbms_stats.delete_table_stats(USER, 'XSOFT_T');

-- 2. public 통계정보 확인
-- 2-1) table
SELECT TABLE_NAME,
       LAST_ANALYZED ANALYZE_TIME,
       NUM_ROWS,
       BLOCKS,
       AVG_ROW_LEN
FROM   USER_TABLES
WHERE  TABLE_NAME = 'XSOFT_T'
;

TABLE_NAME    ANALYZE_TIME   NUM_ROWS     BLOCKS AVG_ROW_LEN          
------------- ------------ ---------- ---------- -----------          
XSOFT_T        
;

-- 2-2) indexes
SELECT INDEX_NAME,
       LAST_ANALYZED ANALYZE_TIME,
       NUM_ROWS,
       LEAF_BLOCKS,
       DISTINCT_KEYS
FROM   USER_INDEXES
WHERE  TABLE_NAME = 'XSOFT_T'
ORDER  BY INDEX_NAME
;

INDEX_NAME    ANALYZE_TIME   NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
------------- ------------ ---------- ----------- -------------
XSOFT_T_N1    
XSOFT_T_N2    
XSOFT_T_N3    
XSOFT_T_N4    
;

-- 2-3) columns
SELECT COLUMN_NAME,
       LAST_ANALYZED ANALYZE_TIME,
       NUM_DISTINCT,
       NUM_NULLS,
       DENSITY
FROM   USER_TAB_COLUMNS
WHERE  TABLE_NAME = 'XSOFT_T'
ORDER  BY COLUMN_NAME
;

COLUMN_NAME   ANALYZE_TIME NUM_DISTINCT  NUM_NULLS    DENSITY
------------- ------------ ------------ ---------- ----------
VAL_1         
VAL_2         
VAL_3         
VAL_4         

IV. 결론

  • Oracle 11g 이전에는 데이터베이스 관리자가 통계정보를 수집하면 바로 Plan 에 반영되어 예기치 못한 결과를 초래 할 수 가 있었음.
  • 이제 11g에서는 통계정보의 Gathering과 Publish를 분리할 수 있게 됨으로써, 기존의 통계수집의 불안함을 극복함
  • 그리고 실 운영환경에서 테스트를 통하여 검증된 통계정보만이 Publish 되도록 하여 애플리케이션의 성능을 극대화 할 수 있게 됨.

참고 사이트

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.