Oracle Databases in the Oracle Cloud Infrastructure (OCI) Database Management service has new capabilities available to help monitor and analyze Optimizer Statistics of Oracle Databases for on-premises and Cloud Databases. The feature allows you to analyze and monitor the statistics collection tasks and Optimizer Statistics Advisor tasks, and implement Optimizer Statistics Advisor recommendations. 

Monitor Optimizer Statistics with Oracle database automatic predefined maintenance windows

The Oracle database collects optimizer statistics for database objects for which statistics are missing statistics stale (out of date). This is done by an automatic task that executes during a predefined maintenance window. Many Database Administrators (DBAs) may have a well-established DIY statistics gathering procedure or may choose to disable automatic statistics gathering for some application schemas. It is important that you monitor and understand if there are any stale statistics that may be impacting database performance and if there are any missing statistics. Database Management now supports the monitoring of Optimizer statistics to solve the below use cases:

  • View the status of Automatic Statistics jobs by completion, failed, detailed drill by objects, etc. 
  • View the status of High-Frequency Statistics jobs by completion, failed, detailed drill by objects, etc. 
  • View the status of Manual Statistics jobs by completion, failed, detailed drill by objects, etc. 
Details page for Optimizer Statistics
Figure 1:  Optimizer Statistics monitoring on managed database details page  

Assuring quality of Optimizer Statistics and optimize SQL execution plans

Good quality statistics are essential to be able to generate optimal SQL execution plans, but sometimes statistics can be of poor quality and this fact could remain unnoticed. For example, older “inherited” systems might use scripts that are no longer understood by the DBAs, thus, there is a reluctance to change them. Oracle’s, practice of does not always include application of best practices.

For these reasons, Oracle Database has an advisor called the Optimizer Statistics Advisor to help improve the quality of statistics in the database. This diagnostic software analyzes information in the data dictionary, assesses the quality of statistics, and discovers how statistics are being gathered. It reports on poor and missing statistics and generatess recommendations to resolve these problems.

Using Database Management, you can monitor and analyze Optimizer Statistics Advisor tasks and implement Optimizer Statistics Advisor recommendations. Advisor tasks in the Advisor tab of the Optimizer statistics section display the status and number of the Optimizer Statistics Advisor tasks executed each day, over the last seven days.

You can drill down to a particular Optimizer Statistics Advisor task in the Advisor task list and examine the Optimizer Statistics Advisor findings for the task and implement the Advisor recommendations.

 

Advisor tab on managed database page
Figure 2:  Advisor tab of the Optimizer statistics section on managed database details page  

Understanding and managing optimizer statistics is key to optimal SQL execution. Knowing when and how to gather statistics in a timely manner is critical to maintaining acceptable performance. Now, you can use the OCI Database Management service to analyze and monitor the statistics collection tasks and Optimizer Statistics Advisor tasks, and implement Optimizer Statistics Advisor recommendations. 

Sign up for an Oracle Cloud Infrastructure trial account! For more information about this feature and how you can use it, see the documentation or this YouTube video.