<<12c 통계정보 Part 2 >>

 

옵티마이져에게 필요한 정보 

: SQL 문에서 액세스 된 모든 오브젝트 (테이블 및 인덱스)와 SQL 문이 실행될 시스템

 

12c 통계정보 Part 1 - Oracle 통계정보 개념 설명 

    http://darkturtle.tistory.com/entry/12c-%ED%86%B5%EA%B3%84%EC%A0%95%EB%B3%B4-Part-1

12c 통계정보 Part 2 - 수집 시기와 방법 

 

<< 요약 >>

: 디폴트로 그냥 사용 하면 됨

  + Direct Loading 시, Online 통계정보 수집을 Disable 시키고, Manual 하게 수집 할것 

 

<<출처 >>

: http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf

 

 

<<통계정보 설정 변경은 4가지 Level 로 가능>>

1) SET_TABLE_PREFS 2) SET_SCHEMA_PREFS 3) SET_DATABASE_PREFS 4) SET_GLOBAL_PREFS

개별 설정 : SET_GLOBAL_PREFS 나 SET_TABLE_PREFS 로 설정 

자동 통계 수집 : 통계정보 누락 이나 만료가 수집 대상 ( 유지 관리 시간 동안 가장 통계정보가 필요한 대상 부터 수집함 )

Staelnss (통계정보 변경에 대한 임계치 - 기존 ROW 의 변경 정도 - 디폴트 10 % ) 

 

-- DB 통계정보 설정 값 확인 하기 
 
sys@TG12102> set linesize 180 
sys@TG12102> set pagesize 30
sys@TG12102> col incremental for a12
sys@TG12102> col granularity for a12
sys@TG12102> col publish for a12
sys@TG12102> col estimate_percent for a30
sys@TG12102> col cascade for a30
sys@TG12102> col method_opt for a30 
sys@TG12102> SELECT 
  2    DBMS_STATS.get_prefs(pname=>'INCREMENTAL') incremental,
  3    DBMS_STATS.get_prefs(pname=>'GRANULARITY') granularity,
  4    DBMS_STATS.get_prefs(pname=>'STALE_PERCENT') stale_percent,
  5    DBMS_STATS.get_prefs(pname=>'ESTIMATE_PERCENT') estimate_percent,
  6    DBMS_STATS.get_prefs(pname=>'CASCADE') cascade,
  7    DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt
  8  FROM dual;
 
INCREMENTAL  GRANULARITY  PUBLISH      ESTIMATE_PERCENT               CASCADE                        METHOD_OPT
------------ ------------ ------------ ------------------------------ ------------------------------ ------------------------------
FALSE        AUTO         10           DBMS_STATS.AUTO_SAMPLE_SIZE    DBMS_STATS.AUTO_CASCADE        FOR ALL COLUMNS SIZE AUTO
 
sys@TG12102> 
 
-- Table 
sys@TG12102>  exec dbms_stats.set_table_prefs ('TEST', 'TEST_TABLE', 'STALE_PERCENT', '5') ;
PL/SQL procedure successfully completed.
 
sys@TG12102> set linesize 180 
sys@TG12102> set pagesize 30
sys@TG12102> col owner for a14
sys@TG12102> col table_name for a30
sys@TG12102> col incremental for a12
sys@TG12102> col granularity for a12
sys@TG12102> col publish for a12
sys@TG12102> col estimate_percent for a30
sys@TG12102> col cascade for a30
sys@TG12102> col method_opt for a30 
sys@TG12102>  SELECT 
  2        owner, table_name,
  3        DBMS_STATS.get_prefs(ownname=>'TEST',tabname=>table_name,pname=>'INCREMENTAL') incremental,
  4        DBMS_STATS.get_prefs(ownname=>'TEST',tabname=>table_name,pname=>'GRANULARITY') granularity,
  5        DBMS_STATS.get_prefs(ownname=>'TEST',tabname=>table_name,pname=>'STALE_PERCENT') stale_percent,
  6        DBMS_STATS.get_prefs(ownname=>'TEST',tabname=>table_name,pname=>'ESTIMATE_PERCENT') estimate_percent,
  7        DBMS_STATS.get_prefs(ownname=>'TEST',tabname=>table_name,pname=>'CASCADE') cascade,
  8        DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt
  9      FROM dba_tables
 10     WHERE OWNER      ='TEST'
 11     AND   table_name ='TEST_TABLE'
 12     ORDER BY owner, table_name;
 
OWNER          TABLE_NAME                     INCREMENTAL  GRANULARITY
-------------- ------------------------------ ------------ ------------
STALE_PERCENT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ESTIMATE_PERCENT               CASCADE                        METHOD_OPT
------------------------------ ------------------------------ ------------------------------
TEST           TEST_TABLE                     FALSE        AUTO
5
DBMS_STATS.AUTO_SAMPLE_SIZE    DBMS_STATS.AUTO_CASCADE        FOR ALL COLUMNS SIZE AUTO
 
sys@TG12102> COL  stale_percent FOR A15
sys@TG12102> SELECT  DBMS_STATS.GET_PREFS('STALE_PERCENT','TEST','TEST_TABLE') AS  stale_percent FROM DUAL ;
 
STALE_PERCENT
---------------
5

 

 

ESTIMATE_PERCENT 매개 변수는 통계를 계산하는 데 사용되는 행의 백분율을 결정합니다. 

Oracle Database 11g 이후부터 기본 ESTIMATE_PRECENT 값은 AUTO_SAMPLE_SIZE ( Oracle 알아서 결정해줌 )

METHOD_OPT 매개 변수는 통계 수집 중 히스토그램 작성을 제어합니다

METHOD_OPT의 기본값 및 권장 값은 'FOR ALL COLUMNS SIZE AUTO'입니다

METHOD_OPT가 'FOR ALL COLUMNS SIZE 1'로 설정된 경우 히스토그램 작성이 사용 불가능합니다. 

 

 

12c, 통계정보 보류 설정 하여, 신규 통계정보 검증 후, 배포 ( Part 1 참조 - Pending 및 Session Level Pending 통계정보 참조 ) 

 http://darkturtle.tistory.com/entry/12c-%ED%86%B5%EA%B3%84%EC%A0%95%EB%B3%B4-Part-1

 

12c, 통계정보 자동 수집 시간

 

--  통계정보 수집 시간 확인
-- 주중 22 이후 4시간    다음날 02 시까지
-- 토일 06 이후 20 시간  다음날 02 시까지
sys@TG12102> col owner for a10
sys@TG12102> col window_name  for a20
sys@TG12102> col REPEAT_INTERVAL for a60
sys@TG12102> col DURATION for a20
sys@TG12102> col ENABLED for a10
sys@TG12102> SELECT OWNER, WINDOW_NAME, REPEAT_INTERVAL, DURATION, ENABLED
  2  FROM   DBA_SCHEDULER_WINDOWS  
  3  WHERE  WINDOW_NAME IN ( SELECT DISTINCT WINDOW_NAME 
  4  FROM DBA_AUTOTASK_JOB_HISTORY WHERE CLIENT_NAME ='auto optimizer stats collection' )
  5  ORDER BY WINDOW_NAME ;
 
OWNER      WINDOW_NAME          REPEAT_INTERVAL                                              DURATION             ENABLED
---------- -------------------- ------------------------------------------------------------ -------------------- ----------
SYS        FRIDAY_WINDOW        freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0        +000 04:00:00        TRUE
SYS        MONDAY_WINDOW        freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00        TRUE
SYS        SATURDAY_WINDOW      freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0         +000 20:00:00        TRUE
SYS        SUNDAY_WINDOW        freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00        TRUE
SYS        THURSDAY_WINDOW      freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00        TRUE
SYS        TUESDAY_WINDOW       freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00        TRUE
SYS        WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00        TRUE
 
7 rows selected.;

 

<<Online 통계정보 수집>>

: 12c부터, piggybacks 불리는 자동 통계정보 수집 개념 도입

   Direct-Path Data Loading(CTAS(create table as select), IAS(Insert as select)) 수행 시

    , 자동으로 통계정보 수집됨 ( 별도 수집 불필요 ) --> 별도 수집 권고!!!

    , 빈 테이블에 데이타 로딩 시만 수집됨( 파티션 별로 Loading 시 수집 안됨 !!! ) 

   실행계획 -  OPTIMIZER STATISTICS GATHERING Operation 

   주의, phggyback 에 의한 자동통계정보는 Histogram 및 인덱스 통계정보는 제외됨 

     -. Directy Loading 시, Constraint / Index 를 제거함 

     -. 추가로 인덱스 생성 시, 자동으로 인덱스에 대한 통계정보가 수집됨 

    /*+ NO_GATHER_OPTIMIZER_STATISTICS */  를 통해서 ONLINE 통계정보 수집 Disable 권고 

 

 

sys@TG12102> conn test/test
Connected.
test@TG12102> show user
USER is "TEST"
test@TG12102> !date
Tue May  8 16:57:20 KST 2018
test@TG12102> create table test_sample as select * from test_table ;
Table created.
 
test@TG12102> !date
Tue May  8 16:57:54 KST 2018
 
test@TG12102> col table_name for a30
test@TG12102> select table_name, num_rows, to_char(last_analyzed,'YYYY/MM/DD HH24:MI:SS') last_ana
  2  from  dba_Tables
  3* where table_name ='TEST_TABLE' ;
TABLE_NAME                       NUM_ROWS LAST_ANA
------------------------------ ---------- -------------------
TEST_TABLE                         900000 2018/04/30 17:37:01
 
test@TG12102> col HISTOGRAM for a30
test@TG12102> col NUM_NULLS for 9999999999
test@TG12102> col COLUMN_NAME for a20
test@TG12102> col NOTES for a20
test@TG12102>   select column_name , num_distinct, num_nulls, histogram, notes
  2  from dba_tab_col_statistics where table_name ='TEST_TABLE' ; 
COLUMN_NAME          NUM_DISTINCT   NUM_NULLS HISTOGRAM                      NOTES
-------------------- ------------ ----------- ------------------------------ --------------------
A4                         900000           0 NONE
A3                         405760           0 NONE
A2                         395168           0 NONE
A1                         406464           0 NONE
 
test@TG12102>