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

h1.통계정보 관련 새로운 기능

자동 컬럼 그룹 감지

확장 통계의 기능

11g R2에서 소개되었으며, dbms_stats 패키지를 사용해서 수집

컬럼 그룹 통계(Column Group Statistic) : 특정 테이블에서 복수 개의 컬럼이 결합된 통계 정보 수집. 오라클 옵티마이저가 SQL문에서 확장 통계가 수집된 컬럼을 사용할 때 이 Cardinality를 예측하는 데 사용
표현식 통계(Expression Statistic) : 조건절에 표현식이 사용된 SQL문을 사용할 때 옵티마이저에게 추가적인 통계를 제공

12c New Feature : 컬럼 그룹 통계가 이점이 있을 것 같은 컬럼의 조합을 감지하고 이를 자동으로 컬럼 그룹 통계로 만드는 작업을 수행. SQL Plan Directives를 통해 이루어 진다.



DBMS_STATS.SEED_COL_USAGE ( sqlset_name IN VARCHAR2, owner_name IN VARCHAR2, time_limit IN POSITIVE DEFAULT NULL);


SQL> exec dbms_stats.seed_col_usage(null,'SCOTT',1800);  

* 컬럼 사용 통계 정보 수집
* 특정 기간 동안 현재 부하를 모니터링해서 생성
* 컬럼 그룹 통계를 생성할 수 있는 특정 컬럼을 제안
* 백그라운드 작업 수행


-- 위의 seed_col_usage를 수행 후, 아래 SQL 문장 수행
SQL> select mgr, job, count(*)
  2  from emp
  3  where job='CLERK' and mgr=7902 and hiredate < sysdate
  4  group by mgr, job;


---------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |   196K|  3840K|  4693   (1)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT    |             |   196K|  3840K|  4693   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL      | COPY_EMP    |   196K|  3840K|  4693   (1)| 00:00:01 |
----------------------------------------------------------------------------------------

SQL> select dbms_stats.report_col_usage('GHLEE','EMP') from dual;

###############################################################################

COLUMN USAGE REPORT FOR GHLEE.EMP
.................................    --> 컬럼 조건 상태 확인

1. HIREDATE                            : RANGE
2. JOB                                 : EQ
3. MGR                                 : EQ

DBMS_STATS.REPORT_COL_USAGE('GHLEE','EMP')   
--------------------------------------------------------------------------------
4. (JOB, MGR)                          : FILTER    --> 컬럼 그룹 통계 제안
###############################################################################

SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS('GHLEE', 'EMP') FROM DUAL

DBMS_STATS.CREATE_EXTENDED_STATS('GHLEE','EMP')
--------------------------------------------------------------------------------
###############################################################################

EXTENSIONS FOR GHLEE.EMP
........................

1. (JOB, MGR)                          : SYS_STULPA1A#B6YL4KQ59DQO3OADQ created
###############################################################################

-- 확장 통계 생성 후 통계 정보 재 수집
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('GHLEE',  'EMP', CASCADE=>TRUE);


SQL> select column_name, num_distinct, histogram
  2  from user_tab_col_statistics
  3  where table_name = 'EMP' 
  4  order by 1;


COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
COMM                                      3 NONE
DEPTNO                                    3 NONE
EMPNO                                    14 NONE
ENAME                                    14 NONE
HIREDATE                                 13 FREQUENCY
JOB                                       5 FREQUENCY
MGR                                       6 FREQUENCY
SAL                                      12 NONE
SYS_STULPA1A#B6YL4KQ59DQO3OADQ            8 FREQUENCY

SQL> insert into emp
  2  select rownum no, ename, job, mgr, hiredate, sal, comm, deptno
  3  from (select * from emp),
  4  (select rownum from dual connect by level <= 200000);

SQL> exec dbms_stats.gather_table_stats('GHLEE', 'EMP', CASCADE=>TRUE);


SQL> select column_name, num_distinct, histogram
  2  from user_tab_col_statistics
  3  where table_name = 'EMP' 
  4  order by 1;

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
COMM                                      3 NONE
DEPTNO                                    3 NONE
EMPNO                               2800014 NONE
ENAME                                    14 NONE
HIREDATE                                 13 FREQUENCY
JOB                                       5 FREQUENCY
MGR                                       6 FREQUENCY
SAL                                      12 NONE
SYS_STULPA1A#B6YL4KQ59DQO3OADQ            8 FREQUENCY


SQL> select mgr, job, count(*)
  2  from emp
  3  where job='CLERK' and mgr=7902 and hiredate < sysdate
  4  group by mgr, job;


-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   200K|  3906K|  4708   (1)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|      |   200K|  3906K|  4708   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | EMP  |   200K|  3906K|  4708   (1)| 00:00:01 |
-----------------------------------------------------------------------------
-- MGR과 JOB 컬럼 값의 선택도가 높지 않기 때문에 FULL TABLE SCAN을 이용


SQL> update emp             
  2  set mgr=empno;

SQL> exec dbms_stats.gather_table_stats('GHLEE', 'EMP', CASCADE=>TRUE);

SQL> select column_name, num_distinct, histogram
  2  from user_tab_col_statistics
  3  where table_name = 'EMP' 
  4  order by 1;

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
COMM                                      3 NONE
DEPTNO                                    3 NONE
EMPNO                               2800014 NONE
ENAME                                    14 NONE
HIREDATE                                 13 FREQUENCY
JOB                                       5 FREQUENCY
MGR                                 2800014 NONE
SAL                                      12 NONE
SYS_STULPA1A#B6YL4KQ59DQO3OADQ      2776064 NONE


SQL> select mgr, job, count(*)
  2  from emp
  3  where job='CLERK' and mgr=7902 and hiredate < sysdate
  4  group by mgr, job;


-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    22 |     4   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT        |          |     1 |    22 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP      |     1 |    22 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_IDX1 |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

통계 정보 수집 동시 실행

Concurrent라는 새로운 설정값으로 복수 개의 파티션 테이블을 가진 스키마가 있는 경우 통계 수집을 동시에 수행 할 수 있다.
Concurrent되면 스케쥴과 관리, 통계 정보 동시 실행을 하기 위해 내부적으로 오라클 스케줄러와 Oracle Advanced Queueing, Resource Manager를 사용한다.
Job Scheduler는 허용되는 Scheduler Job의 총 수를 조절하기 위해 job_queue_processes 파라미터를 정의해서 컨트롤 하는 동시성 레벨을 결정한다.
Concurrent의 설정 값
- MANUAL : 동시 통계 수집은 수동 작업에서만 발생
- AUTOMATIC : 동시 통계 수집은 자동 통계 수집에서만 발생
- ALL : 동시 통계 수집은 수동과 자동 통계 수집에서 발생
- OFF : 동시 통계 수집 비활성화

동시 통계를 사용하려면 CREATE JOB/MANAGE SCHEDULER/MANAGE ANY QUEUE 권한이 필요
SYSAUX 테이블 스페이스가 반드시 온라인이고 사용가능해야 함
JOB_QUEUE_PROCESSES 파라미터 값은 3 이상 이여야함
Database Resource Manager 기능은 사용 가능하도록 설정해야 함



SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     TRUE
resource_manager_cpu_allocation      integer     64
resource_manager_plan                string


SQL> alter system set resource_manager_plan = 'DEFAULT_PLAN';

SQL> show parameter job_queue

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000


SQL> exec dbms_stats.set_global_prefs('CONCURRENT', 'ALL');


SQL> SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;

DBMS_STATS.GET_PREFS('CONCURRENT')
--------------------------------------------------------------------------------
ALL

-- 해당 스키마 각각의 오브젝트(테이블, 파티션, 인덱스, 기타)별로 Job을 생성한다. 이런 통계 작업들은 병렬로 각각의 작업을 수행 할 수 있다.
SQL> exec dbms_stats.gather_schema_stats('GHLEE', CASCADE => TRUE);


SQL> SELECT TARGET, TARGET_TYPE, JOB_NAME, TO_CHAR(START_TIME, 'DD-MON-YYYY HH24:MI:SS') DAY
  2  FROM DBA_OPTSTAT_OPERATION_TASKS
  3  WHERE STATUS = 'COMPLETED'
  4  AND OPID = (SELECT MAX(ID)
  5                FROM DBA_OPTSTAT_OPERATIONS
  6              WHERE OPERATION = 'gather_schema_stats')

TARGET                                                       TARGET_TYPE          JOB_NAME   DAY
------------------------------------------------------------ -------------------- ---------- ------------------------------
GHLEE.RTE_BACKUP_INX.RTE_BACKUP_20151125         INDEX PARTITION                 13-JAN-2017 10:49:35
GHLEE.RTE_BACKUP_INX.RTE_BACKUP_20151126         INDEX PARTITION                 13-JAN-2017 10:49:35
GHLEE.RTE_BACKUP_INX.RTE_BACKUP_20151127         INDEX PARTITION                 13-JAN-2017 10:49:35
GHLEE.RTE_BACKUP_INX.RTE_BACKUP_20151128         INDEX PARTITION                 13-JAN-2017 10:49:35
GHLEE.RTE_BACKUP_INX.RTE_BACKUP_20151129         INDEX PARTITION                 13-JAN-2017 10:49:35
GHLEE.RTE_BACKUP_INX.RTE_BACKUP_20151130         INDEX PARTITION                 13-JAN-2017 10:49:35
GHLEE.RTE_BACKUP_INX.RTE_BACKUP_20151201         INDEX PARTITION                 13-JAN-2017 10:49:35
GHLEE.RTE_BACKUP_INX.RTE_BACKUP_20151202         INDEX PARTITION                 13-JAN-2017 10:49:35
GHLEE.RTE_BACKUP_INX.RTE_BACKUP_20151203         INDEX PARTITION                 13-JAN-2017 10:49:35
GHLEE.RTE_BACKUP_INX.RTE_BACKUP_20151204         INDEX PARTITION                 13-JAN-2017 10:49:35

톰 카이트의 한마디

동시 통계를 사용하기 전에 이 작업이 적절한지 먼저 확인해야 함.
서버의 리소스를 가급적이면 많이 사용하기 위해서 단일 작업으로 디자인 되었다. 모든 머신의 리소스를 소모하는 단일 작업을 위해 고려해야 하는 것은 이 작업 이외의 어떤 작업도 수행하지 않아야 한다는 사실이다. DW와 같은 환경에서 통계 정보를 구동하는 것이 아니라면 높은 레벨의 동시 통계 정보 수집은 주의해야 한다.
또한, JOB_QUEUE_PROCESS 파라미터의 디폴트 값이 1000이라는 사실이다. 만약 많은 개수의 저용량 디스크를 보유하고 있거나 500개 이상의 CPU 개수를 가진 머신이 아니라면
1000이라는 수치는 너무 높을 수 있다. 이 설정 값으로 적절한 값이 사용되는지를 반드시 확인해야 한다.(물리적인 CPU 개수의 2~4배 정도록 시작하는 것이 좋다)

증분 통계 개선 사항

증분 통계의 목적은 변경된 파티션에만 해당 파티션 오브젝트에 대한 글로벌 통계를 도출하여 특정 파티션 테이블에 대한 전체 통계 수집의 오버 헤드를 방지하는 것

파티션 유지 보수 작업에서의 증분 통계

오라클 12c에서는 파티션 유지보수 작업에서 수동으로 통계를 수집하는 경우 데이터 베이스는 해당 파티션 유지보수 작업에서 기존의 세그먼트와 관련된 시놉시스(Synopsis)를 재사용 가능

Synopsis

파티션의 컬럼과 파티션을 위한 통계 메타 데이터를 시놉시스라고 한다.
증분 글로벌 통계는 테이블의 각 파티션마다 시놉시스를 생성하고 이를 저장해서 이용한다.
각 시놉시스는 SYSAUX 테이블스페이스에 10KB 크기로 저장되고 글로벌 통계는 각 파티션의 시놉시스를 집계해서 생성한다.
따라서 Full Table Scan을 줄일 수 있다.

12C에서 비파티션 테이블에도 시놉시스를 만들 수 있다. 이를 통해 파티션 Exchange 작업 동안에 증분 통계를 유지할 수 있어서 파티션 Exchange 작업 후에 따로 통계를 수집할 필요성이 없어짐
incremental_level 설정으로 어떤 또는 언제 시놉시스가 수집되는지를 조정할 수 있다.
incremental_level 설정에 TABLE이나 PARTITION(Default)값을 설정할 수 있음
- PARTITION으로 설정하면 시놉시스는 파티션된 테이블의 파티션 레벨만 수집
- TABLE로 설정되어 있으면 시놉시스는 테이블에 생성


-- INCREMENTAL TRUE로 설정
SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('GHLEE', 'RTE_BAK_20170113', 'INCREMENTAL', 'TRUE');

-- 일반 테이블에 INCREMENTAL 설정
SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('GHLEE', 'RTE_BAK_20170113', 'INCREMENTAL_LEVEL', 'TABLE');

-- 통계 정보 수집
SQL> exec dbms_stats.gather_table_stats('GHLEE', 'RTE_BAK_20170113');

-- PARTITION TABLE에 INCREMENTAL TRUE 설정
SQL>  exec dbms_stats.set_table_prefs('GHLEE', 'RTE_BACKUP', 'INCREMENTAL', 'TRUE');


-- 파티션 통계 정보 수집

SQL> exec dbms_stats.gather_table_stats('GHLEE', 'RTE_BACKUP','RTE_BACKUP_20160224', granularity => 'partition');

-- 파티션 exchange 수행
SQL> alter table CP_LG_RTE_BACKUP exchange partition CP_LG_RTE_BACKUP_20160224 with table RTE_BAK_20170113;

-- 비 파티션 테이블의 시놉시스는 파티션 테이블로 이동되고 글로벌 통계도 업데이트가 되므로, Exhcnage 한 후에 GRADE 파티션 테이블에 대해 통계 정보를 수집할 필요성이 없어진다.

Stale 또는 파티션 통계가 lock된 테이블의 증분 통계

모든 DML 변경이 파티션이나 서브 파티션에서 발생한 경우 12c 이전 버전에서는 기본적으로 파티션이나 서브 파티션은 Stale 된 것으로 간주 된다.
해당 테이블의 글로벌 통계를 포함한 파티션 테이블에 대한 전체 통계를 수집하려면 DBMS_STATS를 실행해야 한다.
특정 파티션에 대한 통계가 Lock 되어 있는 경우는 Stale 되었다고 하더라도 통계를 다시 수집할 수 없다.
12c 부터는 incremental_staleness라는 설정을 통해 Stale 상태로 간주되기 전에 특정 파티션에 허용되는 Stale 비율을 조정할 수 있다.
- USE_STALE_PERCENT : (USE_LOCKED_STATS와 같이 사용 될 수 있음) 오라클이 파티션이나 서브 파티션이 Stale 여부를 결정하는 것은 디폴트 STALE_PERCENT 설정을 사용한다.
- USE_LOCKED_STATS : (USE_STALE_PERCENT와 같이 사용될 수 있음) 만약 특정 파티션이나 서브 파티션이 LOCKED 되어 있는 상태라면 DML 작업으로 변경 여부에 상관없이 Stale 상태를 고려하지 않음
- NULL : Default 값이고 오라클 11g의 동작과 동일. 파티션이나 서브파티션이 DML 작업에 의해서 변경이 발생하면 STALE 상태로 간주.

Bulk Load를 위한 온라인 통계 수집

Bulk Load 작업의 종류
- CREATE TABLE AS SELECT
- INSERT INTO.. SELET /*+ APPEND */를 사용한 Direct path 모드의 insert
- Parallel Insert

Direct Path 모드사용 시 빈 파티션 테이블에 Insert할 때 글로벌 통계 정보만 수집 되고, 파티션 레벨 정보는 수집되지 않는다.
Extended 파티션 구문을 사용하여 빈 파티션에 직접 INSERT 하는 경우 해당 파티션에 대한 통계는 수집 되고, 글로벌 통계는 수집 되지 않는다.

Extended 파티션 Syntax

INSERT INTO mypart_tab PARTITION part_1 VALUES (1,'Some Text');

INCREMENTAL 설정이 활성화 되어 있는 경우 통계는 INSERT 문이 완료됨과 동시에 사용가능하게 된다. INSERT 문이 롤백되면 통계도 삭제 된다.
데이터가 존재하는 오브젝트에 INSERT할 때는 DBA_TAB_COL_STATISTICS 뷰를 통해 확인 가능하다.이 뷰에서 BULK LOAD 동안 컬럼 통계 정보가 수집된다면 이 뷰의NOTES 컬럼에 STATS_ON_LOAD라는 값으로 표시된다.
dbms_stats.gather_table_stats를 GATHER_AUTO로 수집하는 경우에는 누락되거나 Stale 된 통계만을 수집한다. 이런 경우에는 인덱스에 대한 통계가 생성되므로 테이블이나 컬럼 통계 정보를 수집할 필요가 없다.

GATHER AUTO

exec dbms_stats.gather_schema_stats(ownname => 'SCOTT', options => 'GATHER AUTO');

테이블 레벨의 Default로 GATHER AUTO로 설정하는 것은 Bulk Load를 테이블에 수행하는 경우에 통계 수집 시간을 줄이는데 간헐적으로 도움이 된다.

GATHER AUTO

SQL> CREATE TABLE MY_OBJECTS_STATS AS SELECT /*+ GATHER_OPTIMIZER_STATISTICS */ * FROM MY_OBJECTS; ==> Default behavior ;hint is of no help here.

SQL> CREATE TABLE MY_OBJECTS_NOSTATS_HINT AS SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */ * FROM MY_OBJECTS; ==> Hint instructing Oracle not to collect Stats

SYS가 소유한 오브젝트에 대해서는 작동하지 않는다.
Nested table, IOT, External, on commit delete rows로 정의된 GTT에서도 작동하지 않는다.
테이블 Prference중에 Publish가 FALSE로 설정되었거나 Virtual Column을 가진 테이블에서도 작동하지 않는다.

참고자료
11g에서는 통계정보의 'Gathering'과 'Publish'를 분리할 수 있게 되어, 검증된 통계정보만 'Publish'되도록 하여 어플리케이션 성능을 보장받을 수 있음.

SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISH
FROM DUAL;

– TRUE : 통계정보가 수집되자마자 바로 Publish
– FALSE : 수집된 통계정보는 Publish 되지 않고 Pending

참고 사이트 : http://wiki.gurubee.net/pages/viewpage.action?pageId=14221432

Global Temporary Table을 위한 Session-Private 통계 정보

12c 부터 GTT는 각 개별적인 세션별로 통계 정보 세트를 가질 수 있음.
통계 환경 설정중(Preference)중 GLOBAL_TEMP_TABLE_STATS를 SESSION으로 설정하여 사용 가능
세션에 지정된 통계를 설정하는 경우는 사용하고 있는 테이블의 세션 내에서 GTT 통계를 수집할 수 있고 이 통계는 해당 세션에서만 유일
다른 유저는 동시에 필요에 따라서 세션별 통계나 공유 통계 정보 중 하나를 사용할 수 있다.
옵티마이저가 GTT가 포함된 SQL문에 대한 실행 계획을 생성할 때 세션 별 통계 정보가 존재하는지를 체크한다.
만약 존재하지 존재하지 않다면 옵티마이저는 공유 통계 정보(또는 Dynamic Sampling)를 사용한다.
트랜잭션별 GTT에 변경사항을 적용하지 않도록 DBMS_STATS프로그램이 변경되어, GTT에 있는 데이터가 정리되더라도 이에 따른 통계 수집에 미치는 부작용은 없다.
DBMS_STATS 패키지의 환경설정인 GLOBAL_TEMP_TABLE_STATS를 기반으로 통게를 수집하며, 이 값이 SHARED로 설정되어 있다면 DBMS_SATTS에 의해 수집된 통게는 공유 통계가 된다.
환경 설정이 SESSION으로 설정되면 DBMS_STATS가 수집할 때 세션별 통계가 될 것이다.

리포트 모드에서 통계 수집 옵션 수행

실제로 통계 정보가 수집되는 것이 아니라 실제 통계 정보가 수집되는 대상 오브젝트에 대한 분석될 데이터베이스의 리포트 정보 만이 출력
해당 오브젝트가 실제로 통계가 수집되었는지 또는 통계 수집이 필요한지를 정확하게 결정하는데 동움을 준다.


 SQL>  select dbms_stats.report_gather_schema_stats('GHLEE', DETAIL_LEVEL=>'TYPICAL')
    2  from dual;

과거 통계 수집 작업 리포트

dbms_stats.report_stats_operations : 시작과 종료 시간 내에 발생한 모든 통계 작업에 대해서 보여주는 리포트를 생성
dbms_stats.report_single_Stats_operations : 특정 통계 수집 작업에 대한 리포트를 생성

옵티마이저 관련 신규 기능

적응적인 쿼리 최적화(Adaptive Query Optimization)

AQQ는 실행계획을 런타임 실행 시에 조정하고 또한 향후에 더 나은 실행 계획을 생성하기 위해 옵티마이저에 도움을 주는 추가 메타데이터를 수집하는 오라클 데이터베이스 기능 집합
- Adaptive Plan(적응적인 계획)
- Adaptive Statistic(적응적인 통계 정보)

Adaptive Plan

SQL이 실행되는 동안 Statistics Collector가 수집된 통계정보를 기반으로 실행계획을 수정 후 다음 번 쿼리 실행시에 사용
{tip}
12.1.0.0.2에서는 (NL <-> HASH), 병렬 조인 쿼리에서의 분배 방법(Broadcast, Range , Hash, Hybrid)에 대해서만 관여
{tip}
Adaptive Plan의 구조 : Adaptive Plan은 Join이 포함된 SQL에서만 생성된다.
1. SQL문을 파싱하고 해당 SQL문에 대한 실행계획을 생성.
2. 동시에 Adaptive Plan에 대한 자격이 된다면 추가적인 Sub Plan을 생성
3. 이런 SubPlan은 Plan에 삽입되는 통계 수집기 세트와 연관된다.
4. Statistics Collector가 일정량의 데이터를 버퍼링해서 분석하고 이를 기존의 테이블과 컬럼 통계 메타데이터와 일치하는지 여부를 확인한다.
5. 내부적으로 정의된 임계 값을 기준으로 통계 수집기가 런타임 실행 시에 사용될 수 있는 SubPlan을 결정한다.
6. 이 SubPlan과 관련된 실행 계획과 통계 수집기가 함께 나중에 사용하기 위해 커서에 저장된다.
- 오라클은 SQL 실행시 원래의 Plan으로 실행하고, 이 계획을 실행할 때 통계 수집기에 의해서 샘플 데이터가 수집되고 버퍼링 되고 분석된다.
- 샘플링된 로우가 미리 계산된 통계 임계치(예를 들어 Cardinality가 비정상적으로 치우쳤다면(Skew)되었다면)를 크게 벗어난다면 대안실행 계획으로 원래 계획을 바꾼다.
- 실행계획은 쿼리의 실행이나 쿼리의 다음 번 실행 동안 동적으로 변경 할 수 있다.

Statistics Collector는 Adaptive Plan 사용 시에 옵티마이저에 의해서 생성되는 Row Source Operation의 일종이다. 최초 Plan 생성 시에 삽입되고 로우 버퍼링, 통계 정보 비교, 대안 PLAN 결정을 수행한다.

Adaptive Plan 사용 설정

- OPTIMIZER_FEATURE_ENABLE : 12.1.0.0.1 이상으로 설정
- OPTIMIZER_ADAPTIVE_REPORTING_ONLY : Adaptive Plan을 사용하기 위해서는 FALSE로 설정.

Adaptive Plan 조회 : dbms_xplan.display_cursor를 이용하여 조회 가능


SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'all +note +adaptive'));

SQL>  select substr(sql_text, 1, 50), is_resolved_adaptive_plan from v$sql
   2  where is_resolved_adaptive_plan = 'Y'
 

SUBSTR(SQL_TEXT,1,50)                                                                                I
---------------------------------------------------------------------------------------------------- -
SELECT /*+ CONNECT_BY_FILTERING */ s.privilege# FR                                                   Y
SELECT COUNT(*) FROM DBA_SCHEDULER_WINDOWS A, DBA_                                                   Y

-- is_resolved_adaptive_plan 컬럼을 통해 SQL문이 기존의 계획인 Adaptive Plan인지를 파악하는데 도움 될 수 있음

조인방법


SQL> create table testing_p(id number, the_value varchar2(30));
SQL> create index ix_testingP on testing_p(id);

SQL> create table testing_c(id_p number, id_t number, another_value varchar2(30));
SQL> create index ix_testingC_01 on testing_c(id_p);
SQL> create index ix_testingC_02 on testing_c(id_t);
SQL> create index ix_testingC_03 on testing_c(id_p, id_t);

SQL> select a.table_name, a.index_name, b.num_rows table_rows, a.num_rows index_rows
  2  from user_indexes a, user_tables b
  3  where a.table_name = b.table_name
  4  and a.table_name  like 'TESTING%';

TABLE_NAME                     INDEX_NAME                     TABLE_ROWS INDEX_ROWS
------------------------------ ------------------------------ ---------- ----------
TESTING_C                      IX_TESTINGC_02                                     0
TESTING_C                      IX_TESTINGC_03                                     0
TESTING_C                      IX_TESTINGC_01                                     0
TESTING_P                      IX_TESTINGP                                        0

SQL> select count(*)
  2  from (
  3  select b.id_t, b.id_p, b.another_value
  4  from testing_P a, testing_c b
  5  where a.id = b.id_p and a.id between 10001 and 10003);


SQL> select * from table(dbms_xplan.display_cursor(null, null, 'all +note +adaptive'));


--------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |       |       |     1 (100)|
|   1 |  SORT AGGREGATE    |                |     1 |    26 |            |
|   2 |   NESTED LOOPS     |                |     1 |    26 |     0   (0)|
|*  3 |    INDEX RANGE SCAN| IX_TESTINGP    |     1 |    13 |     0   (0)|
|*  4 |    INDEX RANGE SCAN| IX_TESTINGC_01 |     1 |    13 |     0   (0)|
--------------------------------------------------------------------------

-----
   - dynamic statistics used: dynamic sampling (level=2)

-- 통계 정보를 수행하지 않은 상태에서 data insert
SQL> truncate table testing_p;
SQL> truncate table testing_c;

SQL> declare
  2  begin
  3  for tt in 1..1000 loop
  4   insert into testing_p values(tt, 'www.testing.com');
  5   commit;
  6  end loop;
  7  for tt in 1..1000 loop
  8   insert into testing_p values(10001, 'www.testing.com');
  9   commit;
 10  end loop;
 11  end;
 12  /

SQL> declare
  2  begin
  3  for aa in (select id from testing_p) 
  4  loop for zz in 1..3 loop
  5   insert into testing_c values(aa.id, zz, 'www.testing.com');
  6  end loop;
  7  end loop;
  8  commit;
  9  
 10  for zz in 4..1000
 11  loop
 12   insert into testing_c values(1001, zz, 'www.testing.com');
 13  end loop;
 14  commit;
 15  end;
 16  /


SQL> select count(*)
  2  from (
  3  select b.id_t, b.id_p, b.another_value
  4  from testing_P a, testing_c b
  5  where a.id = b.id_p and a.id between 10001 and 10003);


SQL> select * from table(dbms_xplan.display_cursor(null, null, 'all +note +adaptive'));


--------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |       |       |     1 (100)|
|   1 |  SORT AGGREGATE    |                |     1 |    26 |            |
|   2 |   NESTED LOOPS     |                |     1 |    26 |     0   (0)|
|*  3 |    INDEX RANGE SCAN| IX_TESTINGP    |     1 |    13 |     0   (0)|
|*  4 |    INDEX RANGE SCAN| IX_TESTINGC_01 |     1 |    13 |     0   (0)|
--------------------------------------------------------------------------

-----
   - dynamic statistics used: dynamic sampling (level=2)


-- 통계 정보를 수행하지 않은 상태에서 data insert
SQL> truncate table testing_p;
SQL> truncate table testing_c;

SQL> declare
  2  begin
  3  for tt in 1..1000 loop
  4   insert into testing_p values(tt, 'www.testing.com');
  5   commit;
  6  end loop;
  7  for tt in 1..1000 loop
  8   insert into testing_p values(10001, 'www.testing.com');
  9   commit;
 10  end loop;
 11  end;
 12  /

SQL> declare
  2  begin
  3  for aa in (select id from testing_p) 
  4  loop for zz in 1..3 loop
  5   insert into testing_c values(aa.id, zz, 'www.testing.com');
  6  end loop;
  7  end loop;
  8  commit;
  9  
 10  for zz in 4..1000
 11  loop
 12   insert into testing_c values(1001, zz, 'www.testing.com');
 13  end loop;
 14  commit;
 15  end;
 16  /


SQL> select count(*)
  2  from (
  3  select b.id_t, b.id_p, b.another_value
  4  from testing_P a, testing_c b
  5  where a.id = b.id_p and a.id between 10001 and 10003);


SQL> select * from table(dbms_xplan.display_cursor(null, null, 'all +note +adaptive'));


--------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |       |       |     1 (100)|
|   1 |  SORT AGGREGATE    |                |     1 |    26 |            |
|   2 |   NESTED LOOPS     |                |     1 |    26 |     0   (0)|
|*  3 |    INDEX RANGE SCAN| IX_TESTINGP    |     1 |    13 |     0   (0)|
|*  4 |    INDEX RANGE SCAN| IX_TESTINGC_01 |     1 |    13 |     0   (0)|
--------------------------------------------------------------------------

-----
   - dynamic statistics used: dynamic sampling (level=2)

Adaptive Plan은 테이블이 통계 수집이 되지 않았다면 나타나지 않는다. 통계가 오래 되었거나 빈 테이블이거나 Current 상태이거나 상관없이 통계 정보가 존재하는지 유무가 중요 하다.


SQL> exec dbms_stats.gather_table_stats('GHLEE', 'TESTING_P', CASCADE => TRUE);
SQL> exec dbms_stats.gather_table_stats('GHLEE', 'TESTING_C', CASCADE => TRUE);



SQL> SET AUTOTRACE ON
SQL> select count(*)
  2  from (
  3  select b.id_t, b.id_p, b.another_value
  4  from testing_P a, testing_c b
  5  where a.id = b.id_p and a.id between 10001 and 10003);

  COUNT(*)
----------
   3000000

Elapsed: 00:00:00.24

Execution Plan
----------------------------------------------------------

--------------------------------------------------------------------------------
| Id  | Operation                | Name           | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                |     1 |    10 |    22  (55)|
|   1 |  SORT AGGREGATE          |                |     1 |    10 |            |
|*  2 |   HASH JOIN              |                |  3000K|    28M|    22  (55)|
|   3 |    NESTED LOOPS          |                |  3000K|    28M|    22  (55)|
|   4 |     STATISTICS COLLECTOR |                |       |       |            |
|*  5 |      INDEX RANGE SCAN    | IX_TESTINGP    |  1000 |  5000 |     3   (0)|
|*  6 |     INDEX RANGE SCAN     | IX_TESTINGC_01 |  3000 | 15000 |     7   (0)|
|*  7 |    INDEX FAST FULL SCAN  | IX_TESTINGC_01 |  3000 | 15000 |     7   (0)|
--------------------------------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version

* 원래 위에서  "This is an Adaptive Plan"이라는 정보가 표시되어야 하는데, 안나옴


SQL> select count(*)
  2  from (
  3  select b.id_t, b.id_p, b.another_value
  4  from testing_P a, testing_c b
  5  where a.id = b.id_p and a.id between 10001 and 10003);


SQL> select * from table(dbms_xplan.display_cursor(null, null, 'all +note +adaptive'));


---------------------------------------------------------------------------------------------
|   Id  | Operation                | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT         |                |       |       |    22 (100)|          |
|     1 |  SORT AGGREGATE          |                |     1 |    10 |            |          |
|  *  2 |   HASH JOIN              |                |  3000K|    28M|    22  (55)| 00:00:01 |
|-    3 |    NESTED LOOPS          |                |  3000K|    28M|    22  (55)| 00:00:01 |
|-    4 |     STATISTICS COLLECTOR |                |       |       |            |          |
|  *  5 |      INDEX RANGE SCAN    | IX_TESTINGP    |  1000 |  5000 |     3   (0)| 00:00:01 |
|- *  6 |     INDEX RANGE SCAN     | IX_TESTINGC_01 |  3000 | 15000 |     7   (0)| 00:00:01 |
|  *  7 |    INDEX FAST FULL SCAN  | IX_TESTINGC_01 |  3000 | 15000 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Note
-----
   - statistics feedback used for this statement
   - this is an adaptive plan (rows marked '-' are inactive)

{code:xml}
SQL> exec dbms_stats.gather_table_stats('GHLEE', 'TESTING_P', CASCADE => TRUE);
SQL> exec dbms_stats.gather_table_stats('GHLEE', 'TESTING_C', CASCADE => TRUE);



SQL> SET AUTOTRACE ON
SQL> select count(*)
  2  from (
  3  select b.id_t, b.id_p, b.another_value
  4  from testing_P a, testing_c b
  5  where a.id = b.id_p and a.id between 10001 and 10003);

  COUNT(*)
----------
   3000000

Elapsed: 00:00:00.24



--------------------------------------------------------------------------------
| Id  | Operation                | Name           | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                |     1 |    10 |    22  (55)|
|   1 |  SORT AGGREGATE          |                |     1 |    10 |            |
|*  2 |   HASH JOIN              |                |  3000K|    28M|    22  (55)|
|   3 |    NESTED LOOPS          |                |  3000K|    28M|    22  (55)|
|   4 |     STATISTICS COLLECTOR |                |       |       |            |
|*  5 |      INDEX RANGE SCAN    | IX_TESTINGP    |  1000 |  5000 |     3   (0)|
|*  6 |     INDEX RANGE SCAN     | IX_TESTINGC_01 |  3000 | 15000 |     7   (0)|
|*  7 |    INDEX FAST FULL SCAN  | IX_TESTINGC_01 |  3000 | 15000 |     7   (0)|
--------------------------------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version

* 원래 위에서  "This is an Adaptive Plan"이라는 정보가 표시되어야 하는데, 안나옴


SQL> select count(*)
  2  from (
  3  select b.id_t, b.id_p, b.another_value
  4  from testing_P a, testing_c b
  5  where a.id = b.id_p and a.id between 10001 and 10003);


SQL> select * from table(dbms_xplan.display_cursor(null, null, 'all +note +adaptive'));

---------------------------------------------------------------------------------------------
|   Id  | Operation                | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT         |                |       |       |    22 (100)|          |
|     1 |  SORT AGGREGATE          |                |     1 |    10 |            |          |
|  *  2 |   HASH JOIN              |                |  3000K|    28M|    22  (55)| 00:00:01 |
|-    3 |    NESTED LOOPS          |                |  3000K|    28M|    22  (55)| 00:00:01 |
|-    4 |     STATISTICS COLLECTOR |                |       |       |            |          |
|  *  5 |      INDEX RANGE SCAN    | IX_TESTINGP    |  1000 |  5000 |     3   (0)| 00:00:01 |
|- *  6 |     INDEX RANGE SCAN     | IX_TESTINGC_01 |  3000 | 15000 |     7   (0)| 00:00:01 |
|  *  7 |    INDEX FAST FULL SCAN  | IX_TESTINGC_01 |  3000 | 15000 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Note
-----
   - statistics feedback used for this statement
   - this is an adaptive plan (rows marked '-' are inactive)

위의 AUTOTRACE에서 "-": 붙은 라인은 inactive 한 로우다. 이 대시 마크는 실행 계획상의 이 로우가 고려는 됐지만 최적이 아니라서 사용되지 않은 Sub Plan임을 나타낸다.
4번 라인에서 Statistics Collector가 실행된 것이 표시된다. Statistics Collector는 로우를 버퍼링하고 이를 분석해서실행 계획에서 어떤 액세스 방법이 최적인지를 결정한다.

병렬분배방법

- 옵티마이저가 가장 적절한 분배 방법을 충분히 판단하지 못하는 경우 Hybrid hash Distiribution Technique라는 분배 방법을 사용 할 수 있다.
- 이 기능을 사용하기로 선택했다면 실행 시점까지 사용할 분배 방법을 결정하지 않는다.
- 실행 계획을 생성하고 옵티마이저는 작업의 생산자 측의 병렬 서버 프로세스의 앞 쪽에 통계 수집기를 삽입한다.
- Default로 실행계획이 hash broadcast 분배 방식을 사용하지만 로우의 개수가 병렬도 DOP의 2배보다 작다면 실행 계획은 쿼리에서 Broadcast 분배 방식으로 전환된다.


SQL> select count(*) from
  2  (select /*+ parallel(4) full(a) full(b) */
  3  b.id_t, b.id_p, b.another_value
  4  from testing_p a, testing_c b
  5  where a.id = b.id_p
  6  and a.id between 10001 and 10003);


----------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name      | Rows  | Bytes | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |           |     1 |    10 |     8  (38)|        |      |            |
|   1 |  SORT AGGREGATE                  |           |     1 |    10 |            |        |      |            |
|   2 |   PX COORDINATOR                 |           |       |       |            |        |      |            |
|   3 |    PX SEND QC (RANDOM)           | :TQ10002  |     1 |    10 |            |  Q1,02 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE               |           |     1 |    10 |            |  Q1,02 | PCWP |            |
|*  5 |      HASH JOIN                   |           |  3000K|    28M|     8  (38)|  Q1,02 | PCWP |            |
|   6 |       PX RECEIVE                 |           |  1000 |  5000 |     2   (0)|  Q1,02 | PCWP |            |
|   7 |        PX SEND HYBRID HASH       | :TQ10000  |  1000 |  5000 |     2   (0)|  Q1,00 | P->P | HYBRID HASH|
|   8 |         STATISTICS COLLECTOR     |           |       |       |            |  Q1,00 | PCWC |            |
|   9 |          PX BLOCK ITERATOR       |           |  1000 |  5000 |     2   (0)|  Q1,00 | PCWC |            |
|* 10 |           TABLE ACCESS FULL      | TESTING_P |  1000 |  5000 |     2   (0)|  Q1,00 | PCWP |            |
|  11 |       PX RECEIVE                 |           |  3000 | 15000 |     3   (0)|  Q1,02 | PCWP |            |
|  12 |        PX SEND HYBRID HASH (SKEW)| :TQ10001  |  3000 | 15000 |     3   (0)|  Q1,01 | P->P | HYBRID HASH|
|  13 |         PX BLOCK ITERATOR        |           |  3000 | 15000 |     3   (0)|  Q1,01 | PCWC |            |
|* 14 |          TABLE ACCESS FULL       | TESTING_C |  3000 | 15000 |     3   (0)|  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------------------

Adaptive Statisitcs

옵티마이저가 더 나은 실행계획을 생성할 수 있도록 도움을 주기 위해 기존 기능을 업그레이드한 새로운 기능이 추가 되었다.
- Automatic Repotimization(통계 피드백 Statistics Feedback))
- SQL Plan Directive
- Dynamic Staitistics 개선 사항

Automatic Reoptimization

Adaptive Plan은 비효율적인 조인 순서는 차선의 실행 계획이 생성될 가능성이 높아 진다.
SQL문이 실행될 때마다 옵티마이저는 문장이 실행하는 동안에 수집된 통계 정보를 검토 한다.
실행 계획이 생성될 때 사용된 통계 정보와 수집된 통게를 비교해서 만약 통계 정보가 차이가 난다면 옵티마이저는 다음 실행을 위해 대안 계획을 생성한다.
이 Reoptimization 작업은 커서 캐시에서 해당 쿼리가 있는 동안에 여러 번 발생할 수 있다.
Reoptimization의 두 가지 유형
- 통계 피드백(Statistics Feedback)
  - 이전에는 카디널리티 피드백이라고 알려져 있다. 기본적으로 카디널리티의 부정확한 예측 문제가 발생하는 실행 계획을 개선시키다.
  - 특정 SQL문에 의해 반환된 로우를 모니터링하고 그 실행 계획을 생성할 때 계산된 예측값과 비교 한다.
  - 예측값이 너무 차이가 나면 옵티마이저는 다음 번에 사용하기 위해 실제로 수집된 데이터를 저장한다.
  - 11g 에서는 이러한 통계는 커서로 저장되어 Aging Out 될 수 있었다. 12c는 옵티마이저의 SQL Plan Directive에 저장한다.
  - 현재는 수집된 통계의 부분으로 조인 통계가 포함되어 있다.
  - 바인드 변수를 사용한 쿼리의 성능을 향상시키기 위해 Adaptive Cursor Sharing과 같이 작동할 수 있다.
  - V$SQL의 IS_REOPTIMIZABLE이라는 컬림 추가되어 Reoptimization 여부를 확인할 수 있다.

- 성능 피드백(performance Feedback)
  - 병렬 쿼리를 최적화 하는데 사용된다.
  - PARALLEL_DEGREE_POLICY 파라미터를 ADAPTIVE로 설정하면 옵티마이저가 쿼리가 병렬로 실행되어야 하고 이를 선택했다면 쿼리에서 사용되는 병렬도의 값을 결정하는 설정이다.
  - 쿼리가 실행하면 데이터베이스는 쿼리의 성능을 모니터링 한다.
  - 쿼리가 첫 번째 실행을 완료한 후 옵티마이저는 초기에 선택한 dop와 명령문 실행 동안 수집된 다양한 성능 통계를 기반으로 계산된 DOP를 비교한다.
  - 값이 차이가 나는 경우 데이터베이스는 나중에 재파싱을 위해서 문장에 표시를 해두고 피드백이라는 정보로서 실행동안 수집된 통계를 저장한다.
  - 나중에 문장이 파싱할 때 피드백이 사용되고 새로운 DOP 가 계산된다.

SQL Plan Directive

기타 SQL 문의 더 나은 최적화를 위해 옵티마이저에서 추가적인 정보를 제공하는 쿼리의 표현 레벨에서 제공하는 기능이다.
비슷한 WHERE 조건을 가진 여러 개의 다른 SQL문이 단일 쿼리에서 생성된 SQL PLAN Directive의 이점을 누릴 수 있다.
SQL문이 실행할 때 옵티마이저는 쿼리에서 리턴되는 실제의 카?널리티값과 실행 계획 생성 시 계산한 예상 카디널리티를 비교한다.
이 실제의 정보는 SQL Plan Directive에 저장된다.
초기에는 SQL Plan Directive는 커서 캐시에 저장되었지만 주기적으로 SYSAUX 테이블 스페이스로 내려써서 저장된다.
DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE는 메모리에서 영구적인 스토리지로 내려 쓸 수 있다.
내려 쓰지 않으면 Directive는 데이터 Dictionary View에서 볼수 없다.
수고 SQL 문을 컴파일 하는 동안 옵티마이저는 쿼리와 SQL Plan Directive를 검토해서 누락된 확장 통계나 히스토그램이 있는지를 확인한다.
이 누락된 통계나 히스토그램을 일단 기록해두고 나중에 이를 수집한다.
생성된 Directive가 추가 통계를 필요로 하는 경우 옵티마이저는 이러한 통계를 수집하기 위해 Dynamic Sampling을 사용한다.
SQL Plan Directive는 컬럼 그룹이 통계 수집이 필요한지를 판다하는 데 도움이 되는 정보로서 옵티마이저에게 제공된다.
만일 SQL Plan Directive가 특정한 컬럼의 집합이 컬럼 그룹으로 생성되고 통계 수집 할 필요가 있다라고 표시된 경우에 옵티마이저는 자동으로 이 작업을 수행한다.
오라클 데이터베이스에서는 SQL Plan Directive를 이 정보가 더 이상 필요하지 않는 특정한 상황(통계 정보의 재수집 등)이 발생할 때까지 계속해서 이 정보를 사용한다.
자동으로 오라클 데이터베이스에 의해 관리 된다.
특정 SQL Plan Directive가 53주 동안 사용되지 않으면 이를 삭제 한다.
DBMS_APD.DROP_SQL_PLAN_DIRECTIVE 프로시저를 사용해서 SQL PLAN Directive를 삭제 할 수 있다.

히스토그램

데이터의 분포도를 결정하는데 히스토그램을 사용한다.
많은 숫자의 Distinct Value가 Skew된 값이 많을수록 전형적으로 많은 문제를 야기 시켜 왔고 개선 시키 고자 아래와 같은 사항이 포함되었다.

Popular, Unpopular, Almost Popular Value

기존의 높이균형 히스토그램에서 Unpopular Value 여러 개가 하나의 버킷에 저장되었다.
12c의 새로운 Hybrid 히스토그램상에서는 이런 한 버킷의 여러 Unpopular Value 중에서 가장 많은 개수를 가진 값을 Almost Popular Value라고 하고 Endpoint-Value로서 반복 횟수를 저장한다.

Popular Value는 데이터의 빈도수가 많아서 하나 이상의 버킷으로 할당되는 값. 옵티마이저는 해당 값이 popular Value라면 어떤 타입의 히스토그램으로 관리할지를 결정한다.
도수분포 히스토그램(Frequency Histogram)에서는 Unpopular와 Popular Value만이 존재한다. 반면 하이브리드 히스토그램에서는 Almost Popular Value라는 새로운 개념이 등장한다.

Top-Frequency 히스토그램

Distinct Value가 255개보다 적은 경우 : 도수분포 히스토그램으로 생성한다. 데이터베이스에서 각 Distinct Value나 해당 값마다 몇 개의 로우가 들어가 있는지 value에 관한 정보를 담는 버킷 하나씩을 할당한다. 도수 분포 히스토그램은 옵티마이저에게 컬럼 내의 Distinct Value에 대한 분포에 관한 정확한 정보를 제공해주는 가장 좋은 히스토그램 타입이다.
Distinct Value가 254보다 큰 경우 : 높이 균형 히스토그램(Height-Valance Histogram)으로 생성된다. 컬럼의 메타데이터가 요약되어 있다. 옵티마이저가 유니크한 값의 분포도와 카디널리티에 관련된 통계 정보를 가질 수 ㅇ벗기 때문에 문제가 될 가능성이 크가. 이런 여파로 옵티마이저는 해당 값의 분포도에 대해서 추정하는 방법을 사용한다.

데이터베이스는 Distinct 컬럼 값이 카디널리티보다 작은 값은 무시(unpopular Value)하고 좀 더 popular한 컬럼 값(높은 카디널리티를 가진)에 집중한다.
- 사용 가능한 버킷 개수(디폴트로는 254개까지만 특정 개수를 지정할 수도 있다)보다 Distinct Value가 많은 경우
- 사용 가능한 버킷( n )에 의해서 점유되는 로우의 비율은 (1 - (1/n)) * 100 보다 작거나 같다. 따라서 만약 500개 버킷을 가졌다면 99.8%의 데이터가 버킷에 맞춰서 사용할 수 있다. 따라서 1,000,000개의 로우를 가진다면 998,000 로우가 500개의 버킷 내에서 맞춰서 충분할 카디널리티를 표현하는 컬럼 값ㅇ을 가질 수 있다. 실제로 낮은 카디널리티의 값에서는 꽤 많은 공간을 남긴다.
- dbms_stats 패키지의 ESTIMATE_PERCENT 파라미터를 AUTO_SAMPLE_SIZE로 지정한 경우 이전 버전에서 데이터의 비율을 샘플링한 반면에 12C에서는 estimate_percent를 auto_samepl_size 값을 설정되어 있는 경우 옵티마이저는 Full Table Scan을 기반으로 히스토그램을 구축 한다. 이는 이전 버전보다 낮은 빈도의 값에(Unique에 가까운 값)에 훨씬 더 신뢰성 있는 데이터를 구축하는 효과가 있다.

하이브리드 히스토그램

하이브리드 히스토그램은 한 버킷으로 여러 개의 값을 표시하는 경우 컬럼 값을 분배 한다.
또한, 해당 버킷에서 맨 마지막 값의 반복되는 횟수를 나타내는 값을 Endpoint Value로서 버킷에 저장한다.
데이터베이스에서 popular Value와 Almost Popular Value에 대한 정확도를 높여서 더 나은 실행 계획 생성을 유도 할 수 있다.


create table hybrid_histogram(id number, the_column number)
insert into hybrid_histogram values(1,1);
insert into hybrid_histogram values(1,2);
insert into hybrid_histogram values(1,2);
insert into hybrid_histogram values(1,2);
insert into hybrid_histogram values(1,51);
insert into hybrid_histogram values(1,51);
insert into hybrid_histogram values(1,71);
insert into hybrid_histogram values(1,81);
insert into hybrid_histogram values(1,81);
insert into hybrid_histogram values(1,82);
insert into hybrid_histogram values(1,83);
insert into hybrid_histogram values(1,83);
commit;
exec dbms_stats.gather_table_stats(null, 'HYBRID_HISTOGRAM', method_opt => 'for columns the_column size 3');



select table_name, column_name, num_distinct, histogram
from user_tab_col_statistics
where table_name = 'HYBRID_HISTOGRAM';



TABLE_NAME                     COLUMN_NAM NUM_DISTINCT HISTOGRAM
------------------------------ ---------- ------------ ---------------
HYBRID_HISTOGRAM               THE_COLUMN            7 HYBRID


select owner, table_name, column_name, endpoint_value, endpoint_repeat_count
from dba_histograms
where owner = 'GHLEE' and  table_name = 'HYBRID_HISTOGRAM'
order by 1, 2, 3, 4;


OWNER      TABLE_NAME                     COLUMN_NAM ENDPOINT_VALUE ENDPOINT_REPEAT_COUNT
---------- ------------------------------ ---------- -------------- ---------------------
GHLEE      HYBRID_HISTOGRAM               THE_COLUMN              1                     1
GHLEE      HYBRID_HISTOGRAM               THE_COLUMN             51                     2
GHLEE      HYBRID_HISTOGRAM               THE_COLUMN             83                     2

단 3개의 히스토그램 버킷을 만들었음.
THE_COLUMN 컬럼은 7개의 Distinct Value를 가지고 있지만 3개의 버킷에만 저장되었으면, 버킷에 나열된 Endpoint 값을 참고하고 Endpoint값이 반복되는 개수와 Endpoint Repeat Count값과 일치 도고 있다.
예를 들어 71, 81, 83값이 담겨 있는 마지막 버킷에는 Endpoint Value인 83의 repeat_count를 통해 2개 임을 정확히 알 수 있다.

히스토그램 버킷의 최대 개수 증가

히스토그램으로 할당되는 버킷의 최대 개수가 Default는 254rowlaks 2048개 까지 히스토그램을 위한 버킷을 생성할 수 있다.