Oracle Statistics

Optimizer가 CBO 계산시 활용하는 통계정보 데이터 확인법

 

1. 테이블 통계

select 
  TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS
  , NUM_FREELIST_BLOCKS, SAMPLE_SIZE, LAST_ANALYZED
from  dba_tables 
where owner='SCOTT'

2. 인덱스 통계

select
   INDEX_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR , NUM_ROWS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY
  , AVG_DATA_BLOCKS_PER_KEY, SAMPLE_SIZE, LAST_ANALYZED  
from  dba_indexes
where owner='SCOTT'
  and table_name='EMP'
;

3. 컬럼 통계

select
  COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED , SAMPLE_SIZE,  AVG_COL_LEN , HISTOGRAM
from all_tab_columns
where owner ='SCOTT'
and table_name =  'EMP'

 

 

 

출처: http://www.gurubee.net/lecture/3351