COST BASED OPTIMIZER 사용 시 PERFORMANCE가 문제가 되는 경우의 CHECK LIST
========================================================================

Analyze table compute statistics command가 먼저 수행되어야 한다.

우선 아래의 자료를 확보하여야 한다.

hint를 포함한 query문장.
query에서 view를 사용하는 경우 view의 정의.
7.3 version 이상에서는 POSITION, COST, CARDINALITY를 포함한
explain plan.

1. Analyze 수행으로 얻을 수 있는 정보는 아래와 같다.

- query에서 사용되는 모든 table의 정보 (user_tables)

만약 ( blocks + empty_blocks + 1 )의 값이 segment의 size와
다르다면, analyze를 수행하여야 한다.

empty_blocks의 값이 blocks에 비해 너무 크면, initial extent나
next extent의 size를 줄여야 한다.

입력된 row 수에 비하여 HWM(blocks의 값)이 너무 높다면, table
의 재생성을 고려해 보아야 한다.
< 이유 : full table scan 시 HWM까지의 block을 access하게 됨 >

avg_space가 너무 크면, 테이블을 재생성하는 것을 고려.
( 기준 : db_block_size*(100-PCT_USED+PCT_FREE)/200 )


- query에서 사용되거나 사용하고자 하는 모든 index의 정보
(user_indexes)

leaf_blocks*db_block_size의 값이 index segment size보다 훨씬
작다면 initial / next extent를 줄여야 한다.

distinct_keys의 값이 10보다 작은 경우에는 index를 생성할 필요
가 없다.

Non-unique index의 경우 avg_leaf_blocks_per_key의 값이
avg_data_blocks_per_key의 값보다 큰 경우에는 index 재생성을
고려해 보아야 한다.

blevel의 값이 데이타 건수에 비하여 너무 크다면, index rebuild
를 고려해 보아야 한다.
< 이유 : index scan 시 access하여야 하는 minimum blocks 수는
(blevel + 1) + table block >

대용량 db의 batch program이 index range scan을 수행하는 경우,
clustering_factor 값이 작은 index를 사용하도록 유도하는 것이
좋다.
clustering_factor의 정보는 실제 환경보다 나쁘게 계산되는 경향
이 있으므로 index 간의 상대적인 비교로만 사용하여야 한다.

- where 절에서 사용되는 모든 column의 정보 ( user_tab_columns )

- where 절에서 사용되는 모든 column에 대한 histogram 정보
(user_histograms)
적정한 bucket수로 구성되어 있는지 확인.

2. init parameter의 값을 확인. ( select * from v$parameter )


DB_FILE_MULTIBLOCK_READ_COUNT
DB_BLOCK_BUFFERS
HASH_AREA_SIZE
HASH_JOIN_ENABLED
HASH_MULTIBLOCK_IO_COUNT
OPTIMIZER_GOAL
OPTIMIZER_MODE
OPTIMIZER_PERCENT_PARALLEL
SORT_AREA_SIZE
SORT_DIRECT_WRITERS

위와 같은 정보를 수집 후 performance에 영향을 주는 요인들을 분
석해 보아야 한다.



*** Analyze 명령을 사용하는 경우 수집되는 정보는 아래와 같다.

Indexes ( user_indexes )
root block에서 leaf block까지의 depth => blevel
leaf block의 수 => leaf_blocks
distinct index 값의 수 => distinct_keys
한 index에 대한 평균 leaf block 수 => avg_leaf_blocks_per_key
한 index에 대한 평균 data block 수 => avg_data_blocks_per_key
index에 대한 data record의 정렬 정도 => clustering_factor

blevel의 값은 compute/estimate에 관계없이 항상 정확히 계산된
다.


Tables ( user_tables )
record 수 => num_rows
data를 가지고 있는 block의 수 => blocks
아직 사용되지 않은 block의 수 => empty_blocks
data block당 사용 가능한 free space => avg_space
chained row의 수 => chain_cnt
o/h를 포함한 평균 record의 길이(bytes) => avg_row_len

blocks와 empty_blocks의 값은 compute/estimate에 관계없이 항상
정확히 계산된다.


Columns ( user_tab_columns )
column에 관한 통계정보는 histogram(7.3 version 이상)을 사용하
여 구성된다.
histogram에 관하여 세부적인 사항은 를 참조.

전체 column의 distinct 값의 수 => num_distinct
bucket 당 minimum/maximum 값 => low_value / high_value