[20180403]访问dba_autotask_task无输出问题.txt

--//链接http://www.itpub.net/thread-1911421-1-1.html的讨论,还没注意原先的帖子是2015年.

--//如果查询select task_name,client_name,status,CURRENT_JOB_NAME from dba_autotask_task ;没有输出,

--//一定是基表SYS.KET$_CLIENT_TASKS 没有记录.

--//通过测试发现一些以前没有注意的问题.

1.环境:

SYS@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> select count(*) from SYS.KET$_CLIENT_TASKS ;

  COUNT(*)

----------

         3

SYS@book> select task_name,client_name,status,CURRENT_JOB_NAME from dba_autotask_task ;

TASK_NAME               CLIENT_NAME                     STATUS   CURRENT_JOB_NAME

----------------------- ------------------------------- -------- -----------------

AUTO_SQL_TUNING_PROG    sql tuning advisor              ENABLED

auto_space_advisor_prog auto space advisor              ENABLED

gather_stats_prog       auto optimizer stats collection ENABLED

2.测试删除基表KET$_CLIENT_TASKS内容.

--//先做一个备份.千万不要在生产系统做这样的测试:

SYS@book> create table tt as select * from  KET$_CLIENT_TASKS;

Table created.

SYS@book> delete from KET$_CLIENT_TASKS;

3 rows deleted.

SYS@book> commit ;

Commit complete.

SYS@book> select task_name,client_name,status,CURRENT_JOB_NAME from dba_autotask_task;

no rows selected

--//可以发现现在没有输出了.

SYS@book> insert into SYS.KET$_CLIENT_TASKS select * from tt;

3 rows created.

SYS@book> commit ;

Commit complete.

SYS@book> select task_name,client_name,status,CURRENT_JOB_NAME from dba_autotask_task;

TASK_NAME               CLIENT_NAME                     STATUS   CURRENT_JOB_NAME

----------------------- ------------------------------- -------- ----------------

AUTO_SQL_TUNING_PROG    sql tuning advisor              ENABLED

auto_space_advisor_prog auto space advisor              ENABLED

gather_stats_prog       auto optimizer stats collection ENABLED

3.难道是人为删除吗?为了重复测试,我使用原来的冷备份覆盖了数据库.

--//发现重启数据库后发现:

SYS@book> select count(*) from SYS.KET$_CLIENT_TASKS ;

  COUNT(*)

----------

         0

--//再现了作者的问题.我本来想通过logminer发现问题,因为没有打开附加日志居然看不到对SYS.KET$_CLIENT_TASKS的删除操作.

--//不过经过一个"漫长"的分析,我发现设置参数job_queue_processes=0,使用冷备份再次启动,SYS.KET$_CLIENT_TASKS的信息不会删除.

--//也就是某个job或者scheduler,删除了SYS.KET$_CLIENT_TASKS的信息.

--//我看了数据库,发现job_name='ORA$AUTOTASK_CLEAN'可能性最大,其执行脚本如下;

BEGIN

  SYS.DBMS_SCHEDULER.CREATE_JOB

    (

       job_name        => 'SYS.ORA$AUTOTASK_CLEAN'

      ,schedule_name   => 'SYS.DAILY_PURGE_SCHEDULE'

      ,program_name    => 'SYS.ORA$AGE_AUTOTASK_DATA'

      ,comments        => 'Delete obsolete AUTOTASK repository data'

    );

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'SYS.ORA$AUTOTASK_CLEAN'

     ,attribute => 'RESTARTABLE'

     ,value     => FALSE);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'SYS.ORA$AUTOTASK_CLEAN'

     ,attribute => 'LOGGING_LEVEL'

     ,value     => SYS.DBMS_SCHEDULER.LOGGING_OFF);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

    ( name      => 'SYS.ORA$AUTOTASK_CLEAN'

     ,attribute => 'MAX_FAILURES');

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

    ( name      => 'SYS.ORA$AUTOTASK_CLEAN'

     ,attribute => 'MAX_RUNS');

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'SYS.ORA$AUTOTASK_CLEAN'

     ,attribute => 'STOP_ON_WINDOW_CLOSE'

     ,value     => FALSE);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'SYS.ORA$AUTOTASK_CLEAN'

     ,attribute => 'JOB_PRIORITY'

     ,value     => 3);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

    ( name      => 'SYS.ORA$AUTOTASK_CLEAN'

     ,attribute => 'SCHEDULE_LIMIT');

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'SYS.ORA$AUTOTASK_CLEAN'

     ,attribute => 'AUTO_DROP'

     ,value     => FALSE);

  SYS.DBMS_SCHEDULER.ENABLE

    (name                  => 'SYS.ORA$AUTOTASK_CLEAN');

END;

/

--//可以确定调用程序是program_name    => 'SYS.ORA$AGE_AUTOTASK_DATA'.看看SYS.ORA$AGE_AUTOTASK_DATA执行内容:

BEGIN

  SYS.DBMS_SCHEDULER.CREATE_PROGRAM

    (

      program_name         => 'SYS.ORA$AGE_AUTOTASK_DATA'

     ,program_type         => 'STORED_PROCEDURE'

     ,program_action       => 'dbms_autotask_prvt.age'

     ,number_of_arguments  => 0

     ,enabled              => FALSE

     ,comments             => 'deletes obsolete AUTOTASK repository data'

    );

  SYS.DBMS_SCHEDULER.ENABLE

    (name                  => 'SYS.ORA$AGE_AUTOTASK_DATA');

END;

/

--//也就是手工执行dbms_autotask_prvt.age会清除SYS.KET$_CLIENT_TASKS ;看看是否正确.

@ &r/10046on 12

exec dbms_autotask_prvt.age;

@ &r/10046off

--//检查跟踪文件可以发现:

=====================

PARSING IN CURSOR #140216792384000 len=86 dep=1 uid=0 oct=7 lid=0 tim=1522658288077950 hv=4100613328 ad='7bedf198' sqlid='df4jnq7u6nt6h'

DELETE FROM KET$_CLIENT_TASKS WHERE CURR_WIN_START < (SYSTIMESTAMP- INTERVAL '31' DAY)

END OF STMT

PARSE #140216792384000:c=25996,e=26168,p=0,cr=206,cu=0,mis=1,r=0,dep=1,og=1,plh=690981981,tim=1522658288077949

EXEC #140216792384000:c=0,e=109,p=0,cr=6,cu=0,mis=0,r=0,dep=1,og=1,plh=690981981,tim=1522658288078164

STAT #140216792384000 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  KET$_CLIENT_TASKS (cr=6 pr=0 pw=0 time=55 us)'

STAT #140216792384000 id=2 cnt=0 pid=1 pos=1 obj=6275 op='TABLE ACCESS FULL KET$_CLIENT_TASKS (cr=6 pr=0 pw=0 time=49 us cost=3 size=35 card=1)'

CLOSE #140216792384000:c=0,e=2,dep=1,type=3,tim=1522658288078288

=====================

--//可以发现启动调用job_name='ORA$AUTOTASK_CLEAN'.但是删除有条件的WHERE CURR_WIN_START < (SYSTIMESTAMP- INTERVAL '31' DAY).

--//这样也验证我使用冷备份启动后为什么KET$_CLIENT_TASKS没有信息,信息已经删除了.

4.继续分析:

BEGIN

  DBMS_AUTO_TASK_ADMIN.disable(

    client_name => 'auto space advisor',

    operation   => NULL,

    window_name => NULL);

END;

/

SYS@book> select task_name,client_name,status,CURRENT_JOB_NAME from dba_autotask_task;

no rows selected

--//居然还是没有信息插入基表KET$_CLIENT_TASKS .我仔细跟踪DBMS_AUTO_TASK_ADMIN.disable,发现实际修改的基表是:

SYS@book> select CLIENT_ID, OPERATION_ID, STATUS ,ATTRIBUTES, PRIORITY_OVERRIDE from KET$_CLIENT_config;

 CLIENT_ID OPERATION_ID     STATUS ATTRIBUTES PRIORITY_OVERRIDE

---------- ------------ ---------- ---------- -----------------

         0            0          2          0                 0

         4            0          2          5                 0

         5            0          1          5                 0

         6            0          2          5                 0

         1            0          2          1                 0

         2            0          2         20                 0

         3            0          2         34                 0

7 rows selected.

--//CLIENT_ID=5,STATUS=1表示disable.这时我才发现我查询错误的视图,实际上是查询:

SYS@book> column ATTRIBUTES format a56

SYS@book> select client_name, status,attributes,window_group from dba_autotask_client;

CLIENT_NAME                     STATUS   ATTRIBUTES                                               WINDOW_GROUP

------------------------------- -------- -------------------------------------------------------- ---------------

auto optimizer stats collection ENABLED  ON BY DEFAULT, VOLATILE, SAFE TO KILL                    ORA$AT_WGRP_OS

auto space advisor              DISABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL                    ORA$AT_WGRP_SA

sql tuning advisor              ENABLED  ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL   ORA$AT_WGRP_SQ

--//oracle在2个视图DBA_AUTOTASK_TASK,DBA_AUTOTASK_CLIENT上非常容易混淆.

--//最后，贴一下oracle官网对这2个表的解释：

DBA_AUTOTASK_TASK：displays information about current and past automated maintenance tasks.

DBA_AUTOTASK_CLIENT：displays statistical data for each automated maintenance task over 7-day and 30-day periods.

--//至于dba_autotask_task等晚上10点scheduler运行就会有记录了.

--//第二天:

SYS@book> select count(*) from SYS.KET$_CLIENT_TASKS ;

  COUNT(*)

----------

         3

SYS@book> select task_name,client_name,status,CURRENT_JOB_NAME,LAST_GOOD_DATE from dba_autotask_task ;

TASK_NAME               CLIENT_NAME                     STATUS   CURRENT_JOB_NAME LAST_GOOD_DATE

----------------------- ------------------------------- -------- ---------------- ---------------------------------

AUTO_SQL_TUNING_PROG    sql tuning advisor              ENABLED                   2018-04-02 22:00:05.625135 +08:00

auto_space_advisor_prog auto space advisor              ENABLED                   2018-04-02 22:00:06.527905 +08:00

gather_stats_prog       auto optimizer stats collection ENABLED                   2018-04-02 22:00:17.128253 +08:00

--//总之这2个视图DBA_AUTOTASK_TASK,DBA_AUTOTASK_CLIENT非常容易混淆.