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

08. 오브젝트 통계(2)




하이브리드 히스토그램

  • 프리퀀시와 높이균형 히스토그램의 일부 특징을 결합한 것이다.
  • 높이균형 히스토그램과 달리 버킷마다 서로 다른 건수의 로우를 가질 수 있다.
  • 각 버킷의 엔드포인트 값에 프리퀀시(엔드포인트 값의 건수)가 추가되었다.
    SELECT val1, count(*), ratio_to_report(count(*)) OVER ()*100 AS percent
      2  FROM t
      3  GROUP BY val1
      4  ORDER BY val1;
    
          VAL1   COUNT(*) PERCENT
    ---------- ---------- -------
            39          2     0.2
            41          4     0.4
            42         13     1.3
            43         21     2.1
            44         26     2.6
            45         54     5.4
            46         66     6.6
            47         86     8.6
            48         81     8.1
            49         97     9.7
            50        102    10.2
            51        103    10.3
            52         80     8.0
            53         64     6.4
            54         76     7.6
            55         50     5.0
            56         30     3.0
            57         21     2.1
            58         12     1.2
            59          6     0.6
            60          5     0.5
            63          1     0.1
    
    
  • 위 예제처럼 22개의 distinct값을 가진 val1 컬럼을 10개의 버킷으로 히스토그램을 생성하도록 명령한다면, 하이브리드 히스토그램이 만들어질 것이다.
    SYS@2017-11-19 22:57:52> SELECT endpoint_value, endpoint_number,
      2         endpoint_number - lag(endpoint_number,1,0)
      3                           OVER (ORDER BY endpoint_number) AS count,
      4         endpoint_repeat_count
      5  FROM user_tab_histograms
      6  WHERE table_name = 'T'
      7  AND column_name = 'VAL1'
      8  ORDER BY endpoint_number;
    
    ENDPOINT_VALUE ENDPOINT_NUMBER      COUNT ENDPOINT_REPEAT_COUNT
    -------------- --------------- ---------- ---------------------
                39               2          2                     2
                44              66         64                    26
                45             120         54                    54
                46             186         66                    66
                47             272         86                    86
                48             353         81                    81
                49             450         97                    97
                50             552        102                   102
                51             655        103                   103
                52             735         80                    
    
  • endpoint_number : 버킷과 관련된 로우수를 의미한다.
  • endpoint_repeat_count : 엔드포인트 값의 프리퀀시를 제공한다.
SYS@2017-11-19 22:59:55> EXPLAIN PLAN SET STATEMENT_ID '44' FOR SELECT * FROM t WHERE val1 = 44;
SYS@2017-11-19 22:59:55> EXPLAIN PLAN SET STATEMENT_ID '50' FOR SELECT * FROM t WHERE val1 = 50;
SYS@2017-11-19 22:59:55> EXPLAIN PLAN SET STATEMENT_ID '56' FOR SELECT * FROM t WHERE val1 = 56;
SYS@2017-11-19 22:59:55>
SYS@2017-11-19 22:59:55> COLUMN statement_id FORMAT A12
SYS@2017-11-19 22:59:55>
SYS@2017-11-19 22:59:55> SELECT statement_id, cardinality
  2  FROM plan_table
  3  WHERE id = 0
  4  ORDER BY statement_id;

STATEMENT_ID CARDINALITY
------------ -----------
44                    26
50                   102
56                    30
  • 하이브리드 히스토그램에서 제공하는 정보가 높이균형 히스토그램보다 더 정확하다.
  • 그러므로 12.1 버전부터는 높이균형 히스토그램은 사용하지 말도록 하자.

히스토그램이 존재하지 않을 경우

SYS@2017-11-19 22:38:15> SELECT endpoint_value, endpoint_number
  2  FROM user_tab_histograms
  3  WHERE table_name = 'T'
  4  AND column_name = 'ID'
  5  ORDER BY endpoint_number;

ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ---------------
             1               0
          1000               1
SYS@2017-11-19 22:38:15>
  • 최소값과 최대값이 각각 0과 1로 조회된다.

8.2.4 확장통계

  • 데이터 간의 의존관계가 존재하는 컬럼들을 상관관계 컬럼이라고 한다.(예: country = 'Korea' And language = 'Korean' , 국가가 한국이 경우 언어도 대부분 한국어를 쓸 것이다.)
  • 사람은 컬럼간의 상관관계를 쉽게 알아차릴 수 있으나, 옵티마이저는 알 수 없다.
  • 이러한 문제를 해결하기 위해 11.1버전부터 확장통계를 지원하기 시작됐다.
    JIGI@2017-11-19 23:11:07> SELECT dbms_stats.create_extended_stats(ownname   => user,
      2                                          tabname   => 'T',
      3                                          extension => '(upper(pad))') AS ext1,
      4         dbms_stats.create_extended_stats(ownname   => user,
      5                                          tabname   => 'T',
      6                                          extension => '(val2,val3)') AS ext2
      7  FROM dual;
    
    EXT1
    ---------------------------- 
    EXT2
    ----------------------------
    SYS_STU0KSQX64#I01CKJ5FPGFK3W9
    SYS_STUPS77EFBJCOTDFMHM8CHP7Q1
    
    JIGI@2017-11-19 23:16:47> SELECT extension_name, extension
      2  FROM user_stat_extensions
      3  WHERE table_name = 'T';
    
    EXTENSION_NAME                 EXTENSION
    ------------------------------ ---------------
    SYS_STU0KSQX64#I01CKJ5FPGFK3W9 (UPPER("PAD"))
    SYS_STUPS77EFBJCOTDFMHM8CHP7Q1 ("VAL2","VAL3")
    
    JIGI@2017-11-19 23:17:59> SELECT column_name, data_type, hidden_column, data_default
      2  FROM user_tab_cols
      3  WHERE table_name = 'T'
      4  ORDER BY column_id;
    
    COLUMN_NAME                    DATA_TYPE HIDDEN
    ------------------------------ --------- ------
    DATA_DEFAULT
    --------------------------------------------------------------------------------
    ID                             NUMBER    NO
    
    
    VAL1                           NUMBER    NO
    
    
    VAL2                           NUMBER    NO
    
    
    VAL3                           NUMBER    NO
    
    
    PAD                            VARCHAR2  NO
    
    
    SYS_STU0KSQX64#I01CKJ5FPGFK3W9 VARCHAR2  YES
    UPPER("PAD")
    
    SYS_STUPS77EFBJCOTDFMHM8CHP7Q1 NUMBER    YES
    SYS_OP_COMBINED_HASH("VAL2","VAL3")
    
    
  • 확장통계는 해시함수에 기반을 두므로 equal 조건절에서만 동작한다. (between이나 <, > 연산자에서는 확장통계를 이용할 수 없다.)
JIGI@2017-11-19 23:20:58> BEGIN
  2    dbms_stats.drop_extended_stats(ownname   => 'JIGI',
  3                                   tabname   => 'T',
  4                                   extension => '(upper(pad))');
  5    dbms_stats.drop_extended_stats(ownname   => 'JIGI',
  6                                   tabname   => 'T',
  7                                   extension => '(val2,val3)');
  8  END;
  9  /

  • 위 명령어를 사용해 확장통계를 제거할 수 있다.
SYS@2017-11-19 23:23:14> BEGIN
  2    dbms_stats.seed_col_usage(sqlset_name => NULL,
  3                              owner_name => NULL,
  4                              time_limit => 30);
  5  END;
  6  /
SYS@2017-11-19 23:23:15>
  • 어떤 컬럼그룹에 확장통계를 수집할지 결정하는 것은 쉬운일이 아니다.
  • seed_col_usage 프로시저를 통해 보고서를 출력하여 좀더 쉽게 결정할 수 있다.(교재와는 다르게 결과가 정상적으로 나오지 않음)
JIGI@2017-11-19 23:27:57> SELECT dbms_stats.create_extended_stats(ownname => user, tabname => 't')
  2  FROM dual;

DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME=>USER,TABNAME=>'T')
--------------------------------------------------------------------------------
###############################################################################
JIGI@2017-11-19 23:27:57>
JIGI@2017-11-19 23:27:57> PAUSE

JIGI@2017-11-19 23:28:02>
JIGI@2017-11-19 23:28:02> SELECT column_name, data_type, hidden_column, data_default
  2  FROM user_tab_cols
  3  WHERE table_name = 'T'
  4  ORDER BY column_id;

COLUMN_NAME                    DATA_TYPE HIDDEN DATA_DEFAULT
------------------------------ --------- ------ -----------------------------------
VAL1                           NUMBER    NO
VAL2                           NUMBER    NO
VAL3                           NUMBER    NO
VAL4                           NUMBER    NO
SYS_STU4K1K3JNH1Z9#_L_V93K3DT4 NUMBER    YES    SYS_OP_COMBINED_HASH("VAL1","VAL2")
SYS_STUPS77EFBJCOTDFMHM8CHP7Q1 NUMBER    YES    SYS_OP_COMBINED_HASH("VAL2","VAL3")
SYS_STUSS3AZ43WO3NKM#66PIG2JYW NUMBER    YES    SYS_OP_COMBINED_HASH("VAL3","VAL4")
SYS_STUS574STTDWYBF6PGQN#XHGGJ NUMBER    YES    SYS_OP_COMBINED_HASH("VAL1","VAL3")
  • creat_extended_stats 를 이용하여 자동으로 확장통계를 생성할 수 있다.
JIGI@2017-11-19 23:21:42> CREATE TABLE persons (
  2    name VARCHAR2(100),
  3    name_upper AS (upper(name))
  4  );
JIGI@2017-11-19 23:21:42>
JIGI@2017-11-19 23:21:42> INSERT INTO persons (name) VALUES ('Michelle');
JIGI@2017-11-19 23:21:42>
JIGI@2017-11-19 23:21:42> SELECT name
  2  FROM persons
  3  WHERE name_upper = 'MICHELLE';

NAME
----------
Michelle

  • 가상컬럼 : 11.1버전부터 도입된 기술로 확장통계의 사용기반이다.

8.2.5 인덱스통계

  • 교재 309페이지 그림 8-5 참조
    JIGI@2017-11-19 23:40:33> SELECT index_name AS name,
      2         blevel,
      3         leaf_blocks AS leaf_blks,
      4         distinct_keys AS dst_keys,
      5         num_rows,
      6         clustering_factor AS clust_fact,
      7         avg_leaf_blocks_per_key AS leaf_per_key,
      8         avg_data_blocks_per_key AS data_per_key
      9  FROM user_ind_statistics
     10  WHERE table_name = 'T';
    
    NAME       BLEVEL LEAF_BLKS DST_KEYS NUM_ROWS CLUST_FACT LEAF_PER_KEY DATA_PER_KEY
    ---------- ------ --------- -------- -------- ---------- ------------ ------------
    T_VAL2_I        1         3        6     1000        187            1           31
    T_VAL1_I        1         2       22     1000        510            1           23
    T_PK            1         2     1000     1000        982            1            1
    SYS_IL0000
    093649C000
    05$$
    
  • blevel : 리프블록에 도달하기 위해 읽어야할 브랜치 블록의 개수(루트블록 포함)
  • leaf_block : 인덱스에 있는 리프 블록의 개수
  • distinct_keys : distinct 키의 갯
  • num_rows : 키의 개수
  • clustering_factor : 인접한 인덱스 항목중에 테이블의 동일 데이터 블록을 참조하지 않는 항목들의 개수(테이블과 인덱스가 비슷하게 정렬되어 있으면 수치가 낮다)
  • avg_leaf_blocks_per_key : 단일 키를 저장한 리프 블록의 평균개수
  • avg_data_blocks_per_key :단일 키에 의해 참조되는 테이블에 있는 평균 데이터 블록의 개수

8.2.6 파티셔닝된 오브젝트에 대한 통계

JIGI@2017-11-19 23:53:04> CREATE TABLE t (id NUMBER, p DATE, sp NUMBER, pad VARCHAR2(1000))
  2  PARTITION BY RANGE (p)
  3  SUBPARTITION BY HASH (sp) SUBPARTITION TEMPLATE
  4  (
  5    SUBPARTITION sp1,
  6    SUBPARTITION sp2,
  7    SUBPARTITION sp3,
  8    SUBPARTITION sp4
  9  )
 10  (
 11    PARTITION q1 VALUES LESS THAN (to_date('2013-04-01','YYYY-MM-DD')),
 12    PARTITION q2 VALUES LESS THAN (to_date('2013-07-01','YYYY-MM-DD')),
 13    PARTITION q3 VALUES LESS THAN (to_date('2013-10-01','YYYY-MM-DD')),
 14    PARTITION q4 VALUES LESS THAN (to_date('2014-01-01','YYYY-MM-DD'))
 15  );
  • 파티셔닝된 오브젝트는 세그먼트의 집합으로 이루어진 논리적 구조이다.
  • 위 16개의 세그먼트는 실제로 테이블스페이스에 데이터를 보유하고 있는 오브젝트인 반면, 4개의 파티션 및 테이블은 메타데이터에만 있는 오브젝트이다.

8.3 오브젝트 통계 수집하기

  • gather_database_stats : 전체 데이터 베이스 통계 수집
  • gather_dictionary_stats : 데이터 딕셔너리에 대해 수집
  • gather_fixed_objects_stats : 데이터 딕셔너리 중 fixed테이블만 수집
  • gather_schema_stats : 지정된 스키마 전체 통계수집
  • gather_table_stats : 하나의 테이블 통계수집
  • gather_index_stats : 하나의 인덱스 통계수집

    CREATE INDEX, ALTER_INDEX 시에도 자동으로 인덱스가 수집된다.
    12.1 버전부터는 CTAS, DIRECT-PATH INSERT시에도 자동으로 통계가 수집된다.

  • 오브젝트 통계 수집에 사용되는 프로시저의 파라미터 (교재 313페이지 표 8-3 참조)

8.3.1 대상 오브젝트

대상 오브젝트 파라미터는 아래와 같이 어떤 오브젝트에 대한 통계를 수집할지 지정한다.

  • ownname : 스키마의 이름 지정(필수)
  • indname : 인덱스의 이름 지정(필수)
  • tabname : 테이블의 이름 지정(필수)
  • partname : 파티션이나 서브파티션의 이름 (지정하지 않으면 전체 파티션 및 서브파티션 통계 수집, 기본값:널)
  • comp_id : 구성요소의 ID 지정(기본값 : 널)
  • granularity : 파티셔닝된 오브젝트에 대해 어느 레벨까지 통계를 수집할지 지정(기본값 : auto)
  • cascade : 인덱스의 처리 여부(기본값 : dbms_stats.auto_cascade)
  • gather_fixed : fixed 테이블에 대한 통계 수집여부 지정(기본값 : false)
  • gather_sys : sys 스키마의 처리여부 (기본값 : false)
  • gather_temp : 임시테이블의 처리여부 ( 기본값 : false)
  • options : 어느 오브젝트를 처리할지 지정( 기본값 : gather)
  • objlist : options 파라미터의 값에 따라 처리되었거나 처리할 오브젝트 목록 반환
  • force : 잠긴 통계를 덮어쓸지 여부지정
  • obj_filter_list : 파라미터로 전달한 필터 중 적어도 하나를 충복하는 오브젝트에 대해서만 통계를 수집하도록 지정

스테일(stale) : 통계와 실제 데이터의 불일치가 심해져서 통계정보가 부정확하다라는 의미
오라클 엔진은 기본적으로 데이터가 10%이상 변경되었을 경우 스테일 상태로 판정한다.

8.3.2 수집옵션

  • estimate_percent
    • 샘플링을 사용할지 여부 지정(0.000001 ~ 100 사이의 값)
    • 100이나 널이면 샘플링을 하지 않음(기본값 : dbms_stats.auto_sample_size)
    • 수치가 너무 낮을 경우 실 데이터와 불일치하는 통계가 만들어질 수 있음(엔진에 의해 자동으로 커질 수 있음)
    • external 테이블은 샘플링이 되지 않음
  • block_sample : 랜덤로우 샘플링(정확함), 블록 샘플링(속도가 빠름) 중 어느것을 사용할지 지정
  • method_opt : 컬럼 통계와 히스토그램의 수집여부 및 수집방법 지정
    • for all columns size 254 : 모든컬럼에 대해 최대 254개의 버킷을 사용하여 수집
    • for all columns size 1 for columns size 254 col1 : 모든컬럼의 통계를 수집하되, col1 컬럼만 254개의 버킷으로 수집한다.
  • degree : 통계수집 시 병렬도를 지정한다.(기본값 : 널)
  • no_invalidate : 처리된 오브젝트에 의존하는 커서의 무효화를 지정( false로 지정될 경우 모든 커서는 즉시 무효화 된다.)

8.3.3 백업테이블

  • stattab : 통계가 저장될 백업테이블 지정
  • statid : 오브젝트 통계를 구분하기 위한 식별자
  • statown : 지정한 백업테이블의 소유자(기본값 : 널)
    dbms_stats.create_stat_table(ownname => user, stattab => 'MYSTATS', tblspace => 'USERS');
    dbms_stats.upgrade_stat_table(ownname => user, stattab => 'MYSTATS');
    
  • 서로 다른 데이터베이스 버전간에 백업테이블을 이동하려면 백업테이블을 해당버전에 맞게 업그레이드 해줘야한다.
    dbms_stats.upgrade_stat_table(ownname => user, stattab => 'MYSTATS');
    dbms_stats.drop_stat_table(ownname => user, stattab => 'MYSTATS');
    

8.4 dbms_stats 패키지 설정하기

8.4.1 과거의 방법

  • 10.2버전에서는 set_param 프로시저를 이용하였다.
    exec dbms_stats.set_param(pname => 'CASCADE', pval => 'TRUE')
    

8.4.2 현재의 방법

  • 11.1 버전부터는 아래와 같은 프로시저로 변경한다.
  • set_global_prefs : 글로벌 환경 설정(set_param 프로시저 대체)
  • set_databse_prefs : 데이터베이스의 환경설정
  • set_schema_prefs : 특정 스키마의 기본값 설정
  • set_table_prefs : 특정 테이블의 기본값 설정
  • get_prefs : 기본 설정값을 조회한다 (get_param 프로시저 대체)
  • reset_global_pref_defaults : 글로벌 기본 설정값 제거
  • delete_database_pref : 데이터베이스 레벨 기본설정값 제거
  • delete_schema_prefs : 스키마레벨 기본 설정값 제거
  • delete_table_prefs : 테이블 레벨 기본 설정갑 제거
    dbms_stats.set_database_prefs(pname => 'CASCADE', pvalue => 'DBMS_STATS.AUTO_CASCADE');
    dbms_stats.set_table_prefs(ownname => 'SCOTT',
    			pname => 'CASCADE', 
    			pvalue => 'FALSE');
    dbms_stats.delete_schema_prefs(ownname => 'scott', pname => 'cascade');
    

8.5 글로벌 임시 테이블로 작업하기

  • 11.2버전까지 글로벌 임시 테이블(Global Temporary Table)은 통계를 수집할 수 없었다.( dbms_stats 처리 시작단계에서 commit을 수행하기 때문)
  • 12.1 버전부터 통계를 수집할 수 있도록 지원됐다. 일반 테이블처럼 gather_table_stats 프로시저를 이용하면 된다.

8.6 펜딩 오브젝트 통계로 작업하기

  • 11.1버전부터 수집한 통계를 딕셔너리에 바로 반영(publish)하지 않는, 테스트목적의 통계수집이 가능하게 되었다.
    
    -- PUBLISH를 FALSE로 하여 통계에 자동반영되지 않도록 설정한다.
      dbms_stats.set_table_prefs(
        ownname => user,
        tabname => 't',
        pname   => 'publish',
        pvalue  => 'false'
      );
      dbms_stats.gather_table_stats(
        ownname          => user,
        tabname          => 'T',
        estimate_percent => 100,
        method_opt       => 'for all columns size 1',
        cascade          => TRUE
      );
    
    -- 세션이나 힌트등을 사용해 통계가 미치는 영향을 테스트 한다.
    SELECT /*+ opt_param('optimizer_use_pending_statistics' 'true') */ * FROM t;
    ALTER SESSION SET optimizer_use_pending_statistics = TRUE;
    
    -- 테스트가 성공적이면 딕셔너리에 반영한다.
    dbms_stats.publish_pending_stats(ownname => user, tabname => 'T');
    
    -- 테스트에 실패했다면 통계를 삭제한다.
    dbms_stats.delete_pending_stats(ownname => user, tabname => 'T');
    
    -- 통계수집 시 딕셔너리에 자동반영되도록 상태를 설정한다.
      dbms_stats.set_table_prefs(
        ownname => user,
        tabname => 't',
        pname   => 'publish',
        pvalue  => 'true'
      );
    

8.7 파티셔닝된 오브젝트로 작업하기

8.7.1 문제점

  • dbms_stats는 파티션 테이블 및 인덱스에 대해 아래의 2가지 방법으로 통계를 수집할 수 있도록 지원한다.
    • 글로벌 통계 : 오브젝트, 파티션, 서브파티션 레벨에 각각 독립적으로 쿼리를 수행하여 통계를 수집한다.(많은 리소스를 사용, 장시간 소요)
    • 파생통계(또는 집계된 통계) : 물리적 레벨에서만 통계를 수집하고, 여기서 수집된 통계를 이용하여 다른레벨 통계를 생성한다.( 적은 리소스 사용, 물리적 레벨만 정확)

-- 서브파티션 레벨에서만 통계를 수집하도록 한다(파생통계)
JIGI@2017-11-20 01:38:37> BEGIN
  2  	     dbms_stats.delete_table_stats(ownname => user,
  3  					   tabname => 't');
  4  	     dbms_stats.gather_table_stats(ownname => user,
  5  					   tabname => 't',
  6  					   estimate_percent => 100,
  7  					   granularity => 'subpartition');
  8  END;
  9  /

JIGI@2017-11-20 01:39:50> SELECT count(DISTINCT sp)
  2  FROM t;

COUNT(DISTINCTSP)
-----------------
	      100

-- 글로벌레벨의 통계가 부정확한 것을 확인 할 수 있다.
JIGI@2017-11-20 01:39:50> SELECT num_distinct, global_stats
  2  FROM user_tab_col_statistics
  3  WHERE table_name = 'T'
  4  AND column_name = 'SP';

NUM_DISTINCT GLOBAL_STATS
------------ ------------
	  28 NO


-- 파티션 레벨의 통계도 부정확한 것을 확인할 수 있다.
JIGI@2017-11-20 01:41:17> SELECT num_distinct, global_stats
  2  FROM user_part_col_statistics
  3  WHERE table_name = 'T'
  4  AND partition_name = 'Q1'
  5  AND column_name = 'SP';

NUM_DISTINCT GLOBAL_STATS
------------ ------------
	  28 NO


JIGI@2017-11-20 01:41:45> SELECT 'Q1_SP1' AS subpartition_name, count(DISTINCT sp) FROM t SUBPARTITION (q1_sp1)
  2  UNION ALL
  3  SELECT 'Q1_SP2', count(DISTINCT sp) FROM t SUBPARTITION (q1_sp2)
  4  UNION ALL
  5  SELECT 'Q1_SP3', count(DISTINCT sp) FROM t SUBPARTITION (q1_sp3)
  6  UNION ALL
  7  SELECT 'Q1_SP4', count(DISTINCT sp) FROM t SUBPARTITION (q1_sp4);

SUBPARTITION_NAME COUNT(DISTINCTSP)
----------------- -----------------
Q1_SP1				 20
Q1_SP2				 28
Q1_SP3				 25
Q1_SP4				 27

-- 파리션 레벨의 통계는 정확한 것을 확인할 수 있다.
JIGI@2017-11-20 01:41:45> SELECT subpartition_name, num_distinct, global_stats
  2  FROM user_subpart_col_statistics
  3  WHERE table_name = 'T'
  4  AND column_name = 'SP'
  5  AND subpartition_name LIKE 'Q1%'
  6  ORDER BY subpartition_name;

SUBPARTITION_NAME NUM_DISTINCT GLOBAL_STATS
----------------- ------------ ------------
Q1_SP1			    20 YES
Q1_SP2			    28 YES
Q1_SP3			    25 YES
Q1_SP4			    27 YES

8.7.2 증분통계

  • 글로벌 통계수집 기능은 수집된 오브젝트 통계가 정확하나, 수집하는데 시간과 리소스가 많이 든다.
  • 증분통계의 목적은 오브젝트 통계 수집에 필요한 시간과 리소스를 낮추면서도 동일한 정확도를 제공하는 것이다.
  • 중분 통계를 사용하려면 아래의 조건이 충족되어야 한다.
    • 11.1 버전이상
    • 처리대상 테이블의 incremental 설정값이 True 로 설정 되어야 한다.
    • 처리대상 테이블의 publish 설정값이 True(기본값) 로 설정 되어야 한다.
    • 처리대상 테이블의 estimate_percent 파라미터가 dbms_stat_auto_sample_size (기본값) 로 설정 되어야 한다.
    • sysaux 테이블스페이스에 추가 여유공간이 있어야 한다.

8.7.3 통계 복사하기

  • 파티션이 자주 추가되고, 파티션 내용이 극심하게 변하는 상황에서는 파티션 레벨 통계를 자주 수집해줘야 한다.
  • 이렇게 통계를 자주 수집하게되면 상당한 오버헤드를 발생하게 된다.
  • 이런경우 다른 파티션에서 새롭게 추가된 파티션에 통계를 복사할 수 있다.
    dbms_stats.copy_tables_stats(ownname => user, 
    			tabname => 't',
    			srcpartname => 'p_2014_q1',
    			dstpartname => 'p_2015_q1',
    			scale_factor => 1);
    

8.8 오브젝트 통계 수집 스케줄링하기

  • 신규 데이터베이스를 생성할 때, gather_database_stats_job_proc 프로시저를 호출하는 job이 기본적으로 구성된다.
  • 해당 job은 gather_database_stats를 호출할 때와 동일한 수행을 한다.

8.8.1 10g방식

  • 일반 job을 이용하여 수행한다.
    
    -- job을 시작하거나 중지하는 방법
    dbms_scheduler.enable(name => 'sys.gather_stats_job');
    dbms_scheduler.disble(name => 'sys.gather_stats_job');
    
    -- sys 유저 이외의 사용자에게 job실행권한을 줄때
    grant alter on gather_stats_job to system;
    

8.8.2 11g와 12c 방식

  • gather_stats_job 작업은 사라지고 자동화된 유지보수 작업로 통합되었다.
    
    -- 유지보수 작업을 활성화하거나 비활성화 하기
    dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', 
    			operation => null,
    			windows_name => null);
    
    dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', 
    			operation => null,
    			windows_name => null)
    

8.9 오브젝트 통계 복원하기

  • 11.2버전부터 새로 수집된 통계로 인해 비효율적인 실행계획이 유도될 경우 이전 통계로 복원할 수 있도록 지원된다.
    
    -- 실행계획의 기본 보존기간은 31일 이다.
    JIGI@2017-11-20 22:19:07> select dbms_stats.get_stats_history_retention() as retention from dual;
    
     RETENTION
    ----------
            31
    
    -- 보존기간을 14일로 조정한다.
    JIGI@2017-11-20 22:20:31> exec dbms_stats.alter_stats_history_retention(retention => 14);
    
    PL/SQL procedure successfully completed.
    
    
    JIGI@2017-11-20 22:20:49> select dbms_stats.get_stats_history_retention() as retention from dual;
    
     RETENTION
    ----------
            14
    
    -- 14일이 지난 통계를 제거한다.
    JIGI@2017-11-20 22:22:27> exec dbms_stats.purge_stats(before_timestamp => systimestamp - INTERVAL '14' DAY);
    
    PL/SQL procedure successfully completed.
    
    -- 수집된 통계 시점 확인
    IGI@2017-11-20 22:26:02> select stats_update_time from dba_tab_stats_history where owner = 'SYS' and table_name='TAB$';
    
    STATS_UPDATE_TIME
    ---------------------------------------------------------------------------
    20-NOV-17 12.20.53.387586 AM +09:00
    
    
    -- SH스키마의 오브젝트 통계를 하루 전으로 복원한다.
    JIGI@2017-11-20 22:31:59> exec dbms_stats.restore_schema_stats(ownname => 'SH', as_of_timestamp => systimestamp - INTERVAL'1' day, force => TRUE);
    
    PL/SQL procedure successfully completed.
    
    

8.10 오브젝트 통계 잠그기

  • 통계수집이 불가능하거나, 최신 통계데이터 수집을 원하지 않는 경우 통계가 사용되지 않도록하거나, 변경되지 않도록 설정할 수 있다.

-- 특정 스키마의 모든 오브젝트 통계를 잠근다.
dbms_stats.lock_schema_stats(ownname => user);

-- 특정 테이블의 통계를 잠근다.
exec dbms_stats.lock_table_stats(ownname => user, tabname => 'T');

-- 특정 스키마의 모든 오브젝트 통계 잠금을 연다.
exec dbms_stats.unlock_schema_stats(ownname => user);

-- 특정 테이블의 오브텍트 통계잠금을 연다.
exec dbms_stats.unlock_table_stats(ownname => user, tabname => 'T');
  • 테이블의 오브젝트 통계가 잠겼을 경우, 통계를 변경하는 프로시저 수행시 ORA-20005오류가 발생한다.
  • 이 경우 FORCE = TRUE 설정으로 잠금을 무시할 수 있다.
dbms_stats.gather_table_stats(ownname => user,
			     tabname => 'T',
			     force   => TRUE);

8.11 오브텍트 통계 비교하기

  • 아래 3가지 경우 동일한 오브젝트에 대해 여러개의 오브젝트 통계가 존재할 수 있다.
    • dbms_stats 패키지가 현재 통계를 백업테이블에 저장할 경우
    • 오라클 엔진에서 통계를 수집할 때마다(과거 통계를 자동으로 보관)
    • 11.1버전부터 펜딩 통계를 수집할 때
      - 지정된 백업테이블과 현재 오브젝트 통계와 비교하거나, 다른 백업 테이블과 비교한다.
      exec dbms_stats.diff_table_stats_in_stattab(ownname => user,
      					 tabname => 'T', 
      					 stattab1 => 'mystats',
      					 statid1 => 'set1', 
      					 stattablown => user, pctthreshold => 10);
      
      -- 지정된 테이블의 현재와 과거의 이력을 비교하거나(time2를 null로 설정), 두개의 과거의 서로다른 이력을 비교한다.
      exec dbms_stats.diff_table_stats_in_history(ownname => user,
      					 tabname => 'T', 
      					 time1 => systimestamp - 1,
      					 time2 => null,
      					 pctthreshold => 10);
      
      -- 지정된 테이블의 현재(또는 과거) 통계와 펜딩통계를 비교한다.
      exec dbms_stats.diff_table_stats_in_pending(ownname => user,
      					 tabname => 'T', 
      					 time_stamp => null,
      					 pctthreshold => 10);
      
      
      

8.12 오브젝트 통계 삭제하기

  • cascade_parts : 하부의 파티션 통계까지 삭제할지 여부를 지정(기본값 : true)
  • cascade_columns : 컬럼 통계도 삭제할지 지정(기본값 : true)
  • cascade_indexes : 인덱스 통계도 삭제할지 지정(기본값 : true)
  • col_stat_type : 삭제할 통계를 지정(11.1버전부터 사용가능)
  • stat_category : 어느 카테고리의 통계를 삭제할지 지정(쉼표를 구분자로 하는 값의 목록을 지정할 수 있다.)
  • 세부내용은 교재 357페이지 표 8-8 참조

8.13 오브젝트 통계 export, import, get, set

  • 차례대로 통계를 추출하거나 입력, 통계정보를 죄회하거나 저장하는 역할을 한다.

8.14 관리작업의 로깅

  • dbms_stats 패키지는 자신들이 수행한 정보를 데이터 딕셔너리에 기록한다.
  • 12.1 버전부터는 작업이 실행될 당시의 파라미터를 확인할 수 있다.
    
    -- 통계를 수집한 시간을 확인 할 수 있다.
    select operation, start_time, (end_time-start_time) day(1) to second(0) as duration
      2  from dba_optstat_operations
      3* order by start_time desc
    
    OPERATION		       START_TIME				DURATION
    ------------------------------ ---------------------------------------- ------------------------------
    gather_table_stats	       20-NOV-17 10.53.52.896611 PM +09:00	+0 00:00:01
    restore_table_stats	       20-NOV-17 10.53.41.867056 PM +09:00	+0 00:00:01
    gather_table_stats	       20-NOV-17 10.50.28.145428 PM +09:00	+0 00:00:01
    gather_table_stats	       20-NOV-17 10.50.24.011018 PM +09:00	+0 00:00:02
    unlock_schema_stats	       20-NOV-17 10.44.01.708343 PM +09:00	+0 00:00:00
    unlock_table_stats	       20-NOV-17 10.43.43.739795 PM +09:00	+0 00:00:00
    delete_table_stats	       20-NOV-17 10.43.36.255505 PM +09:00	+0 00:00:01
    gather_table_stats	       20-NOV-17 10.41.00.101852 PM +09:00	+0 00:00:01
    gather_table_stats	       20-NOV-17 10.40.35.980898 PM +09:00	+0 00:00:00
    gather_schema_stats	       20-NOV-17 10.40.21.782940 PM +09:00	+0 00:00:00
    lock_schema_stats	       20-NOV-17 10.40.12.187797 PM +09:00	+0 00:00:00
    gather_table_stats	       20-NOV-17 10.39.57.475777 PM +09:00	+0 00:00:01
    unlock_schema_stats	       20-NOV-17 10.37.42.033266 PM +09:00	+0 00:00:00
    lock_table_stats	       20-NOV-17 10.36.45.160291 PM +09:00	+0 00:00:00
    lock_schema_stats	       20-NOV-17 10.36.17.903805 PM +09:00	+0 00:00:00
    

8.15 오브젝트 통계를 최신으로 유지하기 위한 전략

  • 변경이 일어나지 않을 데이터(예: 로그성 데이터) 기본적으로 통계를 수집하는 것은 의미가 없다.
  • 하지만 상당한 데이터 변경이 있어난 테이블의 경우 통계를 재수집하는 것이 좋다.(10% 이상의 로우가 변경되었을 경우 테이블 통계가 스테일한 것으로 간주된다.)
  • 통계수집 주기가 너무 길면, 통계수집에 상당한 시간이 걸리고, 높은 자원 사용량을 보이므로 부하를 분산시키기 위해서라도 통계 수집 주기를 짧게 하기를 권고하고 있다.
  • 대량의 데이터를 적재하거나 변경할 경우 스케줄에 의해 통계수집을 기다리지 말고, 통계를 바로 수집하라.
  • 기본수집JOB을 이용하여 통계를 수집하라.
  • 통계수집 후 비효율적인 실행계획이 나타나면, 과거에 문제없던 통계로 복원하라. 또는 통계수집의 비효율 원인을 찾아 제거하라.
  • 가장좋은 방법은 dbsm_stat 패키지를 이용하여 통계를 수집하는 것이다.

문서정보

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