Database performance recommendations
BMC provides the following recommendations to optimize the performance of databases serving BMC Server Automation:
Oracle database maintenance
Recommendations for gathering statistics
Although Oracle 10g and Oracle 11g provide an automatic job to gather statistics in the entire database, BMC strongly recommends that you exclude the BMC BladeLogic database schema from that job and instead use the statistics gathering package provided with BMC BladeLogic. Details about the package are described in the following sections.
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.
The following table presents more information about the various scripts available in BMC Server Automation for performing these procedures:
|Objective||Details on BMC Server Automation scripts|
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 BBSA<version>-<platform>.zip. For information about this file, see Downloading the installation files.
Note: Before you run the script, ensure that you have met the following prerequisites:
|Gathering statistics at the time of invocation|
To gather statistics immediately, perform any of the following:
The following list describes the stored procedure input 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.
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
To see if statistics are current, you can access the Health and Value Dashboards and review the dates of database statistics there.
Alternatively, 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.
To view the results of the diagnostic, run the
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.
|(Oracle 12c only)||2 x |
In addition, if you have an Oracle 12c database, to improve the performance of database queries from BMC Server Automation, use the following two database parameters:
- optimizer_adaptive_plans (default TRUE)
- optimizer_adaptive_statistics (default FALSE)
For more information, see optimizer-adaptive-features-in-oracle-database-12c-release-2 in Oracle documentation.
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).
SQL database maintenance
As with other products which use Microsoft SQL database, some performance problems in a BMC Server Automation environment can be caused by stale SQL 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 core BMC Server Automation database one time per week.
EXEC sp_updatestats command to run the SQL server built-in procedure against all tables in the schema, to gather statistics and optimize the database. For more information on gathering statistics, see Microsoft documentation.
Gathering statistics is a resource-intensive task and you may experience a fall in database performance while executing this task.
You can also refer to the Health and Value Dashboard for additional statistics about the database server and information you can use for troubleshooting problems. To access the dashboard, see Using the Health and Value Dashboards.