This discussion is archived
5 Replies Latest reply: Jan 3, 2013 2:41 PM by William Robertson RSS

Why is this query running very slow

user578908 Newbie
Currently Being Moderated
Hello experts,

The query below is running super slow (takes about 20-30 mins to run) and I am not sure why. If I remove the "AND journal_line.period_name in ('NOV-12')" from the where clause query runs much faster. Any idea what is happening?
Please help.

Select journal_batch1.NAME,
journal_line.period_name,
gcc.segment8, gcc.segment1, gcc.segment7, gcc.segment2, journal_line.attribute3,
sum(NVL (journal_line.entered_dr, 0)
- NVL (journal_line.entered_cr, 0))
FROM gl_je_batches journal_batch1,
gl_je_headers je1,
gl_ledgers ledger,
gl_encumbrance_types gl_encumbrance_type,
gl_budget_versions gl_budget_version,
gl_je_headers je2,
gl_je_headers je3,
gl_je_categories journal_category,
gl_je_sources journal_source,
gl_daily_conversion_types gl_conversion_type,
gl_je_lines journal_line,
gl_code_combinations gcc,
gl_periods gp,
(SELECT pov.vendor_name,
xte.transaction_number invoice_num,
TO_CHAR (xal.ae_header_id) ae_header_id,
TO_CHAR (xal.ae_line_num) ae_line_num
FROM xla.xla_ae_lines xal,
po_vendors pov,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte
WHERE 1 = 1
AND pov.vendor_id = xal.party_id
AND xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.application_id = xe.application_id
AND xah.event_id = xe.event_id
AND xte.entity_code = 'AP_INVOICES'
AND xe.application_id = xte.application_id
AND xe.entity_id = xte.entity_id
AND    xal.ae_header_id = 4094066 join from reference field 7
AND xal.application_id = 200 -- hard coded for ap
/*AND ae_line_num = 29 join from reference field 8*/
) ap_info
WHERE 1 = 1
AND (je1.je_batch_id = journal_batch1.je_batch_id)
AND (je1.encumbrance_type_id = gl_encumbrance_type.encumbrance_type_id(+))
AND (je1.budget_version_id = gl_budget_version.budget_version_id(+))
AND je1.ledger_id = ledger.ledger_id
AND (je1.parent_je_header_id = je2.je_header_id(+))
AND (je1.reversed_je_header_id = je3.je_header_id(+))
AND (je1.je_category = journal_category.je_category_name
)
AND (je1.je_source = journal_source.je_source_name)
AND (je1.currency_conversion_type =
gl_conversion_type.conversion_type
)
AND (journal_line.je_header_id = je1.je_header_id)
AND (journal_line.code_combination_id =
gcc.code_combination_id
)
AND je1.period_name = gp.period_name
AND journal_line.reference_7 = ap_info.ae_header_id(+)
AND journal_line.reference_8 = ap_info.ae_line_num(+)
AND ledger.ledger_id = 1001
AND journal_line.period_name in ('NOV-12')
AND gcc.segment8 <> '4777'
and gcc.segment1 = '67'
and gcc.segment7 = '1'
and gcc.segment2 = '25286'
group by journal_batch1.NAME,
journal_line.period_name,
gcc.segment8,
gcc.segment1, gcc.segment7,
gcc.segment2, journal_line.attribute3

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points