R12: Receivables Autoinvoice performance too slow

Solution 

Performance issues are usually caused by multiple problems. The root cause can be product related, issues can also be due to hardware constraints, network problems, set up issues or other factors. Properly completing the setup steps shown in this document can help rule out improper or incomplete setup as a cause for any performance problem you may encounter.

Manage Receivables System Options

The settings in System Options that impact AutoInvoice are under the Trans and Customers tab.
a.  Set Purge Interface Table = Y

The interface tables are not meant to store data beyond processing transactions in AutoInvoice. This setting only purges data that has been processed.  Data resulting in error can still be corrected and re-processed.

b.  Set Max Memory (in bytes) = Suggested 3MB (3145728 bytes)

This is the maximum amount of memory used for AutoInvoice validation.

c.  Log File Message Level = 0

Setting the log file message level to 0 provides the least detailed debug messages but still includes errors and warning messages.  After you have completed the setup of AutoInvoice, you should need only minimal messages in the log file.

Tuning Segments
d.  Accounting Flexfield

The Oracle Receivables AutoInvoice Setup Diagnostic Test will show your Accounting Flexfield.  You should use a segment that has already been indexed in the GL_CODE_COMBINATIONS table when your Accounting Flexfield was created.  If you are using concatenated indexes, use the first column of the concatenated index as the tuning segment. 

The indexes on GL_CODE_COMBINATIONS are also included in the AutoInvoice Setup Diagnostic Test.  Typically, the Account Segment will contain the most distinct values of the flexfield and should therefore be used as the tuning segment.  Alternatively, you can choose a segment in your Accounting Flexfield which contains the most distinct values. You can use a query similar to the following to determine which segment has the most distinct values.

SELECT COUNT( DISTINCT segment1),
COUNT( DISTINCT segment2),
COUNT( DISTINCT segment3),
COUNT( DISTINCT segment4),
COUNT( DISTINCT segment5)
FROM GL_CODE_COMBINATIONS; 

Note: This query assumes you are using 5 segments for your Accounting Flexfield. You should modify the query, as needed, if your Accounting Flexfield has more (or less) segments.

e.  System Items

The Oracle Receivables AutoInvoice Setup Diagnostic Test shows indexes on MTL_SYSTEM_ITEMS_B.  You should use a segment that has already been indexed in the MTL_SYSTEM_ITEMS_B table.  If you are using concatenated indexes, use the first column of the concatenated index as the tuning segment.

Typically, the Item Segment will contain the most distinct values of the flexfield and should therefore be used as the tuning segment.  Alternatively, you can choose a segment in your System Items Flexfield which contains the most distinct values. You can use a query similar to the following to determine which segment has the most distinct values.

SELECT COUNT( DISTINCT segment1),
COUNT( DISTINCT segment2),
COUNT( DISTINCT segment3),
COUNT( DISTINCT segment4),
COUNT( DISTINCT segment5)
FROM MTL_SYSTEM_ITEMS_B;

Note: This query assumes you are using 5 segments for your System Items Flexfield. You should modify the query, as needed, if your System Items Flexfield has more (or less) segments.

f.  Territory

The Oracle Receivables AutoInvoice Setup Diagnostic Test shows indexes on RA_TERRITORIES.  You should use a segment that has already been indexed in the RA_TERRITORIES table.  If you are using concatenated indexes, use the first column of the concatenated index as the tuning segment.

Typically, the Country Segment will contain the most distinct values of the flexfield and should therefore be used as the tuning segment.  Alternatively, you can choose a segment which contains the most distinct values.  You can use a query similar to the following to determine which segment has the most distinct values.

SELECT COUNT( DISTINCT segment1),
COUNT( DISTINCT segment2),
COUNT( DISTINCT segment3),
COUNT( DISTINCT segment4),
COUNT( DISTINCT segment5)
FROM RA_TERRITORIES;

Responsibility:  Receivables Manager
Navigation:  Setup > System > System Options > Trans and Customers Tab

Manage System Profile Options

a.  AR:  AutoInvoice Gather Statistics

The recommended setting is Y (Yes) at the Site level.  Setting this profile will gather statistics on the interface and appropriate application tables before running AutoInvoice.

Responsibility: System Administrator
Navigation: Profile > System
Query: AR: AutoInvoice Gather Statistics

b.  AR: Maximum Lines Per AutoInvoice Worker

This profile option should be set if you are running AutoInvoice Master with multiple workers and your transactions typically have a large number of lines. Setting this profile option will help AutoInvoice determine the maximum number of lines to assign to an AutoInvoice worker.

For example, if you typically process invoices with approximately 10,000 lines per invoice, then set the value = 10,000. If you are unsure, or if the number of lines per transaction are low or varied, you should not enter a value for this profile option.

Responsibility: System Administrator
Navigation: Profile > System
Query: AR: Maximum lines per AutoInvoice worker

Create Indexes Based On Your Line Transaction Flexfield (LTF)

Please refer to Note 1068344.1, Setting Up Receivables Descriptive Flexfields, for an in-depth look at setting up Descriptive Flexfields.

NOTE:  For optimal performance, the following concatenated indexes should be created as UNIQUE indexes.

This example assumes that you are using a context that has 3 segments of the flexfield enabled.  You will need to modify the queries below to include ALL enabled segments of your LTF.

You should create the indexes using SQLPlus as follows:

SQL> CONNECT AR/AR

SQL> CREATE UNIQUE INDEX XX_RA_CUSTOMER_TRX_U1 ON RA_CUSTOMER_TRX_ALL
(INTERFACE_HEADER_CONTEXT,
INTERFACE_HEADER_ATTRIBUTE1,
INTERFACE_HEADER_ATTRIBUTE2,
INTERFACE_HEADER_ATTRIBUTE3);

SQL> CREATE UNIQUE INDEX XX_RA_CUSTOMER_TRX_LINES_U1 ON RA_CUSTOMER_TRX_LINES_ALL
(INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2,
INTERFACE_LINE_ATTRIBUTE3);

SQL> CREATE UNIQUE INDEX XX_RA_INTERFACE_LINES_U1 ON RA_INTERFACE_LINES_ALL
(INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2,
INTERFACE_LINE_ATTRIBUTE3);

You only need to create the following indexes if these tables are being populated with interface data.  If you are not using sales credits or accounting distributions, or if you have set up the application to derive these values, then you do not need to create these indexes.

SQL> CREATE UNIQUE INDEX XX_RA_INTERFACE_DIST_U1 ON RA_INTERFACE_DISTRIBUTIONS_ALL
(INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2,
INTERFACE_LINE_ATTRIBUTE3);

SQL> CREATE UNIQUE INDEX XX_RA_INT_SALESCRDS_U1 ON RA_INTERFACE_SALESCREDITS_ALL
(INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2,
INTERFACE_LINE_ATTRIBUTE3);

Other Considerations

1. The purpose of the Line Transaction Flexfield is to uniquely identify transaction lines. Therefore, the indexes should be created as UNIQUE indexes. However, AutoInvoice does not validate or require a unique LTF. If you receive errors stating that the indexes cannot be created because of duplicate keys, then you should create non-unique indexes by removing the UNIQUE constraint from the CREATE INDEX statement. Unique indexes will provide better performance results.
2. Consider using NOLOGGING for these indexes if ARCHIVING is enabled on your database. NOLOGGING should reduce the amount of time to create the index and will save space in the redo log files. Indexes created using NOLOGGING are not archived. Therefore, you should perform a backup after you create the indexes. Your DBA should review and advise whether NOLOGGING is appropriate for your environment.

(Reference article id 1083467.1)