Optimizing an Oracle database for BMC Server Automation

BMC provides the following recommendations to optimize the performance of an Oracle database serving BMC Server Automation:

• Recommendations for gathering statistics
•  Recommendations for configuring the database
•  Recommendations for physical implementation

Recommendations for gathering statistics

As with other products which use an Oracle database, some performance problems in a BMC Server Automation environment can be caused by stale Oracle statistics on the BMC Server Automation schema, due to changing data volumes or changes in column values.

BMC strongly recommends that you gather statistics for the reports data warehouse and the core BMC Server Automation database and then clear the query caches after each data warehouse load.

To facilitate the running of the statistics gathering command, BMC Server Automation has a stored procedure. After the stored procedure is created in the BMC Server Automation schema, you can easily execute the procedure to gather statistics on a regular basis. BMC recommends that you gather statistics one time per week. Running the procedure more frequently than one time per week does not yield any additional performance improvement.

The statistics gathering procedure supplied by BMC Server Automation:

• Collects Oracle optimizer statistics on the tables and indexes for the BMC Server Automation database at regular intervals.
• Generates a PL/SQL script to collect statistics, which can be reviewed and modified (if necessary) before execution.

Location of the BMC Server Automation statistics gathering package

The statistics gathering stored procedures are located in the ..\db_scripts\oracle\utility\gather_schema_stats folder, which is created when you extract external-files.zip. For information about this file, see Obtaining-the-installation-files. The folder contains the following items:

• bl_gather_schema_stats_sp.sql - Loads the BL_GATHER_SCHEMA_STATS stored procedure into the BMC Server Automation database; the procedure can either gather the statistics at the time of invocation, or send out PL/SQL commands for statistics gathering. The stored procedure is loaded automatically during the product installation or upgrade, so you do not have to manually load it.
• gather_bl_schema_stats.sql - A sample script that illustrates how to invoke the stored procedure to collect statistics immediately.
• generate_gather_schema_stats_script.sql - A sample script that illustrates how to invoke the stored procedure to generate a list of PL/SQL commands to gather statistics.
• readme.txt - A detailed read-me file describing logic, usage, and so on.

Gathering statistics at the time of invocation

To gather statistics immediately, perform any of the following:

• Invoke the BL_GATHER_SCHEMA_STATS stored procedure directly from a PL/SQL block, or from any piece of code that is capable of calling Oracle stored procedures.

• Run the sample script gather_bl_schema_stats.sqlin SQL*Plus. You can either execute the script as is, or edit the script to modify the stored procedure input parameters. The following is an example of script execution.

  SQL>@gather_bl_schema_stats.sql
  Finished collecting statistics. Time elapsed: +00 00:01:43.7780
  

  The following table describes the stored procedure input parameters.

   gather_bl_schema_stats.sql parameters

Generating a script to gather statistics at later time

Instead of gathering statistics when you invoke the stored procedure, you might want to generate a PL/SQL script that can be reviewed, edited and executed at later time.

The sample script generate_gather_schema_stats_script.sql provides an illustration of how to use a script to gather statistics at a later time. You can use the sample script as is or modify it to better suit your needs. The sample script creates a statistics collection script with an automatically generated name in the following format:
gather_schema_stats_autogen_YYYY_MM_DD_HH24_MI_SS.sql.

1. To generate a new statistics collection script that you can execute at a later time, execute the generate_gather_schema_stats_script.sqlscript. The following example shows how to execute the script and illustrates the information that is returned upon execution.

   SQL> @generate_gather_schema_stats_script.sql
   Successfully generated stats collection script:
   gather_schema_stats_autogen_2010_02_19_17_44_32.sql
   
2. Review the script to check the statistics collection settings for the various tables.
3. Execute the script to gather statistics at a later time, such as during the maintenance window or during light system usage:

Running gather statistics from the blcli

To run the gather statistics procedures from the BLCLI (for example, from a scheduled NSHScript Job), you can use the BLCLI command DBManager-blGatherSchemaStats command.

Verifying that statistics are current

You can run a database diagnostic to see if the DBMS_STATS on the Oracle schema are stale based on a parameter that you specify. If the previous run of DBMS_STATS is older than the time frame you specified, you are prompted to run the BL_GATHER_SCHEMA_STATS stored procedure to update the statistics. You can also instruct the diagnostic to perform autoremediation by automatically invoking the stored procedure.

The following example shows the command format you would use to run the DBMS_STATS_CHK diagnostic, while the the following table describes the parameters available for the diagnostic.

To view the results of the diagnostic, run the dbdiagnostics command with the getResLastExec parameter, which displays the results of the last execution for this diagnostic.

For a description of all available database diagnostics, see Monitoring-and-diagnosing-issues-in-the-BMC-Server-Automation-environment.

 Recommendations for configuring the database

The following recommendations are for a 10-GB database. Scale the configuration recommendations up or down based on the relative size of your database.

 Recommendations for physical implementation

To avoid disk contention and increase input-output throughput when accessing data, store the physical data and index data files in different tablespaces (that is, across multiple disks).