Oracle 자동 통계 수집 중지 하기

DB 전체 자동 통계 중지

 

BEGIN
  DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/
PL/SQL procedure successfully completed.


col SCHEDULE_NAME for a40
select job_name, enabled, SCHEDULE_NAME, STATE from dba_SCHEDULER_JOBs ;

JOB_NAME                      ENABL SCHEDULE_NAME                  STATE
------------------------------ ----- ------------------------------ -------------------
AUTO_SPACE_ADVISOR_JOB        TRUE  MAINTENANCE_WINDOW_GROUP      SCHEDULED
GATHER_STATS_JOB              FALSE MAINTENANCE_WINDOW_GROUP      DISABLED
FGR$AUTOPURGE_JOB              FALSE                                DISABLED
PURGE_LOG                      TRUE  DAILY_PURGE_SCHEDULE          SCHEDULED

 

 

 

테이블, 사용자별 자동 통계 중지(통계 정보가 안돌고 있는 테이블 확인은 역으로 하면된다)

Prevents automatic gathering

Mainly used for volatile tables:

- Lock without statistics implies dynamic sampling

- Lock with statistics for representative values

 

EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('owner name', 'table name');
EXECUTE DBMS_STATS.LOCK_SCHEMA_STATS ('owner name');

SELECT owner, table_name, stattype_locked
FROM dba_tab_statistics
where stattype_locked is not null