| 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 |
| 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. |
| 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. |
|---|