You are here:

Oracle/performance tuning of report query



One of the query of my report is running for long hours and performance is poor. Please could you let me now if I could tune the query further:

select mp.organization_code
     ,ol.line_number||'.'||ol.shipment_number||decode(ol.option_number,null,null,'.'||ol.option_number) order_line_number
     ,rtl.line_number invoice_line_number
     , order_type_name
     , invoice_type_name
     ,nvl(rtl.quantity_invoiced,rtl.quantity_credited) quantity_invoiced
     ,rtl.extended_amount invoice_amount
     ,rtp.period_name period_num
     ,decode(rtl.interface_line_attribute3,'0',null,rtl.interface_line_attribute3) do_number
     , (select sum(tax_amt)
         from apps.zx_lines
         where application_id = 222
         and trx_line_id = rtl.customer_trx_line_id
         and entity_code = 'TRANSACTIONS'
         and ledger_id =  iclg.ledger_id)  tax_amount
    , rtl.inventory_item_id
    , rtl.description
    , ol.ship_from_org_id
    , rtl.customer_trx_line_id
    , rtl.customer_trx_id
      ----------------------- node 1 intercompany ------------
     ,ich.trx_number trx_number_n1
    , ich.bill_to_site_use_id  bill_to_site_use_id_n1
     ,icd.gl_date gl_date_n1
     ,icl.line_number invoice_line_number_n1
     ,icl.interface_line_attribute3 organization_id_n1
     , invoice_type_name_n1
     ,ich.invoice_currency_code invoice_currency_code_n1
     ,nvl(icl.quantity_invoiced,icl.quantity_credited)   quantity_invoiced_n1
     ,icl.extended_amount invoice_amount_n1
     ,icp.period_name period_num_n1
    , icl.org_id  org_id_n1
    ,  ( select imp.organization_code
         from  mtl_parameters imp
         where organization_id = ol.ship_from_org_id ) organization_code_n1
     , (select sum(tax_amt)
         from apps.zx_lines
         where application_id = 222
         and trx_line_id = icl.customer_trx_line_id
         and entity_code = 'TRANSACTIONS'
         and ledger_id =  iclg.ledger_id)  tax_amount_n1
      , ich.customer_trx_id customer_trx_id_n1
      , rth.bill_to_customer_id
from apps.oe_order_headers_all oh
    ,apps.oe_order_headers_all oh1
    ,apps.oe_order_lines_all ol
    ,apps.ra_customer_trx_lines_all rtl
    ,apps.ra_customer_trx_all rth
    ,apps.ra_cust_trx_types_all rtt
    ,apps.mtl_parameters mp
    ,apps.ra_cust_trx_line_gl_dist_all  rtd
    ,apps.gl_ledgers rtlg
    ,apps.gl_periods rtp
    ,apps.oe_transaction_types_tl ot
    ,apps.hr_operating_units  hu
    ---------- node 1 intercompany ---------
    ,apps.ra_customer_trx_lines_all icl
    ,apps.ra_customer_trx_all ich
    ,apps.ra_cust_trx_types_all ict
    ,apps.ra_cust_trx_line_gl_dist_all icd
    ,apps.gl_ledgers iclg
    ,apps.gl_periods icp
where oh.header_id          = ol.header_id
  and oh1.header_id          = ol.header_id
  and mp.organization_id(+)        = nvl(ol.ship_from_org_id,-1)
  and rth.customer_trx_id          = rtl.customer_trx_id
  and rtl.interface_line_context   = 'ORDER ENTRY'
  and to_number(rtl.sales_order) = oh.order_number
  and rtl.interface_line_attribute6 = to_char(ol.line_id)
  and oh.order_type_id          = ot.transaction_type_id
  and ot.language          =  USERENV('LANG') --'US' changed as part of INC0125712
  and rth.cust_trx_type_id          = rtt.cust_trx_type_id
  and rtd.customer_trx_id          = rth.customer_trx_id
  and rtd.account_class          = 'REC'
  and rth.set_of_books_id          = rtl.set_of_books_id
  and rtd.set_of_books_id          = rth.set_of_books_id  
  and rth.set_of_books_id          = rtlg.ledger_id
  and rtlg.period_set_name          = rtp.period_set_name
  and rtp.adjustment_period_flag = 'N'
  and trunc(rtd.gl_date) between rtp.start_date and rtp.end_date
  and rth.org_id          = hu.organization_id
  and rtt.org_id          = rth.org_id
  and TRUNC(rtp.start_date)  BETWEEN (SELECT gp1.start_date
       FROM gl_periods gp1
      WHERE gp1.period_name = :p_fiscal_period_from
        AND gp1.period_set_name = rtp.period_set_name)
     AND (SELECT gp2.end_date
       FROM gl_periods gp2
      WHERE gp2.period_name = :p_fiscal_period_to
        AND gp2.period_set_name = rtp.period_set_name)
  ----------------------- intercompany node 1 -----------------------  
  and rth.set_of_books_id          = rtl.set_of_books_id
  and rtd.set_of_books_id          = rth.set_of_books_id
  and to_number(icl.sales_order) = oh1.order_number
  and icl.interface_line_attribute6 = to_char(ol.line_id)
  and icl.customer_trx_id          = ich.customer_trx_id
  and ich.cust_trx_type_id          = ict.cust_trx_type_id
  and icd.customer_trx_id          = ich.customer_trx_id
  and icd.account_class          = 'REC'
  and ich.set_of_books_id          = iclg.ledger_id
 and ich.set_of_books_id          = icl.set_of_books_id
  and icd.set_of_books_id          = ich.set_of_books_id
  and iclg.period_set_name         = icp.period_set_name
  and icp.adjustment_period_flag = 'N'
  and TRUNC(icd.gl_date) between icp.start_date and icp.end_date
  and icl.interface_line_context  = 'INTERCOMPANY'
  ------------  organization link between intercompany ----------
  and icl.interface_line_attribute4  = to_char(rtl.org_id) ;

Thanks on advance,

Hi Suman,

You may wish to run your query through an Oracle Explain Plan (reference: 1. An Explain Plan is a way by which you can understand how Oracle is processing your query.

(Explain Plan Reference:
2. How it works:
3. Understanding the Explain Plan:

While the explain plan does not rule out Operating System resources (e.g. memeory, disk swapping, etc.), it will give you an indication on how Oracle is executing your query. This may help to identify if you need new indicies, a re-write  of your query ,etc.)

I hope the reading provided will help you with your situation.



All Answers

Answers by Expert:

Ask Experts


Peter Choi


I am a senior Oracle DBA, PeopleSoft HCM specialist and Project Manager with 15+ years experience. I have been working with PeopleSoft (HRMS/HCM 5, 7, 7.x and 8.9, 9.x), Oracle RDBMS (7.3 - 11gR2) on various Unix and MS-Windows platforms, and some Oracle Application Server (9i - 11gR1). I also have experience with the configuration and administration of PeopleSoft's Internet Architecture (PIA) and the Oracle 11g Fusion Middleware and Oracle Business Intelligence (OBI).

©2016 All rights reserved.