|Applies To||Affected Versions: 4.X; 3.6.X|
Versions 5.x and Version 6.x allow for the database statistic time to be edited thru the Application UI by logging in as an Admin user and navigating to Collectors->Scheduling->Tasks.
|Issue||How to change the time that the application internal database statistics is run.|
This changed from earlier versions. In V3 and V4, this needed to be changed manually.
In V5+, it can be done via the application UI.
Oracle Database Statistics
Oracle database statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement. Because the objects in a database can be constantly changing, statistics must be regularly updated so that they accurately describe these database objects.
Proper management of DB statistics have consistently shown improved performance over a system with no, or invalid DB statistics. Maintaining up to date statistical information about the database contents should improve the general system performance and the overall user experience. ACM 3.6.1+HF5201 introduces a number of enhancements to properly manage Oracle DB statistics and keep them as current as possible.
The current ACM implementation is as follows:
Confirming up-to-date information
Note: the sql queries shown here are intended to be run as the Applicaition Oracle user, AVUSER.
The following query is used to confirm the statistics information for the various tables showing the number of rows expected in each table and the timestamp when this information was computed. Note that certain tables will show null values if this query is run during collection or review operations as these are temporarily set to null to help performance and will be recomputed at the end of that (collection or review) operation.
SELECT table_name, to_char(last_analyzed,'DD-MON-YYYY HH:MI:SS') , num_rows, avg_row_len
Post 3.6 releases will display this information as part of the Admin/Monitoring tab.
Scheduling the batch refresh operation
By default, the batch refresh operation is scheduled nightly, 30-minutes past midnight. If this start time conflicts with any other scheduled collection or review jobs, use the instructions below to modify this schedule.
To view the scheduled time, go to the Admin/Scheduling base page and scroll down to the Database Statistics entry.
Disabling and enabling this feature
While various tests have consistently shown performance improvements with statistics enabled, there may be instances where collectors or other operations seem to ‘hang’ or take a longer time with the only short-term work-around is to disable statistics. Note however that the correct resolution for these cases is to analyze the issue in detail in order provide fixes for those specific problem queries and to re-enable statistics once those issues are resolved.
In ACM 3.6.1, two server-side scripts are provided to disable and enable statistics. Both require that you restart ACM and Oracle to clear all scheduled and cached settings. Run the following command from the database scripts folder.
Note this command may take anywhere between a few minutes to over one hour based on the size of the current database.
Information about the running System
To view the system settings
Run the following query to confirm TRUE or FALSE values for the statistics options in the running system
select * from t_system_settings where upper(parameter) like 'DBS%';
To modify the scheduled task
The refresh statistics task should be scheduled during a time when no other collection or review operations are running. In 3.6.1, this is done as follows: (from KB-1179)
<ruleStartDate>2008-01-01 00:30:00.0 EST</ruleStartDate>
Alternatively, two database entries can be edited to accomplish the same by resetting the default and the current running values. In T_SYSTEM_SETTINGS, edit the value for the DBStatsDatabaseUpdateSchedule parameter. In T_SCHDEULED_TASKS, make the same edit in the RULE column for the gatherDatabaseStatistics entry.
Manually Refreshing statistics
There may be instances where Oracle statistics must be manually refreshed. This can be done using the following commands. Note it is recommended that your first stop any running collectors or review generation/refresh operations prior to executing this command.