Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2008
    Posts
    1

    Unanswered: Oracle Performance

    Hi,

    Please can you help me tuning this proc

    procedure Process_PCN_MAT_Custs(
    p_PCN_OID in varchar2,
    p_days_back in number
    ) is
    vcount number;
    v_newoid varchar2(16);
    pcn_create_date date;
    cursor SAP_Customers( --selects customer list from shipments and backlog with sold/ship/end flags
    x_PCN_OID varchar2,
    x_days_back number,
    x_pcn_create_date date
    ) is
    select
    kunnr,
    max(soldflag) soldflag,
    max(shipflag) shipflag,
    max(endflag) endflag,
    max(custname) custname
    from (
    select /*+DRIVING_SITE(bims_pcn_shipments)*/
    kunnr,
    SoldFlag,
    ShipFlag,
    EndFlag,
    custname
    from
    bims_pcn_shipments
    where
    material_number in (select sap_material_no from pcn.material where pcn_oid = x_PCN_OID)
    and bdr_date > (x_pcn_create_date - x_days_back)
    /* select
    c.partner_no kunnr,
    decode(c.customer_dwid, s.sold_to_dwid, 'Y','N') SoldFlag,
    decode(c.customer_dwid, s.ship_to_dwid, 'Y','N') ShipFlag,
    decode(c.customer_dwid, s.endcust_dwid, 'Y','N') EndFlag,
    c.name1_adrc custname
    from
    bims_product p,
    bims_shipments s,
    bims_customer c
    where
    p.material_number in (select sap_material_no from pcn.material where pcn_oid = x_PCN_OID)
    and p.product_dwid = s.product_dwid
    and (s.sold_to_dwid = c.customer_dwid or s.ship_to_dwid = c.customer_dwid or s.endcust_dwid = c.customer_dwid)
    and s.bdr_date > (x_pcn_create_date - x_days_back)
    and customer_type = 'MTI' */
    UNION
    select /*+DRIVING_SITE(bims_pcn_backlog)*/
    kunnr,
    SoldFlag,
    ShipFlag,
    EndFlag,
    custname
    from
    bims_pcn_backlog
    where
    material_number in (select sap_material_no from pcn.material where pcn_oid = x_PCN_OID)
    and bdr_date > (x_pcn_create_date - x_days_back)
    /* select
    c.partner_no kunnr,
    decode(c.customer_dwid, s.sold_to_dwid, 'Y','N') SoldFlag,
    decode(c.customer_dwid, s.ship_to_dwid, 'Y','N') ShipFlag,
    decode(c.customer_dwid, s.end_cust_dwid,'Y','N') EndFlag,
    c.name1_adrc custname
    from
    bims_product p,
    bims_backlog_current s,
    bims_customer c
    where
    p.material_number in (select sap_material_no from pcn.material where pcn_oid = x_PCN_OID)
    and p.product_dwid = s.product_dwid
    and (s.sold_to_dwid = c.customer_dwid or s.ship_to_dwid = c.customer_dwid or s.end_cust_dwid = c.customer_dwid)
    and s.bdr_date > (x_pcn_create_date - x_days_back)*/
    )
    group by
    kunnr
    ;--end cursor
    cursor PCN_Contacts( --selects contacts based on sap KUNNR
    x_sap_customer_no varchar2
    ) is
    select
    c.contact_oid,
    x.sap_customer_no sap_customer_no,
    first_name || ' ' || last_name contact_name,
    email_address
    from
    pcn.contact c,
    pcn.Contact_to_SAP_Customer x
    where
    c.contact_oid = x.contact_oid (+)
    and (c.sap_customer_no = x_sap_customer_no or x.sap_customer_no = x_sap_customer_no)
    and one_time_contact_flag != 'Y'
    ;--end cursor
    cursor PCN_Contacts_with_All_Flag --selects contacts based on sap KUNNR
    is
    select
    contact_oid,
    sap_customer_no,
    first_name || ' ' || last_name contact_name,
    email_address
    from
    pcn.contact
    where
    receive_all_pcn_flag = 'Y'
    ;--end cursor
    begin
    --get pcn date so we don't have a "sliding window":
    select created_date into pcn_create_date from pcn.pcn_header where pcn_oid = p_pcn_oid;
    --start by deleting all existing Customers and their contacts that were not manually added
    delete from
    pcn.Customer_to_contact
    where
    customer_oid in (select customer_oid from pcn.customer where pcn_oid = p_pcn_oid)
    and manually_added_flag != 'Y'
    ;

    delete from
    pcn.customer
    where
    pcn_oid = p_pcn_oid
    and manually_added_flag != 'Y'
    and customer_oid not in (select customer_oid from pcn.customer_to_contact
    where customer_oid = customer.customer_oid)
    ;
    --loop through affected customers and add them if not already there
    FOR cust IN SAP_Customers(p_PCN_OID,p_days_back, pcn_create_date) LOOP
    --see if record already exists:
    select count(*) into vcount from pcn.customer
    where pcn_oid = p_pcn_oid and sap_customer_no = cust.kunnr;
    if vcount = 0 then
    begin
    v_newoid := pcn.pcn_pkg.newoid();
    pcn.pcn_pkg.iu_customer(
    p_customer_oid => v_newoid,
    p_pcn_oid => p_pcn_oid,
    p_sap_customer_no => cust.kunnr,
    p_customer_name => cust.custname,
    p_manually_added_flag => 'N',
    p_alpha_flag => 'N',
    p_deleted_flag => 'N',
    p_sold_flag => cust.soldflag,
    p_ship_flag => cust.shipflag,
    p_end_flag => cust.endflag,
    p_rsm_worker_no => pcn.pcn_bdr.GetRSMFromLastOrder(cust.kunnr),
    p_timestamp => null,
    p_update_by_worker_no => 0
    );
    exception when others then --BDR is returning invalid customer numbers (such as '0')
    null;
    end;
    else --if it exists it's because somebody manually added it
    select customer_oid into v_newoid from pcn.customer
    where pcn_oid = p_pcn_oid and sap_customer_no = cust.kunnr;
    end if;
    --tie up contacts (join records)
    FOR cont IN PCN_Contacts(cust.kunnr) LOOP
    select count(*) into vcount from pcn.Customer_to_Contact
    where customer_oid = v_newoid and contact_oid = cont.contact_oid;
    if vcount = 0 then --(just making sure it's not already there)
    pcn.pcn_pkg.iu_customer_contact(
    p_customer_to_contact_oid => null,
    p_contact_oid => cont.contact_oid,
    p_customer_oid => v_newoid,
    p_contact_name => cont.contact_name,
    p_email_address => cont.email_address,
    p_approved_flag => 'U',
    p_approval_comment => null,
    p_deleted_flag => 'N',
    p_manually_added_flag => 'N',
    p_timestamp => null,
    p_update_by_worker_no => 0
    );
    end if;
    END LOOP;
    END LOOP;

    end;


    Actually this proc taking more time to execute 10-15 minutes.

    It has been called from .net.

    Please can you help me how to tune this proc.

    Please tell me why it is taking more time. it is urgent.

    Thanks,
    Kala k

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you need indexes.
    run a trace, execute the proc, tkprof the trace including explain, post the output.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Also please use [code] tags to make the code readable if you want people to read it!

  4. #4
    Join Date
    Dec 2008
    Posts
    59
    I am agree with the_duck
    you need indexes.
    run a trace, execute the proc, tkprof the trace including explain, post the output.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •