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

4. 통계정보 I (by ljh1148) [2012.12.08]

04 통계정보 1

통계정보 종류

테이블 통계

인덱스 통계

(3) 컬럼 통계

Workload 시스템 통계 ( 9i 부터 )

통계 항목

시스템 통계 수집 방법과 절차

NoWorkLoad 시스템 통계

통계 항목

Workload 시스템 통계가 수집되기 전까지는 아래 공식을 이용해 추정된 값을 사용한다.

차이점