Open period Request Takes too long

[Saad Qaiser Khan]

Hello Erman,

EBS Release 12.1.3
DB Ver 11.2.0.4

Open period request takes too long to open(approx 2 hours). Opened the SR they recommend me patches to upgrade the version

Open Periods Program Poor Performance in GLOODB and GLOPIB Routines (Doc ID 1312309.1)

R12: GLOOAP Open Period Program Has Poor Performance (Doc ID 1501226.1)

Here are the logs.


>> main() 01-MAR-2017 11:52:15

>> glusca() 01-MAR-2017 11:52:15

<< glusca() 01-MAR-2017 11:52:15

>> glulsr() 01-MAR-2017 11:52:15

<< glulsr() 01-MAR-2017 11:52:15

>> glooap() 01-MAR-2017 11:52:15

>> gloovp() 01-MAR-2017 11:52:15

<< gloovp() 01-MAR-2017 11:52:15

>> glopcs() 01-MAR-2017 11:52:15

<< glopcs() 01-MAR-2017 11:52:15

>> gloodb() 01-MAR-2017 11:52:15

<< gloodb() 01-MAR-2017 11:52:15

>> glucmt() 01-MAR-2017 11:52:15

<< glucmt() 01-MAR-2017 11:52:15

>> glucmt() 01-MAR-2017 11:52:15

<< glucmt() 01-MAR-2017 11:52:15
SHRD0019: glooap - process exiting after successful execution.

<< glooap() 01-MAR-2017 11:52:15

>> launch_req() 01-MAR-2017 11:52:15

<< launch_req() 01-MAR-2017 11:52:15
SHRD0019: glooap - process exiting after successful execution.

<< glooap.xla_balance_open() 01-MAR-2017 11:52:15

Please recommend me any patch or solution.

Thanks and Regards,
Saad

[ErmanArslansOracleBlog]

Hi Saad,

This log doesn't say anything. It is just listing the names of the routines.
Why don't you follow the SR?
There are indeed performance issue recorded for this.
I just searched the Oracle Support with the keyword "glooap performance" and here are the results of my search;

R12: GLOOAP Open Period Program Has Poor Performance(Doc ID 1501226.1)
GLOOAP: Open Periods Program Poor Performance in GLOODB and GLOPIB Routines(Doc ID 1312309.1)
Information Center: Optimizing Performance for Oracle General Ledger(Doc ID 1489537.2)
GLOOAP Open Period Even After Applying Patch 5069154 When Open New Period in New Year Have Poor Performance(Doc ID 404362.1)

[Saad Qaiser Khan]

Because SR isnt replying. and the solution they proposed the patches didn't work. :(

[ErmanArslansOracleBlog]

Then a diagnostic needed. Check the db session of the program? Which sql is the problematic one?

What is its wait event?

Is there any waits in the apps tier? I mean is the code waiting ? Waiting for what?db? Apps? Os?

3 Mar 2017 16:12 tarihinde "Saad Qaiser Khan [via Erman Arslan's Oracle Forum]" <[hidden email]> yazdı:

Because SR isnt replying. and the solution they proposed the patches didn't work. :(

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/Open-period-Request-Takes-too-long-tp2249p2255.html

To start a new topic under Erman Arslan's Oracle Forum, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML

[Saad Qaiser Khan]

Hello,

Thanks erman for the update. If you could guide me a little regarding this or refer to me any document. I would be thankful. How to check all these?

Thanks and Regards,
Saad

[ErmanArslansOracleBlog]

Start by runnung the process once again and check active sessions on db.

Do you have Toad or something like that for easy session diagnostics ?

3 Mar 2017 19:41 tarihinde "Saad Qaiser Khan [via Erman Arslan's Oracle Forum]" <[hidden email]> yazdı:

Hello,

Thanks erman for the update. If you could guide me a little regarding this or refer to me any document. I would be thankful. How to check all these?

Thanks and Regards,
Saad

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/Open-period-Request-Takes-too-long-tp2249p2257.html

To start a new topic under Erman Arslan's Oracle Forum, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML

[Saad Qaiser Khan]

Hello Erman,

Yes I do have toad...

Regards,
Saad

[ErmanArslansOracleBlog]

you can use toad 's session browser to check the waits.

Run the problematic process from ebs. While it is running check the active sessions using toad's session browser. Find the problematic session, check its current statement and use oracle support to search the issues related with that statement.

3 Mar 2017 22:07 tarihinde "Saad Qaiser Khan [via Erman Arslan's Oracle Forum]" <[hidden email]> yazdı:

Hello Erman,

Yes I do have toad...

Regards,
Saad

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/Open-period-Request-Takes-too-long-tp2249p2259.html

To start a new topic under Erman Arslan's Oracle Forum, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML

[Saad Qaiser Khan]

Hello Erman,

I have checked through Toad session, and I can see the concurrent program GLOOAPC but there is no lock wait I can see in it.

        2355 oracle APPS APPS ACTIVE 14 453 GLOOAPC Concurrent Request


but under top sessions

I can see the same SID and current sql as

INSERT                                                          /*+ append  */
      INTO                 gl_balances a (code_combination_id,
                                          period_name,
                                          ledger_id,
                                          currency_code,
                                          period_year,
                                          period_num,
                                          period_type,
                                          actual_flag,
                                          last_updated_by,
                                          last_update_date,
                                          translated_flag,
                                          period_net_dr,
                                          period_net_cr,
                                          period_net_dr_beq,
                                          period_net_cr_beq,
                                          quarter_to_date_dr,
                                          quarter_to_date_cr,
                                          project_to_date_dr,
                                          project_to_date_cr,
                                          begin_balance_dr,
                                          begin_balance_cr,
                                          begin_balance_dr_beq,
                                          begin_balance_cr_beq,
                                          quarter_to_date_dr_beq,
                                          quarter_to_date_cr_beq,
                                          project_to_date_dr_beq,
                                          project_to_date_cr_beq,
                                          template_id)
   SELECT code_combination_id,
          period_name,
          ledger_id,
          currency_code,
          period_year,
          period_num,
          period_type,
          actual_flag,
          0,
          SYSDATE,
          DECODE (currency_code,  'PKR', NULL,  'STAT', NULL,  'R'),
          0,
          0,
          0,
          0,
          quarter_to_date_dr,
          quarter_to_date_cr,
          project_to_date_dr,
          project_to_date_cr,
          begin_balance_dr,
          begin_balance_cr,
          DECODE (currency_code, 'STAT', 0, begin_balance_dr_beq),
          DECODE (currency_code, 'STAT', 0, begin_balance_cr_beq),
          DECODE (currency_code, 'STAT', 0, quarter_to_date_dr_beq),
          DECODE (currency_code, 'STAT', 0, quarter_to_date_cr_beq),
          DECODE (currency_code, 'STAT', 0, project_to_date_dr_beq),
          DECODE (currency_code, 'STAT', 0, project_to_date_cr_beq),
          template_id
     FROM gl_posting_interim b


but still there is no lock. What does that mean can you please elaborate?



Regards,
Saad Qaiser Khan

[ErmanArslansOracleBlog]

Send me the following ->

1)Current wait of this session
2)Execution plan of the current statement.

Using Toad, you can gather both of it.

Note: There are bug records for performance of these posting programs, but I want to see the wait and the execution plan of this statement to filter these bugs.
Maybe we can find a patch using the index_name (if we see an excessive index wait)

[Saad Qaiser Khan]

When I click on explain plan I see nothing means no data. I can see the columns like user,timestamp,statement id and statement but no data. What does that mean. Do I have to setup this?

Regards,
Saad

[ErmanArslansOracleBlog]

No.
Please send me the screenshot of that Explain plan window.

[Saad Qaiser Khan]

Erman how I am unable to see here any attachement? where I can upoad image.

Regards,
Saad

[ErmanArslansOracleBlog]

You can upload it here.

[Saad Qaiser Khan]

<img src="C:/Users/malik.fergusons/Desktop/123456.JPG" alt="Mountain View">


Here it is

[ErmanArslansOracleBlog]

I can't see it. You could not upload the image.
Retry.

[Saad Qaiser Khan]

Hello Erman,

Sorry but I cannot see where to upload the image or attachment. Please tell me how to do it.

Regards,
Saad

[ErmanArslansOracleBlog]

send me via email. [hidden email]

7 Mar 2017 10:10 tarihinde "Saad Qaiser Khan [via Erman Arslan's Oracle Forum]" <[hidden email]> yazdı:

Hello Erman,

Sorry but I cannot see where to upload the image or attachment. Please tell me how to do it.

Regards,
Saad

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/Open-period-Request-Takes-too-long-tp2249p2280.html

To start a new topic under Erman Arslan's Oracle Forum, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML

[Saad Qaiser Khan]

Hello Erman,

The issue has been resolved. After upgrade from 12.0.6 to 12.1.3 which was done by another vendor they made custom indexes in gl_balances. After dropping those the request is now completing in 45 minutes in UAT Server because it is slow. Hopefully in PROD Server it will be completed faster approx in 20 minutes. I learn new things in TOAD because of things you told.

I still don't know why it was doing it. Because of custom indexes.

Regards,
Saad

[ErmanArslansOracleBlog]

Good.
Yes.. I thought it so as well...  As I told you in one of my earlier responses "There are bug records for performance of these posting programs, but I want to see the wait and the execution plan of this statement to filter these bugs.  Maybe we can find a patch using the index_name (if we see an excessive index wait) "