1 2 3 4 Previous Next 57 Replies Latest reply: Mar 19, 2010 5:34 AM by user503699 RSS

    SQL query performance issues.

    753772
      Hi All,

      select a.trx_date,
      g.segment5 dept,
      g.segment4 prd,
      m.segment1 part,
      d.customer_number customer,
      b.quantity_invoiced units,
      -- substr(a.sales_order,1,6) order#,
      substr(ltrim(b.interface_line_attribute1),1,10) order#,
      a.trx_number invoice,
      (b.quantity_invoiced * b.unit_selling_price) sales,
      (b.quantity_invoiced * nvl(price.operand,0)) cos,
      (b.quantity_invoiced * b.unit_selling_price) -
      (b.quantity_invoiced * nvl(price.operand,0)) profit,
      to_char(to_date('&1','yyyy/mm/dd HH24:MI:SS'),'DD-MON-RR') acct_date,
      'DRP',
      l.ship_from_org_id,
      p.organization_code
      from ra_customers d,
      gl_code_combinations g,
      mtl_system_items m,
      ra_cust_trx_line_gl_dist c,
      ra_customer_trx_lines b,
      ra_customer_trx_all a,
      apps.oe_order_lines l,
      apps.HR_ORGANIZATION_INFORMATION i,
      apps.MTL_INTERCOMPANY_PARAMETERS inter,
      apps.HZ_CUST_SITE_USES_ALL site,
      apps.qp_list_lines_v price,
      apps.mtl_parameters p
      where a.trx_date between to_date('&2','yyyy/mm/dd HH24:MI:SS')
      and to_date('&3','yyyy/mm/dd HH24:MI:SS')+0.9999
      and a.batch_source_id = 1001     -- Sales order shipped other OU
      and a.complete_flag = 'Y'
      and a.customer_trx_id = b.customer_trx_id
      and b.customer_trx_line_id = c.customer_trx_line_id
      and a.sold_to_customer_id = d.customer_id
      and b.inventory_item_id = m.inventory_item_id
      and m.organization_id
           = decode(substr(g.segment4,1,2),'01',5004,'03',5004,
           '02',5003,'00',5001,5002)
      and nvl(m.item_type,'0') <> '111'
      and c.code_combination_id = g.code_combination_id
      and l.line_id = b.interface_line_attribute6
      and i.organization_id = l.ship_from_org_id
      and p.organization_id = l.ship_from_org_id
      and i.org_information3 <> '&4'
      and inter.ship_organization_id = i.org_information3
      and inter.sell_organization_id = '&4'
      and inter.customer_site_id = site.site_use_id
      and site.price_list_id = price.list_header_id
      and product_attr_value = to_char(m.inventory_item_id);

      The above query takes 1hr to execute earlier it use to complete within 10mins please let me know what cud be the reason and teh solution. The ra_customer_trx_lines have more than 6XXXXXXX rows. please help.
        1 2 3 4 Previous Next