GENERAL INFORMATION

Task Name : AUTO_STATS_ADVISOR_TASK
Execution Name : EXEC_3528
Created : 04-05-17 02:00:28
Last Modified : 07-24-17 10:07:07

SUMMARY
For execution EXEC_3528 of task AUTO_STATS_ADVISOR_TASK, the Statistics Advisor has 5 finding(s). The findings are related to the following rules: COMPLETEAUTOJOB, USECONCURRENT, AVOIDSTALESTATS, UNLOCKNONVOLATILETABLE, USEINCREMENTAL. Please refer to the finding section for detailed information.

FINDINGS
Rule Name: CompleteAutoJob
Rule Description: Auto Statistics Gather Job should complete successfully
Finding: There were maintenance windows that failed to close.

Recommendation: Please file a Service Request with Oracle.
Rationale: Maintenance window configuration appears to be correct. Requ ires more investigation by Oracle Support.

Rule Name: UseConcurrent
Rule Description: Use Concurrent preference for Statistics Collection
Finding: The CONCURRENT preference is not used.

Recommendation: Set the CONCURRENT preference.
Example:
dbms_stats.set_global_prefs('CONCURRENT', 'ALL');
Rationale: The system's condition satisfies the use of concurrent stati stics gathering. Using CONCURRENT increases the efficiency of statistics gatheri ng.

Rule Name: AvoidStaleStats
Rule Description: Avoid objects with stale or no statistics
Finding: There are 4 object(s) with stale statistics.
Schema:
CTXSYS
Objects:
DR$DBO
DR$PARAMETER
DR$SECTION_ATTRIBUTE
Schema:
DBSNMP
Objects:
BSLN_TIMEGROUPS

Recommendation: Regather statistics on objects with stale statistics.
Example:
-- Gathering statistics for tables with stale or no statisti cs in schema, SH:
exec dbms_stats.gather_schema_stats('SH', options => 'GATHER AUTO')
Rationale: Stale statistics or no statistics will result in bad plans.< /td>

Rule Name: UnlockNonVolatileTable
Rule Description: Statistics for objects with non-volatile should not be locke d
Finding: Statistics are locked on 4 table(s) which are not volatile.< /td>
Schema:
IX
Objects:
ORDERS_QUEUETABLE
STREAMS_QUEUE_TABLE
Schema:
SYS
Objects:
AQ_SRVNTFN_TABLE_1
KUPC$DATAPUMP_QUETAB_1

Recommendation: Unlock the statistics on non-volatile tables, and use gather statistics operations to gather statistics for these tables.
Example:
-- Unlocking statistics for 'SH.SALES':
dbms_stats.unlock_table_stats('SH', 'SALES');
Rationale: Statistics gathering operations will skip locked objects and may lead to stale or inaccurate statistics.

Rule Name: UseIncremental
Rule Description: Statistics should be maintained incrementally when it is ben eficial
Finding: Incremental option should be used on 2 object(s) for statist ics gathering.
Schema:
SH
Objects:
COSTS
SALES

Recommendation: Use the incremental option for statistics gathering on these objects.
Example:
-- Turn on the incremental option for 'SH.SALES':
dbms_stats.set_table_prefs('SH', 'SALES', 'INCREMENTAL', 'TR UE');
Rationale: Using the incremental option reduces the time it takes to ga ther statistics on partitioned tables. However, it does store additional informa tion, which takes up additional space.


SQL> SPOOL OFF