Run Scripts to Gather Schema Statistics for the 12c or 19c Optimizer

After upgrading to Release 5.3.1 and setting initialization parameter optimizer_features_enable to 12.1.0.2 or 19.1.0.0 (see Set Initialization Parameters), you must gather statistics required for the Oracle Optimizer to be effective for accounts used internally by TMS.

Scripts opastats.sql and tmsstats.sql are available for this purpose. Failure to execute these scripts can negatively impact performance.

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 the scripts' parameters see documentation of the Oracle DBMS_STATS package that they call: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036456.

tmsstats.sql: Because 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;".

   exec tms_user_analyze.AnalyzeTables

3. Run tmsstats.sql.

opastats.sql captures new statistics in the OPA application account used by TMS. It prompts for the OPA password. It is also used by Oracle Clinical and RDC Onsite.