오라클 성능 고도화 원리와 해법 II (2012년)
04 통계정보 1
- 실행계획 수립시 CBS는 엑세스할 데이터 특성을 고려하기 위해 통계정보를 이용한다.
- 따라서 최적의 실행계획을 위해서는 통계정보가 데이터 상태를 정확하게 반영하도록 관리해 주어야 한다.
통계정보 종류
- 테이블 통계
- 인덱스 통계
- 컬럼 통계( 히스토그램 포함 )
- 시스템 통계
테이블 통계
- compute : 전수 검사
- estimate : 표본 조사
SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> analyze table emp compute statistics for table;
테이블이 분석되었습니다.
SQL> analyze table emp estimate statistics sample 5000 rows for table;
테이블이 분석되었습니다.
SQL> analyze table emp estimate statistics sample 50 percent for table;
테이블이 분석되었습니다.
SQL> CREATE UNIQUE INDEX SCOTT.PK_EMP_TEST1 ON SCOTT.EMP_TEST1(EMPNO);
인덱스가 생성되었습니다.
SQL> ALTER TABLE SCOTT.EMP_TEST1 ADD (
2 CONSTRAINT PK_EMP_TEST1
3 PRIMARY KEY
4 (EMPNO)
5 USING INDEX SCOTT.PK_EMP_TEST1);
테이블이 변경되었습니다.
SQL> SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS
2 , SAMPLE_SIZE, LAST_ANALYZED
3 FROM DBA_TABLES
4 WHERE OWNER = 'SCOTT'
5 AND TABLE_NAME = 'EMP_TEST1'
6 ;
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS SAMPLE_SIZE LAST_ANA
---------- ---------- ------------ ---------- ----------- ------------------------- ------------------- ----------- --------
SQL> begin
2 dbms_stats.gather_table_stats( 'scott', 'EMP_TEST1', cascade=>false, method_opt=> 'for columns' );
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS
2 , SAMPLE_SIZE, LAST_ANALYZED
3 FROM DBA_TABLES
4 WHERE OWNER = 'SCOTT'
5 AND TABLE_NAME = 'EMP_TEST1';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS SAMPLE_SIZE LAST_ANA
---------- ---------- ------------ ---------- ----------- ------------------------- ------------------- ----------- --------
14 4 0 0 38 0 0 14 12/12/07
- analyze 사용하지 말라는 것이 오라클의 공식적인 입장이다.
- 설명 이유 : 테이블 통계, 인덱스 통게, 컬럼 통계를 따로따로 수집하는 것을 보임으로써 이들 개념을 명확히 이해하는데 도움을 주기위해서
인덱스 통계
-- 인덱스 통계 수집
analyze INDEX emp_pk compute statistics;
-- 테이블에 속한 모든 인덱스 통계 수집
analyze table emp compute statistics for ALL INDEXES;
-- 테이블과 인덱스 통계를 함께 수집
analyze table emp compute statistics for TABLE for ALL INDEXES;
-- 테이블 통계만 돌렸는데... ;;
SQL> SELECT BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, NUM_ROWS, DISTINCT_KEYS
2 , AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY
3 , SAMPLE_SIZE, LAST_ANALYZED
4 FROM DBA_INDEXES
5 WHERE OWNER = 'SCOTT'
6 AND TABLE_NAME = 'EMP_TEST1';
BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY SAMPLE_SIZE LAST_ANA
---------- ----------- ----------------- ---------- ------------- ----------------------- ----------------------- ----------- --------
0 1 1 14 14 1 1 14 12/12/07
--특정 인덱스 통계만 수집
SQL> begin
2 dbms_stats.gather_index_stats( ownname => 'scott', indname => 'PK_EMP_TEST1' );
3 end;
4 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, NUM_ROWS, DISTINCT_KEYS
2 , AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY
3 , SAMPLE_SIZE, LAST_ANALYZED
4 FROM DBA_INDEXES
5 WHERE OWNER = 'SCOTT'
6 AND TABLE_NAME = 'EMP_TEST1'
7 ;
BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY SAMPLE_SIZE LAST_ANA
---------- ----------- ----------------- ---------- ------------- ----------------------- ----------------------- ----------- --------
0 1 1 14 14 1 1 14 12/12/07
-- 테이블에 속한 모든 인덱스 통계도 같이 수집
begin
dbms_stats.gather_table_stats( 'scott', 'EMP_TEST1', cascade=>true ); -- cascade : 디폴트 true
end;
/
-- 생성 및 재생성 할때 같이 생성( 인덱스 통계 )
create index emp_ename_idx on emp( ename ) COMPUTE STATISTICS;
alter index emp_ename_idx rebuild COMPUTE STATISTICS;
-- 10g 부터는 사용자가 이 옵션을 면시하지 않아도 오라클이 알아서 인덱스 통계까지 수집해 준다 ( _optimizer_compute_index_stats=false )
컬럼 통계
-- 컬럼 통계만 수집( 오라클 기본 SIZE : 75, 히스토그램 미생성하고플때 : 1 )
analyze table emp compute statistics for ALL COLUMNS SIZE 254
-- 일부 컬럼만 수집
analyze table emp compute statistics for COLUMNS ENAME SIZE 10, SAL SIZE 20;
-- 히스토그램 버킷 개수를 컬럼별로 지정하지 않고 똑같이 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 indexe columns size 254
-- dbms_stats 패키지는 컬럼 통계만 따로 수집하는 방법은 없음 ( 테이블 통계와 항상 같이 수집 )
SQL> COLUMN NUM_DISTINCT FORMAT A1;
SQL> COLUMN LOW_VALUE FORMAT A5;
SQL> COLUMN NUM_DISTINCT FORMAT A1
SQL> COLUMN LOW_VALUE FORMAT A5
SQL> COLUMN HIGH_VALUE FORMAT A5
SQL> COLUMN DENSITY FORMAT A20
SQL> COLUMN NUM_NULLS FORMAT A1
SQL> COLUMN NUM_BUCKETS FORMAT A1;
SQL> SELECT NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS
2 , LAST_ANALYZED, SAMPLE_SIZE, AVG_COL_LEN, HISTOGRAM
3 FROM DBA_TAB_COLUMNS
4 WHERE OWNER = 'SCOTT'
5 AND TABLE_NAME = 'EMP_TEST1'
6 AND COLUMN_NAME = 'DEPTNO';
NUM_DISTINCT LOW_V HIGH_ DENSITY NUM_NULLS NUM_BUCKETS LAST_ANA SAMPLE_SIZE AVG_COL_LEN HISTOGRAM
------------ ----- ----- ---------- ---------- ----------- -------- ----------- ----------- ---------------
NONE
--위에서 생성안함
SQL> begin
2 dbms_stats.gather_table_stats( 'scott', 'EMP_TEST1', cascade=>true ); -- cascade : 디폴트 true
3 end;
4 /
SQL> SELECT NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS
2 , LAST_ANALYZED, SAMPLE_SIZE, AVG_COL_LEN, HISTOGRAM
3 FROM DBA_TAB_COLUMNS
4 WHERE OWNER = 'SCOTT'
5 AND TABLE_NAME = 'EMP_TEST1'
6 AND COLUMN_NAME = 'DEPTNO';
NUM_DISTINCT LOW_V HIGH_ DENSITY NUM_NULLS NUM_BUCKETS LAST_ANA SAMPLE_SIZE AVG_COL_LEN HISTOGRAM
------------ ----- ----- --------------------- --------- ----------- -------- ----------- ----------- ----------
3 C10B C11F 0 0 1 12/12/07 14 3 NONE <-- ;;
PL/SQL 처리가 정상적으로 완료되었습니다.
-- 컬럼 히스토그램 조회
SQL> SELECT ENDPOINT_VALUE, ENDPOINT_NUMBER
2 FROM DBA_HISTOGRAMS
3 WHERE OWNER = 'SCOTT'
4 AND TABLE_NAME = 'EMP_TEST1'
5 AND COLUMN_NAME = 'DEPTNO'
6 ORDER BY ENDPOINT_VALUE;
ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ---------------
10 0
30 1
시스템 통계 : I/O, CPU, 하드웨어적 특성
- CPU 속도
- 평균적인 Single Block I/O 속도
- 평균적인 Multiblock I/O 속도
- 평균적인 Multiblock I/O 개수
- I/O 서브시스템의 최대 처리량( Throughput )
- 병렬 Slave의 평균적인 처리량 ( Throughput )
설명
- 9i 이전 : 고정된 상수 ( 옵티마이저의 잘못된 판단 유도 )
- 9i 이상 : 제품이 설치된 하드웨어 및 애플리케이션 특성에 맞는 시스템 통계를 수집하고 이를 활용함으로써 옵티마이저가 보다 합리적으로 선택할 수 있도록 하였다.
SQL> COLUMN sname FORMAT A20;
SQL> COLUMN pname FORMAT A10;
SQL> COLUMN pval1 FORMAT 9999999999999999999999999999;
SQL> COLUMN pval2 FORMAT A20;
SQL> select sname, pname, pval1, pval2 from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
-------------------- ---------- ----------------------------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 09-17-2011 10:21
SYSSTATS_INFO DSTOP 09-17-2011 10:21
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1752 -- NoWorkload
SYSSTATS_MAIN IOSEEKTIM 10 -- NoWorkload
SYSSTATS_MAIN IOTFRSPEED 4096 -- NoWorkload
SYSSTATS_MAIN SREADTIM -- Workload
SYSSTATS_MAIN MREADTIM -- Workload
SYSSTATS_MAIN CPUSPEED -- Workload
SYSSTATS_MAIN MBRC -- Workload
SYSSTATS_MAIN MAXTHR -- Workload
SYSSTATS_MAIN SLAVETHR -- Workload
Workload 시스템 통계 ( 9i 부터 )
- 애플리케이션으로 부터 일정 시간 동안 발생한 시스템 부하를 측정. 보관함으로써 그 특성을 최적화 과정에 반영할 수 있게 한 기능
- 통계를 수집하는 동안 애플리케이션이 I/O 집약적인 쿼리를 수행했다면 통계정보에 그것이 반영될것이므로 이를 적용한 이후 옵티마지어는 덜 I/O 집약적인 실행계획을 선택할 것이다.
통계 항목
- CPUSPEED : 현재 시스템에서 단일 CPU가 초당 수행할 수 있는 표준 오퍼레이션 개수( 단위 : 백만/초 )
- SREADTIM : 평균적인 Single Block I/O 속도( 단위 : ms = 1/1000초 )
- MREADTIM : 평균적인 Multiblock I/O 속도( 단위 : ms = 1/1000초 )
- MBRC : Multiblock I/O 방식을 사용할 때 평균적으로 읽은 블록 수
- MAXTHR ; I/O 서브 시스템의 최대 처리량( 단위 : 바이트/초 )
- SLAVETHR : 병렬 Slave의 평균적인 처리량 ( 단위 : 바이트/초 )
시스템 통계 수집 방법과 절차
-- 1. 시스템 통계를 담을 테이블을 생성한다. ( mystats )
begin
dems_stats.create_stat_table(
ownname => user
, stattab => 'mystats'
, global_temporary => FALSE
);
end;
/
-- 2. 시스템 통계를 수집한다.
--방법1
begin
dems_stats.gather_system_stats(
gathering_mode => 'interval'
, interval => 720 --720분
, stattab => 'mystats'
, statid => 'OLTP'
);
end;
/
--방법2 중지해야함
begin
dems_stats.gather_system_stats(
gathering_mode => 'interval'
--, interval => 720
, stattab => 'mystats'
, statid => 'OLTP'
);
end;
/
--중지
begin
dems_stats.gather_system_stats(
gathering_mode => 'stop'
--, interval => 720
, stattab => 'mystats'
, statid => 'OLTP'
);
end;
/
-- 참고, 이미 운영서버에 적용 중인 통계를 mystats 테이블에 담으려면..
begin
dems_stats.export_system_stats( 'mystats', 'OLTP', USER );
end;
/
-- 3. OS상에서 Exp/Imp 명령어를 이용해 이관
-- 4. 복제한 통게 테이터를 테스터 서버에 적용
begin
dems_stats.import_system_stats( 'mystats', 'OLTP', USER );
end;
/
-- 참고, 운영 서버에 수집된 값을 메모해 아래와 같이 수동으로 설정할 수도 있다. ( 메모 )
begin
dems_stats.set_system_stats( 'CPUSPEED',500 );
dems_stats.set_system_stats( 'SREADTIM',5.0 );
dems_stats.set_system_stats( 'MREADTIM',30.0 );
dems_stats.set_system_stats( 'MBRC',12 );
end;
/
-- 5. Shared Pool 비운다.
NoWorkLoad 시스템 통계
- 관리자가 명시적으로 선택하지 않아도 CPU 비용 모델이 기본 비용 모델로 사용되게 하려고 오라클 10g에서 도입
- CPU 비용 모델은 시스템 통계가 있을 때만 활성화되기 때문.
- Workload 시스템 통계를 수집하고 반영하는 순간 NoWorkload 시스템 통계는 무시된다.
통계 항목
- cpuspeednw : 테이터베이스 최초 기동 시 측정된 값( NoWorkload 상태에서 측정된 CPU 속도 ( 단위 : Millions/sec )
- ioseektim : 10ms ( I/O Seek Time을 뜻하며, 데이터를 읽으려고 디스크헤드를 옮기는 데 걸리는 시간을 나타낸다.
- iotfrspeed : 4096 bytes/ms ( I/O Transfer 속도를 뜻하며, 하나의 OS 프로세스가 I/O 서브 시스템으로 부터 데이터를 읽는 속도를 나타낸다. )
Workload 시스템 통계가 수집되기 전까지는 아래 공식을 이용해 추정된 값을 사용한다.
- cpuspeed = cpuspeednw
- mbrc = db_file_multiblock_read_count
- sreadtim = ioseektim + db_block_size / iotfrspeed
- mreadtim = ioseektim + mbrc * db_block_size / iotfrspeed
차이점
- Workload : 실제 애플리케이션에서 발생하는 부하를 기준
- NoWorkload : 모든 데이터파일 중에서 오라크이 무작위로 I/O을 발생시켜 수집. ( 시점 애매함 )
-- NoWorkload 수집방법
begin
dbms_stats.gather_system_stats( gathering_mode => 'NOWORKLOAD' );
end;
/
"구루비 데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3278
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.