인덱스에 대한 자동 통계정보 수집
아래와 같이 압축 된다.
| _optimizer_compute_index_stats = TRUE | _optimizer_compute_index_stats = FLASE | |
| Table Rows = 0 | 인덱스 통계정보 생성되지 않는다. | 인덱스 통계정보 생성되지 않는다. |
| Table Rows > 1 | 인덱스 통계정보 생성된다. | 인덱스 통계정보 생성되지 않는다. |
A. Hidden Parameter Check
SYS>!more check_hidden_parameter.sql
col "Parameter" format a60
col "Session Value" format a30
col "Instance Value" format a30
SELECT
a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM
x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE
a.indx = b.indx
AND
a.indx = c.indx
AND
-- a.ksppinm LIKE '/_%' escape '/'
a.ksppinm LIKE '&input_parameter'
/
SYS>@check_hidden_parameter.sql
Enter value for input_parameter: _optimizer_compute_index_stats
old 15: a.ksppinm LIKE '&input_parameter'
new 15: a.ksppinm LIKE '_optimizer_compute_index_stats'
Parameter Session Value Instance Value
------------------------------------------------------------ ------------------------------ ------------------------------
_optimizer_compute_index_stats TRUE TRUE
B. 통계정보 관련 체크 스트립트
SYS>!more check_table_and_indexes.sql
set linesize 180
set pagesize 2000
col table_name format a20
col index_name format a20
col number_rows format 999,999,999
col empty_blocks format 999,999,999
col avg_space format 999,999,999
col avg_row_len format 999,999
col sample_size format 999,999
col clustering_factor format 999,999
col AVG_LEAF_BLOCKS_PER_KEY format 999,999
set serveroutput on
define __TABLE_OWNER = &1
define __TABLE_NAME = &2
select table_name, num_rows ROW_S, blocks, empty_blocks,
-- avg_space,
avg_row_len, sample_size, to_char(last_analyzed,'YYYYMMDD HH24:MI:SS') ANA
from dba_tables
where owner = upper('&__TABLE_OWNER')
and table_name = upper('&__TABLE_NAME') ;
select index_name, num_rows ROW_S, blevel, leaf_blocks LEAF_B, distinct_keys DIS_KEY,
avg_leaf_blocks_per_key KEYPERLEAF,
-- avg_data_blocks_per_key,
clustering_factor CLUSETERING, sample_size, to_char(LAST_ANALYZED,'YYYYMMDD HH24:MI:SS') ANA
from dba_indexes
where owner = upper('&__TABLE_OWNER')
and table_name = upper('&__TABLE_NAME') ;
C. 메뉴얼 관련 정보
COMPUTE STATISTICS In earlier releases, you could use this clause to start or stop the collection
of statistics on an index.
This clause has been deprecated. Oracle Database now automatically collects statistics
during index creation and rebuild.
This clause is supported for backward compatibility and will not cause errors.
D. 관련 자료
출처 : http://viveklsharma.blogspot.com/2008/07/optimizercomputeindexstats-in-oracle.html
Whileinvestigating optimizer related hidden parameters, I came across_optimizer_compute_index_stats which defaults to true. This parametermeans that Optimizer Statistics will be generated on the Indexes duringcreation or rebuild of these. Therefore, if you have disabled AutomaticStats Gathering Job (dbms_scheduler) that gathers stats on the tableswith stale stats or no stats, creating a new index will have lateststats and can lead to sub-optimal plan.