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 성능과 데이터베이스 크기에 따라 적게는 수초 길게는수분이 소요