000028167 - KB-1274 - Understanding Oracle Database Statistics

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 3Show Document
  • View in full screen mode

Article Content

Article Number000028167
Applies ToAffected 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.
IssueHow 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.
Resolution

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.



Starting in 3.6.1+HF5201, Oracle database statistics are enabled at initial system installation, computed during migration from an older ACM version, refreshed in a nightly scheduled job, and updated after select collection, review operations or a database restore.



This article is intended to present the various tools and queries used to confirm the validity of these statistics and to manually enable or disable that feature if needed. ACM statistics handling prior to 3.6.1 is recapped in KB-1096 - Reviews running slow


Implementation Overview


The current ACM implementation is as follows:


  • Database statistics are computed after a system installation and during the database migration.
  • Statistics for select database tables are refreshed after major load and content changes operations, specifically after collections (ADC/IDC/EDC/RDC) and review generation and refresh actions.
  • A nightly scheduled batch job handles the refresh of all other tables.

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 
FROM user_tables where tablespace_name is not null order by table_name;

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.


Disabling statistics


 


cd /home/oracle/database/DBA/AVDB/scripts
./AVDB_Disable_Schema_Stats.sh
<restart acm and oracle>

Enabling statistics


Note this command may take anywhere between a few minutes to over one hour based on the size of the current database.
cd /home/oracle/database/DBA/AVDB/scripts
./AVDB_Enable_Schema_Stats.sh
<restart acm and oracle>

 


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)


  • Edit the script and modify the schedule entry as needed
  • Run the script
  • Restart ACM
<com.aveksa.common.scheduler.rules.CronRule>
<year>*</year>
<month>*</month>
<dayOfMonth>-</dayOfMonth>
<dayOfWeek>*</dayOfWeek>
<hour>0</hour>
<minute>30</minute>
<ruleStartDate>2008-01-01 00:30:00.0 EST</ruleStartDate>
</com.aveksa.common.scheduler.rules.CronRule>

 

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.
begin
  Database_Statistics.GATHER_DATABASE_STATISTICS;
end;
/
show error

Attachments

    Outcomes