Database statistics generation for Oracle databases

There are several different ways in which to generate database statistics in Guidewire PolicyCenter if using an Oracle database:
  • Use the Oracle Autotask infrastructure to manage the task of gathering database table statistics.
  • Run PolicyCenter batch processing DBStats periodically to collect database table statics.
You enable the use of each method by setting the value of the useoraclestatspreferences attribute on the <tablestatistics> element in file database-config.xml.
use... attribute Statistics Description More information
true Oracle AutoTask Set AUTOSTATS_TARGET parameter to AUTO. Using Oracle AutoTask for statistics generation
false (Default) DBStats batch processing Set AUTOSTATS_TARGET parameter to ORACLE. Database Statistics work queue
Note: A change in the value of useoraclestatspreferences takes effect only during an application upgrade.
Note: Due to Oracle Bug 27818902, using Oracle AutoTask to gather database statistics can create certain issues in Guidewire PolicyCenter. See the Major issues and changes section of the Release Notes for details.

Using DBStats batch processing

If using DBStats batch processing to manage the collection of database statistics:
  • Do not execute Oracle dbms_stats manually.
  • Manually execute, or schedule, DBStats batch processing.
  • Set AUTOSTATS_TARGET parameter to ORACLE using the DBMS_STATS.SET_GLOBAL_PREFS procedure. This action ensures that the automated task 'auto optimizer stats collection' gathers statistics for the Oracle Dictionary only.

See also Disable automatic database statistics generation by Oracle.

Guidewire recommendations for Oracle database installations

  • Guidewire recommends that Oracle implementations only update database statistics during quiet periods, such as weekends or nights, so that these updates do not occur while PolicyCenter is under heavy load. By default, updating statistics on a table or index invalidates existing query plans related to that table or index.
  • Guidewire recommends that Oracle implementations use the NO_INVALIDATE => AUTO_INVALIDATE option while updating database statistics. This is the default option. This option is also what the Guidewire Database Statistics batch process uses, unless the configuration parameter DiscardQueryPlansDuringStatsUpdateBatch is set to true.

    Setting NO_INVALIDATE => FALSE to force immediate invalidation of query plans has a high likelihood of causing issues with concurrent batch updates. Using AUTO_INVALIDATE greatly reduces this risk. Ideally, set the _optimizer_invalidation_period parameter to a low value (a few minutes) to reduce the time window during which Oracle might invalidate a plan.