Database performance recommendations

Location of the TrueSight 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 TSSA<version>-<platform>.zip. For information about this file, see Downloading-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 TrueSight 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.

Note: Before you run the script, ensure that you have met the following prerequisites:

• Access to a fully operational computer with Oracle SQL*Plus installed that can connect remotely or locally to the TrueSight Server Automation database.
• Oracle schema owner credentials for the TrueSight Server Automation database.

• You can also run scripts and invoke the stored procedure as a user with DBA privileges. In this case, you must know the name of the schema owner for the TrueSight Server Automation database, and you must change the current schema before you run scripts or invoke the stored procedure using the ALTER SESSION SET CURRENT_SCHEMA command. For example:

  SQL> ALTER SESSION SET CURRENT_SCHEMA="BLADELOGIC";
  SQL> @gather_bl_schema_stats.sql

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.sql in 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 list describes the stored procedure input parameters.

• p_threshold_rows - Threshold based on the number of rows, the default is one million rows.

• p_threshold_bytes - Threshold based on the table size, the default is one hundred mega bytes.

• p_no_invalidate - Indicates whether to invalidate the child cursors currently cached in the SGA.

• p_print_only - Instead of gathering statistics, send out PL/SQL commands for each table (this parameter requires serveroutput to be enabled on the client and set to unlimited size).

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.sql script. 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:
   

   SQL> @gather_schema_stats_autogen_2010_02_19_17_44_32.sql
   Started stats collection at 2010-02-19 17:44:43
   Finished stats collection at 2010-02-19 17:46:05

Running gather statistics from the blcli

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. 

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

• STATSExpiresDays - Sets the time frame, in days, for the recommended frequency for updating statistics. The default value is 15 days.
• STATSRemediateFlg - Controls autoremediation (execution of the of BL_GATHER_DBMS_STATS PROC script) to update the Schema statistics. Enter Y to turn on autoremediation; enter N to turn it off. The default value is N. Note: Remediation of the statistics could take a considerable amount of time, based on the size of the database on which the script is executed. Before executing the autoremediation of Schema STATS, consult with your DBA. If you use the Oracle STATS GATHER Package for database statistics, BMC recommends that you replace the automatic Oracle job for gathering statistics with the TrueSight Server Automation BL_GATHER_SCHEMA_STATS package.

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-TrueSight-Server-Automation-environment.

1 GB

512 KB

360 MB

2 GB

(Oracle 12c only) pga_aggregate_limit

2 _^x pga_aggregate_target