Confused by DBA_ADVISOR views
Date: Thu, 1 Oct 2009 16:52:57 -0400
Message-ID: <BBB2B987DB6E504D89FC45EA314CE9890EEBD85A_at_edmtpmail01.ad.umassmed.edu>
Hi all,
I am confused with the following query results from Advisor views.
The first one returns the object_id with chained rows and need re-org. But these object_ids point to sys objects in the second query.
With the value of task_id from the first query, the third query indicates that the recommendation for this finding is to Enable row movement and shrink the application table???
I checked that there is no chained rows for this app table, the statistics is fresh.
Could anyone advise me on this?
Thanks,
Lu
SQL> select task_id, object_id, message, more_info
from dba_advisor_findings
where task_id in
(select task_id from dba_advisor_log
where execution_start > sysdate -1)
and type = 'PROBLEM' and message = 'The object has chained rows that can be removed by re-org.';
TASK_ID OBJECT_ID MESSAGE MORE_INFO ---------- ---------- ---------------------------------------- ------------------------------ 15246 4 The object has chained rows that can be 49percent chained rows can be
removed by re-org. removedby re-org.
15246 5 The object has chained rows that can be 11 percent chained rows can be
removed by re-org. removedby re-org.
15246 7 The object has chained rows that can be 47 percent chained rows can be
removed by re-org. removedby re-org.
15246 17 The object has chained rows that can be 48 percent chained rows can be
removed by re-org. removedby re-org.
SQL> select owner,object_name from dba_objects where object_id in
(4,5,7,17);
OWNER OBJECT_NAME ------------------------------ -------------------- SYS TAB$ SYS CLU$ SYS I_TS# SYS FILE$
SQL> select task_id, benefit_type from dba_advisor_recommendations where task_id=15246;
TASK_ID BENEFIT_TYPE
15246 Enable row movement of the table APPLSYS.WF_ITEM_ACTIVITY_STATUSES and perform s
hrink, estimated savings is 16591171 bytes.
15246 Perform shrink, estimated savings is 13062268 bytes.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 01 2009 - 15:52:57 CDT