"오라클 자동 통계정보 스케줄"의 두 판 사이의 차이
DB CAFE
(→2) 원하는 table의 통계정보를 임시 table로 이관.) |
(→자동 통계정보 수집 스케줄 시간 변경) |
||
(같은 사용자의 중간 판 107개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
− | = Oracle 통계정보 갱신 = | + | = Oracle 통계정보 자동 갱신 = |
+ | {{틀:고지 상자 | ||
+ | |내용=통계가 없거나 오래된 테이블/인덱스에 대해서 자동으로 옵티마이저 통계 수집. | ||
+ | # 10g 자동통계 job인 GATHER_STATS_JOB 보다 향상 된 기능 제공 | ||
+ | # 11g Automated Maintenance Tasks Infrastructure(이하 AutoTask)로 통합 | ||
+ | }} | ||
+ | # AutoTask 의 3개의 Task 작업 | ||
+ | ## - 옵티마이저 통계 수집 (Automatic Optimizer Statistics Collection) | ||
+ | ## - 세그먼트 어드바이져 (Automatic Segment Advisor) | ||
+ | ## - SQL 튜닝 어드바이져 (Automatic SQL Tuning Advisor) | ||
− | == | + | == AUTO TASK 실행 가능 환경 확인 == |
+ | === JOB QUEUE PROCESS 개수 확인 === | ||
+ | == 자동 수행 뷰 목록 == | ||
+ | {{틀:고지 상자 | ||
+ | |내용=# JOB_QUEUE_PROCESSES는 DBMS_JOB 작업 및 Oracle Scheduler (DBMS_SCHEDULER) 작업 실행을 위해 생성 할 수있는 인스턴스 당 최대 작업 슬레이브 수를 지정. | ||
+ | # DBMS_JOB 및 Oracle Scheduler는 동일한 작업 조정자와 작업 슬레이브를 공유하며 둘 다 JOB_QUEUE_PROCESSES 매개 변수로 제어. | ||
+ | # '''JOB_QUEUE_PROCESSES의 값이 0으로 설정되면 DBMS_JOB 작업 및 Oracle Scheduler 작업이 인스턴스에서 실행되지 않습니다.''' | ||
+ | #:JOB_QUEUE_PROCESSES가 1-1000 범위의 값으로 설정되면 DBMS_JOB 작업 및 Oracle Scheduler 작업 실행 됨. | ||
+ | # Oracle Scheduler 작업에 대해 생성 된 실제 작업 슬레이브 수는 사용 가능한 리소스, Resource Manager 설정 및 현재 실행중인 작업을 비롯한 여러 요인에 따라 스케줄러에 의해 자동 조정. | ||
+ | #: 그러나 인스턴스에서 DBMS_JOB 작업 및 Oracle Scheduler 작업을 실행하는 총 작업 슬레이브 수는 해당 인스턴스의 JOB_QUEUE_PROCESSES 값을 초과 할 수 없습니다. | ||
+ | # Oracle Scheduler 작업을 실행하는 작업 슬레이브의 수는 MAX_JOB_SLAVE_PROCESSES Scheduler 속성 값으로 추가로 제한 됨. | ||
+ | # Advanced replication은 데이터 새로 고침을 위해 Oracle Scheduler를 사용. | ||
+ | # Oracle Streams Advanced Queuing은 메시지 전파를 위해 Oracle Scheduler를 사용. | ||
+ | # Materialized view는 자동 새로 고침을 위해 Oracle Scheduler 사용. | ||
+ | # JOB_QUEUE_PROCESS를 0으로 설정하면 이러한 기능은 물론 Oracle Scheduler 또는 DBMS_JOB을 사용하는 다른 모든 기능이 비활성화됩니다. | ||
+ | }} | ||
<source lang=sql> | <source lang=sql> | ||
− | + | select * from gv$parameter | |
− | + | where name like '%job_queue_processes%' | |
− | + | ; | |
− | |||
</source> | </source> | ||
− | == | + | |
+ | === 통계 레벨 확인 확인 === | ||
<source lang=sql> | <source lang=sql> | ||
− | + | select * from gv$parameter | |
− | + | where name like '%statistics_level%' | |
− | + | ; | |
</source> | </source> | ||
− | == | + | |
+ | === JOB QUEUE PROCESS 개수 변경 === | ||
<source lang=sql> | <source lang=sql> | ||
− | + | -- 만약 0 이면 적당한 값으로 변경 | |
+ | alter system set job_queue_processes=100 scope=both; | ||
</source> | </source> | ||
− | == | + | |
+ | === 통계 수집 레벨 변경 === | ||
<source lang=sql> | <source lang=sql> | ||
− | + | -- 기본은 'typecal' 로 등록된 스케줄러에 의해 동작. | |
+ | -- all => typical + 일정시간 OS 통계정보와 실행 계획 통계를 수집. | ||
+ | -- 구지 상세하게 통계정보를 수집하고 싶다면 all (비추천), 통계 정보 수집시 라이브러리캐시 정보 갱신 , 라이브러리 캐시 락 발생 가능성 존재 | ||
+ | alter system set statistics_level = all scope=both; | ||
</source> | </source> | ||
− | == | + | |
+ | == 자동 수행 뷰 목록 == | ||
+ | {{틀:고지 상자 | ||
+ | |내용=# '''DBA_AUTOTASK_CLIENT''' 과거 7일간과 과거 30일간에 집계된 AutoTask 의 집계 데이터를 볼 수 있습니다. | ||
+ | # '''DBA_AUTOTASK_CLIENT_HISTORY''' 메인테넌스 윈도우별로 AutoTask의 실행횟수 이력을 제공합니다. | ||
+ | # '''DBA_AUTOTASK_CLIENT_JOB''' 현재 실행중인 AutoTask 를 표시합니다. | ||
+ | # '''DBA_AUTOTASK_JOB_HISTORY''' AutoTask Job 이력을 제공합니다. | ||
+ | # '''DBA_AUTOTASK_OPERATION''' AutoTask 에 관련된 속성을 표시합니다. | ||
+ | # '''DBA_AUTOTASK_SCHEDULE''' 향후 32일간의 메인테넌스 윈도우 스케줄을 표시합니다. | ||
+ | # '''DBA_AUTOTASK_TASK''' AutoTaks의 각종 통계 데이터를 표시합니다. | ||
+ | # '''DBA_AUTOTASK_WINDOW_CLIENTS''' 메인테넌스 윈도우별 상태를 제공합니다. | ||
+ | # '''DBA_AUTOTASK_WINDOW_HISTORY''' 메인테넌스 윈도의 실행 이력을 제공합니다. | ||
+ | }} | ||
+ | |||
+ | == 자동 수행 스케줄 목록 조회 == | ||
+ | # 요일별 자동 수행 목록 | ||
+ | * '''DBA_AUTOTASK_WINDOW_CLIENTS''' | ||
<source lang=sql> | <source lang=sql> | ||
− | + | SELECT WINDOW_NAME | |
− | + | , AUTOTASK_STATUS -- 자동수행 태스크 상태 | |
− | == | + | , OPTIMIZER_STATS -- 통계 수집 작업 |
+ | , SEGMENT_ADVISOR -- 디스크 상태 수집 작업 | ||
+ | , SQL_TUNE_ADVISOR -- 튜닝 상태 수집 작업 | ||
+ | , HEALTH_MONITOR -- 모니터링 | ||
+ | FROM DBA_AUTOTASK_WINDOW_CLIENTS; | ||
+ | |||
+ | -- AUTOTASK OPERATION | ||
+ | SELECT * | ||
+ | FROM DBA_AUTOTASK_OPERATION; | ||
+ | </source> | ||
+ | |||
+ | '''auto optimizer stats collection''' | ||
+ | |||
+ | == 자동 수행 목록 == | ||
+ | * '''DBA_AUTOTASK_CLIENT''' | ||
<source lang=sql> | <source lang=sql> | ||
− | SELECT | + | SELECT CLIENT_NAME |
− | FROM | + | , STATUS |
− | + | , MEAN_JOB_DURATION | |
+ | , TOTAL_CPU_LAST_7_DAYS | ||
+ | FROM DBA_AUTOTASK_CLIENT; | ||
</source> | </source> | ||
− | == | + | |
+ | * MEAN_JOB_DURATION은 평균 job 수행시간. not null이라면 한 번이라도 수행된 적이 있다는 것을 의미. | ||
+ | * TOTAL_CPU_LAST_7_DAYS은 최근 7일동안의 Cumulative CPU time , 이것으로 최근 수행여부를 확인. | ||
+ | |||
+ | == 자동 수행 목록 히스토리 조회 == | ||
+ | * '''DBA_AUTOTASK_CLIENT_HISTORY''' | ||
<source lang=sql> | <source lang=sql> | ||
− | + | SELECT * | |
+ | FROM DBA_AUTOTASK_CLIENT_HISTORY | ||
+ | ORDER BY WINDOW_START_TIME DESC | ||
+ | ; | ||
</source> | </source> | ||
− | + | ||
− | + | '''auto optimizer stats collection''' | |
− | </source> | + | |
− | == | + | == 자동 수행 시작/정지 == |
+ | <source lang=sql> | ||
+ | --disable Autotask | ||
+ | exec dbms_auto_task_admin.disable; | ||
+ | |||
+ | --ensable Autotask | ||
+ | exec dbms_auto_task_admin.enable; | ||
+ | </source> | ||
+ | |||
+ | == 통계정보만 수집 정지 == | ||
<source lang=sql> | <source lang=sql> | ||
− | + | BEGIN | |
− | + | DBMS_AUTO_TASK_ADMIN.DISABLE ( | |
+ | client_name => 'auto optimizer stats collection' | ||
+ | , operation => NULL | ||
+ | , window_name => NULL | ||
+ | ); | ||
+ | END; | ||
</source> | </source> | ||
+ | {{틀:고지 상자 | ||
+ | |내용=* OPERATION 과 WINDOW_NAME 둘 다 NULL이면,해당Client는 Disable/Enable 된다. | ||
+ | * OPERATION이 NULL 이 아니면,WINDOW_NAME은 무시되고 해당 OPERATION은 Disable/Enable 된다. | ||
+ | * OPERATION이 NULL 이고 WINDOW_NAME이 NULL이 아니면, 해당 Client는 지정한 Window에 한하여 Disable/Enable 된다. | ||
+ | }} | ||
− | = | + | == 통계/디스크/튜닝 정보 수집 활성화 == |
− | + | <source lang=sql> | |
− | + | /* | |
− | + | BEGIN | |
+ | DBMS_AUTO_TASK_ADMIN.ENABLE ( | ||
+ | client_name => 'auto optimizer stats collection' | ||
+ | , operation => NULL | ||
+ | , window_name => NULL | ||
+ | ); | ||
+ | */ | ||
+ | |||
+ | BEGIN | ||
+ | -- 1.통계정보 수집 활성화 | ||
+ | dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); | ||
+ | -- 2.디스크 정보 수집 활성화 | ||
+ | dbms_auto_task_admin.enable(client_name => 'auto space advisor', operation => NULL, window_name => NULL); | ||
+ | -- 3.튜닝 정보 수집 활성화 | ||
+ | dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); | ||
+ | END; | ||
+ | </source> | ||
+ | |||
+ | = 자동 통계정보 수집 = | ||
+ | {{틀:고지 상자 | ||
+ | |내용=# oracle 10g 이상인 경우 parameter 등을 변경하면 통계정보가 자동으로 scheduler에 의해 자동 수집 | ||
+ | # oracle의 job object중에 gather_stats_job에 의해 통계정보들이 수집. | ||
+ | # gather_stats_job은 기존 DATA의 10% 이상의 변경이 있거나, 오랫동안 통계치가 변경되지 않거나, 통계치가 없거나 한 Object 에 대한 Gathering 을 수행. | ||
+ | }} | ||
+ | == 자동 통계정보 수집 기능(Automatic Statistics) 대상 == | ||
+ | {{틀:고지 상자 | ||
+ | |내용=# 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일간 보관됩니다. | ||
+ | }} | ||
+ | * 자동 통계정보 수집 옵션 | ||
+ | <source lang=shell> | ||
+ | statistics_level = {all | typical | basic} | ||
+ | |||
+ | typical => 전반적인 DB 성능과 관련 통계정보와 DB 자가 관리 기능관련 정보를 수집(기본값) | ||
+ | all => typical + 일정시간 OS 통계정보와 실행 계획 통계를 수집. | ||
+ | basic => 수집을 하지 않음. | ||
+ | </source> | ||
− | + | == 자동 통계정보 수집 변경 == | |
+ | - 통계정보 parameter 확인 | ||
+ | <source lang=sql> | ||
+ | $ sqlplus / as sysdba | ||
+ | SQL > show parameter statistics_level | ||
+ | </source> | ||
+ | # database level 적용 | ||
+ | <source lang=sql> | ||
+ | SQL > alter system set statistics_level = 'ALL'; | ||
+ | SQL > alter system set statistics_level = 'BASIC'; | ||
+ | </source> | ||
+ | # session level 적용 | ||
<source lang=sql> | <source lang=sql> | ||
− | + | SQL > alter session set statistics_level = 'ALL'; | |
− | + | SQL > alter session set statistics_level = 'BASIC'; | |
− | + | </source> | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | + | == 자동 통계정보 수집 확인 == | |
− | + | <source lang=sql> | |
+ | select job_name, program_name, schedule_name, job_class | ||
+ | from dba_scheduler_jobs | ||
+ | where job_name ='GATHER_ STATS_JOB' | ||
+ | </source> | ||
+ | 참고) | ||
+ | 자동통계정보 수집에서 window의 의미는 수행될 작업에 할당될 시스템 자원에 대한 정보 | ||
− | + | == 자동 통계정보 수집 잡 실행 로그 확인 == | |
− | + | <source lang=sql> | |
− | + | SELECT * | |
+ | FROM ( SELECT LOG_DATE | ||
+ | , JOB_NAME | ||
+ | , STATUS | ||
+ | , ACTUAL_START_DATE | ||
+ | , RUN_DURATION | ||
+ | FROM DBA_SCHEDULER_JOB_RUN_DETAILS | ||
+ | WHERE JOB_NAME = 'GATHER_STATS_JOB' | ||
+ | ORDER BY LOG_ID DESC) | ||
+ | WHERE ROWNUM <= 10; | ||
</source> | </source> | ||
− | + | == 자동 통계정보 수집 시 실행되는 program 확인 == | |
<source lang=sql> | <source lang=sql> | ||
− | + | select program_action | |
− | + | from dba_scheduler_programs | |
− | + | where program_name ='GATHER_STATS_PROG' | |
− | + | </source> | |
− | + | 참고) | |
− | + | dba_scheduler_programs 는 DB내의 모든 scheduler program을 확인하는 system view | |
− | |||
− | |||
− | |||
− | |||
− | + | == 자동 통계정보 수집 시 스케줄 확인 == | |
− | + | <source lang=sql> | |
+ | select * | ||
+ | from dba_scheduler_wingroup_members | ||
+ | where window_group_name ='MAINTENANCE_WINDOW_GROUP'; | ||
+ | </source> | ||
+ | 참고) | ||
+ | dba_scheduler_wingroup_members 는 각각의 time windows 어떻게 정의되어 있는지 확인하는 system view 입니다. | ||
+ | == 자동 통계정보 수집 스케줄 확인 == | ||
+ | <source lang=sql> | ||
+ | select window_name, repeat_interval, duration | ||
+ | from dba_scheduler_windows; | ||
+ | -- where window_name in ('WEEKNIGHT_WINDOW','WEEKEND_WINDOW'); | ||
</source> | </source> | ||
+ | - 매주 월,화,수,목,금요일은 밤 10시에 8시간 동안 수행. | ||
+ | - 토요일 0시에 수행되어 이틀 동안 수행. | ||
− | |||
− | == | + | == 자동 통계정보 수집 스케줄 시간 변경 == |
+ | {{틀:고지 상자 | ||
+ | |내용=* 윈도우가 매우 짧은 기간 동안 열려 있으면 기본 유지 관리 작업이 실패합니다. | ||
+ | * 경우에 따라 작업을 완료까지 시간이 적절하지 않을 수 있습니다. | ||
+ | * MMON이 작업 실행 시도를 기록하거나 DBA_AUTOTASK_TASK에 작업을 표시하지 못할 수 있음. | ||
+ | }} | ||
<source lang=sql> | <source lang=sql> | ||
− | exec | + | -- 1,스케줄 윈도우 확인 |
+ | select window_name,repeat_interval,duration from dba_scheduler_windows; | ||
+ | |||
+ | -- 2. 윈도우 작동 시간 증가 | ||
+ | exec dbms_scheduler.set_attribute('MONDAY_WINDOW','DURATION','+000 04:00:00'); | ||
</source> | </source> | ||
− | == | + | == 자동 통계정보 수집 스케줄 활성화 == |
<source lang=sql> | <source lang=sql> | ||
− | -- | + | -- 토요일 활성화 |
− | exec | + | exec dbms_scheduler.enable('SATURDAY_WINDOW'); |
− | |||
− | |||
</source> | </source> | ||
− | |||
− | |||
+ | == 자동 통계정보 수집 중지 == | ||
<source lang=sql> | <source lang=sql> | ||
− | + | 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'; | |
− | + | </source> | |
− | |||
− | |||
− | |||
− | |||
− | + | == 요일별 통계수집 스케줄러 정지 == | |
− | + | <source lang=sql> | |
− | + | exec dbms_scheduler.disable('WEEKNIGHT_WINDOW'); | |
− | + | exec dbms_scheduler.disable('WEEKEND_WINDOW'); | |
</source> | </source> | ||
− | == | + | == 자동 통계정보 수집중인 작업 수동 종료 == |
<source lang=sql> | <source lang=sql> | ||
− | + | -- 작동중인 토요일 스케줄러 종료 | |
+ | execute dbms_scheduler.close_window ('SATURDAY_WINDOW'); | ||
</source> | </source> | ||
− | == | + | == 자동 통계정보 수집 재설정 == |
+ | <source lang=sql> | ||
+ | SQL> exec dbms_scheduler.enable('GATHER_STATS_JOB'); | ||
+ | SQL> select job_name, state from dba_scheduler_jobs where job_name ='GATHER_STATS_JOB'; | ||
+ | </source> | ||
+ | == DBA_AUTOTASK_WINDOW_CLIENTS 테이블 WINDOW_NEXT_TIME 컬럼이 과거날짜인경우 == | ||
+ | * DBA_AUTOTASK_WINDOW_CLIENTS.WINDOW_NEXT_TIME이 과거 날짜인 경우 윈도우를 DROP후 재생성 필요. | ||
<source lang=sql> | <source lang=sql> | ||
− | + | -- 윈도우 drop할경우 발생된느 에러는 무시해도 됨. | |
− | + | @?/RDBMS/ADMIN/CATNOMWN.SQL | |
− | ( | + | or |
+ | -- 수동 스케줄 삭제 | ||
+ | EXECUTE DBMS_SCHEDULER.DROP_WINDOW('MONDAY_WINDOW'); | ||
+ | EXECUTE DBMS_SCHEDULER.DROP_WINDOW('TUESDAY_WINDOW'); | ||
+ | EXECUTE DBMS_SCHEDULER.DROP_WINDOW('WEDNESDAY_WINDOW'); | ||
+ | EXECUTE DBMS_SCHEDULER.DROP_WINDOW('THURSDAY_WINDOW'); | ||
+ | EXECUTE DBMS_SCHEDULER.DROP_WINDOW('FRIDAY_WINDOW'); | ||
+ | EXECUTE DBMS_SCHEDULER.DROP_WINDOW('SATURDAY_WINDOW'); | ||
+ | EXECUTE DBMS_SCHEDULER.DROP_WINDOW('SUNDAY_WINDOW'); | ||
+ | |||
+ | -- 그리고 스케줄 재생성 | ||
+ | @?/RDBMS/ADMIN/CATMWIN.SQL | ||
</source> | </source> | ||
− | == | + | == 스케줄잡이 실패하거나 계속작동중인경우 강제 종료 == |
+ | * 작업이 작동을 멈춘 경우 마지막으로 성공한 (또는 실패한) 작업이 여전히 멈춰 있고 DBA_AUTOTASK_TASK가 작업이 일관되게 존재하는 것으로 나타나면이 작업 삭제 | ||
<source lang=sql> | <source lang=sql> | ||
− | + | exec dbms_scheduler.drop_job (job_name => '<job_name>',force =>true); | |
+ | eg: | ||
+ | exec dbms_scheduler.drop_job (job_name => 'ora$at_os_opt_sy_2611',force =>true); | ||
+ | |||
+ | Note: <JOB_NAME> is from DBA_SCHEDULER_JOB_RUN_DETAILS.JOB_NAME | ||
</source> | </source> | ||
− | == | + | == 스케줄러 미작동시 최후의 방법 == |
+ | * 최후의 방법이니 운영중인 시스템에서는 절대 수행 불가 | ||
<source lang=sql> | <source lang=sql> | ||
− | + | -- 1. 모든 스케줄러 작업이 완료 될 때까지 확인 / 대기 | |
+ | select owner,job_name from dba_scheduler_running_jobs; | ||
+ | |||
+ | -- 2. 스케줄러 중지 | ||
+ | exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE'); | ||
+ | alter system set job_queue_processes=0; | ||
+ | exec dbms_ijob.set_enabled(FALSE); | ||
+ | |||
+ | -- 3. Flush shared pool once | ||
+ | alter system flush shared_pool; | ||
+ | alter system flush shared_pool; | ||
+ | |||
+ | -- 4. 스케줄러 활성화 | ||
+ | exec dbms_ijob.set_enabled(TRUE); | ||
+ | alter system set job_queue_processes=1000; | ||
+ | exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE'); | ||
</source> | </source> | ||
+ | |||
+ | [[Category:oracle]] |
2023년 3월 30일 (목) 21:15 기준 최신판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 Oracle 통계정보 자동 갱신
- 2 자동 통계정보 수집
- 2.1 자동 통계정보 수집 기능(Automatic Statistics) 대상
- 2.2 자동 통계정보 수집 변경
- 2.3 자동 통계정보 수집 확인
- 2.4 자동 통계정보 수집 잡 실행 로그 확인
- 2.5 자동 통계정보 수집 시 실행되는 program 확인
- 2.6 자동 통계정보 수집 시 스케줄 확인
- 2.7 자동 통계정보 수집 스케줄 확인
- 2.8 자동 통계정보 수집 스케줄 시간 변경
- 2.9 자동 통계정보 수집 스케줄 활성화
- 2.10 자동 통계정보 수집 중지
- 2.11 요일별 통계수집 스케줄러 정지
- 2.12 자동 통계정보 수집중인 작업 수동 종료
- 2.13 자동 통계정보 수집 재설정
- 2.14 DBA_AUTOTASK_WINDOW_CLIENTS 테이블 WINDOW_NEXT_TIME 컬럼이 과거날짜인경우
- 2.15 스케줄잡이 실패하거나 계속작동중인경우 강제 종료
- 2.16 스케줄러 미작동시 최후의 방법
1 Oracle 통계정보 자동 갱신[편집]
android 통계가 없거나 오래된 테이블/인덱스에 대해서 자동으로 옵티마이저 통계 수집.
- 10g 자동통계 job인 GATHER_STATS_JOB 보다 향상 된 기능 제공
- 11g Automated Maintenance Tasks Infrastructure(이하 AutoTask)로 통합
- AutoTask 의 3개의 Task 작업
- - 옵티마이저 통계 수집 (Automatic Optimizer Statistics Collection)
- - 세그먼트 어드바이져 (Automatic Segment Advisor)
- - SQL 튜닝 어드바이져 (Automatic SQL Tuning Advisor)
1.2 자동 수행 뷰 목록[편집]
android # JOB_QUEUE_PROCESSES는 DBMS_JOB 작업 및 Oracle Scheduler (DBMS_SCHEDULER) 작업 실행을 위해 생성 할 수있는 인스턴스 당 최대 작업 슬레이브 수를 지정.
- DBMS_JOB 및 Oracle Scheduler는 동일한 작업 조정자와 작업 슬레이브를 공유하며 둘 다 JOB_QUEUE_PROCESSES 매개 변수로 제어.
- JOB_QUEUE_PROCESSES의 값이 0으로 설정되면 DBMS_JOB 작업 및 Oracle Scheduler 작업이 인스턴스에서 실행되지 않습니다.
- JOB_QUEUE_PROCESSES가 1-1000 범위의 값으로 설정되면 DBMS_JOB 작업 및 Oracle Scheduler 작업 실행 됨.
- Oracle Scheduler 작업에 대해 생성 된 실제 작업 슬레이브 수는 사용 가능한 리소스, Resource Manager 설정 및 현재 실행중인 작업을 비롯한 여러 요인에 따라 스케줄러에 의해 자동 조정.
- 그러나 인스턴스에서 DBMS_JOB 작업 및 Oracle Scheduler 작업을 실행하는 총 작업 슬레이브 수는 해당 인스턴스의 JOB_QUEUE_PROCESSES 값을 초과 할 수 없습니다.
- Oracle Scheduler 작업을 실행하는 작업 슬레이브의 수는 MAX_JOB_SLAVE_PROCESSES Scheduler 속성 값으로 추가로 제한 됨.
- Advanced replication은 데이터 새로 고침을 위해 Oracle Scheduler를 사용.
- Oracle Streams Advanced Queuing은 메시지 전파를 위해 Oracle Scheduler를 사용.
- Materialized view는 자동 새로 고침을 위해 Oracle Scheduler 사용.
- JOB_QUEUE_PROCESS를 0으로 설정하면 이러한 기능은 물론 Oracle Scheduler 또는 DBMS_JOB을 사용하는 다른 모든 기능이 비활성화됩니다.
select * from gv$parameter
where name like '%job_queue_processes%'
;
1.2.1 통계 레벨 확인 확인[편집]
select * from gv$parameter
where name like '%statistics_level%'
;
1.2.2 JOB QUEUE PROCESS 개수 변경[편집]
-- 만약 0 이면 적당한 값으로 변경
alter system set job_queue_processes=100 scope=both;
1.2.3 통계 수집 레벨 변경[편집]
-- 기본은 'typecal' 로 등록된 스케줄러에 의해 동작.
-- all => typical + 일정시간 OS 통계정보와 실행 계획 통계를 수집.
-- 구지 상세하게 통계정보를 수집하고 싶다면 all (비추천), 통계 정보 수집시 라이브러리캐시 정보 갱신 , 라이브러리 캐시 락 발생 가능성 존재
alter system set statistics_level = all scope=both;
1.3 자동 수행 뷰 목록[편집]
android # DBA_AUTOTASK_CLIENT 과거 7일간과 과거 30일간에 집계된 AutoTask 의 집계 데이터를 볼 수 있습니다.
- DBA_AUTOTASK_CLIENT_HISTORY 메인테넌스 윈도우별로 AutoTask의 실행횟수 이력을 제공합니다.
- DBA_AUTOTASK_CLIENT_JOB 현재 실행중인 AutoTask 를 표시합니다.
- DBA_AUTOTASK_JOB_HISTORY AutoTask Job 이력을 제공합니다.
- DBA_AUTOTASK_OPERATION AutoTask 에 관련된 속성을 표시합니다.
- DBA_AUTOTASK_SCHEDULE 향후 32일간의 메인테넌스 윈도우 스케줄을 표시합니다.
- DBA_AUTOTASK_TASK AutoTaks의 각종 통계 데이터를 표시합니다.
- DBA_AUTOTASK_WINDOW_CLIENTS 메인테넌스 윈도우별 상태를 제공합니다.
- DBA_AUTOTASK_WINDOW_HISTORY 메인테넌스 윈도의 실행 이력을 제공합니다.
1.4 자동 수행 스케줄 목록 조회[편집]
- 요일별 자동 수행 목록
- DBA_AUTOTASK_WINDOW_CLIENTS
SELECT WINDOW_NAME
, AUTOTASK_STATUS -- 자동수행 태스크 상태
, OPTIMIZER_STATS -- 통계 수집 작업
, SEGMENT_ADVISOR -- 디스크 상태 수집 작업
, SQL_TUNE_ADVISOR -- 튜닝 상태 수집 작업
, HEALTH_MONITOR -- 모니터링
FROM DBA_AUTOTASK_WINDOW_CLIENTS;
-- AUTOTASK OPERATION
SELECT *
FROM DBA_AUTOTASK_OPERATION;
auto optimizer stats collection
1.5 자동 수행 목록[편집]
- DBA_AUTOTASK_CLIENT
SELECT CLIENT_NAME
, STATUS
, MEAN_JOB_DURATION
, TOTAL_CPU_LAST_7_DAYS
FROM DBA_AUTOTASK_CLIENT;
- MEAN_JOB_DURATION은 평균 job 수행시간. not null이라면 한 번이라도 수행된 적이 있다는 것을 의미.
- TOTAL_CPU_LAST_7_DAYS은 최근 7일동안의 Cumulative CPU time , 이것으로 최근 수행여부를 확인.
1.6 자동 수행 목록 히스토리 조회[편집]
- DBA_AUTOTASK_CLIENT_HISTORY
SELECT *
FROM DBA_AUTOTASK_CLIENT_HISTORY
ORDER BY WINDOW_START_TIME DESC
;
auto optimizer stats collection
1.7 자동 수행 시작/정지[편집]
--disable Autotask
exec dbms_auto_task_admin.disable;
--ensable Autotask
exec dbms_auto_task_admin.enable;
1.8 통계정보만 수집 정지[편집]
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE (
client_name => 'auto optimizer stats collection'
, operation => NULL
, window_name => NULL
);
END;
android * OPERATION 과 WINDOW_NAME 둘 다 NULL이면,해당Client는 Disable/Enable 된다.
- OPERATION이 NULL 이 아니면,WINDOW_NAME은 무시되고 해당 OPERATION은 Disable/Enable 된다.
- OPERATION이 NULL 이고 WINDOW_NAME이 NULL이 아니면, 해당 Client는 지정한 Window에 한하여 Disable/Enable 된다.
1.9 통계/디스크/튜닝 정보 수집 활성화[편집]
/*
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE (
client_name => 'auto optimizer stats collection'
, operation => NULL
, window_name => NULL
);
*/
BEGIN
-- 1.통계정보 수집 활성화
dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
-- 2.디스크 정보 수집 활성화
dbms_auto_task_admin.enable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
-- 3.튜닝 정보 수집 활성화
dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;
2 자동 통계정보 수집[편집]
android # oracle 10g 이상인 경우 parameter 등을 변경하면 통계정보가 자동으로 scheduler에 의해 자동 수집
- oracle의 job object중에 gather_stats_job에 의해 통계정보들이 수집.
- gather_stats_job은 기존 DATA의 10% 이상의 변경이 있거나, 오랫동안 통계치가 변경되지 않거나, 통계치가 없거나 한 Object 에 대한 Gathering 을 수행.
2.1 자동 통계정보 수집 기능(Automatic Statistics) 대상[편집]
android # 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 => 수집을 하지 않음.
2.2 자동 통계정보 수집 변경[편집]
- 통계정보 parameter 확인
$ sqlplus / as sysdba
SQL > show parameter statistics_level
- database level 적용
SQL > alter system set statistics_level = 'ALL';
SQL > alter system set statistics_level = 'BASIC';
- session level 적용
SQL > alter session set statistics_level = 'ALL';
SQL > alter session set statistics_level = 'BASIC';
2.3 자동 통계정보 수집 확인[편집]
select job_name, program_name, schedule_name, job_class
from dba_scheduler_jobs
where job_name ='GATHER_ STATS_JOB'
참고) 자동통계정보 수집에서 window의 의미는 수행될 작업에 할당될 시스템 자원에 대한 정보
2.4 자동 통계정보 수집 잡 실행 로그 확인[편집]
SELECT *
FROM ( SELECT LOG_DATE
, JOB_NAME
, STATUS
, ACTUAL_START_DATE
, RUN_DURATION
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE JOB_NAME = 'GATHER_STATS_JOB'
ORDER BY LOG_ID DESC)
WHERE ROWNUM <= 10;
2.5 자동 통계정보 수집 시 실행되는 program 확인[편집]
select program_action
from dba_scheduler_programs
where program_name ='GATHER_STATS_PROG'
참고) dba_scheduler_programs 는 DB내의 모든 scheduler program을 확인하는 system view
2.6 자동 통계정보 수집 시 스케줄 확인[편집]
select *
from dba_scheduler_wingroup_members
where window_group_name ='MAINTENANCE_WINDOW_GROUP';
참고) dba_scheduler_wingroup_members 는 각각의 time windows 어떻게 정의되어 있는지 확인하는 system view 입니다.
2.7 자동 통계정보 수집 스케줄 확인[편집]
select window_name, repeat_interval, duration
from dba_scheduler_windows;
-- where window_name in ('WEEKNIGHT_WINDOW','WEEKEND_WINDOW');
- 매주 월,화,수,목,금요일은 밤 10시에 8시간 동안 수행. - 토요일 0시에 수행되어 이틀 동안 수행.
2.8 자동 통계정보 수집 스케줄 시간 변경[편집]
android * 윈도우가 매우 짧은 기간 동안 열려 있으면 기본 유지 관리 작업이 실패합니다.
- 경우에 따라 작업을 완료까지 시간이 적절하지 않을 수 있습니다.
- MMON이 작업 실행 시도를 기록하거나 DBA_AUTOTASK_TASK에 작업을 표시하지 못할 수 있음.
-- 1,스케줄 윈도우 확인
select window_name,repeat_interval,duration from dba_scheduler_windows;
-- 2. 윈도우 작동 시간 증가
exec dbms_scheduler.set_attribute('MONDAY_WINDOW','DURATION','+000 04:00:00');
2.9 자동 통계정보 수집 스케줄 활성화[편집]
-- 토요일 활성화
exec dbms_scheduler.enable('SATURDAY_WINDOW');
2.10 자동 통계정보 수집 중지[편집]
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';
2.11 요일별 통계수집 스케줄러 정지[편집]
exec dbms_scheduler.disable('WEEKNIGHT_WINDOW');
exec dbms_scheduler.disable('WEEKEND_WINDOW');
2.12 자동 통계정보 수집중인 작업 수동 종료[편집]
-- 작동중인 토요일 스케줄러 종료
execute dbms_scheduler.close_window ('SATURDAY_WINDOW');
2.13 자동 통계정보 수집 재설정[편집]
SQL> exec dbms_scheduler.enable('GATHER_STATS_JOB');
SQL> select job_name, state from dba_scheduler_jobs where job_name ='GATHER_STATS_JOB';
2.14 DBA_AUTOTASK_WINDOW_CLIENTS 테이블 WINDOW_NEXT_TIME 컬럼이 과거날짜인경우[편집]
- DBA_AUTOTASK_WINDOW_CLIENTS.WINDOW_NEXT_TIME이 과거 날짜인 경우 윈도우를 DROP후 재생성 필요.
-- 윈도우 drop할경우 발생된느 에러는 무시해도 됨.
@?/RDBMS/ADMIN/CATNOMWN.SQL
or
-- 수동 스케줄 삭제
EXECUTE DBMS_SCHEDULER.DROP_WINDOW('MONDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.DROP_WINDOW('TUESDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.DROP_WINDOW('WEDNESDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.DROP_WINDOW('THURSDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.DROP_WINDOW('FRIDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.DROP_WINDOW('SATURDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.DROP_WINDOW('SUNDAY_WINDOW');
-- 그리고 스케줄 재생성
@?/RDBMS/ADMIN/CATMWIN.SQL
2.15 스케줄잡이 실패하거나 계속작동중인경우 강제 종료[편집]
- 작업이 작동을 멈춘 경우 마지막으로 성공한 (또는 실패한) 작업이 여전히 멈춰 있고 DBA_AUTOTASK_TASK가 작업이 일관되게 존재하는 것으로 나타나면이 작업 삭제
exec dbms_scheduler.drop_job (job_name => '<job_name>',force =>true);
eg:
exec dbms_scheduler.drop_job (job_name => 'ora$at_os_opt_sy_2611',force =>true);
Note: <JOB_NAME> is from DBA_SCHEDULER_JOB_RUN_DETAILS.JOB_NAME
2.16 스케줄러 미작동시 최후의 방법[편집]
- 최후의 방법이니 운영중인 시스템에서는 절대 수행 불가
-- 1. 모든 스케줄러 작업이 완료 될 때까지 확인 / 대기
select owner,job_name from dba_scheduler_running_jobs;
-- 2. 스케줄러 중지
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
alter system set job_queue_processes=0;
exec dbms_ijob.set_enabled(FALSE);
-- 3. Flush shared pool once
alter system flush shared_pool;
alter system flush shared_pool;
-- 4. 스케줄러 활성화
exec dbms_ijob.set_enabled(TRUE);
alter system set job_queue_processes=1000;
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');