Running Scripts to Gather Schema Statistics for the 12c Optimizer

After upgrading from a release prior to TMS 5.3 and setting initialization parameter optimizer_features_enable to 12.1.0.2 you must gather statistics required for the Oracle 12c Optimizer to be effective for accounts used internally by TMS.

Failure to execute these scripts can negatively impact performance.

Scripts opastats.sql and tmsstats.sql, are available for this purpose. If your database contains large amounts of data, the scripts may take a long time to run. You may want to edit the scripts; for information on opastat.sql and tmsstats.sql parameters see the following documentation of the DBMS_STATS package that they call: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036456.

Note:

This information is also available in the Installation Guide. You need to do this only once after installation. You should also do it after loading a dictionary. It is especially important after loading WHODrug Format C.

tmsstats.sql: Since TMS already has a procedure, AnalyzeTable in tmspb_user_analyze.sql, that runs as part of every activation job, you may prefer to edit it if required and call it from tmsstats.sql to keep the two synchronized.

The tmsstats.sql script prompts for the password of the account it processes: TMS.

To change how TMS analyzes tables and ensure that tmsstats and AnalyzeTable remain in synch:

1. Update the "tmspb_user_analyze.sql.lAnalyzeTable" procedure to use different parameters in the call to "dbms_stats.gather_table_stats" to improve the performance in your environment.
2. Edit tmsstats.sql to call tms_user_analyze.AnalyzeTable.

   The package tms_user_analyze and the script tmsstats.sql needs to be in sync. To do so, replace last line "exec dbms_stats.gather_schema_stats;" in tmsstats.sql with "exec tms_user_analyze.AnalyzeTables;".

3. Run tmsstats.sql.

opastats.sql: Captures new statistics in the OPA application account used by TMS. It is also used by Oracle Clinical and RDC Onsite.