행위

오라클 자동 통계정보 스케줄

DB CAFE

Dbcafe (토론 | 기여)님의 2020년 2월 11일 (화) 17:48 판 (→‎통계정보 수집 정지)
thumb_up 추천메뉴 바로가기

목차

1 Oracle 통계정보 갱신[편집]

1.1 통계 정보 수집 시간[편집]

1.1.1 자동 수행 스케줄 목록 조회[편집]

--CLIENT_NAME, STATUS 조회 
SELECT * 
FROM   DBA_AUTOTASK_CLIENT
-- WHERE  CLIENT_NAME = 'auto optimizer stats collection';

-- AUTOTASK OPERATION
SELECT * 
  FROM DBA_AUTOTASK_OPERATION; 

-- 자동윈도우 전체 조회 
SELECT * 
  FROM DBA_AUTOTASK_WINDOW_CLIENTS;

auto optimizer stats collection

1.1.2 자동 수행 목록 히스토리 조회[편집]

SELECT * FROM DBA_AUTOTASK_CLIENT_HISTORY
ORDER BY WINDOW_START_TIME DESC
;

auto optimizer stats collection

1.1.3 통계정보 수집 정지[편집]

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE (  
    client_name  => 'auto optimizer stats collection'
,   operation    => NULL
,   window_name  => NULL 
);
END;
  • 만약OPERATION과WINDOW_NAME이 둘 다NULL이면,해당Client는 Disable/Enable된다.
  • 만약OPERATION이NULL이 아니면,WINDOW_NAME은 무시되고 해당 OPERATION은 Disable/Enable된다.
  • 만약 OPERATION이 NULL이고 WINDOW_NAME이 NULL이 아니면, 해당 Client는 지정한 Window에 한하여 Disable/Enable된다.

1.1.4 통계정보 수집 시작[편집]

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE (  
    client_name  => 'auto optimizer stats collection'
,   operation    => NULL
,   window_name  => NULL 
);
END;

1.2 해당 스키마에 해당하는 테이블의 통계정보 조회[편집]

SELECT TABLE_NAME, NUM_ROWS, CHAIN_CNT
     , BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN 
  FROM DBA_TABLES 
 WHERE OWNER = '스키마명';

1.3 해당 스키마에 해당하는 인덱스의 통계정보 조회[편집]

SELECT TABLE_NAME, INDEX_NAME, STATUS, NUM_ROWS, LEAF_BLOCKS, BLEVEL 
  FROM DBA_INDEXES 
 WHERE OWNER = '스키마명';

1.4 테이블의 통계정보 갱신[편집]

파라미터

  • Ownname => 스키마 이름
  • Indname => 인덱스 이름
  • Partname => 파티션 이름
  • Tabname => 테이블 이름
  • Statown => 통계 테이블이 속한 스키마의 이름
  • Stattab => 특정 통계 테이블의 이름
  • Statid => stattab 내의 통계 정보의 ID 설정
  • no_validate => TRUE로 설정할 시 해당 객체를 참조하는 커서의 재 파싱 실행.
  • gather_sys => ‘SYS' 유저의 객체에 대해 통계 정보 생성
  • interval => 지정 분 동안의 DB 실시간 SGA 사용량의 의거한 통계 정보 생성. Gathering_mode='INTERVAL' 일 때에만 사용 가능.
  • Degree => 병렬 처리 개수 설정. NULL 시 해당 객체의 생성시에 명시된 DEGREE 값이 적용. DBMS_STATS.DEFAULT_DEGREE 를 사용하여 INIT 파라미터 값 사용 가능.
  • Cascade => 테이블 대상 시에 해당 테이블의 모든 인덱스에 대해서도 통계 정보 생성. 이 때 인덱스 통계 정보는 병렬 사용 불가능.
  • gather_temp => global temporary table에 통계 정보 저장. 해당 테이블은 “on commit ---serve rows” 옵션을 사용해야만 함. 이 테이블에 저장된 통계 정보는 모든 세션들에 대해 공유 가능
  • tblspace => 통계 테이블이 생성될 테이블스페이스의 이름. 지정하지 않을 시에는 실행 유저의 Default 테이블스페이스 사용.


EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'스키마명', tabname=>'테이블명', DEGREE => 8);

1.5 테이블,인덱스포함 통계정보 갱신[편집]

EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', ‘table_name', method_opt => 'for all indexed columns',cascade => true);
BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(OWNNAME			=> 'ROWNER',
                            	  TABNAME			=> 'TB_XXXX',
                            	  CASCADE			=> TRUE,
                            	  ESTIMATE_PERCENT	=> NULL, 
                            	  METHOD_OPT		=>'FOR ALL COLUMNS SIZE 120');
END;
  • method_opt의 값 예시
for all column size 1: 모든 컬럼에 대해 Histogram을 수집하지 않는다.
for all column size auto: Oracle이 Data의 분포를 고려하여 Bucket Size를 계산.(단, 해당 Coloumn이 Predicate로 사용된 적이 있는 경우에만 Histogram 수집)
for all column size skewonly: Oracle이 Data의 분포를 고려하여 Bucket Size를 계산.(단, 해당 Coloumn이 Predicate로 사용된 적이 없어도 Histogram 수집)
for all indexed column size skewonly: Index가 존재하는 모든 컬럼에 대해서..
for all indexed column size auto: Index가 존재하는 모든 컬럼에 대해서..
for columns c1 size 5 c2 size skewonly: 개별 컬럼에 대해서..
for all hidden columns size skewonly: Function Based Index에 의해 생성된 Hidden Cloumn에 대해서..
for columns SYS_NC00003$ size skewonly: Hidden Column 이름을 직접명시해서..

android 모든 컬럼, 모든 Index대상 컬럼, 개별컬럼, Hidden컬럼들에 대해서, Bucket Size를 정할 수 있는 여러가지 옵션(skewonly, auto, 숫자)을 주어 히스토그램을 생성할 수 있다.


1.6 스키마안의 모든 세그먼트에 대한 통계정보 갱신[편집]

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('스키마명');

1.7 DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신[편집]

ANALYZE TABLE 스키마명.테이블명 COMPUTE STATISTICS;
​

1.8 DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신(쿼리 생성)[편집]

SELECT 'ANALYZE TABLE 스키마명.' || TABLE_NAME || ' COMPUTE STATISTICS;' 
  FROM DBA_TABLES 
 WHERE OWNER = '스키마명';

1.9 DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 갱신[편집]

ANALYZE INDEX 스키마명.인덱스명 VALIDATE STRUCTURE;
SELECT NAME, BLOCKS, LF_ROWS, DEL_LF_ROWS 
  FROM INDEX_STATS;

​

1.10 DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 갱신(쿼리 생성)[편집]

SELECT 'ANALYZE INDEX 스키마명.' || INDEX_NAME || ' VALIDATE STRUCTURE;' 
  FROM DBA_INDEXES WHERE OWNER = '스키마명';

2 Oracle Table의 통계정보를 옮기는 방법.[편집]

보통, Test 시스템에서 가동 시스템으로 데이타를 이관하는 경우, 데이타 뿐만 아니라 통계정보도 이관하여, Test계와 가동계를 동일하게 유지하고자 하는 경우.

  • 통계정보 추출
-- 테이블 별 추출 일때
DBMS_STATS.EXPORT_TABLE_STATS(
ownname   VARCHAR2,                -- schema name
tabname   VARCHAR2,                -- table name
partname  VARCHAR2 DEFAULT NULL,   -- partition name
stattab   VARCHAR2,                -- stat table name
statid    VARCHAR2 DEFAULT NULL,   -- optional identifier
cascade   BOOLEAN  DEFAULT TRUE,   -- TRUE = indexes too 
statown   VARCHAR2  DEFAULT NULL); -- stat table schema 

예시) 
EXEC DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME=>'MIG_ADM',TABNAME=>'EMP' ,STATTAB=>'MIG_TAB_STATS_BAK' ,STATOWN=>'MIG_ADM', STATID=>'MIG_20181118', CASCADE=>TRUE) ;

-- 스키마 별 추출 일때
예시) STATS 은 통계 추출 한 임시테이블  
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS (   ownname =>'XX_DBA'				,   stattab => 'STATS',   statid  => '12C_20190514',   statown =>'DBA_cykim');
  • 통계정보 입력
DBMS_STATS.IMPORT_TABLE_STATS(
ownname       VARCHAR2, 
tabname       VARCHAR2,
partname      VARCHAR2 DEFAULT NULL,
stattab       VARCHAR2, 
statid        VARCHAR2 DEFAULT NULL,
cascade       BOOLEAN  DEFAULT TRUE,
statown       VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN  DEFAULT to_no_invalidate_type(get_param('NO_INVALIDATE')),
force         BOOLEAN DEFAULT FALSE); 

예시)
EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME=>'MIG_ADM',TABNAME=>'EMP' ,STATTAB=>'MIG_TAB_STATS_BAK' ,STATOWN=>'MIG_ADM', STATID=>'MIG_20181118', CASCADE=>TRUE) ;
  • 예 : SCOTT의 EMP 통계를 SCOTT2의 EMP2로 이관

2.1 1) 통계정보를 임시로 저장할 Table생성.[편집]

exec dbms_stats.create_stat_table('SCOTT','STATS');

2.2 2) 통계정보 이관할 스키마/테이블 의 통계정보를 임시 table로 이관.[편집]

-- 테이블 
exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','emp stats',TRUE);
-- 스키마 
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS (   ownname =>'XX_DBA'				,   stattab => 'STATS',   statid  => 'XX_20190514',   statown =>'XX_DBA');
  • 참고 (updated: 2010/08/03)

"emp stats"로 중간에 space가 들어가면 에러발생함 

exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','EMP_STATS',TRUE,'SCOTT');
BEGIN dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','EMP STATS',TRUE,'SCOTT'); END;
*
ERROR at line 1:
ORA-20001: EMP STATS is an invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 8240
ORA-06512: at "SYS.DBMS_STATS", line 9188
ORA-06512: at line 1

* 주의 (updated: 2010/08/03)
STATS table의 데이터를 쿼리 해보면 EMP table의 원래 소유자인 SCOTT이 C5 column에 명시되어 있음.
만약 다른 계정으로, 즉 SCOTT2 계정으로 통계정보를 옮기려면 STATS table의 SCOTT값을 SCOTT2로 update 해주어야 함.
두개의 DB의 계정이 동일하다면 변경해 줄 필요 없음

2.3 3) 통계정보 백업 테이블 통계 수집[편집]

EXEC DBMS_STATS.GATHER_TABLE_STATS('MIG_ADM','MIG_TAB_STATS_BAK',DEGREE=>4,GRANULARITY=>'ALL',CASCADE=>TRUE);

2.4 4) 임시 table을 export, 그리고 target db로 import.[편집]

$exp scott/tiger tables=STATS file=expstat.dmp
 $imp scott/tiger file=expstat.dmp full=y log=implog.txt
(만약 동일 DB라면 이 과정은 생략 가능합니다.)

2.5 5) 임시 Table로 부터 통계치를 원하는 table에 넣는다.[편집]

EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME=>'MIG_ADM',TABNAME=>'EMP' ,STATTAB=>'MIG_TAB_STATS_BAK' ,STATOWN=>'MIG_ADM', STATID=>'MIG_20181118', CASCADE=>TRUE) ;

2.6 6) 통계정보 테이블 삭제[편집]

EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT2','STAT_TAB');

3 자동 통계정보 수집[편집]

- AWR(Automactic Workload Repository) 분석, Self-Tuing 및 일반적인 Tuning 을 목적으로 하는 자료로 특정 시간 동안 DB에서 발생한 여러 가지 상황 정보를 의미 합니다. Wait Events, Latches, Enqueues, Cpu Consumption, SGA Compoenent, PGA 등에 대한 자료 입니다. AWR의 정보는 default로 7일 보관 됩니다. 

     CBO(Cost-Based Optimizer)

DB 의 object 즉, application 및 oracle sys 및 system user의 table, index 에 대한 통계정보 수집을 합니다. 이 수집된 정보는 Optimizer 가 수행계획 수립 시에 의해 사용됩니다. Optimizer historical 통계정보는 default로31일간 보관됩니다.

  • 자동 통계정보 수집 옵션
statistics_level = {all | typical | basic} 

typical => 전반적인 DB 성능과 관련 통계정보와 DB 자가 관리 기능관련 정보를 수집(기본값)
all => typical + 일정시간 OS 통계정보와 실행 계획 통계를 수집.
basic => 수집을 하지 않음.

3.1 자동 통계정보 수집 변경[편집]

- 통계정보 parameter 확인 

$ sqlplus / as sysdba
SQL > show parameter statistics_level
  1. database level 적용
SQL > alter system set statistics_level = 'ALL';
SQL > alter system set statistics_level = 'BASIC';
  1. session level 적용
SQL > alter session set statistics_level = 'ALL';
SQL > alter session set statistics_level = 'BASIC';

3.2 자동 통계정보 수집 확인[편집]

SQL > select job_name, program_name, schedule_name, job_class
from dba_scheduler_jobs
where job_name ='GATHER_ STATS_JOB'

참고) 자동통계정보 수집에서 window의 의미는 수행될 작업에 할당될 시스템 자원에 대한 정보


3.3 자동 통계정보 수집 시 실행되는 program 확인[편집]

SQL > select program_action from dba_scheduler_programs where program_name ='GATHER_STATS_PROG'

참고) dba_scheduler_programs 는 DB내의 모든 scheduler program을 확인하는 system view

3.4 자동 통계정보 수집 시 스케줄 확인[편집]

SQL > select * from dba_scheduler_wingroup_members where window_group_name ='MAINTENANCE_WINDOW_GROUP';

참고) dba_scheduler_wingroup_members 는 각각의 time windows 어떻게 정의되어 있는지 확인하는 system view 입니다.

3.5 자동 통계정보 수집 시 스케줄 상세 확인[편집]

SQL> select window_name, repeat_interval, duration
from dba_scheduler_windows
where window_name in ('WEEKNIGHT_WINDOW','WEEKEND_WINDOW');

- 매주 월,화,수,목,금요일은 밤 10시에 8시간 동안 수행. - 토요일 0시에 수행되어 이틀 동안 수행.

3.6 자동 통계정보 수집 중지[편집]

SQL> select job_name, state from dba_scheduler_jobs where job_name ='GATHER_STATS_JOB';
SQL> exec dbms_scheduler.disable('GATHER_STATS_JOB');
SQL> select job_name, state from dba_scheduler_jobs where job_name ='GATHER_STATS_JOB';

3.7 자동 통계정보 수집 재설정[편집]

SQL> exec dbms_scheduler.enable('GATHER_STATS_JOB');
SQL> select job_name, state from dba_scheduler_jobs where job_name ='GATHER_STATS_JOB';
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_자동_통계정보_스케줄&oldid=3348"