오라클 성능 고도화 원리와 해법 II (2016년)
04. 통계정보 I
(1) 테이블 통계
analyze table emp compute statistics for TABLE;
analyze table emp estimate statistics sample 5000 rows for TABLE;
analyze table emp estimate statistics sample 50 percent for TABLE;
analyze 는 사용중단, dbms_stats 패키지 사용 권고
begin
dbms_stats.gather_table_stats('scott' , 'emp'
, cascade=>false, method_opt=>'for columns' );
end ;
select
TABLE_NAME, 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'
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ---------------- ------------ ------------------------ ------------------ ---------------------- -------------------------------------------------- -------------------------------------- ---------------------- --------------------------
SALGRADE 5 5 0 0 10 0 0 5 2013/01/18 22:00:13
BONUS 0 0 0 0 0 0 0 0 2013/01/18 22:00:10
EMP 14 5 0 0 38 0 0 14 2017/04/27 17:45:54
DEPT 4 5 0 0 20 0 0 4 2013/01/18 22:00:13
(2) 인덱스 통계
analyze INDEX PK_EMP compute statistics; --인덱스 통계
analyze table scott.emp compute statistics for ALL INDEXES ; --테이블에 속한 모든 인덱스 통계
analyze table scott.emp compute statistics for TABLE for ALL INDEXES ; -- 테이블과 인덱스 통계 동시
begin
dbms_stats.gather_index_stats( ownname => 'scott' , indname => 'PK_EMP' );
end ;
begin
dbms_stats.gather_index_stats( 'scott' , 'emp' , cascade=>true);
end ;
select
INDEX_NAME, 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'
;
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY SAMPLE_SIZE LAST_ANALYZED
------------------------------ ------------ ---------------------- ---------------------------------- ---------------- -------------------------- ---------------------------------------------- ---------------------------------------------- ---------------------- --------------------------
PK_EMP 0 1 1 14 14 1 1 14 2017/04/27 17:53:18
compute statistics 옵션 : 인덱스를 최초 생성하거나 재생성할 때 설정하면 자동으로 인덱스 통계까지 수집
create index emp_name_idx on scott.emp(ename) COMPUTE STATISTICS ;
alter index emp_name_idx rebulid COMPUTE STATISTICS ;
"10g부터는 자동설정 , _optimizer_compute_index_stats --> false 로 설정으로 비활성 "
(3) 컬럼 통계
analyze table scott.emp compute statistics for ALL COLUMNS SIZE 254;
analyze table scott.emp compute statistics for COLUMNS ename SIZE 10, sal SIZE 20;
analyze table scott.emp compute statistics for COLUMNS SIZE 20 ename，sal, hiredate;
"size 옵션은 히스토그램의 최대 버킷 개수를 지정하는 옵션으로서 1부터 254까지 허용(default : 75, disabled : 1 )"
"테이블, 인덱스，컬럼 통계를 아래와 같이 동시에 수집"
analyze table scott.emp compute statistics
for table
for all indexes
for all indexed columns size 254;
*dbms_stats 패키지에 컬럼통계만 따로 수집을 불가능 *
select
COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED , SAMPLE_SIZE, AVG_COL_LEN , HISTOGRAM
from all_tab_columns
where owner ='SCOTT'
and table_name = 'EMP'
COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE AVG_COL_LEN HISTOGRAM
------------------------------ ------------------------ -------------------------------- -------------------------------- -------------- ------------------ ---------------------- -------------------------- ---------------------- ---------------------- ------------------
EMPNO 14 C24A46 C25023 7.1e-002 0 1 2017/04/28 11:30:42 14 3 NONE
ENAME 14 4144414D53 57415244 7.1e-002 0 1 2017/04/28 11:28:37 14 5 NONE
JOB 5 414E414C595354 53414C45534D414E 3.6e-002 0 5 2017/04/28 11:30:42 14 7 FREQUENCY
MGR 6 C24C43 C25003 3.8e-002 1 6 2017/04/28 11:26:51 14 3 FREQUENCY
HIREDATE 13 77B40C11010101 77BB0517010101 3.6e-002 0 13 2017/04/28 11:28:37 14 7 FREQUENCY
SAL 12 C209 C233 3.6e-002 0 12 2017/04/28 11:28:37 14 3 FREQUENCY
COMM 4 80 C20F 0.25 10 1 2017/04/28 11:26:51 14 2 NONE
DEPTNO 3 C10B C11F 3.6e-002 0 3 2017/04/28 11:30:42 14 2 FREQUENCY
"컬럼 히스토그램 조회"
select
ENDPOINT_VALUE, ENDPOINT_NUMBER, ENDPOINT_ACTUAL_VALUE
from dba_histograms
where owner ='SCOTT'
and table_name = 'EMP'
and COLUMN_NAME ='DEPTNO'
order by ENDPOINT_VALUE
ENDPOINT_VALUE ENDPOINT_NUMBER ENDPOINT_ACTUAL_VALUE
---------------------------- ------------------------------ -----------------------------------------------------
10 3
20 8
30 14
(4) 시스템 통계
- CPU 속도
- 평균적인 Single Block I/O 속도
- 평균적인 Multiblock I/O 속도
- 평균적인 Multiblock I/O 개수
- I/O 서브시스탱의 최대 처리량 (Throughput)
- 병렬 Slave의 평균적인 처리량 (Throughput)
- 과거에는 이들 항목이 고정된 상수였( 실제 오라클이 설치된 운영 시스댐 사양이 달라 옵티마이저가 잘못된 선택 가능 )
- 시스템 사양뿐만 아니라 애플리케이션이 OLTP성이냐 DW 성이냐에 따라서도 위 항목의 특성이 상이
- 오라클은 9i부터， 제품이 설치된 하드웨어 및 애플리케이션 특성에 맞는 시스템 통계를 수집하고 이를 활용함으로써 옵티마이저가 보다 합리적으로 선택할 수 있도록 함
 |
Workload 시스템 통계
- 9i에서 처음 도입된 Workload 시스템 통계는, 애플리케이션으로부터 일정 시간 동안 발생한 시스템 부하를 측정/보관함으로써 그 특성을 최적화 과정에 반영
- 통계를 수집하는 동안 애플리케이션이 I/O 집약적인 쿼리를 주로 수행했다면 통계 정보에 그것이 반영될것 이므로 이를 적용한 이후 옵티마이저는 덜 I/O 집약적인 실행 계획을 선택
 |
- 단일 CPU로 초당 1,149백만 개의 표준 오퍼레이션을 수행
- Single Block I/O 속도는 평균적으로 5.253 ms이다
- Multiblock I/O 속도는 평균적으로 3.122 ms이다 .
- Multiblock I/O 방식을 사용할 때 평균적으로 15개 블록씩 읽는다 .
- I/O 서브시스램으로부터 초당 최대 1.73 GB를 읽을 수 있다.
- 평균적으로 병렬 Slave가 초당 20MB를 읽는다.
(1) 실제 애플리케이션에서 발생하는 부하를 측정한 값
- 수집기간 동안 애플리케이션에서 Full Table Scan 이 발생하지 않는다면 mreadtim와 mbrc 항목이 측정되지 않을것 이며， 병렬쿼리가 수행되지 않는다면 slavethr 항목이 측정되지 않는다.
(2) Workload 시스템 통계를 제대로 활용하려면 통계 수집 전략 수립
- 차세대 시스템을 구축 중이라면 대표성 있는 시간대를 선택해 현 운영 서버에서 실제로 수집한 시스템 통계를 테스트 서버로 Export/Import 하고서 개발을 진행
- 개발 완료 후 현재와 다른 사양을 가진 서버에서 운영할 계획이거나 전혀 새로운 애플리케이션을 개발할 때는 그 특성 이 잘 반영되도록 부하 테스트 시나리오를 만들고 시스템 통계를 수집
- 운영 서버에서 수집한 정보를 테스트 서버로 복제할 때를 기준으로 시스템 통계 수집 방법과 절차
"1.시스템 통계를 담을 테이블(mystats)을 생성"
begin
dbms_stats.create_stat_table(
ownname => USER
, stattab => 'mystats'
, tblspace => 'USERS'
, global_temporary => FALSE
) ;
end;
"2. 시스템 통계를 수집"
"<방법1> 720분 동안 수집하고 자동으로 중지 "
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/lmp 명령어를 이용해 통계 테이블(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 ) ;
end;
"5. 시 스템 통계를 적용하더라도 기존에 캐싱돼 있던 실행계획에는 영향을 미치지 않으므로 Shared Pool을 비운다."
alter system flush shared_pool ;
NoWorkload 시스템 통계
- 관리자가 명시적으로 선택하지 않더라도 CPU 비용 모델이 기본 비용 모델로 사용되게 하려고 오라클 10g에서 NoWorkload 시스템 통계를 도입
- CPU 비용 모델은 시스템 통계가 있을 때만 활성화
 |
- Workload 시스템 통계를 수집하고 반영하는 순간 NoWorkload 시스템 통계는 무시
- Workload 시스템 통계가 수집되기 전까지는 아래 공식을 이용해 추정된 값을 사용
- cpuspeed = cpuspeednw
- mbrc = db_file_multiblock_read_count
- sreadtim = ioseektim + db_block_size / iotfrspeed
- mreadtim = ioseektim + mbrc * db_block_size / iotfrspeed
- 시스템 통계를 제대로 활용하려면 Workload 시스템 통계가 바람직하지만 이를 수집하기 어려운 환경이 존재하며， 그럴때 NoWorkload 시스템 통계를 사용
(EX)시스템 통계는 프로젝트 초기, 늦어도 단위 테스트 전에는 확정 돼야 하는데 그 시점에 의미 있게 부하 태스트 시나리오를만들기 어려울때가많다. - 어떤 이유에서건 NoWorkload 시스템 통계를 이용하기로 했다면 기본 설정 값을 그대로 사용하지 말고， 적당한 부하를 준 상태에서 NoWorkload 시스템 통계를 수집
- 부하가 없는 상태에서 수집하는 것이라고 오해하기 쉬운데. Workload와 마찬가지로 부하를 준 상태에서 측정된 값을 사용해야 시스템 통계로서 의미가 있음
- Workload 통계와 통계를 수집하는 방식에 차이, Workload는 실제 애플리케이션에서 발생하는 부하를 기준으로 각 항목의 통계치를 측정하는 반면 NoWorkload는 모든 데이터파일 중에서 오라클이 무작위로 I/O를 발생시켜 통계를 수집
- 따라서 시스템 부하 정도가 심할 때 NoWorkload 시스템 통계를 수집하면 구해진 값들도 달라진다.
- 이런 차이점을 이해한다면 Workload와 마찬가지로 NoWorkload도 시스템 부하가 어느 정도 있는 상태에서 수집되는 것이 바람직하다
- 시스템 통계를 도입한 취지가 최적화 과정에 시스템 특성을 반하자는 것임을 상기하자.
- NoWorkload 시스템 통계를 수집할 때는 dbms_stats.gather_system_stats 프로시저를 아무런 인자도 주지 않고 호출하거나 gathering_mode 인자를 아래와 같이 지정
begin
dbms_stats.gather_system_stats( gathering_mode => 'NOWORKLOAD') ;
end;
- 이 프로시저를 실행하는 동안 I/O 서브시스템에 약간의 부하가 발생하며. I/O 성능과 데이터베이스 크기에 따라 적게는 수초 길게는수분이 소요
"구루비 주주클럽 스터디모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3351
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.