Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Speeding Up Financials Imports

EXPORT / IMPORT

Oracle Version 7 saw the introduction of an additional EXPORT parameter STATISTICS=

The default however is STATISTICS=ESTIMATE so if you leave this line out of your export parameter file you will generate statistics on import. This cannot be overridden on import.

For some reason the command appears to have changed from ANALYZE= to STATISTICS= so check which one is in force for your version of Oracle by running the following at the command line.

exp help=y
On the very few occasions I have used the default statistics option when exporting (ver 7.0.16) whilst rebuilding a Peoplesoft HR database ) I encountered several ora 600 errors during the import. ( for various reasons this was not reported to Oracle ), I believe this problem has been fixed in subsequent releases.

Given that the cost based optimiser appeared not work as expected ( 7.0, 7.1 releases ) I found setting the optimiser to RULE based optimisation and using STATISTICS = NONE for the export solved the problem. Setting rule based optimisation is achieved by adding the following line in the initSID.ora file. ( Don't forget to restart the database using the new initSID.ora )

optimizer_mode=rule
It should be noted at this time that the Oracle Finacials manuals state that the optimiser should be set to RULE based optimisation, as such any statistics generated will generally not be used by the optimiser. hence, it is a pointless exercise collecting stats on importing. In addition the ananlyze command that is executed during the import will slow the import down.

However: Whilst the Oracle Financials release 10.4 manual ( part number A12999-5 August 1994 ) recommends setting optimisation to RULE when setting up a Financials instance, the manual also indicates that "some" Oracle Applications SQL statements contain optimisation hints and as such the you should run $AD_TOP/sql/ADXANLYZ.sql in order to analyze the applications tables and indexes.

cd $AD_TOP/sql
sqlplus username/password
SQL> @ADXANLYZ.sql
Buried in appendix A of this manual it states that the above command should be run after:
  1. A significant amount of transaction data has been stored.
  2. After an upgrade.
  3. As part of systems maintenance, approximately once a month.
The manual does not however tell you for which applications this command should be run or indeed if all applications require analyzing.

Thus in order to speed up imports add the line STATISTICS=NONE to the export parameter file prior to exporting the database. and as required run ADXANLYZ.sql at a later time. This should speed up your import process.

Exit to : Oracle Hints and Tips