pin_ledger_report Poor Performance On Upgraded 7.3.1 (Doc ID 841943.1)

Last updated on SEPTEMBER 26, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.3.1.0.0 to 7.3.1.0.1 [Release 7.3.1]
Information in this document applies to any platform.
Checked for relevance on 15-Jul-2011


Symptoms

pin_ledger_report performance downgraded after upgrade to 7.3.1 from 6.5_SP1 (6.5.0.1).

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


Cause

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