Oracle 11g 之自動收集統計資訊
在Oracle的11g版本中提供了統計資料自動收集的功能。在部署安裝11g Oracle軟體過程中,其中有一個步驟便是提示是否啟動這個功能(預設是啟用這個功能)。
一、檢視自動收集統計資訊的任務及狀態:
SQL> select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor DISABLED
SQL>
二、禁止自動收集統計資訊的任務
SQL> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
PL/SQL procedure successfully completed.
SQL> select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection DISABLED
auto space advisor ENABLED
sql tuning advisor DISABLED
三、啟用自動收集統計資訊的任務
SQL> exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
PL/SQL procedure successfully completed.
SQL> select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor DISABLED
四、獲得當前自動收集統計資訊的執行時間:
SQL> col WINDOW_NAME format a20
SQL> col REPEAT_INTERVAL format a70
SQL> col DURATION format a20
SQL> set line 180
SQL> select t1.window_name,t1.repeat_interval,t1.duration
from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');
WINDOW_NAME REPEAT_INTERVAL DURATION
-------------------- ---------------------------------------------------------------------- --------------------
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
7 rows selected.
其中:WINDOW_NAME:任務名 REPEAT_INTERVAL:任務重複間隔時間 DURATION:持續時間
五.修改統計資訊執行的時間:
1.停止任務:
SQL> BEGIN
DBMS_SCHEDULER.DISABLE(
name => '"SYS"."THURSDAY_WINDOW"',
force => TRUE); --停止任務是true
END;
/
SQL>
2.修改任務的持續時間,單位是分鐘:
SQL> BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."THURSDAY_WINDOW"',
attribute => 'DURATION',
value => numtodsinterval(60,'minute'));
END;
/
3.開始執行時間,BYHOUR=2,表示2點開始執行:
SQL> BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."THURSDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
value => 'freq=daily;byday=THU;byhour=10;byminute=40;bysecond=0');
END;
/
4.開啟任務:
SQL> BEGIN
DBMS_SCHEDULER.ENABLE(
name => '"SYS"."THURSDAY_WINDOW"');
END;
/
5.檢視修改後的情況:
SQL> select t1.window_name,t1.repeat_interval,t1.duration
from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');
WINDOW_NAME REPEAT_INTERVAL DURATION
-------------------- ---------------------------------------------------------------------- --------------------
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=10;byminute=40;bysecond=0 +000 01:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
六.檢視統計資訊執行的歷史記錄
--維護視窗組
select * from dba_scheduler_window_groups;
--維護視窗組對應視窗
select * from dba_scheduler_wingroup_members
--維護視窗歷史資訊
select* from dba_scheduler_windows
--查詢自動收集任務正在執行的job
select * from DBA_AUTOTASK_CLIENT_JOB;
--查詢自動收集任務歷史執行狀態
select * from DBA_AUTOTASK_JOB_HISTORY;
select * from DBA_AUTOTASK_CLIENT_HISTORY;
一、檢視自動收集統計資訊的任務及狀態:
SQL> select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor DISABLED
SQL>
二、禁止自動收集統計資訊的任務
SQL> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
PL/SQL procedure successfully completed.
SQL> select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection DISABLED
auto space advisor ENABLED
sql tuning advisor DISABLED
三、啟用自動收集統計資訊的任務
SQL> exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
PL/SQL procedure successfully completed.
SQL> select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor DISABLED
四、獲得當前自動收集統計資訊的執行時間:
SQL> col WINDOW_NAME format a20
SQL> col REPEAT_INTERVAL format a70
SQL> col DURATION format a20
SQL> set line 180
SQL> select t1.window_name,t1.repeat_interval,t1.duration
from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');
WINDOW_NAME REPEAT_INTERVAL DURATION
-------------------- ---------------------------------------------------------------------- --------------------
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
7 rows selected.
其中:WINDOW_NAME:任務名 REPEAT_INTERVAL:任務重複間隔時間 DURATION:持續時間
五.修改統計資訊執行的時間:
1.停止任務:
SQL> BEGIN
DBMS_SCHEDULER.DISABLE(
name => '"SYS"."THURSDAY_WINDOW"',
force => TRUE); --停止任務是true
END;
/
SQL>
2.修改任務的持續時間,單位是分鐘:
SQL> BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."THURSDAY_WINDOW"',
attribute => 'DURATION',
value => numtodsinterval(60,'minute'));
END;
/
3.開始執行時間,BYHOUR=2,表示2點開始執行:
SQL> BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."THURSDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
value => 'freq=daily;byday=THU;byhour=10;byminute=40;bysecond=0');
END;
/
4.開啟任務:
SQL> BEGIN
DBMS_SCHEDULER.ENABLE(
name => '"SYS"."THURSDAY_WINDOW"');
END;
/
5.檢視修改後的情況:
SQL> select t1.window_name,t1.repeat_interval,t1.duration
from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');
WINDOW_NAME REPEAT_INTERVAL DURATION
-------------------- ---------------------------------------------------------------------- --------------------
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=10;byminute=40;bysecond=0 +000 01:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
六.檢視統計資訊執行的歷史記錄
--維護視窗組
select * from dba_scheduler_window_groups;
--維護視窗組對應視窗
select * from dba_scheduler_wingroup_members
--維護視窗歷史資訊
select* from dba_scheduler_windows
--查詢自動收集任務正在執行的job
select * from DBA_AUTOTASK_CLIENT_JOB;
--查詢自動收集任務歷史執行狀態
select * from DBA_AUTOTASK_JOB_HISTORY;
select * from DBA_AUTOTASK_CLIENT_HISTORY;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31397003/viewspace-2147195/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【Oracle】11g Oracle自動收集統計資訊Oracle
- ORACLE 11g 自動收集統計資訊Oracle
- Oracle 11g無法自動收集統計資訊Oracle
- oracle 11g自動收集統計資訊介紹Oracle
- Oracle統計資訊自動收集Oracle
- [統計資訊系列7] Oracle 11g的自動統計資訊收集Oracle
- 關於oracle自動收集統計資訊Oracle
- oracle 11g統計資訊收集Oracle
- Oracle 11g 修改表級別的自動收集統計資訊比率Oracle
- Oracle 11g系統自動收集統計資訊的一些知識Oracle
- Oracle11g 統計資訊-----統計資訊自動收集任務Oracle
- Oracle11g 統計資訊——統計資訊自動收集任務Oracle
- Oracle10g 統計資訊的自動收集Oracle
- ORACLE10g自動收集CBO統計資訊Oracle
- Oracle 11g新特性之收集多列統計資訊Oracle
- Oracle 11g手工收集表統計資訊Oracle
- Oracle 11g 統計資訊收集指令碼Oracle指令碼
- Oracle的自動統計資訊不收集直方圖的資訊Oracle直方圖
- oracle 統計資訊過期判斷和自動收集Oracle
- [zt] Oracle10g 自動收集收集CBO統計資訊設定Oracle
- ORACLE 11G 自動收集Oracle
- 開啟oracle10g統計資訊自動收集功能Oracle
- Oracle收集統計資訊Oracle
- Oracle 統計資訊收集Oracle
- 收集oracle統計資訊Oracle
- ORACLE 收集統計資訊Oracle
- 雞肋 -- ORACLE10g自動收集CBO統計資訊(ZT)Oracle
- oracle10g如何啟用禁用自動統計資訊收集Oracle
- 關閉特定物件統計資訊自動收集物件
- 【統計資訊】Oracle常用的收集統計資訊方式Oracle
- 關閉及開啟oracle10g統計資訊自動收集功能Oracle
- Oracle收集統計資訊之NO_INVALIDATE引數Oracle
- Oracle檢視查詢慢之統計資訊收集Oracle
- 禁用11g的統計資料自動收集功能
- Oracle的奇葩設定之自動統計資訊更新Oracle
- oracle收集統計資訊job停止Oracle
- 關閉及開啟oracle10g統計資訊自動收集功能(轉)Oracle
- 關於ORACLE自動統計CBO統計資訊Oracle