pin_ledger_report Poor Performance On Upgraded 7.3.1
Last updated on SEPTEMBER 26, 2016
Applies to:Oracle Communications Billing and Revenue Management - Version 220.127.116.11.0 to 18.104.22.168.1 [Release 7.3.1]
Information in this document applies to any platform.
Checked for relevance on 15-Jul-2011
pin_ledger_report performance downgraded after upgrade to 7.3.1 from 6.5_SP1 (22.214.171.124).
When executing pin_ledger_report on upgraded environment, after 2 hours it did not finished. On
the same server, on 6.5 it took 2 hours. GL report on 7.3.1 version is supposed to be much faster.
- On 7.3.1 the statistics on JOURNAL_T and ITEM_T is updated.
- Only 37 GL_SEGMENTS are defined
- Checked for similar <bug 7518432> and the index "I_ITEM_GLSEG_EFF__ID" on item_t is mentioned as
one index that should not exist, but based on the SQL launched by pin_ledger_report, there is a
hint on the query to use that index.
Should that index be dropped, and receive a patch for ledger not to have this hint?
The query which was taking a lot of time to resolve is:
This has a cost of 4,900, a reduction of 4,000.
Could the proposed query by used as a fix on the query for the pin_ledger_report? In the execution plan for the proposed query, the index "I_ITEM_GLSEG_EFF__ID" on item_t is not used on
the execution plan.
-- Steps To Reproduce:
pin_ledger_report -mode run_report -detail -segment .xxxxx -start 01/01/2009 -end 01/02/2009 -type billed
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms