Purging Data in Oracle Applications

Purge Concurrent Request and/or Manager Data Program :

Some times when we submit any request, it waits for about half a minute or a minute for the request to commit, this happens when large amount of records or rows exist in the Concurrent related tables. This type of performance issues can be resolved by running "Purge Concurrent Requests and/or Manager Data" Concurrent program.

The “Purge Concurrent Requests and/or Manager Data” concurrent program will delete information about completed concurrent requests from a set of tables.

It can also delete the log and output files for those concurrent requests from your UNIX file system. Most companies run this report and delete all recordsolder than 7 -31 days.

                                                                (or)

When the tables FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROCESSES has large amount of records or rows, the performance diminishes.

You have to run Purge Concurrent Request and/or Manager Data program on a regular basis depending on the amount of requests being run.

How to run the Purge Concurrent Request and/or Manager Data program :

-------------------------------------------------------------------------------------------------------------------

1. Log in to Application as System Administrator responsibility.

2. Navigate to Request> Run> Single Request

3. Query up Purge Concurrent Requests.

Program options:

-------------------------

ENTITY = ALL   : Purge of concurrent requests, concurrent managers, request log files,
manager log files and report output files. The following tables are purged
-  Fnd_Concurrent_Processes
-  Fnd_Dual
-  Fnd_Concurrent_Requests,
-  Fnd_Run_Requests
-  Fnd_Conc_Request_Arguments
-  Fnd_Dual
-  Fnd_Context_Env
-  Deletes concurrent requests’ log and out files from OS

ENTITY = MANAGER   : Purge of concurrent managers and manager log files.
The following tables are purged    

-  Fnd_Concurrent_Processes
-  Fnd_Dual
-  Deletes concurrent manager log files from OS

ENTITY = REQUEST   : Purge of concurrent requests, request log files and output files.
The following tables are purged
– Fnd_Concurrent_Requests,
– Fnd_Run_Requests
– Fnd_Conc_Request_Arguments
– Fnd_Dual
– Deletes concurrent requests’ log and out files from OS

 Mode                  :  AGE         Number of days.
                            : COUNT     Number of records

Mode Value                :   valid values are 1 – 9999999
User Name                   :   application username
Oracle ID                      :   Oracle ID
Program Application      :   application
Program                       :   program
Manager  Application     :   application associated with the concurrent manager
Manager                       :   concurrent manager
Resp. Application          :   application associated with the responsibility
Responsibility               :   responsibility or “All”.
Report                          :   No       Run the program but do not generate a report.
                                    :   Yes      Run the program and generate a report.

WARNING : The only option which purges all tables is the option “ENTITY = ALL”.
It is better to use this option to synchronise the Concurrent Requests
and Concurrent Processes tables.

Detailed information of the tables that will be purged:

FND_CONCURRENT_REQUESTS
This table contains a complete history of all concurrent requests.

FND_RUN_REQUESTS
When a user submits a report set, this table stores information about the
reports in the report set and the parameter values for each report.

FND_CONC_REQUEST_ARGUMENTS
This table records arguments passed by the concurrent manager to each program
it starts running.

FND_DUAL
This table records when requests do not update database tables.

FND_CONCURRENT_PROCESSES
This table records information about Oracle Applications and operating system
processes.

FND_CONC_STAT_LIST
This table collects runtime performance statistics for concurrent requests.

FND_CONC_STAT_SUMMARY
This table contains the concurrent program performance statistics generated by
the Purge Concurrent Request and/or Manager Data program. The Purge Concurrent
Request and/or Manager Data program uses the data in FND_CONC_STAT_LIST to
compute these statistics.

FND_CONC_PP_ACTIONS
Stores the post request processing actions(e.g., print, notify) for each
submitted request. There’s a concurrent_request_id here for each request_id
in the FND_CONCURRENT_REQUESTS.

FND_RUN_REQ_PP_ACTIONS
Stores the post request processing actions(e.g., print, notify) for
submitted request set programs that are stored in FND_RUN_REQUESTS

FND_ENV_CONTEXT
This table stores information about environment name and value for each of the
concurrent process

Purge Obsolete Workflow Runtime Data Concurrent Program :

------------------------------------------------------------------------------------------------

This program purges obsolete runtime information associated with work items as well as obsolete design information, such as activities that are no longer in use and expired users and roles, and obsolete runtime information not associated with work items,

such as notifications or Oracle XML Gateway transactions that were not handled through a workflow process.

1.  Navigate to the Submit Requests form in Oracle Applications to submit the Purge Obsolete Workflow Runtime Data concurrent program. When you install and set up Oracle Applications and Oracle Workflow,

your system administrator needs to add this concurrent program to a request security group for the responsibility that you want to run this program from.

2.  Submit the Purge Obsolete Workflow Runtime Data concurrent program as a request.

3.  In the Parameters window, enter values for the following parameters:

Item Type                   :           Item type associated with the obsolete runtime data you want to delete. Leave this argument null to delete obsolete runtime data for all item types.

Item Key                     :            A string generated from the application object's primary key. The string uniquely identifies the item within an item type. If null, the program purges all items in the specified itemtype.

Age                               :         Minimum age of data to purge, in days, if the persistence type is set to 'TEMP'. The default is 0.

Persistence Type     :              Persistence type to be purged, either 'TEMP' for Temporary or 'PERM' for    Permanent. The default is 'TEMP'.

Core Workflow         :               Enter 'Y' to purge only obsolete runtime data associated with work items, or 'N' to purge all obsolete runtime data as well obsolete design data. The  default  is  'N'  Only

Transaction Type     :             The Oracle XML Gateway transaction type to purge. Leave this argument null to purge the runtime data for all transaction types.

Transaction subtype :          The Oracle XML Gateway transaction subtype to purge. The transaction subtype is a code for a particular transaction within the application specified by the  transaction type.

Leave this argument null to purge the runtime data for all transactions of the specified transaction type.

4.  Choose OK to close the Parameters window.

5.  When you finish modifying the print and run options for this request, choose submit to submit the Request.