Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    42

    Unanswered: Execution Time In Ref Cursor.

    Hi
    Can anybody please help me out in solving a problem related to REF CURSOR.
    I have declared the following REF CURSOR In Package Specification
    TYPE ABC IS RECORD(
    v_address_key VARCHAR2(12),
    v_source_key VARCHAR2(30),
    v_address_status VARCHAR2(1),
    v_org_name VARCHAR2(30),
    v_po_box VARCHAR2(6),
    v_sub_premise VARCHAR2(60),
    v_premise_name VARCHAR2(60),
    v_thoroughfare_number VARCHAR2(60),
    v_dep_thoroughfare VARCHAR2(45),
    v_thoroughfare VARCHAR2(60),
    v_dbl_dep_locality VARCHAR2(30),
    v_locality VARCHAR2(60),
    v_town VARCHAR2(60),
    v_county VARCHAR2(60),
    v_postal_outcode VARCHAR2(4),
    v_postal_incode VARCHAR2(4),
    v_cherished_name VARCHAR2(30),
    v_source_address_structure VARCHAR2(1),
    order_status VARCHAR2(2));

    TYPE bms_delete_output_cursor IS REF CURSOR RETURN ABC;

    Then i am passing the result set to JAVA through the REF CURSOR after inserting the records in a GLOBAL TEMPORARY TABLE TAB3. TAB3 May contain atmost 10 records.

    OPEN v_delete_output FOR
    SELECT TR_SUB_PREM_KEY AS v_address_key,
    source_key AS v_source_key,
    address_status AS v_address_status,
    TR_ORG_NAME AS v_org_name,
    TR_PO_BOX AS v_po_box,
    TR_SUB_PREMISE AS v_sub_premise,
    TR_PREMISE_NAME AS v_premise_name,
    TR_THOROUGHFARE_number AS v_thoroughfare_number,
    TR_DEPEND_THOROUGHFARE AS v_dep_thoroughfare,
    TR_THOROUGHFARE AS v_thoroughfare,
    TR_DOUBLE_DEPEND_LOCALITY AS v_dbl_dep_locality,
    TR_LOCALITY AS v_locality,
    TR_TOWN AS v_town,
    TR_COUNTY AS v_county,
    TR_POSTAL_OUTCODE AS v_postal_outcode,
    TR_POSTAL_INCODE AS v_postal_incode,
    CHERISHED_NAME AS v_cherished_name,
    'S' AS v_source_address_structure,
    order_status
    FROM TAB3
    WHERE Order_Status = '1' AND
    ROWNUM=1;

    When i am running this for a single user it is working fine but when running for Concurrent users, in some cases it is taking time (around 3 seconds whereas out target time is 200 Milliseconds)

    Thanks in advance

    Regards
    Rasmi

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Hi Rasmi

    It looks like you need to tune the whole database not bits of code. Look at my earlier reponse on things to try to do regards your database configuration and partitioning. This will reduce the overall load so that your job gets done in a timely manner.

    Alan

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore,India
    Posts
    51

    Soln for u Rashmi

    Hey Rashmi,
    Seems your REF cursor is fine. It has got to do with your table and commit points
    Look into them..
    Cheers..

Posting Permissions

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