'Is there any approach to increasing the performance of below query?
The below query is returning almost 800k records for period Sep-21 with all account and LOB and is taking 9+hrs to generate a report.
the code is pretty straight forward. When tried with individual queries, i.e, inline and outer query, it was taking less time (Individual), but when i join those 2 query with the condition
AND gl.gl_sl_link_table (+) =XAL.gl_sl_link_table
AND gl.gl_sl_link_id (+) =XAL.gl_sl_link_id
this was taking time. Is there any approach to modify this query, such that overall performance is increased?
SELECT
XAH.AE_HEADER_ID,
XAH.APPLICATION_ID,
XAH.LEDGER_ID,
XAH.ENTITY_ID,
XAH.EVENT_ID,
XAH.EVENT_TYPE_CODE,
XAH.ACCOUNTING_DATE,
XAH.GL_TRANSFER_STATUS_CODE,
XAH.GL_TRANSFER_DATE,
XAH.JE_CATEGORY_NAME,
XAH.ACCOUNTING_BATCH_ID,
XAH.PERIOD_NAME,
XAH.LEGAL_ENTITY_ID,
XAH.DESCRIPTION,
XAL.ENTERED_DR,
XAL.ENTERED_CR,
XAL.ACCOUNTED_CR,
XAL.ACCOUNTED_DR,
XAL.CURRENCY_CONVERSION_RATE,
XAL.CURRENCY_CONVERSION_DATE,
XAL.CURRENCY_CODE,
XAL.CURRENCY_CONVERSION_TYPE,
XAL.CODE_COMBINATION_ID,
XAL.DESCRIPTION DESCRIPTION1,
XASV.SEGMENT_VALUE ,
XASV.SEGMENT_TYPE_CODE,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
pbe.sourceref "SOURCE_REFERENCE",
gl.name AS "BATCH_NAME",
DECODE(gl.status, 'P', 'Posted', 'U', 'UnPosted', gl.status) AS "BATCH_STATUS"
FROM
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
XLA_AE_SEGMENT_VALUES XASV,
GL_CODE_COMBINATIONS GCC,
pjb_rev_distributions prd,
pjb_billing_events pbe,
(
SELECT
gir.gl_sl_link_table,
gir.gl_sl_link_id,
gjb.name,
gjb.status
FROM
gl_import_references gir,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl
,GL_ledgers gl
WHERE
1 = 1
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.je_line_num = gir.je_line_num
and gjh.ledger_id =gl.ledger_id
and gl.LEDGER_CATEGORY_CODE = 'PRIMARY'
) gl
WHERE
XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
AND XAH.APPLICATION_ID='10036'
AND XAH.EVENT_TYPE_CODE in ('IP_REVENUE','EX_REVENUE','IP_REVENUE_ADJ','EX_REVENUE_ADJ')
AND XAH.PERIOD_NAME = (:PERIOD_NAME)
AND XASV.AE_HEADER_ID(+)=XAL.AE_HEADER_ID
and XAL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND (GCC.SEGMENT2 IN (:ACCOUNT) OR 'All' IN (:ACCOUNT || 'All'))
AND (GCC.SEGMENT5 IN (:LOB) OR 'All' IN (:LOB || 'All'))
AND XAH.event_id = prd.sla_event_id (+)
AND prd.transaction_id = pbe.event_id (+)
AND gl.gl_sl_link_table (+) =XAL.gl_sl_link_table
AND gl.gl_sl_link_id (+) =XAL.gl_sl_link_id
SQL Explain plan
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|

