실행계획 수립시 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'
---------------------------------------------------------------
NUM_ROWS : 14
BLOCKS : 5
EMPTY_BLOCKS : 0
AVG_SPACE : 0
AVG_ROW_LEN : 37
AVG_SPACE_FREELIST_BLOCKS : 0
NUM_FREELIST_BLOCKS : 0
SAMPLE_SIZE : 14
LAST_ANALYZED : 2010-04-14 오후 10:00:17
---------------------------------------------------------------
analyze INDEX emp_pk compute statistics ; -- 인덱스 통계 수집
analyze table emp compute statistics for ALL INDEXS ; -- 테이블에 속한 모든 인덱스의 통계 수집
analyze table emp compute statistics for TABLE for ALL INDEXS ; -- 테이블과 인덱스의 통계를 함께 수집
-- dbms_stats 패키지 이용
-- 특정 인덱스 통계만 수집
begin
dbms_stats.gather_index_stats (ownname => 'scott', indname => 'emp_pk');
end;
/
-- 테이블에 속한 모든 인덱스 통계도 같이 수집
begin
dbms_stats.gather_table_stats ('scoot', 'emp', cascade=>true);
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' ;
---------------------------------------------------------------
BLEVEL : 0
LEAF_BLOCKS : 1
CLUSTERING_FACTOR : 1
NUM_ROWS : 14
DISTINCT_KEYS : 14
AVG_LEAF_BLOCKS_PER_KEY : 1
AVG_DATA_BLOCKS_PER_KEY : 1
SAMPLE_SIZE : 14
LAST_ANALYZED : 2010-04-14 오후 10:00:17
---------------------------------------------------------------
인덱스를 최초 생성하거나 재성성시 compute statistics 옵션을 주면 자동으로 인덱스 통계까지 수집된다.
10g 부터는 이 옵션을 명시하시하지 않다도 자동으로 인텍스 통계까지 수집
=> 이 기능을 방지를 위해서는 _optimizer_compute_index_stats를 false로 설정
create index emp_ename_idx on emp(ename) COMPUTE STATISTICS ;
alter index emp_ename_idx rebuild COMPUTE STATISTICS ;
analyze table emp compute statistics for ALL COLUMNS SIZE 254 ; -- 테이블, 인덱스를 제외한 컬럼의 통계정보만 수집
size 옵션은 히스토그램의 최대 버킷 개수를 지정하는 옵션
기본값은 75이고 1에서부터 254까지 허용된다.
히스토그램이 생성되지 않도록 하기 위해서는 1로 지정한다.
analyze table emp compute statistics for COLUMNS ENAME SIZE 10, SAL SIZE 20 ; -- ename과 sal만 통계 수집
analyze table emp compute statistics for COLUMNS SIZE 20 ENAME, SAL, HIREDATE ; -- ename, sal, hiredate 모두 20으로 지정
테이블, 인덱스, 컬럼에 대해서 동시에 통계 수집할 경우 아래와같이 한다.
analyze table emp compute statistics
for table
for all indexes
for all indexed columns size 254 ; --
dbms_stats 패키지는 컬럼만 따로 통계를 수집하는 방법은 없다.
수집된 컬럼 통게 조회
-- 컬럼 통계 조회
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' ;
---------------------------------------------------------------
num_distinct : 3
low_value : C10B
HIGH_VALUE : C11F
DENSITY :0.0357142857142857
NUM_NULLS : 0
NUM_BUCKETS : 3
LAST_ANALYZED : 2010-04-14 오후 10:00:17
SAMPLE_SIZE : 14
AVG_COL_LEN : 2
HISTOGRAM : FREQUENCY
---------------------------------------------------------------
-- 컬럼 히스토그램 조회
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
-------------- ---------------
10 3
20 8
30 14
시스템 통계는 I/O, CPU 성능과 같은 하드웨어 특성을 측정한 것으로 아래와 같은 정보들을 포함한다.
과거에 이 항목들은 고정된 상수였다.
설치된 운영 시스템의 사양과 운영 환경(OLTP, DW)이이 반영되지 않아 시스템이 다를 경우 잘못된 선택을 하기 쉬웠다.
옵티마이저가 하드웨어 및 애플리케이션의 특성을 시스템 통계에 반영합으로써 옵티마이저가 더욱 합리적인 선택을 할 수 있게 되었다(9i이상)
SQL> SET LINE 100
SQL> SET PAGESIZE 100
SQL> COLUMN sname FORMAT A20
SQL> COLUMN pname FORMAT A20
SQL> COLUMN pval2 FORMAT A40
SQL>
SQL> SELECT sname ,
2 pname ,
3 pval1 ,
4 pval2
5 FROM sys.aux_stats$ ;
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- ----------------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 04-17-2010 09:28
SYSSTATS_INFO DSTOP 04-17-2010 09:28
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 470.445344
SYSSTATS_MAIN IOSEEKTIM 10 NoWorkload(10g 추가)
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 5
SYSSTATS_MAIN MREADTIM 30
SYSSTATS_MAIN CPUSPEED 500 Workload(9i 이후)
SYSSTATS_MAIN MBRC 12
SYSSTATS_MAIN MAXTHR 524288000
SYSSTATS_MAIN SLAVETHR 5242880
13 개의 행이 선택되었습니다.
Workload 시스템 통계
9i에서 도입된 Workload 시스템 통계는, 애플리케이션으로부터 일정 시간동안 발생한 시스템 부하를 측정,보관함으로써 그 특성을 최적화 과정에 반영할 수 있게 한 기능이다.
Workload 시스템 통계 항목에는 아래와 같이 6가지가 있다.
| 통계항목 | 설명 |
|---|---|
| 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 : 바이트/초) |
앞에서 보았다 sys.aux_stats$ 조회결과를 해석하면 다음과같다.
뒤에서 설명할 NoWorkload 시스템은 오라클이 무작위로 I/O를 발생시켜 측정한 값인 반면 Workload 시스템 통께는 실제 애플리케이션에서 발생하는 부하를 측정한 값이다.
때문에 Workload 시스템 통께를 제대로 활용하려면 통계 수집 전략을 잘 세워야 한다.
운영서버에서 수집한 정보를 테스트 서버로 복제할 때를 기준으로 시스템 통게 수집 방법과 절차를 설명하면 다음과 같다.
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'
); -- 720분 동안 수집하고 자동으로 멈춤
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에 저장하려면 아래 명령어 사용
{code:sql}
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;
/
만약 운영 서버에 수집된 값을 수정으로 설정하려면 아래와 같이 수행
begin
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);
dbms_stats.set_system_stats('MAXTHR', 524288000);
dbms_stats.set_system_stats('SLAVETHR', 5242880);
end;
/
5. 시스템 통게를 적용하더라도 캐싱되어 있는 실행계획에는 영향을 미치지 않으로 Shared Pool Flush
alter system flush shared_pool ;
NoWorkload 시스템 통계
관리자가 명시적으로 선택하지 않더라도 CPU 비용 모델이 기본 비용 모델로 사용하기 위해서 오라클 10g에서 NoWorkload 시스템 통계를 도입하였다.
CPU 비용 모델은 시스템 통계가 있을때만 활욜되기때문이다.
No Workload 시스템 통계 항목과 (처음 데이터베이스를 기동할 때 설정되는) 기본 값은 다음표와 같다.
| 통계 항목 | 기본값 | 설명 | |
|---|---|---|---|
| cpuspeednw | 데이터베이스 최초 기동 시 측정된 값 | NoWorkload 상태에서 측정된 CPU 속도(eksdnl : Millions/sec) | |
| ioseektim | 10ms | I/O Seek Time을 뜻하며, 데이터를 읽으려고 디스크 헤드(head)를 옮기는 데 걸리는 시간을 나타낸다. 대개 5~15ms의 수치를 보이며, 디스크 회전 속도와 디스키 또는 RAID 스팩에 따라 달라진다. | |
| io seek time = seek time + latency time + operating system overhead time | |||
| iotfrspeed | 4096 bytes/ms | I/O Transfer 속도를 뜻하며, 하나의 OS 프로세스가 I/O 서브 시스템으로부터 데이터를 읽는 속도를 나타낸다. 이 값은 초당 몇 MB에서 수백 MB까지 아주 다양하다. |
Workload 시스템 통계를 수집하고 반영하는 순간 NoWorkload 시스템 통계는 무시된다.
Workload 시스템 통계가 수집되기 전까지는 아래 공식을 이용하여 추정된 값을 사용한다.
Workload 통계와 주요한 차이점은 통계를 수집하는 방식에 있다. 즉, Workload 는 실제 애플리케이션에서 발생하는 부하를 기준으로 각 항목의 통계치를 측정하는 반면 NoWorkload는 모든 데이터파일 중에서 무작위로 I/O를 발생시켜 통계를 수집한다.
따라서, 시스템 부하 정도가 심할때 NoWorkload 시스템 통께를 수집하면 구해진 값들도 달라진다.
NoWorkload 시스템 통계 수집시는 아래와 같이 수행하면 된다.
begin
dbms_stats.gather_system_stats(gathering_mode => 'NOWORKLOAD');
end;
/