Oracle Cloud Infrastructure Documentation

Monitor and Analyze Optimizer Statistics

You can monitor the optimizer statistics for a Managed Database, analyze the statistics collection tasks and Optimizer Statistics Advisor tasks, and implement Optimizer Statistics Advisor recommendations in Database Management.

To go to the Optimizer statistics section, go to the Managed database details page and click Optimizer statistics on the left pane under Resources.

About Optimizer Statistics

The optimizer statistics are a collection of data that describe details about the database and the objects in the database. The statistics provide a statistically correct picture of data storage and distribution, which is used by the optimizer when evaluating access paths. The optimizer uses statistics to get an estimate of the number of rows (and number of bytes) retrieved from a table, partition, or index. The optimizer estimates the cost for the access, determines the cost for possible plans, and then picks the execution plan with the lowest cost.

Optimizer statistics include:

  • Table statistics: Includes the number of rows, number of blocks, and average row length.

  • Column statistics: Includes the number of distinct values and nulls in a column and the distribution of data.

  • Index statistics: Includes the number of leaf blocks, index levels, and the index clustering factor.

  • System statistics: Includes CPU and I/O performance and utilization.

For more information on:

About Optimizer Statistics Collection

In Oracle Database, optimizer statistics collection is the gathering of optimizer statistics for database objects, including fixed objects. By default, Oracle Database uses automatic optimizer statistics collection. In this case, the database automatically runs DBMS_STATS to collect optimizer statistics for all schema objects for which statistics are missing or stale. The process eliminates many manual tasks associated with managing the optimizer, and significantly reduces the risks of generating suboptimal execution plans because of missing or stale statistics. You can also gather statistics manually using the DBMS_STATS package.

Oracle Database 19c introduces high-frequency automatic optimizer statistics collection. This lightweight task periodically gathers statistics for stale objects, and the default interval is 15 minutes. In contrast to the automated statistics collection job, the high-frequency task does not perform actions such as purging statistics for non-existent objects or invoking Optimizer Statistics Advisor.

For information on:

About Optimizer Statistics Advisor

Optimizer Statistics Advisor is a built-in diagnostic software that executes tasks to analyze how the optimizer statistics are currently gathered, the effectiveness of the existing statistics collection jobs, and the quality of the gathered statistics. Optimizer Statistics Advisor maintains rules, which embody Oracle best practices based on the current feature set. In this manner, the advisor always provides the most up-to-date recommendations for statistics collection.

For more information on Optimizer Statistics Advisor, see About Optimizer Statistics Advisor in Oracle Database SQL Tuning Guide.

Monitor Optimizer Statistics Summary

You can monitor a summary of the object statistics in the Managed Database and the tasks and sub-tasks performed to gather optimizer statistics.

The following tiles are displayed on the Summary tab of the Optimizer statistics section, and provide an overview of the optimizer statistics:

  • Object statistics: Displays the current statistics collected for the objects in the Managed Database. This pie chart provides an insight into the number of objects for which fresh statistics, stale statistics, or no statistics were collected.

  • Statistics gathering tasks: Displays the status and number of optimizer statistics gathering tasks or the status and number of the database objects for which statistics are gathered each day, over the last seven days. Each bar in the chart denotes the number of tasks run on a particular day or the number of objects for which statistics were collected on a particular day, and the color denotes the status. By default, this bar chart provides an overview of the number of automatic optimizer statistics gathering tasks broken down by status. Select the Object status option in the View by drop-down list to view the number of objects for which statistics were gathered. In addition, you can select an option in the Type drop-down list to view the number of tasks or objects based on the mechanism used to gather statistics, for example, automatic, manual, or both.

  • Statistics gathering task list: Displays the list of optimizer statistics gathering tasks over the last seven days, along with the following information for each task:

    • Task name: Name of the optimizer statistics gathering task.

    • Status: Status of the optimizer statistics gathering task.

    • Scope: Scope of the optimizer statistics gathering task, such as Database, Schema, Index, or Table.

    • Type: Type of optimizer statistics gathering task: Auto or Manual.

    • Target: Target on which the optimizer statistics gathering task was run.

    • Objects: Total number of database objects scanned and the number of database objects for which the optimizer statistics gathering task is completed.

    • Duration: Duration of the optimizer statistics gathering task.

    • Start time: Start time of the optimizer statistics gathering task.

    You can use the drop-down lists and fields above the list of optimizer statistics gathering tasks to filter the tasks by a specific type or status, or to search for a specific task. You can also click a particular task status bar in the Statistics gathering tasks bar chart to view the list of optimizer statistics gathering tasks run on that particular day. Note that this capability is not available if the Object status is displayed in the Statistics gathering tasks bar chart.

View Optimizer Statistics Gathering Task Details

Click the name of the task in the Statistics gathering task list to go to the Statistics gathering task details page. Note that you must have the following privileges to view the details of the optimizer statistics gathering task:

  • ANALYZE ANY
  • ANALYZE ANY DICTIONARY

The following tiles are displayed on the Statistics gathering task details page:

  • Statistics gathering task information: Displays the information pertaining to the optimizer statistics gathering task.
  • Statistics gathering sub-task status: Displays the status of the sub-tasks performed as part of the optimizer statistics gathering task, in a pie chart.
  • Statistics gathering sub-task status by object type: Displays a break down of the status of the sub-tasks by database object type in a bar chart. Each bar in the chart denotes a database object type and the color denotes the status of the sub-task.
  • Statistics gathering sub-tasks: Displays a list of sub-tasks run as part of the optimizer statistics gathering task.

Analyze Optimizer Statistics Advisor Tasks and Implement Recommendations

You can monitor and analyze Optimizer Statistics Advisor tasks and implement Optimizer Statistics Advisor recommendations.

Note

For Autonomous Databases, Optimizer Statistics Advisor is disabled by default and the Advisor tab does not display data.

To verify if Optimizer Statistics Advisor is enabled for Autonomous Databases, run the following query:

select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;

To enable Optimizer Statistics Advisor for Autonomous Databases, run the following query:

exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','TRUE');

The following tiles are displayed on the Advisor tab of the Optimizer statistics section, and provide an overview of the Optimizer Statistics Advisor tasks:

  • Task summary: Displays the status of the Optimizer Statistics Advisor tasks executed over the last seven days.

  • Advisor tasks: Displays the status and number of the Optimizer Statistics Advisor tasks executed each day, over the last seven days. Each bar in the chart denotes the number of tasks executed on a particular day and the color denotes the status. By default, this bar chart provides an overview of the number of automatic Optimizer Statistics Advisor tasks broken down by status. Select an option in the Type drop-down list to view the Optimizer Statistics Advisor tasks based on the mechanism used to gather them, for example, automatic, manual, or both.

  • Advisor task list: Displays the list of Optimizer Statistics Advisor tasks over the last seven days, along with the following information for each task:

    • Execution name: Name of the execution of the Optimizer Statistics Advisor task.

    • Task name: Name of the Optimizer Statistics Advisor task.

    • Status: Status of the Optimizer Statistics Advisor task.

    • Type: Type of Optimizer Statistics Advisor task: Auto or Manual.

    • Findings: Findings of the Optimizer Statistics Advisor task.

    • Duration: Duration of the Optimizer Statistics Advisor task.

    • Start time: Start time of the Optimizer Statistics Advisor task.

    • Message: Message, if any.

You can use the drop-down lists and fields above the list of Optimizer Statistics Advisor tasks to filter the tasks by a specific type or status, or search for a specific execution or task. You can also click a particular task status bar in the Advisor tasks bar chart to view the list of Optimizer Statistics Advisor tasks executed on that particular day.

Implement Optimizer Statistics Advisor Recommendations

Click the execution name of the Optimizer Statistics Advisor task in the Advisor task list to go to the Optimizer statistics advisor details page.

The following tiles are displayed on the Optimizer statistics advisor details page:

  • Optimizer statistics advisor information: Displays the information pertaining to the Optimizer Statistics Advisor task.

  • Optimizer statistics advisor findings: Displays the findings of the Optimizer Statistics Advisor task along with the following information:

    • Rule: Oracle-supplied standard by which Optimizer Statistics Advisor performs its checks.

    • Finding: Findings provided by the Optimizer Statistics Advisor.

    • Recommendation: Recommendations provided by the Optimizer Statistics Advisor.

    • Rationale: Rationale for the recommendation provided by the Optimizer Statistics Advisor.

    • Details: Click the Actions icon (Actions) to view more details about the finding and an example of the script that can be used to implement the recommendation.

After reviewing the findings on the Optimizer statistics advisor details page, you can create a job to implement all the recommendations.

Note

If the database user does not have the required privileges, the findings may not be displayed on the Optimizer statistics advisor details page and the Implement all recommendations button may not be available even if there are findings for the task. It's recommended that you set the Advanced diagnostics credential to a user with the required privileges. For information, see Set Preferred Credentials in Database Management.

To implement the Optimizer Statistics Advisor recommendations:

  1. Click Implement all recommendations in the Optimizer statistics advisor findings section.
  2. In the Implement all recommendations panel:
    1. Provide the following information to create a job to implement the recommendations:
      1. Job name: Review the auto-populated name of the job and make changes to it, if required.
      2. Job description: Review the auto-populated description of the job and make changes to it, if required.
    2. Specify job parameters:
      1. Credential type: Select one of the available options to specify database credentials to connect to the Managed Database. For information on credential types, see Use Credentials to Perform Diagnostics & Management Tasks.
        Note

        The specified user must have the following privileges to submit the job to implement recommendations:
        • ANALYZE ANY
        • ANALYZE ANY DICTIONARY
      2. Bucket name: Select the Oracle Object Storage bucket in which the job output will be stored. The Oracle Object Storage bucket can be in the same compartment as the job or in another compartment, and you must have the required Oracle Cloud Infrastructure Object Storage service permissions to select the bucket. For more information, see Additional Permissions Required to Use Diagnostics & Management.
    3. Optionally, click Show SQL to view the SQL statement that will be executed.
    4. Click Implement all recommendations.

All the Optimizer Statistics Advisor recommendations will now be implemented and you can monitor this job in the Jobs section on the Managed database details page.