<<12c 통계정보 Part 1 >>
비용 기반 최적화 도구가 실행 계획의 비용을 정확하게 결정하려면 SQL 문에서 액세스되는
모든 개체 (테이블 및 인덱스)에 대한 정보와 SQL 문이 실행될 시스템에 대한 정보가 있어야합니다
옵티 마이저 통계는 데이터베이스와 데이터베이스의 오브젝트를 설명하는 데이터 콜렉션입니다.
이러한 확장은 히스토그램, 열 그룹 및 표현식 통계입니다.
비균일한 데이터 분포를 정확하게 반영하려면 열에 히스토그램이 필요합니다.
빈도 막대 그래프는 열의 고유 값 수가 허용되는 최대 버킷 수보다 적을 때 작성됩니다.
이것은 기본적으로 254이지만 Oracle Database 12c부터 최대 2048까지 DBMS_STATS 프로 시저를 사용하여 수정할 수 있습니다.
<<EXTENDED STATISTICS>>
: 테이블 내 칼럼 그룹의 통계정보 ( 국가코드 + 도시코드 )
--> DBMS_STATS.CREATE_EXTENED_STATS
<<Step1: Seed Column Usage>.
begin
dbms_stats.seed_col_usage(null,null,300);
end;
/
-- 관려 쿼리 수행
SELECT * FROM TEST.TEST_TABLE WHERE A1 = 11 AND A2 ='11 INSERT';
-- 수집 결과 확인
SELECT DBMS_STATS.REPORT_COL_USAGE('TEST','TEST_TABLE') FROM DUAL ;
-- 특정 유저의 모든 테이블
SELECT DBMS_STATS.REPORT_COL_USAGE('TEST',NULL) FROM DUAL ;
<<Step 2: Create the Column Groups>>
확장 통계가 생성되면 통계가 수집 될 때마다 자동으로 유지 관리됩니다.
<<Step 3: Re-gather Statistics>>
SQL 계획 지시문은 자동으로 열 그룹을 만드는 데 사용되지 않습니다 (기본값).
<<Expression Statistics>>
: Function Based Index 와 동일한 개념 ( SEGMENT 가 아닌 STATISTICS 로 해결한다는것 뿐 )
<< 확장 통계정보 제약 사항 >>
1. "=" 혹은 IN LIST 에서만 사용됨
2. 칼럼 HISTOGRAM 존재 하고, 칼럼 그룹 HISTOGRAM 이 존재 하지 않는 경우, 사용되지 않음 !!!
SELECT DBMS_STATS.CREATE_EXTENDED_STATS('TEST,'TEST_TABLE','(UPPER(A3))') FROM DUAL ;
EXEC DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST_TABLE');
-- EXTENTION STATISTICS 결과 확인
SELECT *
FROM DBA_STAT_EXTENSIONS
WHERE OWNER = 'TEST'
AND TABLE_NAME = 'TEST_TABLE';
<<12C,Incremental global statistics 지원 >>
글로벌 통계는 새로운 파티션 개요와 기존 파티션의 개요를 사용하여 자동으로 정확하게 업데이트됩니다.
INCREMENTAL 통계는 서브 파티션에 적용되지 않습니다.
Exchange Partition 명령을 사용하면 파티션되지 않은 테이블의 데이터를 파티션 된 테이블의 지정된 파티션으로 스왑 할 수 있습니다.
명령은 실제로 데이터를 이동하지 않습니다. 대신 파티션에서 테이블로 또는 그 반대로 포인터를 교환하기 위해 데이터 사전을 갱신합니다.
Oracle Database 12c에서는 Exchange로 전환하기 전에 파티션되지 않은 테이블에 필요한 통계 (개요)를 생성 할 수 있으므로
파티션 교환로드 중에 교환되는 통계를 사용하여 증분 글로벌 통계를 자동으로 유지 관리 할 수 있습니다.
<< 통계정보 복구 >>
-- DBMS_STATS.RESTORE_TABLE_STATS
SELECT *
FROM DBA_TAB_STATS_HISTORY
WHERE OWNER = 'TEST'
ORDER BY STATS_UPDATE_TIME DESC ;
BEGIN
DBMS_STATS.RESTORE_TABLE_STATS(ownname =>'TEST',
tabname => 'TEST_TABLE',
as_of_timestamp => SYSTIMESTAMP-7,
force => FALSE,
no_invalidate => FALSE);
-- 바로 INVALIDATE -> NO -> FALSE -> INVALIDATE 시킴 -- 기존 SQL 커서 무효화시킴
END;
/
<< Pending Statistics >>
: 통계정보 수집 하고, 바로 반영 하지 않고, Pending 절차를 거침
To activate pending statistics collection, you need to use one of the DBMS_STATS.SET_*_PREFS procedures
to change value of the parameter PUBLISH from TRUE (default) to FALSE for the object(s) you wish to create pending statistics for
1. 테이블 PENDING 설정
BEGIN
DBMS_STATS.SET_TABLE_PREFS('TEST', 'TEST_TABLE', 'PUBLISH', 'FALSE');
END;
/
2. 테이블 통계정보 수집
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('TEST', 'TEST_TABLE');
END;
/
3. 테이블 통계정보 PEDNING 확인
SELECT * FROM DBA_TAB_PENDING_STATS ;
4. 세션에서 PENDING 통계정보 이용 가능하도록 설정
ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = TRUE ;
5. 관련 쿼리 수행을 통한 검증
6. PENDING(보류) 통계정보 반영
BEGIN
DBMS_STATS.PUBLISH_PENDING_STATS('TEST', 'TEST_TABLE');
END;
/
SELECT * FROM DBA_TABLES WHERE OWNER ='TEST' AND TABLE_NAME ='TEST_TABLE';
-- DBA_TAB_PENDING_STATS.LAST_ANALYZED --> 2018/04/25 18:58:45
7. 테이블 PENDING 설정 해제
BEGIN
DBMS_STATS.SET_TABLE_PREFS('TEST', 'TEST_TABLE', 'PUBLISH', 'TRUE');
END;
/
-- DBA_TABLES.LAST_ANALYZED --> 2018/04/25 18:58:45
<< 통계정보 비교 >>
-- SYSDATE - 현재 통계정보 , NULL 현재 통계정보
-- SYSDATE -5 - 5일전
set long 9999
SELECT REPORT, MAXDIFFPCT
FROM TABLE(DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY('TEST','TEST_TABLE',NULL, SYSDATE-5,10)) ;
<< 통계정보 잠금>>
통계가 잠기면 통계가 잠금 해제되거나 GATHER _ * _ STATS 프로 시저의 FORCE 매개 변수가 TRUE로 설정되지 않으면 해당 통계를 수정할 수 없습니다
sys@TG12102> exec dbms_stats.lock_table_stats('TEST','TEST_TABLE');
PL/SQL procedure successfully completed.
sys@TG12102> exec dbms_stats.gather_table_stats('TEST','TEST_TABLE');
BEGIN dbms_stats.gather_table_stats('TEST','TEST_TABLE'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 34634
ORA-06512: at line 1
sys@TG12102> exec dbms_stats.gather_table_stats('TEST','TEST_TABLE',FORCE=>TRUE);
PL/SQL procedure successfully completed.
sys@TG12102>
-. Partition 별로 통계정보 Lock 가능
-. 테이블 Level 로 통계정보 Lock 시, 파티션별 통계정보 수집 불가
exec dbms_stats.lock_partition_stats('TEST','TEST_TABLE','TEST_TABLE_PART_1');
<<추가 통계정보>>
<<Dynamic Statistics(Sampling)>>
사용 가능한 통계가 충분하지 않으면 기존 통계 정보와 함께 동적 통계가 사용됩니다.
Single Table Access, Join and Group-by Predicates 에 대한 통계정보 수집
OPTIMIZER_DYNAMIC_SAMPLING 힌트를 통해서 Dynamic Statistics 를 강제할 수 있음
sys@TG12102> alter session set optimizer_dynamic_sampling=11;
Session altered.
<< System Statistics>>
시스템 통계를 수동으로 수집하려면 기본값을 무시하고 Oracle Optimizer의 비용 계산에 영향을 미칩니다.
이렇게하면 SQL 실행 계획이 변경 될 수 있으므로 프로덕션 시스템에서 구현하기 전에 변경의 이점을 평가하는 것이 중요합니다.
<<Dictionay Table 통계정보>>
<<Fixed Object 통계정보>>
sys@TG12102> BEGIN
2 DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
3 END;
4 /
PL/SQL procedure successfully completed.
<< Expression Statistics >>
sys@TG12102> exec dbms_stats.flush_database_monitoring_info ;
PL/SQL procedure successfully completed.
: This procedure flushes in-memory monitoring information for all tables in the dictionary
12cR2 에만 있고, 12cR1 에는 없음 아래 테이블
sys@TG12102> desc DBA_EXPRESSION_STATISTICS
ERROR:
ORA-04043: object DBA_EXPRESSION_STATISTICS does not exist
sys@TG12201> col table_name for a30
sys@TG12201> col EXPRESSION_TEXT for a30
sys@TG12201> select SNAPSHOT, TABLE_NAME, EVALUATION_COUNT, FIXED_COST, EXPRESSION_TEXT
from DBA_EXPRESSION_STATISTICS
order by SNAPSHOT, TABLE_NAME, EVALUATION_COUNT desc ;
sys@TG12201> /
SNAPSHOT TABLE_NAME EVALUATION_COUNT FIXED_COST EXPRESSION_TEXT
---------- ------------------------------ ---------------- ---------- ------------------------------
CUMULATIVE AQ$_QUEUES 20 2.6559E-08 "TABLE_OBJNO"
CUMULATIVE AQ$_QUEUE_TABLES 132 2.6559E-08 "OBJNO"
<< 결론 >>
통계를 이해하고 관리하는 것이 최적의 SQL 실행의 핵심입니다. 적시에 통계를 수집하는시기와 방법을 알고 있으면 좋은 성과를 유지하는 것이 중요합니다