Oracle GL Posting(GLPPOS) Program Has Poor Performance or taking time to finish

Posting(GLPPOS) Program Performance Issue in Oracle Applications

Posting(GLPPOS)

Oracle General Ledger Posting(GLPPOS) Program taking many hours to process or Performance problem or poor performance issue or running too long to finish/complete.

We have faced this issue and log file was showing glpibr.concurrency() process taking time to finish.
Below are details collected during analysis and by running below mentioned workaround solutions, problem got fixed.

Verify basic below steps: 

• Verify logs of posting programs facing performance issue, also if program is getting errored out, check any error message is there or not. This will be helpful to search issue on Oracle Metalink
• When Last time 'Gather Schema Statistics' program ran successfully
• Last time 'Program - Optimizer' program ran successfully

Major Causes found pointing to poor performance of posting:

• Running parallel posting Jobs
• Custom Indexes on Oracle gl tables
• Invalid objects/Indexes
• Memory/Space issue
• Poor Gathered statistics affecting execution of sql's

In most of the cases ,suggested below solutions:

• Search for and recompile all Invalid Objects
• Close as many open periods as possible. The number of open periods has a tremendous effect on posting performance.
• Break your posting jobs into smaller sized batches.
• Parallel processing relies heavily on number of processors and RAM.
• Check to see how much memory is installed on the host.
• Run Oracle Optimizer

          Name = Program - Optimizer

          Parameters pop-up window select:

          Gather Statistics = Yes

          Maintain Indexes = Yes

          Before proceeding, check to make  sure this request completes with a status of 'Normal.'

• Run the program 'Gather Schema Statistics' to gather statistics for Cost Based Optimizer (CBO).

         Responsibility: System Administrator

         Navigation Path: Requests -> Run

Quick Workaround Solutions to fix immediately:

• Ask DBA to verify tablespaces for below tables listed and their respective indexes. 
• Ask DBA to maintain the standards of having PCT free tablespaces GLX_DTL which holds indexes and GLD_DTL which holds tables and are having appropriate % of free space.
• Ask Unix tean to veryfy server memory utilisation and kill process any consuming lots of memory
• Run Oracle Optimizer from oracle general ledger responsibility

          Name = Program - Optimizer
          Parameters pop-up window select:
          Gather Statistics = Yes
          Maintain Indexes = Yes

          Before proceeding, check to make  sure this request completes with a status of 'Normal.'

 

•  Verify the below tables and their all indexes are valid:
  GL_JE_BATCHES
  GL_JE_HEADERS
  GL_JE_LINES
  GL_CODE_COMBINATIONS
  GL_BALANCES

•  Ask DBA to gather statistics for each of the index of these tables by running below script in apps schema.

         Begin
             Fnd_Stats.Gather_Index_Stats
            (
                 'GL',
                 'GL_JE_HEADERS_N1', --change index each time
                 100,
                 NULL,
                 Null,
                null,
                'Y'
            );
           commit;
        End;

      Verify above proc completes normally for all indexes

• Run program 'Gather Table Statistics' for each above table:

          Responsibility: System Administrator
          Navigation Path: Requests -> Run > Gather Table Statistics
          For Example
          Parameters:
          Table Owner : GL
          Table Name :  GL_JE_LINES
          Estimate Percent  100
          Degree of parallelism
          Partition Name
          Backup Flag :NOBACKUP
          Granularity : DEFAULT
          History Mode: LASTRUN
          Invalidate Dependent Cursors:Y

Do it for each table and make sure that each program completes normal

You can do above steps also by running below sql-
         Fnd_Stats.GATHER_TABLE_STATS --similor to the one doing for index

After all above steps , now you can ask user to test posting programs performance. Definately there will be improvement.

Also you can run below program for all schema. This one you can run where there will not be any load or no one is accessing application. This program generally takes time to finish for all schema.
You can schedule it on weekends also.

Best practice is to run this program at least once in a month as server uses statistics of objects generated while creating execution plan.

• Run the program 'Gather Schema Statistics' to gather statistics for Cost Based Optimizer (CBO).

         Responsibility: System Administrator
         Navigation Path: Requests -> Run


If in worst case , issue still persists, you can raise SR with oracle, they will provide you solution but that one is little time consuming process. So you can perform all above steps firsts to verify.

Verify last_analysed_date column in below tables for index and tables-

Select *
          From Dba_Tables Where Table_Name In ('GL_JE_BATCHES','GL_JE_HEADERS','GL_JE_LINES','GL_CODE_COMBINATIONS','GL_BALANCES');
       
Select *
          FROM dba_indexes
          Where Table_Name In ('GL_JE_BATCHES','GL_JE_HEADERS','GL_JE_LINES','GL_CODE_COMBINATIONS','GL_BALANCES')
          Order By Index_Name;

Below Metalink Documents you can refer to get more information:

• Troubleshooting Issues with Posting (Doc ID 114345.1)
• GLPPOS Posting Program Has Poor Performance In glpipi gl_posting_interim_nn (Doc ID 115853.1)
• Parallel Posting jobs lock Rollback Space (Doc ID 1063964.6)
• Posting Perfomance effected by GLPIBR and GLPUBR (Doc ID 1057653.6)
• GLPPOS Module: Posting Program is Hanging on glpubr.concurrency() Routine (Doc ID 1096873.1)
• How To Gather Statistics For Oracle Applications Prior to 11.5.10 (Doc ID 122371.1)