by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

4. 통계정보 I




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 )

(3) 컬럼 통계



-- 컬럼 통계만 수집( 오라클 기본 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


h4 (4) 시스템 통계 : I/O, CPU, 하드웨어적 특성

  • CPU 속도
  • 평균적인 Single Block I/O 속도
  • 평균적인 Multiblock I/O 속도
  • 평균적인 Multiblock I/O 개수
  • I/O 서브시스템의 최대 처리량( Throughput )
  • 병렬 Slave의 평균적인 처리량 ( Throughput )

h4 설명

  • 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;
/

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.