Whitepaper: The WHY of setting up Autoinvoice options for performance improvement

My earlier post http://plsql-apps.blogspot.co.at/2013/07/basic-pre-requisites-setups-to-decrease.html details about the different set of options to tackle and improve autoinvoice performance. This post details the in depth information on why these values need to be set and what are the default values that Oracle comes seeded with.

-       Setting purge interface table = Y

    With this option set to Yes, the processed records in the interface table is purged after each autoinvoice run automatically. By default or when set to No the purge of records does not happen until the purge concurrent program is run 

-       Set Max Memory (in bytes) = Suggested less than 3MB (3145728 bytes)

The default is 65535 bytes. Enter a lower number if AutoInvoice displays the message 'Failed to allocate memory for scratch_memory.' Enter a higher number if AutoInvoice displays the message 'The given piece of memory is not large enough to hold a single row.'  if you use AutoInvoice to import no more than 100 invoices at a time, enter a value of 102400. This is the amount of space that is allocated for autoinvoice validation step only.

-       Log File Message Level = 0

This is the amount of log messages to be printed during the autoinvoice process, higher the number slower the autoinvoice process and higher the number of messages to debug.

-       Tuning Segments

o    Accounting Flex

o    System Items

o    Territory

Autoinvoice uses the above segments frequently for invoice processing, in order to improve performance during runtime the segments from these flexfields can be tuned for the above set. For ex. RA_INTERFACE_DISTRIBUTIONS_ALL table consists of both code_combination_id and segments(n) column, during runtime all lines are updated with the segment values. By defining a tuning segment in the setup these column values are tuned for better performance. 

Oracle Application Setup: Manage System Profile Options

-       AR:  AutoInvoice Gather Statistics

When you submit the AutoInvoice Master program, AutoInvoice can first analyze the interface tables (RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL, and RA_INTERFACE SALESCREDITS_ALL) and automatically gather statistics to determine how best to execute the transaction import. If you want AutoInvoice to automatically gather statistics, then set this profile option to Yes. If the number of records to be imported and the number of worker processes are approximately the same as the previous submission of AutoInvoice, then you may set the profile option to No and skip this analysis.

-       AR: Maximum Lines Per AutoInvoice Worker

The default value for this profile option is 100000. This profile option allows you to give AutoInvoice a "tip" on how it should group the records so that all lines for an invoice can be assigned to a single or multiple workers. Here worker means number of spawned programs to group the number of records. If the profile option is set to 10,000 lines per worker, and an invoice has more than 10,000 lines, then all the lines on the invoice would be assigned to a single worker.

Sequence of grouping and assigning to workers:

-       Transaction lines are ordered and grouped by the grouping and line ordering rules defined.

-       After which the below grouping is done to assign to workers, if none of the below value changes then the records are processed by a single worker regardless of the maximum lines per worker else the profile option is used to group and to be processed by multiple workers

org_id || '~' || \n\
batch_source_name || '~' || \n\
orig_system_bill_customer_id || '~' || \n\
orig_system_bill_customer_ref || '~' || \n\
orig_system_bill_address_id || '~' || \n\
orig_system_bill_address_ref || '~' || \n\
NVL(cons_billing_number, trx_number) || '~' || \n\
NVL(cons_billing_number, purchase_order) || '~' || \n\
NVL(cons_billing_number, currency_code) || '~' || \n\
NVL(cons_billing_number, conversion_type) \n\ 

Truncate Interface Tables Vs. Delete Records

There is a need to frequently truncate tables.  TRUNCATE resets the table definition to an empty table by resetting the high water mark. This could increase the performance of autoinvoice as it releases the occupied blocks and table scan can be performed effectively. Whereas a delete does not relinquish segment space thus a table in which all records have been deleted retains all of its original blocks.