Using Oracle AutoTask for statistics generation

The purpose of the useoraclestatspreferences attribute on the <databasestatistics> element in file database-config.xml is twofold:
  • To set the table statistics preferences according to those set for Oracle in file database-config.
  • To have the Oracle AutoTask infrastructure manage the task of collecting table statistics, based on preferences that Guidewire sets in the base configuration.
To set Oracle to collect table statistics, set this attribute value to true, for example:
<database name="ExampleDatabase" dbtype="oracle" env="oracle" printcommands="true">
  <databasestatistics databasedegree="4" useoraclestatspreferences="true">
  ...

Any change to this attribute value takes effect only during an upgrade, either a full upgrade or a rolling (configuration) upgrade. To force PolicyCenter to recognize the change without an application upgrade, increment the application metadata version and restart the application server.

After you set this attribute to true, the next application upgrade does the following:
  • It clears all existing preferences for table statistics.
  • It resets the preferences for table statistics to those currently defined for Oracle table statistics in database-config.xml.
  • It creates a new tab named Oracle Statistics Preferences in the Server Tools Info Pages > Database Catalog Statistics Information screen.

To confirm that the application upgrade set the database statistics preferences, review the Oracle Statistics Preferences tab and verify the preferences.

Enabling Oracle AutoTask

after you set this parameter to true for the first time, do the following:
  • Ensure that the statistics target setting has the default value of AUTO.
  • Ensure that you enable Oracle AutoTask for statistics collection.
EXEC dbms_stats.set_global_prefs('AUTOSTATS_TARGET','AUTO');
EXEC dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => NULL, 
      window_name => NULL);

Gathering new statistics

If you enabled the database before you set useoraclestatspreferences to true, you need to delete the existing schema statistics and gather new statics. The Oracle DBA can use the following SQL commands for this task.
EXEC dbms_stats.delete_schema_stats('PCUSER');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'PCUSER', options=>'GATHER');
Note: Replace PCUSER with the actual PolicyCenter database user.

After the application upgrade

After you perform an application upgrade with useoraclestatspreferences set to true, there is no longer any need to run DBStats batch processing. At this point, Oracle AutoTask automatically handles statistics collection during the maintenance windows defined for the database. To disable DBStats batch processing, remove its schedule from scheduler-config.xml.

Future application upgrades

For future application upgrades, Guidewire recommends that you consider setting the updatestatistics attribute on the <upgrade> element in database-config.xml to true, for example:
<upgrade degree-parallel-ddl="6" degree-of-parallelism="6" ora-parallel-dml="enable_all" 
      updatestatistics="true">

Setting the updatestatistics attribute to true allows the PolicyCenter upgrader to create any additional histograms required by the new application version.

For more information, refer to the following Oracle documentation:

Resetting useoraclestatspreferences

Any change to the useoraclestatspreferences attribute in database-config.xml (from false to true or from true to false) takes effect only after an upgrade, either a full upgrade or a rolling (configuration) upgrade. However, if you reset this attribute from true to false, PolicyCenter throws an exception during the next upgrade and prevents the upgrade from continuing due to locked table statistics in the Oracle database. Review the details of the exception provided in the server log to determine which table statistics need to be unlocked. See Revert to DBStats batch processing for database statistics for information on how to unlock the table statistics.

See also