DBA_AUTOTASK_TASK and DBA_AUTOTASK_CLIENT Shows Different Status For Auto Opt

郑全

In this Document
Symptoms
Cause
Solution
References

---

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.3 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Symptoms

We are Trying to Disable the Auto Optimizer Stats Collection in 11G.
DBA_AUTOTASK_CLIENT shows that the 'auto optimizer stats collection' is disabled. But DBA_AUTOTASK_TASK shows that the Task is Enabled.

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

PL/SQL procedure successfully completed.

Checking the Status from DBA_AUTOTASK_TASK & DBA_AUTOTASK_CLIENT


SQL> select client_name,status from DBA_AUTOTASK_TASK;
.
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED <<===
auto space advisor ENABLED
sql tuning advisor ENABLED
.
SQL> select client_name,status from Dba_Autotask_Client;
.
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection DISABLED <<===
auto space advisor ENABLED
sql tuning advisor ENABLED

Cause

This is expected Behaviour.
The views assume that there is a one to many relationship between CLIENTS and TASKS.
Task can be used by different/multiple client. So even though if we disable the client, the DBA_AUTOTASK_TASK may still show the status as enabled. In current version, the TASKS has only one CLIENT. But in future version of oracle , the TASKS can have multiple CLIENTS so the status in DBA_AUTOTASK_TASK will show as ENABLED.
So the correct way to check the status is through DBA_AUTOTASK_CLIENT.

Solution

Use DBA_AUTOTASK_CLIENT to check the status.

SQL> select client_name,status from Dba_Autotask_Client;

References

BUG:8605352 - WRONG STATUS INFO IN DBA_AUTOTASK_TASK
NOTE:779596.1 - How Can We Find Out Status Of Task 'Auto Optimizer Stats Collection'

相关内容 产品 Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

[此贴子已经被admin于2012-7-18 13:41:25编辑过]