Run Scripts to Gather Schema Statistics for the 19c Optimizer
After upgrading to release 5.4.1 and setting initialization parameter optimizer_features_enable to 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:
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.