查询统计信息的收集所对应的task,以及当前状态
col CLIENT_name for a50
col TASK_name for a20
SELECT clIEnt_name,task_name,status FROM dba_autotask_task WHERE clIEnt_name = ‘auto optimizer stats collection‘;auto optimizer stats collection gather_stats_prog ENABLED;
CLIENT_name TASK_name STATUS--------------- ------------------ ----------------- -------auto optimizer stats collection gather_stats_prog ENABLED
select * from v$version;
BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/sql Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNS for linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - Production
下面查询dba_scheduler_programs
--在scheduler_programs中，程序GATHER_STATS_PROG调用
--dbms_stats.gather_database_stats_job_proc来实现统计信息的自动收集
col PROGRAM_ACTION for a50
SELECT program_action,number_of_arguments,enabledFROM dba_scheduler_programsWHERE owner = ‘SYS‘AND program_name = ‘GATHER_STATS_PROG‘;
PROGRAM_ACTION NUMBER_OF_ARGUMENTS ENABL-------------------------------------------------- ------------------- -----dbms_stats.gather_database_stats_job_proc 0 TRUE
2、查看自动收集统计信息是否开启
SELECT CLIENT_name,STATUS FROM DBA_autoTASK_CLIENT WHERE CLIENT_name = ‘auto optimizer stats collection‘;
CLIENT_name STATUS---------------------------------------------------------------- --------auto optimizer stats collection ENABLED
统计信息收集的窗口组，如下查询
SELECT window_groupFROM dba_autotask_clIEntWHERE clIEnt_name = ‘auto optimizer stats collection‘;
WINDOW_GROUP----------------------------------------------------------------ORA$AT_WGRP_OS
查询统计信息收集的具体窗口
--统计信息收集的时间窗口
--如下查询周一至周五时间为22点，周六日为6点
--此外持续也不相同，周一至周五为4小时，周六日为20个小时
--enabled为true表明当前的这些作业处于激活状态
col REPEAT_INTERVAL for a60
col DURATION for a30
SELECT w.window_name,w.repeat_interval,w.duration,w.enabled FROM dba_autotask_window_clIEnts c,dba_scheduler_windows w WHERE c.window_name = w.window_name AND c.optimizer_stats = ‘ENABLED‘;
WINDOW_name REPEAT_INTERVAL DURATION ENABL------------------------------ ------------------------------------------------------------ ------------------------------ -----MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUETUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUEWednESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUETHURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUEFRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUESATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUESUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE
查看自动收集统计信息历史执行情况
SELECT * FROM dba_autotask_clIEnt_history WHERE clIEnt_name liKE ‘%stats%‘;
CLIENT_name WINDOW_name WINDOW_START_TIME WINDOW_DURATION JOBS_CREATED JOBS_STARTED JOBS_COMPLETED WINDOW_END_TIME---------------------------------------------------------------- ----------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------ ------------ -------------- --------------------------------------------------------------------------------auto optimizer stats collection WednESDAY_WINDOW 24-APR-19 10.00.00.084731 PM +08:00 +000000000 03:59:59.989882 1 1 1 25-APR-19 02.00.00.074613 AM +08:00auto optimizer stats collection THURSDAY_WINDOW 25-APR-19 10.00.00.075150 PM +08:00 +000000000 03:59:59.973396 1 1 1 26-APR-19 02.00.00.048546 AM +08:00auto optimizer stats collection FRIDAY_WINDOW 26-APR-19 10.00.00.088460 PM +08:00 +000000000 03:59:59.986848 1 1 1 27-APR-19 02.00.00.075308 AM +08:00auto optimizer stats collection SATURDAY_WINDOW 27-APR-19 06.00.02.066587 AM +08:00 +000000000 19:59:58.014463 5 5 5 28-APR-19 02.00.00.081050 AM +08:00auto optimizer stats collection SUNDAY_WINDOW 28-APR-19 06.00.00.180330 AM +08:00 +000000000 19:59:59.883249 5 5 5 29-APR-19 02.00.00.063579 AM +08:00auto optimizer stats collection MONDAY_WINDOW 29-APR-19 10.00.00.071791 PM +08:00 +000000000 04:00:00.027280 1 1 1 30-APR-19 02.00.00.099071 AM +08:00auto optimizer stats collection TUESDAY_WINDOW 30-APR-19 10.00.00.093139 PM +08:00 +000000000 03:59:59.989633 1 1 1 01-MAY-19 02.00.00.082772 AM +08:00auto optimizer stats collection WednESDAY_WINDOW 01-MAY-19 10.00.00.104952 PM +08:00 +000000000 03:59:59.971842 1 1 1 02-MAY-19 02.00.00.076794 AM +08:00auto optimizer stats collection THURSDAY_WINDOW 02-MAY-19 10.00.01.068409 PM +08:00 +000000000 03:59:58.983145 1 1 1 03-MAY-19 02.00.00.051554 AM +08:00auto optimizer stats collection FRIDAY_WINDOW 03-MAY-19 10.00.00.077000 PM +08:00 +000000000 03:59:59.993378 1 1 1 04-MAY-19 02.00.00.070378 AM +08:00auto optimizer stats collection SATURDAY_WINDOW 04-MAY-19 06.00.01.062949 AM +08:00 +000000000 19:59:59.018980 5 5 5 05-MAY-19 02.00.00.081929 AM +08:00auto optimizer stats collection SUNDAY_WINDOW 05-MAY-19 06.00.00.073691 AM +08:00 +000000000 20:00:01.010166 5 5 5 06-MAY-19 02.00.01.083857 AM +08:00auto optimizer stats collection MONDAY_WINDOW 06-MAY-19 10.00.00.090751 PM +08:00 +000000000 03:59:59.989213 1 1 1 07-MAY-19 02.00.00.079964 AM +08:00auto optimizer stats collection TUESDAY_WINDOW 07-MAY-19 10.00.00.064104 PM +08:00 +000000000 04:00:00.016201 1 1 1 08-MAY-19 02.00.00.080305 AM +08:00auto optimizer stats collection WednESDAY_WINDOW 08-MAY-19 10.00.00.065315 PM +08:00 +000000000 04:00:00.018545 1 1 1 09-MAY-19 02.00.00.083860 AM +08:00auto optimizer stats collection THURSDAY_WINDOW 09-MAY-19 10.00.00.073837 PM +08:00 +000000000 03:59:59.980112 1 1 1 10-MAY-19 02.00.00.053949 AM +08:00auto optimizer stats collection FRIDAY_WINDOW 10-MAY-19 10.00.00.074381 PM +08:00 +000000000 03:59:59.999777 1 1 1 11-MAY-19 02.00.00.074158 AM +08:00auto optimizer stats collection SATURDAY_WINDOW 11-MAY-19 06.00.01.071340 AM +08:00 +000000000 19:59:59.004645 5 5 5 12-MAY-19 02.00.00.075985 AM +08:00auto optimizer stats collection SUNDAY_WINDOW 12-MAY-19 06.00.01.066112 AM +08:00 +000000000 19:59:59.015558 5 5 5 13-MAY-19 02.00.00.081670 AM +08:00auto optimizer stats collection MONDAY_WINDOW 13-MAY-19 10.00.01.074773 PM +08:00 +000000000 03:59:58.975154 1 1 1 14-MAY-19 02.00.00.049927 AM +08:00CLIENT_name WINDOW_name WINDOW_START_TIME WINDOW_DURATION JOBS_CREATED JOBS_STARTED JOBS_COMPLETED WINDOW_END_TIME---------------------------------------------------------------- ----------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------ ------------ -------------- --------------------------------------------------------------------------------auto optimizer stats collection TUESDAY_WINDOW 14-MAY-19 10.00.00.098044 PM +08:00 +000000000 03:59:59.951603 1 1 1 15-MAY-19 02.00.00.049647 AM +08:00auto optimizer stats collection WednESDAY_WINDOW 15-MAY-19 10.00.00.099538 PM +08:00 +000000000 03:59:59.989331 1 1 1 16-MAY-19 02.00.00.088869 AM +08:00auto optimizer stats collection THURSDAY_WINDOW 16-MAY-19 10.00.00.107495 PM +08:00 +000000000 03:59:59.938235 1 1 1 17-MAY-19 02.00.00.045730 AM +08:00auto optimizer stats collection FRIDAY_WINDOW 17-MAY-19 10.00.00.084708 PM +08:00 +000000000 03:59:59.985142 1 1 1 18-MAY-19 02.00.00.069850 AM +08:00auto optimizer stats collection SATURDAY_WINDOW 18-MAY-19 06.00.01.090394 AM +08:00 +000000000 19:59:58.980792 5 5 5 19-MAY-19 02.00.00.071186 AM +08:00auto optimizer stats collection SUNDAY_WINDOW 19-MAY-19 06.00.01.075840 AM +08:00 +000000000 19:59:58.978655 5 5 5 20-MAY-19 02.00.00.054495 AM +08:00auto optimizer stats collection MONDAY_WINDOW 20-MAY-19 10.00.00.109763 PM +08:00 +000000000 03:59:59.956340 1 1 1 21-MAY-19 02.00.00.066103 AM +08:00auto optimizer stats collection TUESDAY_WINDOW 21-MAY-19 10.00.00.081639 PM +08:00 +000000000 03:59:59.965244 1 1 1 22-MAY-19 02.00.00.046883 AM +08:00auto optimizer stats collection WednESDAY_WINDOW 22-MAY-19 10.00.01.060724 PM +08:00 +000000000 03:59:59.041174 1 1 1 23-MAY-19 02.00.00.101898 AM +08:00auto optimizer stats collection THURSDAY_WINDOW 23-MAY-19 10.00.00.092859 PM +08:00 +000000000 03:59:59.978915 1 1 1 24-MAY-19 02.00.00.071774 AM +08:0030 rows selected统计信息收集调度时间禁用及修改
1、启用自动收集统计信息
BEGIN DBMS_auto_TASK_admin.ENABLE(clIEnt_name => ‘auto optimizer stats collection‘,operation => NulL,window_name => NulL);END;
2、禁用自动收集统计信息
BEGIN DBMS_auto_TASK_admin.disABLE(clIEnt_name => ‘auto optimizer stats collection‘,window_name => NulL);END;
或者：
--禁用自动收集exec DBMS_auto_TASK_admin.disABLE(clIEnt_name => ‘auto optimizer stats collection‘,operation => NulL,window_name => NulL);--启用自动收集exec DBMS_auto_TASK_admin.ENABLE(clIEnt_name => ‘auto optimizer stats collection‘,window_name => NulL);--查看jobsql> select clIEnt_name,status from dba_autotask_clIEnt;CLIENT_name STATUS---------------------------------------------------------------- --------auto optimizer stats collection ENABLEDauto space advisor ENABLEDsql tuning advisor ENABLEDsql>
3、--执行上面的代码后，验证是否被禁用
CLIENT_name STATUS---------------------------------------------------------------- --------auto optimizer stats collection ENABLED
关闭单个调度时间窗口
BEGIN DBMS_auto_TASK_admin.disable(clIEnt_name => ‘auto optimizer stats collection‘,window_name => ‘MONDAY_WINDOW‘);END;/
--验证关闭情况，如下，optimizer_stats列为Disabled
SELECT window_name,window_next_time,window_active,optimizer_stats FROM dba_autotask_window_clIEnts WHERE window_name = ‘MONDAY_WINDOW‘ ORDER BY window_next_time;
WINDOW_name WINDOW_NEXT_TIME WINDO OPTIMIZE------------------------------ --------------------------------------------------------------------------- ----- --------MONDAY_WINDOW 27-MAY-19 10.00.00.000000 PM PRC FALSE ENABLED
关闭所有时间调度窗口，验证略
BEGIN DBMS_auto_TASK_admin.disable ( clIEnt_name => ‘auto optimizer stats collection‘,operation => NulL,window_name => NulL); END; /
开启单个调度时间窗口及所有时间调度窗口，只需要使用enable过程
-- 注：单个应指定窗口名字，如window_name => ‘MONDAY_WINDOW‘ BEGIN DBMS_auto_TASK_admin.enable ( clIEnt_name => ‘auto optimizer stats collection‘,window_name => NulL); END; /
--修改时间窗口到特定的时间--如下示例，将周五时间窗口时间到晚间23点30分BEGIN DBMS_SCHEDulER.disABLE(name => ‘"SYS"."FRIDAY_WINDOW"‘,force => TRUE);END;BEGIN DBMS_SCHEDulER.SET_ATTRIBUTE(name => ‘"SYS"."FRIDAY_WINDOW"‘,attribute => ‘REPEAT_INTERVAL‘,VALUE => ‘FREQ=WEEKLY;BYDAY=FRI;BYHOUR=23;BYMINUTE=30;BYSECOND=0‘);END;BEGIN DBMS_SCHEDulER.ENABLE(name => ‘"SYS"."FRIDAY_WINDOW"‘);END;
--验证修改SELECT w.window_name,dba_scheduler_windows w WHERE c.window_name = w.window_name AND c.optimizer_stats = ‘ENABLED‘ AND c.window_name = ‘FRIDAY_WINDOW‘;
WINDOW_name REPEAT_INTERVAL DURATION ENABLED------------------------------ -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- -------FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
--新增维护时间窗口-- 假定我们要处理的是修改周一的时间窗口-- 首先关闭周一的时间窗口BEGIN DBMS_auto_TASK_admin.disable(clIEnt_name => ‘auto optimizer stats collection‘,window_name => ‘MONDAY_WINDOW‘);END;/
--接下来创建一个窗口并设定时间调度间隔--如下，每周一5点执行，持续时间为1小时BEGIN DBMS_SCHEDulER.create_window(window_name => ‘STATS_WINDOW‘,resource_plan => ‘DEFAulT_MAINTENANCE_PLAN‘,repeat_interval => ‘freq=daily;byday=MON;byhour=5;byminute=0; bysecond=0‘,duration => INTERVAL ‘1‘ HOUR,comments => ‘Test window for stats task‘);EXCEPTION WHEN OTHERS THEN IF sqlCODE = -27477 THEN NulL; ELSE RAISE; END IF;END;BEGIN DBMS_SCHEDulER.set_attribute(‘STATS_WINDOW‘,‘SYstem‘,TRUE); DBMS_SCHEDulER.set_attribute(‘STATS_WINDOW‘,‘FolLOW_DEFAulT_TIMEZONE‘,TRUE);EXCEPTION WHEN OTHERS THEN IF sqlCODE = -27477 THEN NulL; ELSE RAISE; END IF;END;/
SELECT window_name,repeat_interval,enabled FROM dba_scheduler_windows WHERE window_name = ‘STATS_WINDOW‘;
--接下来将窗口STATS_WINDOW添加到维护窗口组
BEGIN DBMS_SCHEDulER.add_window_group_member(‘MAINTENANCE_WINDOW_GROUP‘,‘STATS_WINDOW‘);END;/
SELECT WINDOW_name,WINDOW_NEXT_TIME,WINDOW_ACTIVE,OPTIMIZER_STATS FROM DBA_autoTASK_WINDOW_CLIENTS WHERE WINDOW_name in (‘STATS_WINDOW‘,‘MONDAY_WINDOW‘) ORDER BY WINDOW_NEXT_TIME;手工执行统计信息的自动收集
--执行下面的这个存储过程EXEC DBMS_auto_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;
--如下查询，scott表上的统计信息已更新select table_name,NUM_ROWS,BLOCKS,LAST_ANALYZED from dba_tables where table_name=‘TB_OBJs‘;总结
以上是内存溢出为你收集整理的Oracle 11G统计信息自动收集及调整全部内容，希望文章能够帮你解决Oracle 11G统计信息自动收集及调整所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）