Validating the Impact of Pending Optimizer Statistics

Before you gather pending optimizer statistics, you can validate the impact of gathering those statistics on your database workload by using SPA Quick Check.

Note:

You can use SPA Quick Check to validate the impact of gathering pending optimizer statistics in databases running Oracle Database 11g Release 1 (11.1) and later.

To validate the impact of gathering pending optimizer statistics:

  1. On the Database Home page in Cloud Control, from the Performance menu, select SQL, and then Optimizer Statistics.The Optimizer Statistics Console page appears.

  2. In the Operations section, click Gather.The Gather Optimize Statistics wizard appears.

  3. In the Validate with SQL Performance Analyzer section at the bottom of the Gather Optimizer Statistics: Scope page, enable the Validate impact of stats on SQL performance prior to publishing (recommended) option. The database global statistics gathering option PUBLISH will be set to FALSE temporarily during the process. Then click Next.
    Description of the illustration sqc_gather_stats.gif follows
    Description of the illustration sqc_gather_stats.gif

  4. Continue through the wizard, and on the Gather Optimizer Statistics: Scope page, click Submit.Along with gathering pending statistics, this starts a job that creates a SQL Performance Analyzer task that validates the impact of gathering optimizer statistics for the database.

  5. When the job starts, a Confirmation message appears on the Manage Optimizer Statistics page that says that the Gather Optimizer Statistics job has been successfully submitted. Click the link in that message.The SQL Performance Analyzer Home page appears.

  6. In the SQL Performance Analyzer Tasks table at the bottom of the page, make sure that the statistics gathering job has completed. It may take several minutes for the job to complete. Then select the row for the Gather Optimizer Statistics job and click View Latest Report.The SQL Performance Analyzer Task Report page appears.

  7. View the table at the bottom of the page to see what the result of publishing the pending optimizer statistics would be on the most impactful SQL statements in the workload.