Poor Performance of Posting: Single Ledger: Query#1

We were upgrading to Oracle database 12c. During regression testing we found that following sql is running very poorly for some JV Batches. Query is given below. If you look at the query you would notice that POSTING_RUN_ID is hard coded. It means this is a dynamic sql and Oracle developer had appended POSTING_RUN_ID to the query instead of using a bind variable. Biggest drawback of hard coded value: Every time sql is different, it has different sql_id, plan hash value and i can not pin a sqlplan for this query. This query works fine when a JE header has less number of lines. In our case it does not work very well when one header has 200k lines. it trying to access GL_JE_LINES using unique index but LINE_NUM is not used in the query so it ends up accessing 200K for each Header record and hence slow performance. following bug were filed longtime back but no resolution

Bug 6415832: No resolution

Bug 7044466: No resolution. As per this bug customer suggested creating a new index on JE_HEADER_ID, CODE_COMBINATION_ID and asked Oracle's blessing but i think it was not approved because no such index in Rel12.1.3. To solve our problem in PROD, we are thinking about creating an index on CODE_COMBINATION_ID , PERIOD_NAME and JE_HEADER_ID. This index works great for us.

I wonder what is the use of where clause in this query? Subquery has same where clause. So if record in not found pi.period_net_dr and pi.period_net_dr will be just overwritten. Am i right?

UPDATE GL_POSTING_INTERIM PI SET (PI.period_net_dr, PI.period_net_cr) = (SELECT nvl(PI.period_net_dr,0) + nvl(sum(decode(JEL.period_name, PI.period_name, decode(sign(JEL.stat_amount), 1, abs(JEL.stat_amount), 0 ), 0 )), 0), nvl(PI.period_net_cr,0) + nvl(sum(decode(JEL.period_name, PI.period_name, decode(sign(JEL.stat_amount), 1, 0, abs(JEL.stat_amount)), 0 )), 0) FROM GL_JE_BATCHES JEB, GL_JE_HEADERS JEH, GL_JE_LINES JEL WHERE JEB.status = 'I' AND JEB.chart_of_accounts_id = 50225 AND JEB.posting_run_id = 229329 AND JEB.average_journal_flag = 'N' AND JEH.je_batch_id = JEB.je_batch_id AND JEH.status != 'P' AND JEL.je_header_id = JEH.je_header_id AND nvl(JEL.status,'U') = 'U' AND JEL.stat_amount is not null AND PI.ledger_id = JEH.ledger_id AND PI.code_combination_id = JEL.code_combination_id AND PI.actual_flag = JEH.actual_flag AND PI.period_name = JEL.period_name AND PI.currency_code = 'STAT' AND nvl(PI.budget_version_id, -1) = nvl(JEH.budget_version_id, -1) AND nvl(PI.encumbrance_type_id, -1) = nvl(JEH.encumbrance_type_id, -1) GROUP BY JEH.ledger_id, JEH.actual_flag, JEH.encumbrance_type_id, JEH.budget_version_id, JEL.period_name, JEL.code_combination_id) WHERE (PI.ledger_id, PI.actual_flag, nvl(PI.budget_version_id, -1), nvl(PI.encumbrance_type_id, -1), PI.currency_code, PI.period_name, PI.code_combination_id) IN (SELECT JEH2.ledger_id, JEH2.actual_flag, nvl(JEH2.budget_version_id, -1), nvl(JEH2.encumbrance_type_id, -1), 'STAT', JEH2.period_name, JEL2.code_combination_id FROM GL_JE_BATCHES JEB2, GL_JE_HEADERS JEH2, GL_JE_LINES JEL2 WHERE JEB2.status = 'I' AND JEB2.chart_of_accounts_id = 50225 AND JEB2.posting_run_id = 229329 AND JEB2.average_journal_flag = 'N' AND JEH2.je_batch_id = JEB2.je_batch_id AND JEL2.je_header_id = JEH2.je_header_id AND nvl(JEL2.status,'U') = 'U' AND JEL2.stat_amount is not null)