Oracle 19C 为Optimizer Statistics Advisor任务生成报告

使用以下查询，其中占位符具有以下定义：

• tname 是任务的名称。
• exec 是执行的名称。
• type是输出的类型：TEXT，HTML，或XML。
• sect是该报告的部分：SUMMARY，FINDINGS，ERRORS，和ALL。
• lvl是报告的格式：BASIC，TYPICAL，ALL，或SHOW_HIDDEN。

SET LINESIZE 3000
SET LONG 500000
SET PAGESIZE 0
SET LONGCHUNKSIZE 100000

SELECT DBMS_STATS.REPORT_ADVISOR_TASK('tname', 'exec', 'type', 'sect', 'lvl') AS REPORT
FROM   DUAL;

例如，要打印的报告AUTO_STATS_ADVISOR_TASK，请使用以下查询：

SELECT DBMS_STATS.REPORT_ADVISOR_TASK('AUTO_STATS_ADVISOR_TASK',NULL,'TEXT','ALL','ALL') AS REPORT 
FROM   DUAL;

以下样本报告显示了四个发现：

GENERAL INFORMATION
-------------------------------------------------------------------------------
 Task Name	 : AUTO_STATS_ADVISOR_TASK
 Execution Name  : EXEC_136	
 Created	 : 09-05-16 02:52:34 
 Last Modified	 : 09-05-16 12:31:24 
-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
 For execution EXEC_136 of task AUTO_STATS_ADVISOR_TASK, the Statistics Advisor
 has 4 findings. The findings are related to the following rules: 
 AVOIDSETPROCEDURES, USEDEFAULTPARAMS, USEGATHERSCHEMASTATS, NOTUSEINCREMENTAL. 
Please refer to the finding section for detailed information.

-------------------------------------------------------------------------------
FINDINGS
-------------------------------------------------------------------------------
 Rule Name:	    AvoidSetProcedures
 Rule Description:  Avoid Set Statistics Procedures				
 Finding:  There are 5 SET_[COLUMN|INDEX|TABLE|SYSTEM]_STATS procedures being	
           used for statistics gathering.
 Recommendation:  Do not use SET_[COLUMN|INDEX|TABLE|SYSTEM]_STATS procedures.
                  Gather statistics instead of setting them.			
 Rationale:  SET_[COLUMN|INDEX|TABLE|SYSTEM]_STATS will cause bad plans due to
             wrong or inconsistent statistics.				   
----------------------------------------------------
 Rule Name:	    UseDefaultParams						
 Rule Description:  Use Default Parameters in Statistics Collection Procedures  
 Finding:  There are 367 statistics operations using nondefault parameters.	
 Recommendation:  Use default parameters for statistics operations.		
 Example:

 -- Gathering statistics for 'SH' schema using all default parameter values:	
 BEGIN dbms_stats.gather_schema_stats('SH'); END;				
 Rationale:  Using default parameter values for statistics gathering operations 
             is more efficient.	
----------------------------------------------------
 Rule Name:	    UseGatherSchemaStats					
 Rule Description:  Use gather_schema_stats procedure
 Finding:  There are 318 uses of GATHER_TABLE_STATS.
 Recommendation:  Use GATHER_SCHEMA_STATS instead of GATHER_TABLE_STATS.	
 Example:

 -- Gather statistics for 'SH' schema:						
 BEGIN dbms_stats.gather_schema_stats('SH'); END;				
 Rationale:  GATHER_SCHEMA_STATS has more options available, including checking 
             for staleness and gathering statistics concurrently. Also it is
             more maintainable for new tables added to the schema. If you only
             want to gather statistics for certain tables in the schema, specify
             them in the obj_filter_list parameter of GATHER_SCHEMA_STATS.						
----------------------------------------------------
 Rule Name:	    NotUseIncremental

 Rule Description:  Statistics should not be maintained incrementally when it is not							
 Finding:  Incremental option has been turned on for 10 tables, which will not benefit 
           from using the incremental option.
 Schema:
 SH
 Objects:
 CAL_MONTH_SALES_MV    
 CAL_MONTH_SALES_MV    
 CHANNELS
 COUNTRIES   
 CUSTOMERS    
 DIMENSION_EXCEPTIONS    
 FWEEK_PSCAT_SALES_MV    
 FWEEK_PSCAT_SALES_MV    
 PRODUCTS    
 PROMOTIONS    
 SUPPLEMENTARY_DEMOGRAPHICS 
 TIMES

 Recommendation:  Do not use the incremental option for statistics gathering on these objects.						
 Example:									
 --
 Turn off the incremental option for 'SH.SALES':     
 dbms_stats.set_table_prefs('SH', 'SALES', 'INCREMENTAL', 'FALSE');		
 Rationale:  The overhead of using the incremental option on these tables
             outweighs the benefit of using the incremental option.