auto task for 11g

最新推荐文章于 2024-04-17 11:02:06 发布
转载 最新推荐文章于 2024-04-17 11:02:06 发布 · 1.1k 阅读 · 0

本文深入探讨了 Oracle 11g 数据库的三个预定义自动维护任务，包括自动优化器统计信息收集、自动段指导和自动 SQL 优化指导。介绍了任务的执行方式、状态管理、维护窗口的差异以及与资源管理器的整合。同时提供了如何启用、禁用任务以及调整窗口时间的实用指南。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >



Oracle 11g 数据库有三个预定义自动维护任务：

Automatic Optimizer Statistics Collection（自动优化器统计信息收集）

收集数据库中所有无统计信息或仅有过时统计信息的 Schema 对象的 Optimizer（优化器）统计信息。SQL query optimizer（SQL 查询优化器）使用此任务收集的统计信息提高 SQL 执行的性能。

Automatic Segment Advisor（自动段指导）

识别有可用回收空间的段，并提出如何消除这些段中的碎片的建议。您也可以手动运行 Segment Advisor 获取更多最新建议，或获取 Automatic Segment Advisor 没有检查到的那些有可能做空间回收的段的建议。

Automatic SQL Tuning Advisor（自动 SQL 优化指导）

检查高负载 SQL 语句的性能，并提出如何优化这些语句的建议。您可以配置此指导，自动应用建议的SQL profile。
Note 466920.1  - 11g New Feature: Health Monitor


Note 755838.1 - New 11g Default Jobs

实施


它们在 Oracle10g 中为独立作业，且在 DBA_SCHEDULER_JOBS.JOB_NAME 中可见。

这在 Oracle11g 中有所更改。相关视图为 DBA_AUTOTASK_WINDOW_CLIENTS。
现在，这些作业只有当被真正执行时，才在 DBA_SCHEDULER_JOBS 中可见，且名称为系统生成的名称。

SQL> desc DBA_AUTOTASK_WINDOW_CLIENTS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 WINDOW_NAME                               NOT NULL VARCHAR2(30)
 WINDOW_NEXT_TIME                                   TIMESTAMP(6) WITH TIME ZONE
 WINDOW_ACTIVE                                      VARCHAR2(5)
 AUTOTASK_STATUS                                    VARCHAR2(8)
 OPTIMIZER_STATS                                    VARCHAR2(8)
 SEGMENT_ADVISOR                                    VARCHAR2(8)
 SQL_TUNE_ADVISOR                                   VARCHAR2(8)
 HEALTH_MONITOR                                     VARCHAR2(8)

 select * from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME
------------------------------
WINDOW_NEXT_TIME
---------------------------------------------------------------------------
WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
----- -------- -------- -------- -------- --------
MONDAY_WINDOW
08-DEC-08 10.00.00.000000 PM EUROPE/VIENNA
FALSE ENABLED ENABLED ENABLED ENABLED DISABLED

...

SUNDAY_WINDOW
07-DEC-08 06.00.00.000000 AM EUROPE/VIENNA
FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
7 rows selected.


要启用或禁用所有窗口的所有自动维护任务，在不使用任何参数的情况下调用 ENABLE 或 DISABLE 程序。

SQL> execute DBMS_AUTO_TASK_ADMIN.DISABLE;


要禁用特定维护任务，使用 DISABLE 程序，如下所示：

SQL> BEGIN
       dbms_auto_task_admin.disable(
       client_name => 'sql tuning advisor',
       operation => NULL,
       window_name => NULL);
     END;  
    /


要再次启用该特定维护任务，使用 ENABLE 程序，如下所示：

SQL> BEGIN
       dbms_auto_task_admin.enable(
       client_name => 'sql tuning advisor',
       operation => NULL,
       window_name => NULL);
     END;
     /



client_name 参数使用的任务名称列在 DBA_AUTOTASK_CLIENT 数据库字典视图中。

示例：
auto optimizer stats collection
auto space advisor
sql tuning advisor



另一个差异是预定义调度程序窗口：

  •   Oracle10g : WEEKNIGHT_WINDOW and WEEKEND_WINDOW
  •   Oracle11g : MONDAY_WINDOW .... SUNDAY_WINDOW. 

为了向下兼容，WEEKNIGHT_WINDOW 和 WEEKEND_WINDOW 仍然存在。

窗口打开持续的时间在 11g 中有所更改。星期一到星期五从晚上 10 点到凌晨 2 点，星期六到星期日从上午 6 点到凌晨 2 点。

您可以使用 DBMS_SCHEDULER.SET_ATTRIBUTE 程序调整预定义维护窗口时间，使其适用于您的数据库环境。
示例：以下脚本将 WEEKNIGHT_WINDOW 改为每个工作日从午夜至上午 8 点（窗口持续时间为 8 小时不变）：
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(
'WEEKNIGHT_WINDOW', 
'repeat_interval',
'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0');

每个窗口还有一个名为 DEFAULT_MAINTENANCE_PLAN 的预定义资源计划，一旦打开相关窗口，该计划将激活。这是 10g 和 11g 之间的另一个差异。
SQL> select window_name, resource_plan from dba_scheduler_windows;

WINDOW_NAME                    RESOURCE_PLAN
------------------------------ ------------------------------
MONDAY_WINDOW                  DEFAULT_MAINTENANCE_PLAN
TUESDAY_WINDOW                 DEFAULT_MAINTENANCE_PLAN
WEDNESDAY_WINDOW               DEFAULT_MAINTENANCE_PLAN
THURSDAY_WINDOW                DEFAULT_MAINTENANCE_PLAN
FRIDAY_WINDOW                  DEFAULT_MAINTENANCE_PLAN
SATURDAY_WINDOW                DEFAULT_MAINTENANCE_PLAN
SUNDAY_WINDOW                  DEFAULT_MAINTENANCE_PLAN
WEEKNIGHT_WINDOW
WEEKEND_WINDOW

9 rows selected.


SQL> select * from dba_rsrc_plans where plan='DEFAULT_MAINTENANCE_PLAN'
PLAN_ID PLAN NUM_PLAN_DIRECTIVES
---------- ------------------------------ -------------------
CPU_METHOD MGMT_METHOD
------------------------------ ------------------------------
ACTIVE_SESS_POOL_MTH PARALLEL_DEGREE_LIMIT_MTH
------------------------------ ------------------------------
QUEUEING_MTH SUB
------------------------------ ---
COMMENTS
--------------------------------------------------------------------------------
STATUS MAN
------------------------------ ---
11187 DEFAULT_MAINTENANCE_PLAN 4
EMPHASIS EMPHASIS
ACTIVE_SESS_POOL_ABSOLUTE PARALLEL_DEGREE_LIMIT_ABSOLUTE
FIFO_TIMEOUT NO
Default plan for maintenance windows that prioritizes SYS_GROUP operations and a
llocates the remaining 5% to diagnostic operations and 25% to automated maintena
nce operations.
YES


SQL> select * from DBA_RSRC_PLAN_DIRECTIVES where plan='DEFAULT_MAINTENANCE_PLAN';

PLAN GROUP_OR_SUBPLAN TYPE
------------------------------ ------------------------------ --------------
CPU_P1 CPU_P2 CPU_P3 CPU_P4 CPU_P5 CPU_P6 CPU_P7
---------- ---------- ---------- ---------- ---------- ---------- ----------
CPU_P8 MGMT_P1 MGMT_P2 MGMT_P3 MGMT_P4 MGMT_P5 MGMT_P6
---------- ---------- ---------- ---------- ---------- ---------- ----------
MGMT_P7 MGMT_P8 ACTIVE_SESS_POOL_P1 QUEUEING_P1 PARALLEL_DEGREE_LIMIT_P1
---------- ---------- ------------------- ----------- ------------------------
SWITCH_GROUP SWITC SWITCH_TIME SWITCH_IO_MEGABYTES
------------------------------ ----- ----------- -------------------
SWITCH_IO_REQS SWITC MAX_EST_EXEC_TIME UNDO_POOL MAX_IDLE_TIME
-------------- ----- ----------------- ---------- -------------
MAX_IDLE_BLOCKER_TIME SWITCH_TIME_IN_CALL
--------------------- -------------------
COMMENTS
--------------------------------------------------------------------------------
STATUS MAN
------------------------------ ---
DEFAULT_MAINTENANCE_PLAN SYS_GROUP CONSUMER_GROUP
100 0 0 0 0 0 0
0 100 0 0 0 0 0
0 0
FALSE
FALSE

Directive for system operations
NO

DEFAULT_MAINTENANCE_PLAN OTHER_GROUPS CONSUMER_GROUP
0 70 0 0 0 0 0
0 0 70 0 0 0 0
0 0
FALSE
FALSE

Directive for all other operations
NO

DEFAULT_MAINTENANCE_PLAN ORA$AUTOTASK_SUB_PLAN PLAN
0 25 0 0 0 0 0
0 0 25 0 0 0 0
0 0
FALSE
FALSE

Directive for automated maintenance tasks
NO

DEFAULT_MAINTENANCE_PLAN ORA$DIAGNOSTICS CONSUMER_GROUP
0 5 0 0 0 0 0
0 0 5 0 0 0 0
0 0
FALSE
FALSE

Directive for automated diagnostic tasks
NO

10g 与 11g 之间变化的摘要：

主题 10g 11g
作业 在 DBA_SCHEDULER_JOBS 中独立作业 AUTOTASKS 有命名前缀“ORA$AT”，且只有作业执行后才可见
维护窗口 2 个窗口：WEEKNIGHT 和 WEEKEND 每天有不同的窗口
Resource manager 默认不启用 每个窗口都有预定义资源计划
     


相关视图：


DBA_AUTOTASK_CLIENT
DBA_AUTOTASK_CLIENT_HISTORY
DBA_AUTOTASK_CLIENT_JOB
DBA_AUTOTASK_JOB_HISTORY
DBA_AUTOTASK_OPERATION
DBA_AUTOTASK_SCHEDULE
DBA_AUTOTASK_TASK
DBA_AUTOTASK_WINDOW_CLIENTS
DBA_AUTOTASK_WINDOW_HISTORY

References

NOTE:466920.1 - 11g New Feature: Health monitor
NOTE:755838.1 - New 11g Default Jobs

NOTE:858852.1 - DBA_AUTOTASK_TASK and DBA_AUTOTASK_CLIENT Shows Different Status For Auto Optimizer Stats Collection


SELECT * FROM dba_tab_stats_history 查看表统计时间
SELECT window_name, job_name, job_duration
FROM DBA_AUTOTASK_JOB_HISTORY 
WHERE JOB_NAME like 'ORA$AT_OS%';

Login as SYSDBA



@?/rdbms/admin/catnomwn.sql -- this drops the maintenance window, it will give some errors that can be ignored.



-- Drop the windows manually:

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 -- this recreates it


set pages 1000
column WINDOW_NAME format a16
column WINDOW_NEXT_TIME format a38
column WINDOW_ACTIVE format a5
column OPTIMIZER_STATS format a10
select WINDOW_NAME, WINDOW_NEXT_TIME , WINDOW_ACTIVE,OPTIMIZER_STATS from DBA_AUTOTASK_WINDOW_CLIENTS order by WINDOW_NEXT_TIME ;


column next_start_date format a36
column comments format a38
select window_group_name, enabled, number_of_windows, next_start_date, comments from dba_scheduler_window_groups;


SQL> select client_name, status, window_group from DBA_AUTOTASK_CLIENT;

1.  Statistics_level must NOT BE = basic.  Should be =TYPICAL otherwise stats not collected.

sqlplus / as sysdba
show parameter statistics_level

NAME                                               TYPE        VALUE                                                                                                
-------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
statistics_level                                   string      TYPICAL                                                                                              

 

2.  Are the autotasks (3 of them) listed and ENABLED?

set pages 1000
select client_name, status, window_group from dba_autotask_client;

3.  Check the Windows and WINDOW_NEXT_TIME for day returned.

select * from DBA_AUTOTASK_WINDOW_CLIENTS;

4.  This checks history of the optimizer collection.

col job_name format a23
col job_start_time format a40
col job_duration format a14
select job_name, job_start_time, job_duration
from DBA_AUTOTASK_JOB_HISTORY
where client_name like 'auto optimizer stats collection'
order by job_start_time; 

5) Here is command to enable autotask.

 -->  execute DBMS_AUTO_TASK_ADMIN.ENABLE;

Then AUTOTASK_STATUS in DBA_AUTOTASK_WINDOW_CLIENTS view will show ENABLED for all Windows.

 

6) To manually run, just to ensure at least 1 run:

SQL> exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

7) Recheck history using the query in #4.

6) If ever there is a large data load (ie - HR2HR or other import), manually run the job (step 4) when you are satisfied that the data load is finished and successful.

In 11g, the automated maintenance task that collects statistics is called 'auto optimizer stats collection'. You can determine its status by selecting fromDBA_AUTOTASK_CLIENT:

SELECT CLIENT_NAME,
       STATUS
FROM   DBA_AUTOTASK_CLIENT
WHERE  CLIENT_NAME = 'auto optimizer stats collection'
/

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED


The DBA_AUTOTASK_CLIENT view also displays frequency information about current and past automated maintenance tasks in columns MEAN_INCOMING_TASKS_7_DAYS and MEAN_INCOMING_TASKS_30_DAYS so you can see this historical information using a query like:

SELECT CLIENT_NAME               ,
       STATUS                    ,
       MEAN_INCOMING_TASKS_7_DAYS,
       MEAN_INCOMING_TASKS_30_DAYS
FROM   DBA_AUTOTASK_CLIENT
WHERE  CLIENT_NAME = 'auto optimizer stats collection'
/ 
  •  How to enable auto stats collection?

If for some reason automatic optimizer statistics collection is disabled, you can enable it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

exec DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',  
operation => NULL,  
window_name => NULL);

 

  •  How to disable the auto stats collection?

In situations when you want to disable automatic optimizer statistics collection, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

exec DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',  
operation => NULL,  
window_name => NULL);

 


For details of the other columns in DBA_AUTOTASK_CLIENT,see:

Oracle Database Reference
11g Release 2 (11.2)
Part Number E25513-01
http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_3084.htm
NOTE that the status in DBA_AUTOTASK_TASK and DBA_AUTOTASK_CLIENT may be different. See:

Document 858852.1  DBA_AUTOTASK_TASK and DBA_AUTOTASK_CLIENT Shows Different Status For Auto Optimizer Stats Collection

10g

The following is the 'old method' for checking the current and past automated maintenance tasks:

SELECT j.job_name     ,
       j.program_name ,
       j.schedule_name,
       j.job_class    ,
       p.enabled
FROM   dba_scheduler_programs p,
       dba_scheduler_jobs j
WHERE  p.program_name=j.program_name;


JOB_NAME
------------------------------
PROGRAM_NAME
--------------------------------------------------------------------------------
SCHEDULE_NAME
--------------------------------------------------------------------------------
JOB_CLASS                      ENABL
------------------------------ -----
..
GATHER_STATS_JOB
GATHER_STATS_PROG
MAINTENANCE_WINDOW_GROUP
AUTO_TASKS_JOB_CLASS           TRUE

 

  • How do you disable the GATHER_STATS_JOB for 10g?
    The most direct approach is to disable the GATHER_STATS_JOB as follows:

    SQL> exec sys.dbms_scheduler.disable ('GATHER_STATS_JOB'); 
  • How do you enable the GATHER_STATS_JOB for 10g?
    This is enabled by default. If you have disabled it, then you can re-enable it as

    SQL> exec sys.dbms_scheduler.enable (€"SYS"."GATHER_STATS_JOB");
Note: If the 10g method is used on 11g, then it does not return any information as the implementation is quite different in 11g:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning and Real Application Testing options

SQL> SELECT job_name     ,
       job_type     ,
       program_name ,
       schedule_name,
       job_class
FROM   dba_scheduler_jobs
WHERE  job_name = 'GATHER_STATS_JOB';

no rows selected

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
相关视图：
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] 



oracle启用自动维护任务,ORACLE 11G 维护窗口和自动维护任务
weixin_28759829的博客
04-03 995
维护窗口是一个连续的时间间隔，用于管理自动维护任务，所谓自动维护窗口是按照预定义一个时间段，比如晚上22：10~04:00就可以称为一个窗口。自动维护任务是一种按规则自动启动的数据库维护操作任务。自动维护任务在维护窗口打开时自动运行。(就例如闹钟响了，该起床了，时间到了，任务自动执行，)在Oracle Database 11g中，自动维护任务功能依赖于在维护窗口期间启用的资源管理器。因此，窗口打开...
SYS_AUTO_SQL_TUNING_TASK 导致 ORA-07445错误
cuimiwan3140的博客
09-30 1981
1.先贴报错 ----敏感信息已经经过处理 Fri Sep 30 11:01:37 2016 Begin automatic SQL Tuning Advisor r...
Oracle ABP(Autotask Background Process)
dba成长之路
09-12 4527
ABP相当于自动任务与调度程序之间的中介，其主要作用是将自动任务转换成Autotask作业，供调度程序执行。同样重要的是，ABP还维护所有任务执行的历史记录。ABP将其专用资料档案库存储在sysaux表空间中，您可以通过DBA_AUTOTASK_TASK 查案该资料档案库。         ABP是在启动维护窗口时，有MMON 启动的，所有实例只需要一个ABP。MMON进程将监视ABP，并在必要
11g autotask management
clj229982100的博客
09-10 142
11g 中增加了自动任务管理： SQL> select client_name,task_name from dba_autotask_task;（或者dba_autotask_client)CLIENT_NAME ...
oracle查看自动任务,Oracle 自动收集任务-统计信息、SQL Tuning
weixin_29387991的博客
04-03 898
-- 查看自动收集任务状态SELECT CLIENT_NAME,STATUS FROM DBA_AUTOTASK_CLIENT;SELECT CLIENT_NAME, STATUSFROM DBA_AUTOTASK_CLIENTWHERE CLIENT_NAME = 'auto optimizer stats collection';-- 关闭信息自动收集auto optimizer stats ...
【Oracle Database 12c新特性】SYS_AUTO_SPM_EVOLVE_TASK 自动作业
weixin_34406061的博客
08-03 259
Oracle Database 12c中引入了一个新的自动系统作业，即SYS_AUTO_SPM_EVOLVE_TASK。 该作业将在每天的自动维护作业窗口中自动执行。 SYS_AUTO_SPM_EVOLVE_TASK负责检索和排序SPM中未被接受的执行计划non-accepted plan以便verification。 当此执行计划经过verified，过该计划满足性能阀值则...
[yzhpdh]Adaptive Hypergraph Auto-Encoder for Relational Data Clustering
ye6pipipihou的博客
01-06 775
超图信息编码器
【解决】kotlin写Android项目第一次编译报Execution failed for task ‘:app:kaptDebugKotlin‘.异常
苹果是我咬的的博客
10-22 1万+
【解决】kotlin第一次编译Android项目报Execution failed for task ':app:kaptDebugKotlin'.异常 Execution failed for task ':app:kaptDebugKotlin'. > A failure occurred while executing org.jetbrains.kotlin.gradle.internal.KaptWithoutKotlincTask$KaptExecutionWorkAction &g
Oracle 11g 自动任务
热门推荐
dataminer 的博客
11-21 1万+
Automated maintenance tasks are tasks that are started automatically at regular intervals to perform maintenance operations on the database. An example is a task that gathers statistics on schema obje
Oracle 11G统计信息自动收集及调整
ZSY老司机的博客
06-23 416
查询统计信息的收集所对应的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;
oracle数据库优化-11G自动维护任务autotask
oradbm的博客
06-26 4643
1. ORACLE 11G自动维护任务 相关查询： select window_name, a1.RESOURCE_PLAN, a1.start_date, a1.end_date, a1.duration, a1.ENABLED From dba_scheduler_windows a1; sele...
oracle自动任务、窗口
weixin_39735909的博客
04-17 227
【代码】oracle自动任务、窗口。
Oracle数据库自动维护任务(Automated Maintenance Tasks)
bing_yuan的博客
02-14 1892
此外，可以使用DBMS_SCHEDULER PL/SQL包自定义维护窗口，比如希望将预定义的维护窗口调整为适合您的数据库环境的时间，或者创建一个新的维护窗口。Scheduler窗口可以是一个简单的重复间隔(例如“在每个星期六的午夜到早上6点之间”)，也可以是一个更复杂的间隔(例如“在每个月的最后一个工作日的午夜到早上6点之间，不包括公司假期”)。DBA_AUTOTASK_WINDOW_CLIENTS 列出属于MAINTENANCE_WINDOW_GROUP的窗口，以及每个维护任务的窗口的启用或禁用状态。
oracle 11g 自动任务,Oracle 11g R1中的自动数据库维护任务管理（三）
weixin_39595164的博客
04-08 168
完整介绍资源管理器已经超出了本文的范围，我在文末同样列出了参考链接。有关的视图下面的视图显示有关自动数据库维护任务的信息：DBA_AUTOTASK_CLIENT_JOBDBA_AUTOTASK_CLIENTDBA_AUTOTASK_JOB_HISTORYDBA_AUTOTASK_WINDOW_CLIENTSDBA_AUTOTASK_CLIENT_HISTORY此外，你可能还应该...
oracle 11g 使用 DBMS_AUTO_TASK_ADMIN.DISABLE 禁止自动运行
一名数据库爱好者的专栏
10-26 2270
查看默认情况下自动运行的三个 job SQL> set lines 200; set pages 200; col client_name format a40; col status format a20; SQL> SQL> select client_name,status from dba_autotask_client; CLIENT_NAME ...
oracle自动收集统计信息
汪灵骅的博客
01-18 2232
BEGINEND;BEGINEND;/先停止（下掉整行）BEGINend;BEGINvalue =>byday=MON;byhour=2;byminute=0;end;BEGINEND;BEGINend;BEGINEND;BEGINEND;
自动维护作业 AUTO TASK 管理
07-24 1103
1.监视： 视图DBA_AUTOTASK_JOB_HISTORY来跟踪自动作业情况。 可以通过查询视图DBA_AUTOTASK_HISTORY追踪job运行情况： set line 16...
oracle的优化(3)——自动收集统计信息Auto Maintenance Tasks和动态采样optimizer_dynamic_sampling...
weixin_33912453的博客
01-05 236
在oracle中10g开始，由于CBO被广泛推广，而oracle的CBO又是基于对象的统计信息的，所以自动收集对象统计信息Automatic Optimizer Statistics Collection这一特性被引入。那我们就来研究一下这一特性。 在oracle10g的时候自动收集对象统计信息的是GATHER_STATS_JOB任务，如果用dbca建库的话，那默认就会跑这...
Oracle中的自动维护任务
wang_san_shi的专栏
04-30 3338
Oracle中的自动维护任务 Oracle中自动维护任务对应的3条SQL 如下： （1）call dbms_space.auto_space_advisor_job_proc( ) （2）calldbms_stats.gather_database_stats_job_proc ( ) （3）insert into wri$_adv_objspace_trend_dataselec
class TaskQueueManager : public QObject//负责管理队列的出队进队，管理插入、管理米数、管理换卷的时候需要做的额外的判断 { public: TaskQueueManager(QObject* parent = nullptr); ~TaskQueueManager(); void InitTaskQueueManager(); void EnqueueInsert(const DefectInfoItem& data); void EnqueueUpdateReel(const QSharedPointer<TaskStruct::UpDataByReelTask> task); void EnqueueUpdateMeter(const QSharedPointer<TaskStruct::UpDataByMeterTask> task); QSharedPointer<TaskStruct::DatabaseTaskBase> DequeueTask(); void StartTimers(); void StopTimers(); private slots: void OnInsertTimerTimeout(); void OnMeterTimerTimeout(); private: void FlushInsertQueue(); QString GeneratorTableName(DefectInfoItem& data); private: QQueue<QSharedPointer<TaskStruct::DatabaseTaskBase>> reelQueue; QMutex reelQueueMutex; QQueue<QSharedPointer<TaskStruct::DatabaseTaskBase>> otherQueue; QMutex otherQueueMutex; QQueue<DefectInfoItem>insertQueue;//插入队列 QMutex insertQueueMutex;//插入队列锁 //米数更新用的成员变量 CurrentRunData currentMeterData; QMutex meterDataMutex; bool meterDataDirty = false; QTimer* insertTimer;//插入定时器 QTimer* meterTimer;//米数更新定时器(米数不用接收到就马上执行，而是要等上50毫秒再执行) protected: Q_OBJECT }; TaskQueueManager::TaskQueueManager(QObject* parent) : QObject(parent) { } TaskQueueManager::~TaskQueueManager() { this->StopTimers(); { QMutexLocker locker(&this->insertQueueMutex); if (!this->insertQueue.isEmpty()) { FlushInsertQueue(); } } delete this->insertTimer; delete this->meterTimer; } void TaskQueueManager::InitTaskQueueManager() { qDebug() << QStringLiteral("TaskQueueManager线程:") << QThread::currentThreadId(); this->insertTimer = new QTimer(); this->insertTimer->setInterval(500); this->meterTimer = new QTimer(); this->meterTimer->setInterval(50); connect(this->insertTimer, &QTimer::timeout, this, &TaskQueueManager::OnInsertTimerTimeout); connect(this->meterTimer, &QTimer::timeout, this, &TaskQueueManager::OnMeterTimerTimeout); } void TaskQueueManager::EnqueueInsert(const DefectInfoItem& data) { qDebug() << QStringLiteral("EnqueueInsert线程:") << QThread::currentThreadId(); QMutexLocker locker(&this->insertQueueMutex); this->insertQueue.enqueue(data); qDebug() << this->insertQueue.size(); } void TaskQueueManager::EnqueueUpdateReel(const QSharedPointer<TaskStruct::UpDataByReelTask> task) { QMutexLocker locker(&this->reelQueueMutex); this->reelQueue.enqueue(task); } void TaskQueueManager::EnqueueUpdateMeter(const QSharedPointer<TaskStruct::UpDataByMeterTask> task) { QMutexLocker locker(&this->meterDataMutex); this->currentMeterData = task->currentRunData; this->meterDataDirty = true; } QSharedPointer<TaskStruct::DatabaseTaskBase> TaskQueueManager::DequeueTask() { qDebug() << "reelQueue" << this->reelQueue.size(); qDebug() << "otherQueue" << this->otherQueue.size(); qDebug() << QStringLiteral("DequeueTask线程:") << QThread::currentThreadId(); { QMutexLocker locker(&this->reelQueueMutex); if (!this->reelQueue.isEmpty()) { return this->reelQueue.dequeue(); } } { QMutexLocker locker(&this->otherQueueMutex); if (!this->otherQueue.isEmpty()) { return this->otherQueue.dequeue(); } } return nullptr; } void TaskQueueManager::StartTimers() { qDebug() << QStringLiteral("StartTimers线程:") << QThread::currentThreadId(); this->meterTimer->start(); this->insertTimer->start(); } void TaskQueueManager::StopTimers() { qDebug() << QStringLiteral("StopTimers线程:") << QThread::currentThreadId(); if (this->meterTimer && this->meterTimer->isActive()) { this->meterTimer->stop(); } if (this->insertTimer && this->insertTimer->isActive()) // 修复重复判断 { this->insertTimer->stop(); } } void TaskQueueManager::OnInsertTimerTimeout() { qDebug() << QStringLiteral("OnInsertTimerTimeout线程:") << QThread::currentThreadId(); QMutexLocker locker(&this->insertQueueMutex); if (!insertQueue.isEmpty()) { FlushInsertQueue(); } } void TaskQueueManager::FlushInsertQueue() { qDebug() << QStringLiteral("FlushInsertQueue线程:") << QThread::currentThreadId(); if (this->insertQueue.isEmpty()) { return; } QHash<QString, QQueue<DefectInfoItem>> l_groupedTasks; while (!this->insertQueue.isEmpty()) { auto l_defectInfoItem = this->insertQueue.dequeue(); QString l_tableName = this->GeneratorTableName(l_defectInfoItem); l_groupedTasks[l_tableName].enqueue(l_defectInfoItem); } for (auto it = l_groupedTasks.begin(); it != l_groupedTasks.end(); ++it) { auto l_batchTask = QSharedPointer<TaskStruct::BatchInsertTask>::create(); l_batchTask->defectItems = it.value(); l_batchTask->tableName = it.key(); { QMutexLocker locker(&this->otherQueueMutex); this->otherQueue.enqueue(l_batchTask); } } } QString TaskQueueManager::GeneratorTableName(DefectInfoItem& data) { QString l_timePart = data.startTime; QString l_tableName = "test_defect_table";//QString("reel_%1").arg(l_timePart.replace("-", "").replace(":", "").replace(" ", "").replace(".", "")); return l_tableName; } void TaskQueueManager::OnMeterTimerTimeout() { QSharedPointer<TaskStruct::UpDataByMeterTask> task; { QMutexLocker locker(&meterDataMutex); if (!this->meterDataDirty) { return; } task = QSharedPointer<TaskStruct::UpDataByMeterTask>::create(); task->currentRunData = currentMeterData; this->meterDataDirty = false; } QMutexLocker locker(&otherQueueMutex); this->otherQueue.enqueue(task); } class TaskWorker : public QObject { public: explicit TaskWorker(QObject* parent = nullptr); ~TaskWorker(); void InitTaskWorker(); bool DoInsertNgDataList(const QQueue<DefectInfoItem>& dataList, const QString& tableName); bool DoUpdateReel(CurrentRunData& data); bool DoUpdateMeter(CurrentRunData& data); bool IsDatabaseOpen(); bool OpenDataBaseConnect(); bool CloseConnection(); private: bool ExecuteCommand(QSqlQuery& query); QSqlDatabase taskDB; protected: Q_OBJECT }; TaskWorker::TaskWorker(QObject* parent) : QObject(parent) { } TaskWorker::~TaskWorker() { this->CloseConnection(); } void TaskWorker::InitTaskWorker() { qDebug() << QStringLiteral("InitTaskWorker线程:") << QThread::currentThreadId(); if (!OpenDataBaseConnect()) { qDebug() << QStringLiteral("数据库连接失败!"); } } bool TaskWorker::OpenDataBaseConnect() { ConfigManager* l_configInstance = ConfigManager::GetConfigInstance(); ConfigManager::DAOConfig* l_daoConfig = l_configInstance->GetConfig(); QString l_driverName; switch (l_daoConfig->type) { case ConfigManager::DataBaseType::SQLite: l_driverName = l_driverName = "QSQLITE"; break; case ConfigManager::DataBaseType::MySQL: l_driverName = l_driverName = "QMYSQL"; break; case ConfigManager::DataBaseType::PostgreSQL: l_driverName = l_driverName = "QPSQL"; break; case ConfigManager::DataBaseType::Oracle: l_driverName = l_driverName = "QOCI"; break; default: l_driverName = "QMYSQL"; } if (QSqlDatabase::contains("TaskConnection")) { this->taskDB = QSqlDatabase::database("TaskConnection"); if (this->taskDB.isOpen()) { this->taskDB.close(); } } else { this->taskDB = QSqlDatabase::addDatabase(l_driverName, "TaskConnection"); } if (l_daoConfig->type != ConfigManager::DataBaseType::SQLite) { this->taskDB.setHostName(l_daoConfig->host); this->taskDB.setPort(l_daoConfig->port); } taskDB.setDatabaseName(l_daoConfig->dbName); taskDB.setUserName(l_daoConfig->account); taskDB.setPassword(l_daoConfig->password); if (!taskDB.open()) { QSqlError l_error = taskDB.lastError(); //qDebug() << QStringLiteral("驱动错误:") << l_error.driverText(); //qDebug() << QStringLiteral("错误描述:") << l_error.text(); return false; } else { //qDebug() << QStringLiteral("数据库连接创建成功，线程ID:") << QThread::currentThreadId(); //qDebug() << QStringLiteral("子线程 数据库连接成功"); return true; } qDebug() << QStringLiteral("OpenDataBaseConnect线程:") << QThread::currentThreadId(); } class XZJDATABASE_EXPORT TaskProcessor : public QObject { public: TaskProcessor(QObject* parent = nullptr); ~TaskProcessor(); void InitTaskProcessor(); QSharedPointer<TaskQueueManager> GetTaskQueueManager(); public slots: void StartProcessing(); void StopProcessing(); public slots: void ProcessTask(); private: QSharedPointer<TaskQueueManager> queueManager; // 队列管理器 QSharedPointer<TaskWorker> worker; // 数据库操作实例 QMutex waitMutex; // 等待锁 QWaitCondition waitCondition; // 线程等待条件 std::atomic<bool> isRunning{ false }; // 运行状态 protected: Q_OBJECT }; TaskProcessor::TaskProcessor(QObject* parent) : QObject(parent) { } TaskProcessor::~TaskProcessor() { this->StopProcessing(); } void TaskProcessor::InitTaskProcessor() { qDebug() << QStringLiteral("InitTaskProcessor线程:") << QThread::currentThreadId(); this->queueManager = QSharedPointer<TaskQueueManager>::create(this); this->worker = QSharedPointer<TaskWorker>::create(this); this->queueManager->InitTaskQueueManager(); this->worker->InitTaskWorker(); } QSharedPointer<TaskQueueManager> TaskProcessor::GetTaskQueueManager() { return this->queueManager; } void TaskProcessor::StartProcessing() { this->isRunning = true; this->queueManager->StartTimers(); this->ProcessTask(); } void TaskProcessor::StopProcessing() { this->isRunning = false; this->waitCondition.wakeAll(); this->queueManager->StopTimers(); } void TaskProcessor::ProcessTask() { qDebug() << QStringLiteral("ProcessTask线程:") << QThread::currentThreadId(); while (this->isRunning) { qDebug() << QStringLiteral("ProcessTask 循环执行"); auto task = queueManager->DequeueTask(); if (!task) { qDebug() << "null data"; QMutexLocker locker(&waitMutex); waitCondition.wait(&waitMutex, 50); } else { switch (task->type) { case TaskStruct::TaskType::BatchInsert: { qDebug() << QStringLiteral("TaskStruct::TaskType::BatchInsert 批量插入"); auto bacthInsertTask = task.staticCast<TaskStruct::BatchInsertTask>(); bool success = this->worker->DoInsertNgDataList(bacthInsertTask->defectItems, bacthInsertTask->tableName); if (success) { LOG_INFO("Batch insert completed successfully."); qDebug() << "Batch insert completed successfully."; } else { LOG_ERROR("Batch insert failed."); qDebug() << "Batch insert failed."; } break; } case TaskStruct::TaskType::UpdateReel: { auto reelTask = task.staticCast<TaskStruct::UpDataByReelTask>(); bool success = this->worker->DoUpdateReel(reelTask->currentRunData); if (success) { LOG_INFO("Reel update completed."); } else { LOG_ERROR("Reel update failed."); } break; } case TaskStruct::TaskType::UpdateMeter: { auto meterTask = task.staticCast<TaskStruct::UpDataByMeterTask>(); bool success = this->worker->DoUpdateMeter(meterTask->currentRunData); if (success) { LOG_INFO("Meter update completed."); } else { LOG_ERROR("Meter update failed."); } break; } } } } } //可以选择缓存然后等初始化完成后通过OnThread中进行数据的再次添加 class XZJDATABASE_EXPORT TaskAccessService : public QObject { public: explicit TaskAccessService(QObject* parent = nullptr); ~TaskAccessService() override; void InitTaskAccessService(); void InsertNgData(const DefectInfoItem& data); void UpDateByChangeReel(const CurrentRunData& data); void UpdateByChangeMeter(const CurrentRunData& data); private: QSharedPointer<TaskProcessor> processor; QThread* workerThread; int testcount = 0; protected: Q_OBJECT }; TaskAccessService::TaskAccessService(QObject* parent) : QObject(parent) { } TaskAccessService::~TaskAccessService() { if (this->workerThread && this->workerThread->isRunning()) { this->processor->StopProcessing(); this->workerThread->quit(); this->workerThread->wait(1000); this->workerThread->terminate(); } delete this->workerThread; } void TaskAccessService::InitTaskAccessService() { qDebug() << QStringLiteral("InitTaskAccessService线程:") << QThread::currentThreadId(); this->workerThread = new QThread(); this->processor = QSharedPointer<TaskProcessor>::create(); this->processor->moveToThread(this->workerThread); this->processor->InitTaskProcessor(); connect(workerThread, &QThread::started, processor.data(), &TaskProcessor::StartProcessing); this->workerThread->start(); } void TaskAccessService::InsertNgData(const DefectInfoItem& data) { this->testcount++; this->processor->GetTaskQueueManager()->EnqueueInsert(data); qDebug() << this->testcount; } void TaskAccessService::UpDateByChangeReel(const CurrentRunData& data) { auto task = QSharedPointer<TaskStruct::UpDataByReelTask>::create(); task->currentRunData = data; this->processor->GetTaskQueueManager()->EnqueueUpdateReel(task); } void TaskAccessService::UpdateByChangeMeter(const CurrentRunData& data) { auto task = QSharedPointer<TaskStruct::UpDataByMeterTask>::create(); task->currentRunData = data; this->processor->GetTaskQueueManager()->EnqueueUpdateMeter(task); } void XZJDataBaseModel::Init() { QString configPath = PathManager::GetPathInstance()->GetConfigFilePath(); ConfigManager* l_configInstance = ConfigManager::GetConfigInstance(); l_configInstance->LoadJsonConfig(configPath); ConfigManager::DAOConfig* l_daoConfig = l_configInstance->GetConfig(); PrintRollProductionDAO* l_daoInstance = PrintRollProductionDAO::GetDAOInstance(); l_daoInstance->OpenDataBaseConnect(l_daoConfig->type, l_daoConfig->host, l_daoConfig->port, l_daoConfig->dbName, l_daoConfig->account, l_daoConfig->password); this->taskAccessService = QSharedPointer<TaskAccessService>::create(); this->taskAccessService->InitTaskAccessService(); //this->TestQuery(); this->TestInsert(); } 这些是我优化后的代码，请帮我分析下是否还有问题，以及insert流程能否走通，以及关于Processor中的ProcessTask函数是否有其他方法实现在不用定时器的情况下，以及Service中需要考虑子线程和主线程之间的顺序关系，比如子线程还未初始化，主线程就通过service进行调用接口，并且你不要通过一些比如在未初始化完全前就禁止调用接口的方式来解决这个问题。我的主线程目前就一个创建service对象、初始化service、然后开始调用对象中的函数，其他的你看下分别有哪些问题以及有哪些方法解决？
最新发布
07-24
for (auto& task : batch) Process(task); batch.clear(); } } ``` **优化效果**： 1. **零延迟响应**：条件变量即时唤醒 2. **吞吐量提升**：批量处理减少锁竞争 3. **资源高效**：CPU利用率$ \eta \approx 100\...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值