Revert to DBStats batch processing for database statistics

You must perform a sequence of manual steps if you want to revert to using the table statistics preferences set in database-config.xml rather than using Oracle AutoTask infrastructure to manage the task of collecting table statistics. 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, which can be either a full database upgrade or a rolling configuration update. However, if you attempt to reset this attribute from true to false, PolicyCenter throws an exception during the next upgrade and prevents the upgrade from continuing due to locked statistics in certain Oracle database tables. Review the exception log detail to determine which tables statistics need to be unlocked.

Procedure

Reset attribute useoraclestatspreferences to false in file database-config.
Start the application server in upgrade mode.
The upgrade fails due to locked table statistics in the Oracle database.
Review the server log for which table statistics need to be unlocked.
Search for text that is similar to the following:
```
com.guidewire.pl.system.exception.UpgradeException: The following tables have locked statistics...
```

Unlock the specified table statistics.

The following example code illustrates what SQL commands the BillingCenter database DBA needs to execute.

SET serverout on timing on
DECLARE
CURSOR locked_tables_cur IS SELECT table_name
FROM user_tab_statistics
WHERE STATTYPE_LOCKED IS NOT NULL;
str VARCHAR2(256);
BEGIN
FOR locked_tables IN locked_tables_cur
LOOP
str:= 'EXEC DBMS_STATS.UNLOCK_TABLE_STATS(USER, ''' || locked_tables.table_name|| ''')' ;
dbms_output.put_line(str);
EXECUTE IMMEDIATE str;
END LOOP;
END;
/

This step is mandatory. Otherwise, the PolicyCenter upgrade fails.

Delete the current table statistics preferences. The following example code illustrates what SQL commands the BillingCenter database DBA needs to execute.

EXEC DBMS_STATS.DELETE_SCHEMA_PREFS('PCUSER', 'NO_INVALIDATE'); 
EXEC DBMS_STATS.DELETE_SCHEMA_PREFS('PCUSER', 'CASCADE'); 
EXEC DBMS_STATS.DELETE_SCHEMA_PREFS('PCUSER', 'STALE_PERCENT'); 
EXEC DBMS_STATS.DELETE_SCHEMA_PREFS('PCUSER', 'ESTIMATE_PERCENT'); 
EXEC DBMS_STATS.DELETE_SCHEMA_PREFS('PCUSER', 'DEGREE'); 
EXEC DBMS_STATS.DELETE_SCHEMA_PREFS('PCUSER', 'METHOD_OPT');

Note: Replace PCUSER with the actual PolicyCenter database user.

Restart the application server in upgrade mode.
After the application upgrade completes, schedule the execution of DBStats batch processing to collect database table statistics.