목차

I. OVERVIEW

• 1. 10g 이하 통계정보 관리
• 2. 10g에서 통계정보 Export/Import
• 3. 11g 통계정보 관리

II. Pending Statistics

• 1. 통계정보 Gathering 및 Publish 절차
• 2. Pending Statistics 장점
• 3. 통계정보 Gathering 및 Publish Commands
  • 3-1) Publish Attribute 확인방법
  • 3-2) Publish Attribute 설정을 변경하는 방법
  • 3-3) Pending 통계정보를 사용하는 방법
  • 3-4) Pending 통계정보를 Publish 하는 방법

III. 실습 예제

• 1. Changing Global and Table Statistic Preferences
  • 1-1) STALE_PERCENT 소개
  • 1-2) STALE_PERCENT 기본 값 확인
  • 1-3) GLOBAL_STALE_PERCENT 값 변경
  • 1-4) LOCAL_STALE_PERCENT 값 변경
  • 1-5) LOCAL_STALE_PERCENT 값 제거
  • 1-6) GLOBAL_STALE_PERCENT 값 제거
  • 1-7) STALE_PERCENT 테스트

• 2. Gathering Pending Statistics
  • 2-1) 테스트

• 3. Testing Pending Statistics
  • 3-1) Sessin level에서 pending statistics를 사용하지 않는 방법
  • 3-2) 테스트

• 4. Publishing Pending Statistics
  • 4-1) pending 통계정보를 publish로 변경
  • 4-2) pending 통계정보 이용한 테스트

• 5. Resetting Statistics and Preferences
  • h5. 5-1) 테스트

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 펑션 파라미터 설명

• 각 속성별 데이터 확인 뷰
  • USER_TAB_STATISTICS, USER_IND_STATISTICS 뷰는 10g에도 존재함
  • USER_TAB_PENDING_STATS, USER_IND_PENDING_STATS 뷰는 11g new feature

• 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 프로시저 파라미터 설명

• 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 프로시저 파라미터 설명

• 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 되도록 하여 애플리케이션의 성능을 극대화 할 수 있게 됨.

참고 사이트

• http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/STAT64/Default.aspx

문서에 대하여

• 최초작성자 : ~xsoft
• 최초작성일 : 2011년 10월 15일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.