Oracle11g 統計資訊-----統計資訊自動收集任務
11g中統計資訊自動收集任務的名稱是auto optimizer stats collection。11g中自動任務預設的執行時間視窗為:
週一到週五是晚上10點開始到2點結束
週末是早上六點,持續20個小時。
1、檢視自動收集任務及狀態
select client_name,status from Dba_Autotask_Client where client_name='auto optimizer stats collection';
2、停止自動收集任務
SQL> BEGIN 2 DBMS_AUTO_TASK_ADMIN.DISABLE( 3 client_name => 'auto optimizer stats collection', 4 operation => NULL, window_name => NULL); 5 END; 6 /
PL/SQL procedure successfully completed.
SQL> select client_name,status from Dba_Autotask_Client where client_name='auto optimizer stats collection';
CLIENT_NAME STATUS ---------------------------------------------------------------- -------- auto optimizer stats collection DISABLED
但是此時再查詢DBA_ATUOTASK_TASK檢視時,顯示該任務狀態還是ENABLED
SQL> select client_name,status from dba_autotask_task where client_name='auto optimizer stats collection';
CLIENT_NAME STATUS ---------------------------------------------------------------- -------- auto optimizer stats collection ENABLED
Oracle給出的解釋是在現在的版本中(11.1 to 11.2)一個client對應一個task,但是在將來的版本中會出現多個client會對應一個task,所以一個client被disabled了,不會改變task的狀態。[ID 858852.1]
3、啟動自動收集任務
SQL> BEGIN 2 DBMS_AUTO_TASK_ADMIN.ENABLE( 3 client_name => 'auto optimizer stats collection', 4 operation => NULL, window_name => NULL); 5 END; 6 /
PL/SQL procedure successfully completed.
SQL> select client_name,status from Dba_Autotask_Client where client_name='auto optimizer stats collection';
CLIENT_NAME STATUS ---------------------------------------------------------------- -------- auto optimizer stats collection ENABLED
4、檢視自動收集任務歷史執行狀態
SQL> SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed 2 FROM dba_autotask_client_history 3 WHERE client_name like '%stats%';
CLIENT_NAME WINDOW_NAME JOBS_CREATED JOBS_STARTED JOBS_COMPLETED ---------------------------------------- ------------------------------ ------------ ------------ -------------- auto optimizer stats collection SATURDAY_WINDOW 5 5 5 auto optimizer stats collection SUNDAY_WINDOW 6 6 6
通過時間視窗名稱可以看出是周幾執行的,在時間視窗內建立了幾次job,執行了幾次job,當然可以加上window_start_time來檢視具體執行的日期。
5、檢視自動收集任務執行時間視窗
SQL> select WINDOW_NAME, WINDOW_NEXT_TIME , WINDOW_ACTIVE,OPTIMIZER_STATS from DBA_AUTOTASK_WINDOW_CLIENTS order by WINDOW_NEXT_TIME ;
WINDOW_NAME WINDOW_NEXT_TIME WINDO OPTIMIZE ------------------------------ ----------------------------------------------- ----- -------- MONDAY_WINDOW 26-NOV-12 10.00.00.000000 PM PRC TRUE ENABLED TUESDAY_WINDOW 27-NOV-12 10.00.00.000000 PM PRC FALSE ENABLED WEDNESDAY_WINDOW 28-NOV-12 10.00.00.000000 PM PRC FALSE ENABLED THURSDAY_WINDOW 29-NOV-12 10.00.00.000000 PM PRC FALSE ENABLED FRIDAY_WINDOW 30-NOV-12 10.00.00.000000 PM PRC FALSE ENABLED SATURDAY_WINDOW 01-DEC-12 06.00.00.000000 AM PRC FALSE ENABLED SUNDAY_WINDOW 02-DEC-12 06.00.00.000000 AM PRC FALSE ENABLED
我將系統日期改為11月26日22點以後,MONDAY_WINDOW執行時間視窗自動啟用。
6、查詢自動收集任務正在執行的JOB
select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB where client_name='auto optimizer stats collection';
這個查詢沒有結果也很正常,只有job正在執行時,該查詢才有結果。
7、與時間視窗相關的檢視
查詢自動收集任務所屬時間視窗組
SQL> select client_name,window_group from dba_autotask_client where client_name='auto optimizer stats collection';
CLIENT_NAME WINDOW_GROUP ---------------------------------------- --------------------------------------------------- auto optimizer stats collection ORA$AT_WGRP_OS
查詢自動收集任務所屬時間視窗組詳細資訊
SQL> select * from dba_scheduler_window_groups where window_group_name='ORA$AT_WGRP_OS';
WINDOW_GROUP_NA ENABL NUMBER_OF_WINDOWS NEXT_START_DATE COMMENTS --------------- ----- ----------------- ----------------------------------- --------------------------------------------- ORA$AT_WGRP_OS TRUE 7 26-NOV-12 10.00.00.000000 PM PRC auto optimizer stats collection
檢視自動收集任務所屬時間視窗組包含的子時間視窗
SQL> select * from dba_scheduler_wingroup_members where window_group_name='ORA$AT_WGRP_OS';
WINDOW_GROUP_NA WINDOW_NAME --------------- ------------------------------ ORA$AT_WGRP_OS MONDAY_WINDOW ORA$AT_WGRP_OS TUESDAY_WINDOW ORA$AT_WGRP_OS WEDNESDAY_WINDOW ORA$AT_WGRP_OS THURSDAY_WINDOW ORA$AT_WGRP_OS FRIDAY_WINDOW ORA$AT_WGRP_OS SATURDAY_WINDOW ORA$AT_WGRP_OS SUNDAY_WINDOW
檢視子時間視窗資訊
SQL> select a.window_name,a.next_start_date,a.active from dba_scheduler_windows a 2 inner join dba_scheduler_wingroup_members b on a.window_name = b.window_name 3 where b.window_group_name='ORA$AT_WGRP_OS';
WINDOW_NAME NEXT_START_DATE ACTIV ------------------------------ ----------------------------------- ----- MONDAY_WINDOW 26-NOV-12 10.00.00.000000 PM PRC TRUE TUESDAY_WINDOW 27-NOV-12 10.00.00.000000 PM PRC FALSE WEDNESDAY_WINDOW 28-NOV-12 10.00.00.000000 PM PRC FALSE THURSDAY_WINDOW 29-NOV-12 10.00.00.000000 PM PRC FALSE FRIDAY_WINDOW 30-NOV-12 10.00.00.000000 PM PRC FALSE SATURDAY_WINDOW 01-DEC-12 06.00.00.000000 AM PRC FALSE SUNDAY_WINDOW 02-DEC-12 06.00.00.000000 AM PRC FALSE
通過這個查詢可以看出DBA_AUTOTASK_WINDOW_CLIENTS檢視的資訊其實和上面的結果一樣。
總結一下統計資訊自動收集任務執行的步驟:
首先是dba_autotask_task-->dba_autotask_client建立自動執行任務
再根據時間視窗及資源組建立自動執行作業
dba_autotask_client-->dba_scheduler_window_groups-->dba_scheduler_windows
-->dba_scheduler_jobs
dba_autotask_client-->dba_scheduler_job_classes
---檢視哪些表的統計資訊是舊的
Please create a test table in schema TBCS:
create table mytest(id number, name varchar2(100));
insert into mytest
select rownum,dbms_random.string('a',100)
from dual
connect by level <= 10000;
commit;
1. Pleas update SR with the following output:
$sqlplus / as sysdba
declare
mystaleobjs dbms_stats.objecttab;
begin
-- check whether there is any stale objects
dbms_stats.gather_schema_stats(ownname=>'TBCS',
options=>'LIST STALE',
objlist=>mystaleobjs);
for i in 1 .. mystaleobjs.count loop
dbms_output.put_line(mystaleobjs(i).objname);
end loop;
end;
/
declare
mystaleobjs dbms_stats.objecttab;
begin
-- check whether there is any stale objects
dbms_stats.gather_schema_stats(ownname=>'TBCS',
options=>'LIST EMPTY',
objlist=>mystaleobjs);
for i in 1 .. mystaleobjs.count loop
dbms_output.put_line(mystaleobjs(i).objname);
end loop;
end;
/
2. Please implement the following and provide relevant trace:
idle>exec DBMS_STATS.SET_GLOBAL_PREFS('trace', 4+8+16+64+128+512+2048);
PL/SQL procedure successfully completed.
idle>execute DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
idle>exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;
PL/SQL procedure successfully completed.
To disable trace, you can :
idle>exec DBMS_STATS.SET_GLOBAL_PREFS('trace', 0);
-----如果考慮表大考慮調整收集到的百分比
begin
DBMS_STATS.SET_TABLE_PREFS (
ownname => 'TBCS',
tabname => 'RECEPTION',
pname => 'ESTIMATE_PERCENT',
pvalue => '1'); ------------- you
can adjust the percent to a smaller value(0.01 or 0.1 etc)
end;
/
---手動呼叫自動維護視窗的JOB
|
SQL> execute DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SQL> exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS; This will prompt the Automated Maintenance Tasks subsystem into starting a job that will gather optimizer statistics, unless such a job is already running (for example if a maintenance window is currently open). If an immediate job is created it will be named ORA$_AT_OS_MANUAL_nnnnnn (nnnnn is one or more decimal digits). Unlike regular Automated Maintenance jobs, the "MANUAL" job is not tied to a specific maintenance window. |
---檢視歷史的維護視窗
select * from DBA_AUTOTASK_JOB_HISTORY WHERE client_name ='auto optimizer stats collection' order by window_start_time desc;
---批量檢查
set lines 500
set long 9999
set pages 999
set serveroutput on size 1000000
set feedback off
SET MARKUP HTML ON SPOOL ON HEAD "
spool query_result.html
set echo off
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
alter session set nls_timestamp_tz_format='yyyy/mm/dd hh24:mi:ss tzh:tzm';
select to_char(systimestamp,'yyyy/mm/dd hh24:mi:ss tzh:tzm') from dual;
select * from dba_autotask_client;
select * from DBA_SCHEDULER_WINGROUP_MEMBERS order by window_group_name,window_name;
select * from DBA_AUTOTASK_WINDOW_CLIENTS;
select * from DBA_AUTOTASK_SCHEDULE;
select * from DBA_AUTOTASK_WINDOW_HISTORY order by window_start_time desc;
select * from DBA_AUTOTASK_JOB_HISTORY WHERE client_name ='auto optimizer stats collection' order by window_start_time desc;
spool off
SET MARKUP HTML OFF
相關檢視:
dba_autotask_task
dba_autotask_client
dba_autotask_client_job
dba_autotask_window_clients
dba_autotask_client_history
dba_scheduler_jobs
dba_scheduler_job_classes
dba_scheduler_window_groups
dba_scheduler_windows
dba_scheduler_wingroup_members
自定義自動收集任務時間視窗:
How to use an own Maintenance Window for the Statistics Collection in 11g [ID 1300313.1]
MOS相關文件: FAQ: Automatic Statistics Collection [ID 1233203.1]
How to Gather Optimizer Statistics on 11g ( Doc ID 749227.1 )
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29446986/viewspace-1692544/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle11g 統計資訊——統計資訊自動收集任務Oracle
- Oracle統計資訊自動收集Oracle
- 關於oracle自動收集統計資訊Oracle
- 修改自動收集統計資訊任務的執行時間
- [統計資訊系列7] Oracle 11g的自動統計資訊收集Oracle
- ORACLE 11g 自動收集統計資訊Oracle
- Oracle的自動統計資訊不收集直方圖的資訊Oracle直方圖
- 【Oracle】11g Oracle自動收集統計資訊Oracle
- Oracle 11g 之自動收集統計資訊Oracle
- 關閉特定物件統計資訊自動收集物件
- Oracle10g 統計資訊的自動收集Oracle
- ORACLE10g自動收集CBO統計資訊Oracle
- 【統計資訊】Oracle常用的收集統計資訊方式Oracle
- Oracle收集統計資訊Oracle
- Oracle 統計資訊收集Oracle
- 收集oracle統計資訊Oracle
- ORACLE 收集統計資訊Oracle
- Greenplum自動統計資訊收集-暨統計資訊不準引入的broadcastmotion一例AST
- Oracle 11g無法自動收集統計資訊Oracle
- oracle 11g自動收集統計資訊介紹Oracle
- oracle 統計資訊過期判斷和自動收集Oracle
- [zt] Oracle10g 自動收集收集CBO統計資訊設定Oracle
- 收集全庫統計資訊
- mysql如收集統計資訊MySql
- MySQL 5.5 統計資訊收集MySql
- 開啟oracle10g統計資訊自動收集功能Oracle
- 微課sql最佳化(3)、統計資訊收集(2)-如何收集統計資訊SQL
- MySQL系統如何收集統計資訊MySql
- 關於ORACLE自動統計CBO統計資訊Oracle
- oracle收集統計資訊job停止Oracle
- 雞肋 -- ORACLE10g自動收集CBO統計資訊(ZT)Oracle
- oracle10g如何啟用禁用自動統計資訊收集Oracle
- 收集資料庫統計資訊需要收集直方圖資訊.資料庫直方圖
- 效能優化——統計資訊——SQLServer自動更新和自動建立統計資訊選項 (轉載)優化SQLServer
- 【統計資訊】Oracle統計資訊Oracle
- oracle 統計資訊檢視與收集Oracle
- 6 收集資料庫統計資訊資料庫
- Fixed Objects Statistics統計資訊收集 - 2Object