클라우드 데이터베이스 Oracle 12c 가이드 (2016년)
통계정보 관련 새로운 기능
자동 컬럼 그룹 감지
확장 통계의 기능
- 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
톰 카이트의 한마디
- 동시 통계를 사용하기 전에 이 작업이 적절한지 먼저 확인해야 함.
- 서버의 리소스를 가급적이면 많이 사용하기 위해서 단일 작업으로 디자인 되었다. 0 모든 머신의 리소스를 소모하는 단일 작업을 위해 고려해야 하는 것은 이 작업 이외의 어떤 작업도 수행하지 않아야 한다는 사실이다.
- 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
Global Temporary Table을 위한 Session-Private 통계 정보
- 12c 부터 GTT는 각 개별적인 세션별로 통계 정보 세트를 가질 수 있음.
- 통계 환경 설정중(Preference)중
GLOBAL_TEMP_TABLE_STATS를 SESSION으로 설정하여 사용 가능 - 세션에 지정된 통계를 설정하는 경우는 사용하고 있는 테이블의 세션 내에서 GTT 통계를 수집할 수 있고 이 통계는 해당 세션에서만 유일
- 다른 유저는 동시에 필요에 따라서 세션별 통계나 공유 통계 정보 중 하나를 사용할 수 있다.
- 옵티마이저가 GTT가 포함된 SQL문에 대한 실행 계획을 생성할 때 세션 별 통계 정보가 존재하는지를 체크한다.
- 만약 존재하지 존재하지 않다면 옵티마이저는 공유 통계 정보(또는 Dynamic Sampling)를 사용한다.
- 트랜잭션별 GTT에 변경사항을 적용하지 않도록 DBMS_STATS프로그램이 변경되어, GTT에 있는 데이터가 정리되더라도 이에 따른 통계 수집에 미치는 부작용은 없다.
- DBMSSTATS 패키지의 환경설정인 `GLOBALTEMPTABLESTATS`를 기반으로 통게를 수집하며, 이 값이 SHARED로 설정되어 있다면 DBMS_SATTS에 의해 수집된 통게는 공유 통계가 된다.
- 환경 설정이 SESSION으로 설정되면 DBMS_STATS가 수집할 때 세션별 통계가 될 것이다.
리포트 모드에서 통계 수집 옵션 수행
- 실제로 통계 정보가 수집되는 것이 아니라 실제 통계 정보가 수집되는 대상 오브젝트에 대한 분석될 데이터베이스의 리포트 정보 만이 출력
- 해당 오브젝트가 실제로 통계가 수집되었는지 또는 통계 수집이 필요한지를 정확하게 결정하는데 동움을 준다.
SQL> select dbms_stats.report_gather_schema_stats('GHLEE', DETAIL_LEVEL=>'TYPICAL')
from dual;
과거 통계 수집 작업 리포트
dbms_stats.report_stats_operations: 시작과 종료 시간 내에 발생한 모든 통계 작업에 대해서 보여주는 리포트를 생성dbms_stats.report_single_Stats_operations: 특정 통계 수집 작업에 대한 리포트를 생성
"구루비 데이터베이스 스터디모임" 에서 2016년에 "클라우드 데이터베이스 Oracle 12c 가이드" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4268
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.