0
0

11g中autotask disable后应该查哪个表来验证?

小荷 发表于 2012年04月11日 19:27 | Hits: 41243
Tag: Working case | ..experience

在oracle11g中,有个autotask的新特性,可以用来自动的收集优化信息,收集段信息等等。在EM中,我们看进菜单server-Oracle Scheduler-Automated Maintenance Tasks来看到他们:

问题的起源是在一个纯命令行的环境,收到一个change,需要disable autotask中的auto optimizer stats collection,于是按照原定的计划,先检查原来的状态,再执行package对其进行disable,最后再坚持一次是否已经更改成功:

SQL>selectCLIENT_NAME,TASK_NAME,STATUSfromDBA_AUTOTASK_TASK;
CLIENT_NAMETASK_NAMESTATUS
----------------------------------- ---------------------------------------------------------------- --------
sqltuningadvisorAUTO_SQL_TUNING_PROGENABLED
autooptimizerstatscollectiongather_stats_progENABLED
autospaceadvisorauto_space_advisor_progENABLED
 
SQL>BEGIN
2DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name=>'auto optimizer stats collection',
operation=>NULL,
window_name=>NULL);
END;
/34567
 
PL/SQLproceduresuccessfullycompleted.
 
SQL>showerror
Noerrors.
 
 
SQL>selectCLIENT_NAME,TASK_NAME,STATUSfromDBA_AUTOTASK_TASK;
 
CLIENT_NAMETASK_NAMESTATUS
----------------------------------- ---------------------------------------------------------------- --------
sqltuningadvisorAUTO_SQL_TUNING_PROGENABLED
autooptimizerstatscollectiongather_stats_progENABLED
autospaceadvisorauto_space_advisor_progENABLED
 
SQL>

很奇怪吧,status竟然没改变。

OK,我们一步一步来查。

我们做个10046的trace,发现在操作过程中,只是更新了KET$_CLIENT_CONFIG表,并没有对其他什么表进行操作。

6ssdb0220:BRM1AP1:/opt/app/oracle/admin/BRM1AP/diag/rdbms/brm1ap/BRM1AP1/trace>ssdb0220:BRM1AP1:/opt/app/oracle/admin/BRM1AP/diag/rdbms/brm1ap/BRM1AP1/trace>cat BRM1AP1_ora_24510.trc
Trace file /opt/app/oracle/admin/BRM1AP/diag/rdbms/brm1ap/BRM1AP1/trace/BRM1AP1_ora_24510.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_2
System name:    SunOS
Node name:      ssdb0220
Release:        5.10
Version:        Generic_144488-09
Machine:        sun4u
Instance name: BRM1AP1
Redo thread mounted by this instance: 1
Oracle process number: 64
Unix process pid: 24510, image: oracle@ssdb0220 (TNS V1-V3)
 
 
*** 2012-04-10 19:28:29.945
*** SESSION ID:(3.36061) 2012-04-10 19:28:29.946
*** CLIENT ID:() 2012-04-10 19:28:29.946
*** SERVICE NAME:(SYS$USERS) 2012-04-10 19:28:29.946
*** MODULE NAME:(sqlplus@ssdb0220 (TNS V1-V3)) 2012-04-10 19:28:29.946
*** ACTION NAME:() 2012-04-10 19:28:29.946
 
WAIT #18446744071463951432: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=622534396974
 
*** 2012-04-10 19:28:39.946
WAIT #18446744071463951432: nam='SQL*Net message from client' ela= 10000599 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=622544399225
CLOSE #18446744071463951432:c=0,e=66,dep=0,type=1,tim=622544400245
=====================
PARSING IN CURSOR #18446744071463946160 len=145 dep=0 uid=0 oct=47 lid=0 tim=622544400901 hv=3446470561 ad='a6e1575e0' sqlid='f5cdzuv6qtxx1'
BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;
END OF STMT
PARSE #18446744071463946160:c=0,e=430,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=622544400894
=====================
PARSING IN CURSOR #18446744071526933536 len=306 dep=1 uid=0 oct=47 lid=0 tim=622544413441 hv=4261146041 ad='a6efe4ab8' sqlid='6ampntmyzrvdt'
BEGIN   UPDATE KET$_CLIENT_CONFIG      SET LAST_CHANGE = SYSTIMESTAMP    WHERE CLIENT_ID = 0      AND OPERATION_ID = 0;   UPDATE KET$_CLIENT_CONFIG      SET LAST_CHANGE = SYSTIMESTAMP,          STATUS = :sta    WHERE CLIENT_ID = :cid      AND OPERATION_ID = :oid;   EXCEPTION WHEN OTHERS THEN RAISE;   END;
END OF STMT
PARSE #18446744071526933536:c=0,e=498,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=0,tim=622544413433
BINDS #18446744071526933536:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=0000 frm=00 csi=00 siz=72 off=0
  kxsbbbfp=ffffffff7de7dfa8  bln=22  avl=02  flg=05
  value=1
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=0000 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=ffffffff7de7dfc0  bln=22  avl=02  flg=01
  value=4
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=0000 frm=00 csi=00 siz=0 off=48
  kxsbbbfp=ffffffff7de7dfd8  bln=22  avl=01  flg=01
  value=0
=====================
PARSING IN CURSOR #18446744071526921904 len=97 dep=2 uid=0 oct=6 lid=0 tim=622544416363 hv=2474724754 ad='a6e44e660' sqlid='8r2zud29s2mck'
UPDATE KET$_CLIENT_CONFIG SET LAST_CHANGE = SYSTIMESTAMP WHERE CLIENT_ID = 0 AND OPERATION_ID = 0
END OF STMT
PARSE #18446744071526921904:c=0,e=767,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=270131105,tim=622544416360
EXEC #18446744071526921904:c=0,e=1690,p=0,cr=1,cu=2,mis=0,r=1,dep=2,og=1,plh=270131105,tim=622544418350
STAT #18446744071526921904 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  KET$_CLIENT_CONFIG (cr=1 pr=0 pw=0 time=1241 us)'
STAT #18446744071526921904 id=2 cnt=1 pid=1 pos=1 obj=749200 op='INDEX UNIQUE SCAN KET$_CL_PK (cr=1 pr=0 pw=0 time=65 us cost=1 size=18 card=1)'
CLOSE #18446744071526921904:c=0,e=15,dep=2,type=3,tim=622544419162
=====================
PARSING IN CURSOR #18446744071526911240 len=115 dep=2 uid=0 oct=6 lid=0 tim=622544419705 hv=1796764683 ad='9ae946b90' sqlid='5vz88ptpjhx0b'
UPDATE KET$_CLIENT_CONFIG SET LAST_CHANGE = SYSTIMESTAMP, STATUS = :B1 WHERE CLIENT_ID = :B3 AND OPERATION_ID = :B2
END OF STMT
PARSE #18446744071526911240:c=0,e=376,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=270131105,tim=622544419702
BINDS #18446744071526911240:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=202001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=ffffffff7de7dfa8  bln=22  avl=02  flg=09
  value=1
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=202001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=ffffffff7de7dfc0  bln=22  avl=02  flg=09
  value=4
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=202001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=ffffffff7de7dfd8  bln=22  avl=01  flg=09
  value=0
EXEC #18446744071526911240:c=0,e=1477,p=0,cr=1,cu=1,mis=0,r=1,dep=2,og=1,plh=270131105,tim=622544421566
STAT #18446744071526911240 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  KET$_CLIENT_CONFIG (cr=1 pr=0 pw=0 time=203 us)'
STAT #18446744071526911240 id=2 cnt=1 pid=1 pos=1 obj=749200 op='INDEX UNIQUE SCAN KET$_CL_PK (cr=1 pr=0 pw=0 time=57 us cost=1 size=21 card=1)'
CLOSE #18446744071526911240:c=0,e=5,dep=2,type=3,tim=622544421922
EXEC #18446744071526933536:c=10000,e=7736,p=0,cr=2,cu=3,mis=0,r=1,dep=1,og=1,plh=0,tim=622544422087
WAIT #18446744071463946160: nam='reliable message' ela= 3193 channel context=44522785944 channel handle=44255547448 broadcast message=45060028288 obj#=-1 tim=622544427249
EXEC #18446744071463946160:c=30000,e=26615,p=0,cr=2,cu=4,mis=0,r=1,dep=0,og=1,plh=0,tim=622544427847
 
*** 2012-04-10 19:28:46.188
WAIT #18446744071463946160: nam='log file sync' ela= 6212237 buffer#=3259 sync scn=822653827 p3=0 obj#=-1 tim=622550640231
WAIT #18446744071463946160: nam='SQL*Net message to client' ela= 22 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=622550642501
 
*** 2012-04-10 19:28:52.093
WAIT #18446744071463946160: nam='SQL*Net message from client' ela= 5903444 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=622556546148
CLOSE #18446744071463946160:c=0,e=259,dep=0,type=1,tim=622556547295
=====================
PARSING IN CURSOR #18446744071463945296 len=6 dep=0 uid=0 oct=44 lid=0 tim=622556547801 hv=3480936638 ad='0' sqlid='23wm3kz7rps5y'
commit
END OF STMT
PARSE #18446744071463945296:c=0,e=310,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=622556547796
XCTEND rlbk=0, rd_only=1, tim=622556548334
EXEC #18446744071463945296:c=0,e=262,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=622556548475
WAIT #18446744071463945296: nam='SQL*Net message to client' ela= 9 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=622556548649
 
*** 2012-04-10 19:29:00.553
WAIT #18446744071463945296: nam='SQL*Net message from client' ela= 8457017 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=622565005749
CLOSE #18446744071463945296:c=0,e=61,dep=0,type=1,tim=622565006575
=====================
PARSING IN CURSOR #18446744071463938696 len=55 dep=0 uid=0 oct=42 lid=0 tim=622565006873 hv=2217940283 ad='0' sqlid='06nvwn223659v'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #18446744071463938696:c=0,e=169,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=622565006872
EXEC #18446744071463938696:c=0,e=2426,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=622565009463
ssdb0220:BRM1AP1:/opt/app/oracle/admin/BRM1AP/diag/rdbms/brm1ap/BRM1AP1/trace>

那么是否在DBA_AUTOTASK_TASK和KET$_CLIENT_CONFIG之前有什么继承的关系呢?

我们进一步来看DBA_AUTOTASK_TASK ,却发现它是基于KET$_CLIENT_TASKS表的,而不是基于KET$_CLIENT_CONFIG表。

SQL>l
 1*selectobject_name,object_type,ownerfromdba_objectswhereobject_name='DBA_AUTOTASK_TASK'
SQL> /
 
OBJECT_NAME                   OBJECT_TYPE         OWNER
------------------------------ ------------------- ------------------------------
DBA_AUTOTASK_TASK             VIEW               SYS
DBA_AUTOTASK_TASK             SYNONYM             PUBLIC
 
SQL>
 
 
SQL>l
 1*selectTEXTfromdba_viewswhereowner='SYS'andVIEW_NAME='DBA_AUTOTASK_TASK'
SQL> /
 
TEXT
--------------------------------------------------------------------------------
SELECT
      C.CNAME_KETCL,
      O.PRG_KETOP,
      TG.TNAME_KETTG,
      T.TARGET_NAME,
      O.OPNAME_KETOP,
      dbms_auto_task.decode_attributes(T.ATTRIBUTES),
      T.TASK_PRIORITY,
      T.PRIORITY_OVERRIDE,
      DECODE(T.STATUS,1,'DISABLED',2,'ENABLED',13,'DEFERRED','INVALID'), <<<<<<<<<seecode1meandisable,code2meanenable.
      T.WINDOW_NAME,
      T.CURR_JOB_NAME,
      SJ.STATE,
      DECODE(T.EST_TYPE,1,'DERIVED',2,'FORCED',3,'LOCKED','N/A'),
      T.EST_WEIGHT,
      T.EST_DURATION,
      T.EST_CPU_TIME,
      T.EST_TEMP,
      T.EST_DOP,
      T.EST_IO_RATE,
      T.EST_UNDO_RATE,
      T.RETRY_COUNT,
      T.LG_DATE,
      T.LG_PRIORITY,
      T.LG_DURATION,
      T.LG_CPU_TIME,
      T.LG_TEMP,
      T.LG_DOP,
      T.LG_IO_RATE,
      T.LG_UNDO_RATE,
      T.LG_CPU_WAIT,
      T.LG_IO_WAIT,
      T.LG_UNDO_WAIT,
      T.LG_TEMP_WAIT,
      T.LG_CONCURRENCY,
      T.LG_CONTENTION,
      W.NEXT_START_DATE,
      T.LT_DATE,
      T.LT_PRIORITY,
      CASET.LT_TERM_CODE
        WHENNULLTHEN'N/A'
        WHEN10THEN'SUCCEEDED'
        WHEN11THEN'FAILED'
        WHEN12THEN'STOPPED BY USER ACTION'
        WHEN13THEN'STOPPED AT END OF MAINTENANCE WINDOW'
        WHEN14THEN'STOPPED AT INSTANCE SHUTDOWN'
        WHEN15THEN'STOPPED'
        ELSE'UNKNOWN'
      END,
      T.LT_DURATION,
      T.LT_CPU_TIME,
      T.LT_TEMP,
      T.LT_DOP,
      T.LT_IO_RATE,
      T.LT_UNDO_RATE,
      T.LT_CPU_WAIT,
      T.LT_IO_WAIT,
      T.LT_UNDO_WAIT,
      T.LT_TEMP_WAIT,
      T.LT_CONCURRENCY,
      T.LT_CONTENTION,
      T.MG_DURATION,
      T.MG_CPU_TIME,
      T.MG_TEMP,
      T.MG_DOP,
      T.MG_IO_RATE,
      T.MG_UNDO_RATE,
      T.MG_CPU_WAIT,
      T.MG_IO_WAIT,
      T.MG_UNDO_WAIT,
      T.MG_TEMP_WAIT,
      T.MG_CONCURRENCY,
      T.MG_CONTENTION,
      T.INFO_FIELD_1,
      T.INFO_FIELD_2,
      T.INFO_FIELD_3,
      T.INFO_FIELD_4
    FROM KET$_CLIENT_TASKST,X$KETCLC,X$KETOPO,X$KETTGTG, <<<<<<<<<<<<<<<<<<T.status,TmeantableKET$_CLIENT_TASKS
          DBA_SCHEDULER_WINDOWSW,DBA_SCHEDULER_JOBSSJ
  WHERET.CLIENT_ID=C.CID_KETCL
    AND(BITAND(C.ATTR_KETCL,2048)=0
            OR999999<(SELECTTO_NUMBER(VALUE)
                          FROMV$SYSTEM_PARAMETER
                          WHERENAME='_automatic_maintenance_test'))
    ANDC.CID_KETCL>0
    ANDT.CLIENT_ID=O.CID_KETOP
    ANDT.OPERATION_ID=O.OPID_KETOP
    ANDT.TARGET_TYPE=TG.TID_KETTG
    ANDT.WINDOW_NAME=W.WINDOW_NAME(+)
    ANDT.CURR_JOB_NAME=SJ.JOB_NAME(+)
 
 
SQL>

那么 KET$_CLIENT_CONFIG和KET$_CLIENT_TASKS是否有继承关系呢?查他们的object_type发现都是table,于是再去找他们的定义,在$ORACLE_HOME/rdbms/admin/catatsk.sql中,我们看到了这2个表的定义:

/opt/app/oracle/product/11.2.0/db_2/rdbms/admin>vi catatsk.sql
...
-- Create configuration table
CREATE TABLE KET$_CLIENT_CONFIG (
      CLIENT_ID          NUMBER,             -- 0 for AUTOTASK Configuration
      OPERATION_ID       NUMBER,             -- 0 for Client Configuration
      STATUS             NUMBER DEFAULT 1,   -- 2 enabled, 1 - disabled, others
      ATTRIBUTES         NUMBER DEFAULT 0,   -- attribute flags
      PRIORITY_OVERRIDE  NUMBER DEFAULT 0,   -- 1 -medium, 2 - high, 3 - urgent
      LAST_CHANGE        TIMESTAMP WITH
                           TIME ZONE
                         DEFAULT SYSTIMESTAMP,     -- last change timestamp
      SERVICE_NAME       VARCHAR2 (64)
                           DEFAULT NULL, -- Service Affinity
      GENERATOR_JOB_NAME VARCHAR2 (64)
                           DEFAULT NULL, -- Name of Task List Generator job
      FIELD_1            NUMBER,         -- Spare field
      FIELD_2            TIMESTAMP WITH
                           TIME ZONE,     -- Spare field
      FIELD_3            VARCHAR2(2000)
                            DEFAULT NULL, -- Spare field
      CONSTRAINT KET$_CL_PK
         PRIMARY KEY (OPERATION_ID, CLIENT_ID)
         USING INDEX TABLESPACE SYSAUX)
  TABLESPACE SYSAUX;
 
-- Create main repository table
CREATE TABLE KET$_CLIENT_TASKS (
      CLIENT_ID           NUMBER,
      OPERATION_ID        NUMBER,
      TARGET_TYPE         NUMBER,
      TARGET_NAME         VARCHAR2(513),
      ATTRIBUTES          NUMBER DEFAULT 0, -- attribute mask
      ATTRIBUTES_OVERRIDE NUMBER DEFAULT 0, -- attribute mask or NULL
      TASK_PRIORITY       NUMBER DEFAULT 0, -- 1:medium, 2:high, 3:urgent
      PRIORITY_OVERRIDE   NUMBER DEFAULT 0, -- 1:medium, 2:high, 3:urgent
      STATUS              NUMBER DEFAULT 0, -- 1:disabled, 2:enabled, 13:defer
      --
      -- Task Arguments
      --
      ARG_4               VARCHAR2(1024),  -- argument 4
      ARG_5               VARCHAR2(1024),  -- argument 5
      ARG_6               VARCHAR2(1024),  -- argument 6
      WINDOW_NAME         VARCHAR2(65),    -- if STATUS == 13, deferred to
      CURR_JOB_NAME       VARCHAR2(65),    -- May be NULL if no current job
      CURR_WIN_START      TIMESTAMP WITH TIME ZONE, -- current MW
      --
      -- EST - Estimated resource usage
      --
      EST_TYPE            NUMBER DEfAULT 0,
                               -- 0: none, 1 : derived, 2: forced, 3: locked
      EST_WEIGHT          NUMBER,
      EST_DURATION        NUMBER,
      EST_CPU_TIME        NUMBER,
      EST_TEMP            NUMBER,
      EST_DOP             NUMBER,
      EST_IO_RATE         NUMBER,
      EST_UNDO_RATE       NUMBER,
      RETRY_COUNT         NUMBER DEFAULT 0,  -- number of failure retries
      GOOD_COUNT          NUMBER DEFAULT 0,
      --
      -- LG - data for Last Good (non-failure) run
      --
      LG_JOB_LOG_ID       NUMBER DEFAULT 0,  -- FK dba_scheduler_job_log
      LG_DATE             TIMESTAMP WITH TIME ZONE,
      LG_PRIORITY         NUMBER,      -- task priority
      LG_DURATION         NUMBER,      -- duration of job
      LG_CPU_TIME         NUMBER,      -- CPU time consumed
      LG_TEMP             NUMBER,      -- max Temp Space used
      LG_DOP              NUMBER,      -- max DOP used
      LG_IO_RATE          NUMBER,      -- mean I/O rate
      LG_UNDO_RATE        NUMBER,      -- undo generation rate
      LG_CPU_WAIT         NUMBER,      -- cumulative CPU wait
      LG_IO_WAIT          NUMBER,      -- cumulativeI/O Wait
      LG_UNDO_WAIT        NUMBER,      -- undo wait
      LG_TEMP_WAIT        NUMBER,      -- temp space wait
      LG_CONCURRENCY      NUMBER,      -- concurrency wait
      LG_CONTENTION       NUMBER,      -- contention wait
      --
      -- LT - data from the Last Try (successful or not)
      --
      Lt_JOB_LOG_ID       NUMBER DEFAULT 0, -- FK dba_scheduler_job_log
      LT_DATE             TIMESTAMP WITH TIME ZONE,
      LT_PRIORITY         NUMBER DEFAULT 0, -- Priority at last try
      LT_TERM_CODE        NUMBER DEFAULT 0, -- 10, 11, 12, 13, 14, 15
      LT_ERROR            NUMBER DEFAULT 0, -- error from last try
      --
      -- Last Try stats
      LT_DURATION         NUMBER,      -- elapsed time of last try
      LT_CPU_TIME         NUMBER,      -- CPU time consumed
      LT_TEMP             NUMBER,      -- max Temp Space used
      LT_DOP              NUMBER,      -- max DOP used
      LT_IO_RATE          NUMBER,      -- mean I/O rate
      LT_UNDO_RATE        NUMBER,
      LT_CPU_WAIT         NUMBER,
      LT_IO_WAIT          NUMBER,
      LT_UNDO_WAIT        NUMBER,
      LT_TEMP_WAIT        NUMBER,
      LT_CONCURRENCY      NUMBER,
      LT_CONTENTION       NUMBER,
      --
      -- MG - Averaged (Mean) Good run stats
      --
      MG_DURATION         NUMBER DEFAULT 0,      -- elapsed time
      MG_CPU_TIME         NUMBER DEFAULT 0,
      MG_TEMP             NUMBER DEFAULT 0,
      MG_DOP              NUMBER DEFAULT 0,
      MG_IO_RATE          NUMBER DEFAULT 0,
      MG_UNDO_RATE        NUMBER DEFAULT 0,
      MG_CPU_WAIT         NUMBER DEFAULT 0,
      MG_IO_WAIT          NUMBER DEFAULT 0,
      MG_UNDO_WAIT        NUMBER DEFAULT 0,
      MG_TEMP_WAIT        NUMBER DEFAULT 0,
      MG_CONCURRENCY      NUMBER DEFAULT 0,
      MG_CONTENTION       NUMBER DEFAULT 0,
      --
      -- Fields that may be used by Clients to store task-related data
      --
      INFO_FIELD_1        VARCHAR2 (4000),
      INFO_FIELD_2        CLOB,
      INFO_FIELD_3        NUMBER,
      INFO_FIELD_4        NUMBER,
      CONSTRAINT KET$_TSK_PK
        PRIMARY KEY (CLIENT_ID, OPERATION_ID, TARGET_TYPE, TARGET_NAME)
      USING INDEX TABLESPACE SYSAUX
     )
    TABLESPACE SYSAUX;
--

发现他们也没有继承关系,是独立的2个表。

所以DBMS_AUTO_TASK_ADMIN.DISABLE这个package不会改变DBA_AUTOTASK_TASK的状态。他们之间没有关系。

我们再来看看DBA_AUTO%这样类似的table,发现有一个table叫做DBA_AUTOTASK_CLIENT,我来继续查一下它是如何定义的:

SQL> l
  1* select TEXT from dba_views where owner='SYS' and VIEW_NAME='DBA_AUTOTASK_CLIENT'
SQL> /
 
TEXT
--------------------------------------------------------------------------------
SELECT "CNAME_KETCL","STATUS","RESOURCE_CONSUMER_GROUP","CLIENT_TAG","PRIORITY_O
VERRIDE","ATTRIBUTES","WINDOW_GROUP","SERVICE_NAME","RESOURCE_PERCENTAGE","USE_R
ESOURCE_ESTIMATES","MEAN_JOB_DURATION","MEAN_JOB_CPU","MEAN_JOB_ATTEMPTS","MEAN_
INCOMING_TASKS_7_DAY","MEAN_INCOMING_TASKS_30_DAY","TOTAL_CPU_LAST_7_DAYS","TOTA
L_CPU_LAST_30_DAYS","MAX_DURATION_LAST_7_DAYS","MAX_DURATION_LAST_30_DAYS","WIND
OW_DURATION_LAST_7_DAYS","WINDOW_DURATION_LAST_30_DAYS" FROM (
   WITH ZH AS (SELECT * FROM DBA_AUTOTASK_CLIENT_HISTORY
                        WHERE WINDOW_END_TIME > (SYSDATE - INTERVAL ' 720' HOUR)
)
   SELECT C.CNAME_KETCL,
          DECODE(dbms_auto_task.get_client_status_override(CR.CLIENT_ID),
                 1, 'DISABLED',
                 decode(CR.STATUS, 2, 'ENABLED',  1, 'DISABLED', 'INVALID'))
            AS STATUS,
          (SELECT SJC.RESOURCE_CONSUMER_GROUP
             FROM DBA_SCHEDULER_JOB_CLASSES SJC
            WHERE C.HJC_KETCL = SJC.JOB_CLASS_NAME) AS RESOURCE_CONSUMER_GROUP,
          C.CTAG_KETCL AS CLIENT_TAG,
          DECODE(CR.PRIORITY_OVERRIDE,
                     NULL, NULL,
                     1, 'MEDIUM',
                     2, 'HIGH',
                     3, 'URGENT','INVALID') AS PRIORITY_OVERRIDE,
          DBMS_AUTO_TASK.DECODE_ATTRIBUTES(
            DBMS_AUTO_TASK.RECONCILE_ATTRIBUTES(C.ATTR_KETCL,
                        CR.ATTRIBUTES, 0, 0, 0, 0)) AS ATTRIBUTES,
          C.WGRP_KETCL AS WINDOW_GROUP,
          CR.SERVICE_NAME,
          (SELECT CPU_P1+CPU_P2+CPU_P3+CPU_P4+CPU_P5+CPU_P6+CPU_P7+CPU_P8
             FROM DBA_RSRC_PLAN_DIRECTIVES RPD,
                  DBA_SCHEDULER_JOB_CLASSES SJC
            WHERE RPD.PLAN = 'ORA$AUTOTASK_HIGH_SUB_PLAN'
              AND RPD.GROUP_OR_SUBPLAN = SJC.RESOURCE_CONSUMER_GROUP
              AND SJC.JOB_CLASS_NAME = C.HJC_KETCL) AS RESOURCE_PERCENTAGE,
          CASE BITAND(21,
                         DBMS_AUTO_TASK.RECONCILE_ATTRIBUTES(C.ATTR_KETCL,
                                    CR.ATTRIBUTES, 0, 0, 0, 0))
          WHEN 0 THEN 'TRUE'
          ELSE 'FALSE' END AS USE_RESOURCE_ESTIMATES,
          (SELECT NUMTODSINTERVAL(AVG((EXTRACT(DAY
                                         FROM JRD.RUN_DURATION)*24*60*60)
                       + (EXTRACT(HOUR FROM JRD.RUN_DURATION)*60*60)
                       + (EXTRACT(MINUTE FROM JRD.RUN_DURATION)*60)
                       + EXTRACT(SECOND FROM JRD.RUN_DURATION)),'SECOND')
             FROM DBA_SCHEDULER_JOB_RUN_DETAILS JRD,
                  DBA_SCHEDULER_JOB_LOG JL
            WHERE JL.JOB_CLASS IN
                     (C.UJC_KETCL, C.HJC_KETCL, C.MJC_KETCL)
              AND JL.LOG_ID = JRD.LOG_ID) AS MEAN_JOB_DURATION,
          (SELECT NUMTODSINTERVAL(AVG((EXTRACT(DAY FROM JRD.CPU_USED)*24*60*60)
                       + (EXTRACT(HOUR FROM JRD.CPU_USED)*60*60)
                       + (EXTRACT(MINUTE FROM JRD.CPU_USED)*60)
                       + EXTRACT(SECOND FROM JRD.CPU_USED)),'SECOND')
             FROM DBA_SCHEDULER_JOB_RUN_DETAILS JRD,
                  DBA_SCHEDULER_JOB_LOG JL
            WHERE JL.JOB_CLASS IN
                       (C.UJC_KETCL, C.HJC_KETCL, C.MJC_KETCL)
              AND JL.LOG_ID = JRD.LOG_ID) AS MEAN_JOB_CPU,
           (SELECT AVG(TR.RETRY_COUNT)
              FROM KET$_CLIENT_TASKS TR
             WHERE C.CID_KETCL = TR.CLIENT_ID)
              AS MEAN_JOB_ATTEMPTS,
           (SELECT SUM(JOBS_CREATED)/CASE COUNT(CH.WINDOW_NAME) WHEN 0 THEN 1
                                   ELSE COUNT(CH.WINDOW_NAME) END
              FROM ZH CH
             WHERE CH.CLIENT_NAME = C.CNAME_KETCL
               AND CH.JOBS_CREATED<>0
               AND CH.WINDOW_START_TIME > (SYSDATE -INTERVAL '168' HOUR))
                  AS MEAN_INCOMING_TASKS_7_DAY,
           (SELECT SUM(JOBS_CREATED)/CASE COUNT(CH.WINDOW_NAME) WHEN 0 THEN 1
                                   ELSE COUNT(CH.WINDOW_NAME) END
              FROM ZH CH
             WHERE CH.CLIENT_NAME = C.CNAME_KETCL
               AND CH.JOBS_CREATED<>0
               AND CH.WINDOW_START_TIME > (SYSDATE -INTERVAL '720' HOUR))
                 AS MEAN_INCOMING_TASKS_30_DAY,
          (SELECT NUMTODSINTERVAL(SUM((EXTRACT(DAY FROM JRD.CPU_USED)*24*60*60)
                       + (EXTRACT(HOUR FROM JRD.CPU_USED)*60*60)
                       + (EXTRACT(MINUTE FROM JRD.CPU_USED)*60)
                       + EXTRACT(SECOND FROM JRD.CPU_USED)),'SECOND')
              FROM DBA_SCHEDULER_JOB_RUN_DETAILS JRD,
                   DBA_SCHEDULER_JOB_LOG JL
             WHERE JL.JOB_CLASS IN
                       (C.UJC_KETCL, C.HJC_KETCL, C.MJC_KETCL)
               AND JL.LOG_ID = JRD.LOG_ID
               AND JRD.LOG_DATE > (SYSDATE - INTERVAL '168' HOUR))
                               AS TOTAL_CPU_LAST_7_DAYS,
          (SELECT NUMTODSINTERVAL(SUM((EXTRACT(DAY FROM JRD.CPU_USED)*24*60*60)
                       + (EXTRACT(HOUR FROM JRD.CPU_USED)*60*60)
                       + (EXTRACT(MINUTE FROM JRD.CPU_USED)*60)
                       + EXTRACT(SECOND FROM JRD.CPU_USED)),'SECOND')
              FROM DBA_SCHEDULER_JOB_RUN_DETAILS JRD,
                   DBA_SCHEDULER_JOB_LOG JL
             WHERE JL.JOB_CLASS IN
                       (C.UJC_KETCL, C.HJC_KETCL, C.MJC_KETCL)
               AND JL.LOG_ID = JRD.LOG_ID
               AND JRD.LOG_DATE > (SYSDATE - INTERVAL '720' HOUR))
                               AS TOTAL_CPU_LAST_30_DAYS,
           (SELECT MAX(JRD.RUN_DURATION)
              FROM DBA_SCHEDULER_JOB_RUN_DETAILS JRD,
                   DBA_SCHEDULER_JOB_LOG JL
             WHERE JL.JOB_CLASS IN
                       (C.UJC_KETCL, C.HJC_KETCL, C.MJC_KETCL)
               AND JL.LOG_ID = JRD.LOG_ID
               AND JRD.LOG_DATE > (SYSDATE - INTERVAL '168' HOUR))
                                AS MAX_DURATION_LAST_7_DAYS,
           (SELECT MAX(JRD.RUN_DURATION)
              FROM DBA_SCHEDULER_JOB_RUN_DETAILS JRD,
                    DBA_SCHEDULER_JOB_LOG JL
             WHERE JL.JOB_CLASS IN
                       (C.UJC_KETCL, C.HJC_KETCL, C.MJC_KETCL)
               AND JL.LOG_ID = JRD.LOG_ID
               AND JRD.LOG_DATE > (SYSDATE - INTERVAL '720' HOUR))
                                AS MAX_DURATION_LAST_30_DAYS,
          (SELECT NUMTODSINTERVAL(SUM(
                     (EXTRACT(DAY FROM CH.WINDOW_DURATION)*24*60*60)
                     + (EXTRACT(HOUR FROM CH.WINDOW_DURATION)*60*60)
                     + (EXTRACT(MINUTE FROM CH.WINDOW_DURATION)*60)
                     + EXTRACT(SECOND FROM CH.WINDOW_DURATION)),
                   'SECOND')
              FROM ZH CH
             WHERE CH.CLIENT_NAME = C.CNAME_KETCL
               AND CH.JOBS_CREATED > 0
               AND CH.WINDOW_END_TIME > (SYSDATE - INTERVAL '168' HOUR))
                  AS WINDOW_DURATION_LAST_7_DAYS,
          (SELECT NUMTODSINTERVAL(SUM(
                     (EXTRACT(DAY FROM CH.WINDOW_DURATION)*24*60*60)
                     + (EXTRACT(HOUR FROM CH.WINDOW_DURATION)*60*60)
                     + (EXTRACT(MINUTE FROM CH.WINDOW_DURATION)*60)
                     + EXTRACT(SECOND FROM CH.WINDOW_DURATION)),
                   'SECOND')
              FROM ZH CH
             WHERE CH.CLIENT_NAME = C.CNAME_KETCL
               AND CH.JOBS_CREATED > 0
               AND CH.WINDOW_END_TIME > (SYSDATE - INTERVAL ' 720' HOUR))
                  AS WINDOW_DURATION_LAST_30_DAYS
     FROM X$KETCL C, KET$_CLIENT_CONFIG CR
    WHERE C.CID_KETCL = CR.CLIENT_ID
      AND CR.OPERATION_ID = 0
      AND C.CID_KETCL > 0
      AND (BITAND(C.ATTR_KETCL,2048) = 0
          OR 999999<(SELECTTO_NUMBER(VALUE)
                        FROMV$SYSTEM_PARAMETER
                        WHERENAME= '_automatic_maintenance_test')))
 
 
SQL>

我们看到DBA_AUTOTASK_CLIENT是基于KET$_CLIENT_CONFIG的。所以问题到这里就明了了。

我们disable autotask应该查的表应该是DBA_AUTOTASK_CLIENT,而不是DBA_AUTOTASK_TASK:

SQL>selectCLIENT_NAME,STATUSfromDBA_AUTOTASK_CLIENT;
 
CLIENT_NAMESTATUS
---------------------------------------------------------------- --------
autooptimizerstatscollectionDISABLED
autospaceadvisorENABLED
sqltuningadvisorENABLED
 
SQL>

看到确实已经变成disable了。

最后,贴一下oracle官网对这2个表的解释:

  1. DBA_AUTOTASK_TASK:displays information about current and past automated maintenance tasks.
  2. DBA_AUTOTASK_CLIENT:displays statistical data for each automated maintenance task over 7-day and 30-day periods.

呵呵,它也没说disable autotask之后该查哪个表吧。

原文链接: http://www.oracleblog.org/working-case/how-to-check-disable-autotask/

0     0

评价列表(0)