Performance problem, some queries are slow. Why it is important to run the Oracle Table Analyze Job?

Document ID : KB000024021
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Here you can have more information about the Oracle Table Analyze Job. Actually the recommendations are to set this job scheduled one time per week during the off-peak hours /or weekends in order to improve database performance.

 

Solution:

This job runs basic database optimization commands and is optimizing the database statistics and reducing the time to open the queries, thus improving general performance.

We recommend setting it scheduled automatically one time per week.

Explanation:

Analyze the Oracle Database Schema.

When running SQL statements against an Oracle database, the Oracle engine needs to decide how a query (select/insert/update/delete) should be executed. It uses what it calls an Optimizer to determine what the execution plan is to be. There are different optimizer modes that are available. The required mode for CA PPM is CHOOSE. This is the Oracle-recommended mode and is the default value.

Optimizer mode CHOOSE relies on statistics about tables and indexes being available. It uses those statistics to determine the best path of execution for a query. Those statistics need to be refreshed under certain circumstances such as when the schema changed or the data volume changed. The process of refreshing those statistics is called analyzing the schema.

You can use one of the following methods to analyze the database schema:

Use the Oracle Table Analyze job

To schedule this job, your CA PPM administrator must have granted you the access right to run this job or you must be part of the Report and Job Administrator group. In addition, you must be part of the Job Users group to get access to the Reports and Jobs page.

It is recommended that you run this job at least once a week during a period of lower user activity, such as on a weekend night.

It is also recommended to update the Estimate Percent under the job parameters to be 100. If left blank, it will run at the default level of 50%. The Estimate percent allows you to increase or decrease the analyze compute rate from PPM.

Use the 'admin db analyze' command line utility

Generally speaking, there is no need to use this method over the job. This method performs the same analyze command that the scheduled job performs. However, during a CA Clarity PPM patch release application, it may be necessary to analyze the database without having CA Clarity PPM up and running.

When you execute the 'admin db analyze' command, it uses a database stored procedure named CMN_JOB_ANALYZE_SP.  

If the Database Administrator elects to do their own maintenance plan on the database, it is important that the database statistics are updated using one of these methods in addition to the maintenance plan.

Note: See the Administration Guide for more information on scheduling jobs.