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\
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.
