꿈꾸는 개발자, DBA 커뮤니티 구루비

하이브리드 히스토그램

프리퀀시와 높이균형 히스토그램의 일부 특징을 결합한 것이다.
높이균형 히스토그램과 달리 버킷마다 서로 다른 건수의 로우를 가질 수 있다.
각 버킷의 엔드포인트 값에 프리퀀시(엔드포인트 값의 건수)가 추가되었다.


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 : 하나의 인덱스 통계수집
{info}
CREATE INDEX, ALTER_INDEX 시에도 자동으로 인덱스가 수집된다.
12.1 버전부터는 CTAS, DIRECT-PATH INSERT시에도 자동으로 통계가 수집된다.
{info}
오브젝트 통계 수집에 사용되는 프로시저의 파라미터 (교재 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 패키지를 이용하여 통계를 수집하는 것이다.

08. 오브젝트 통계(2) (by jiginet) [2017.11.24]

하이브리드 히스토그램

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

8.2.4 확장통계

8.2.5 인덱스통계

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

8.3 오브젝트 통계 수집하기

8.3.1 대상 오브젝트

8.3.2 수집옵션

8.3.3 백업테이블

8.4 dbms_stats 패키지 설정하기

8.4.1 과거의 방법

8.4.2 현재의 방법

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

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

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

8.7.1 문제점

8.7.2 증분통계

8.7.3 통계 복사하기

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

8.8.1 10g방식

8.8.2 11g와 12c 방식

8.9 오브젝트 통계 복원하기

8.10 오브젝트 통계 잠그기

8.11 오브텍트 통계 비교하기

8.12 오브젝트 통계 삭제하기

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

8.14 관리작업의 로깅

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