실행계획 수립시 CBO는 SQL 문장에서 액세스할 데이터 특성을 고려하기 위해 통계 정보를 이용한다.
옵티마이저가 참조하는 통계 정보(4가지)
| analyze table emp compute statistics for TABELE ; |
| analyze table emp estimate statistics sample 5000 rows for TABLE ; |
| anlayze table emp estimate statistics sample 50 percent for TABLE ; |
begin
dbms_stats.gather_table_stats('scott', 'emp', cascade=>false, methode_opt=>'for columns') ;
end ;
/
SELECT 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'
AND table_name = 'EMP'
analyze INDEX emp_pk compute statistics ;
analyze table emp compute statistics for ALL INDEXS ;
analyze table emp compute statistics for TABLE for ALL INDEXS ;
-- 특정 인덱스 통계만 수집
begin
dbms_stats.gather_index_stats (ownname => 'scott', indname => 'emp_pk');
end;
/
SELECT 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'
AND index_name = 'PK_EMP' ;
create index emp_ename_idx on emp(ename) COMPUTE STATISTICS ;
alter index emp_ename_idx rebuild COMPUTE STATISTICS ;
10g 부터는 이 옵션을 명시하시하지 않다도 자동으로 인텍스 통계까지 수집
=> 이 기능을 방지를 위해서는 _optimizer_compute_index_stats를 flase로 설정
analyze table emp compute statistics for ALL COLUMNS SIZE 254 ;
analyze table emp compute statistics for COLUMNS ENAME SIZE 10, SAL SIZE 20 ;
analyze table emp compute statistics for COLUMNS SIZE 20 ENAME, SAL, HIREDATE ;
analyze table emp compute statistics
for table
for all indexes
for all indexed columns size 254 ;
-- 컬럼 통계 조회
SELECT num_distinct ,
low_value ,
high_value ,
density ,
num_nulls ,
num_buckets ,
last_analyzed ,
sample_size ,
avg_col_len ,
histogram
FROM dba_tab_columns
WHERE owner = 'SCOTT'
AND table_name = 'EMP'
AND column_name = 'DEPTNO' ;
-- 컬럼 히스토그램 조회
SELECT endpoint_value ,
endpoint_number
FROM dba_histograms
WHERE owner = 'SCOTT'
AND table_name = 'EMP'
AND column_name = 'DEPTNO'
ORDER BY endpoint_value ;
ENDPOINT_VALUE ENDPOINT_NUMBER
시스템 통계는 I/O, CPU 성능과 같은 하드웨어 특성을 측정한 것
+ CPU 속도
+ 평균적인 Single Block I/O 속도
+ 평균적인 Multiblock I/O 속도
+ 평균적인 Multiblock I/O 개수
+ I/O 서브시스템의 최대 처리량(Throughput)
+ 병렬 Slave의 평균적인 처리량(Throughput)
SELECT sname ,
pname ,
pval1 ,
pval2
FROM sys.aux_stats$ ;
Workload 시스템 통계
spuspeed 현재 시스템에서 단일 CPU가 초당 수행할 수 있는 표준 오퍼레이션 개수(단위 : 백만/초)
sreadtim 평균적인 Single Block I/O 속도(단위 : ms = 1/1000초)
mreadtim 평균적인 Multi Block I/O 속도(단위 : ms = 1/1000초)
mbrc Multi Block I/O 방식을 사용할 때 평균적으로 읽은 블록 수
maxthr I/O 서버시스템의 최대 처리량(단위 : 바이트/초)
slavethr 병렬 Slave의 평균적인 처리량(eksdnl : 바이트/초)
1. 시스템 통계를 담을 테이블(mystats)를 생성
begin
dbms_stats.create_stat_table(
ownname => USER,
stattab => 'mystats',
tblspace => 'USERS',
global_temporary => FALSE
);
end;
/
2. 시스템 통계를 수집
<방법1>
begin
dbms_stats.gather_system_stats(
gathering_mode => 'interval',
interval => 720,
stattab => 'mystats',
statid => 'OLTP'
);
end;
/
<방법2>
begin
dbms_stats.gather_system_stats(
gathering_mode => 'start',
stattab => 'mystats',
statid => 'OLTP'
);
end;
/
begin
dbms_stats.gather_system_stats(
gathering_mode => 'stop',
stattab => 'mystats',
statid => 'OLTP'
);
end;
/
운영중인 시스템 통계를 mystats에 저장하려면 아래 명령어 사용
begin
dbms_stats.export_system_stats('mystats', 'OLTP', 'USER);
end;
/
3. OS 상에서 Exp/Imp 명령러를 이용해 통계 테이블(mystats)을 테스트 서버로 복제
4. 복제한 통계 데이터를 테스트 서버에 적용
begin
dbms_stats.import_system_stats('mystats', 'OLTP', 'USER);
end;
/
만약 운영 서버에 수집된 값을 수정으로 설정하려면 아래와 같이 수행
begine
dbms_stats.set_system_stats('CPUSPEED', 500);
dbms_stats.set_system_stats('SREADTIM', 5.0);
dbms_stats.set_system_stats('MREADTIM', 30.0);
dbms_stats.set_system_stats('MBRC', 12);
end;
/
5. 시스템 통게를 적용하더라도 캐싱되어 있는 실행계획에는 영향을 미치지 않으로 Shared Pool Flush
alter system flush shared_pool ;
NoWorkload 시스템 통계
cpuspeednw
ioseektim
iotfrspeed
NoWorkload 시스템 통계 수집시는 아래와 같이 수행
begin
dbms_stats.gather_system_stats(gathering_mode = > 'NOWORKLEAD');
end;
/