VIEW: SYS.DBA_AUTOTASK_CLIENT

Source

View Text - Preformatted

SELECT "CNAME_KETCL","STATUS","RESOURCE_CONSUMER_GROUP","CLIENT_TAG","PRIORITY_OVERRIDE","ATTRIBUTES","WINDOW_GROUP","SERVICE_NAME","RESOURCE_PERCENTAGE","USE_RESOURCE_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","TOTAL_CPU_LAST_30_DAYS","MAX_DURATION_LAST_7_DAYS","MAX_DURATION_LAST_30_DAYS","WINDOW_DURATION_LAST_7_DAYS","WINDOW_DURATION_LAST_30_DAYS","LAST_CHANGE" 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,
                     0, 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,
              CR.LAST_CHANGE AS LAST_CHANGE
     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 < (SELECT TO_NUMBER(VALUE)
                         FROM V$SYSTEM_PARAMETER
                        WHERE NAME = '_automatic_maintenance_test')))

View Text - HTML Formatted

SELECT "CNAME_KETCL"
, "STATUS"
, "RESOURCE_CONSUMER_GROUP"
, "CLIENT_TAG"
, "PRIORITY_OVERRIDE"
, "ATTRIBUTES"
, "WINDOW_GROUP"
, "SERVICE_NAME"
, "RESOURCE_PERCENTAGE"
, "USE_RESOURCE_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"
, "TOTAL_CPU_LAST_30_DAYS"
, "MAX_DURATION_LAST_7_DAYS"
, "MAX_DURATION_LAST_30_DAYS"
, "WINDOW_DURATION_LAST_7_DAYS"
, "WINDOW_DURATION_LAST_30_DAYS"
, "LAST_CHANGE"
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
, 0
, 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
, CR.LAST_CHANGE AS LAST_CHANGE
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 < (SELECT TO_NUMBER(VALUE)
FROM V$SYSTEM_PARAMETER
WHERE NAME = '_AUTOMATIC_MAINTENANCE_TEST')))