Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2007
    Posts
    9

    Angry Unanswered: LOV displays results after a long time

    Hello to ALL,

    In a Form (made under 6i) i find that the LOV shows results after about 3 minutes. If the query associated with that LOV is executed in TOAD it takes merely 9 seconds. I cannot find out any reason that could contribute to this abnormal delay.

    If any one had experienced similar problems and found the cause or developed a solution I shall be very thankful if you could help me out.

    Regards

    S K SONNY

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Did you run the same query (in Forms and TOAD) connected to the same schema? Is there, perhaps, a database link involved?

  3. #3
    Join Date
    Oct 2007
    Posts
    9
    Yes the same query was run under FORMS & TOAD connected to same schema (no db link was made). The query uses one view and one table. The view is generated from tables in the same schema. The view uses outer joins twice using (+) notation and the query also uses out join using the same + notation. I am thinking of replacing this outer joins using the industry standard joining syntax.

    But, nonetheless I cannot understand why the query displays the LOV results after so much longer delay whereas the same result set is fetched by toad in just 9 sec.

    Thanks for quick reply anyway.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, this will probably not solve the problem, but - won't cost much to try: recompile the form (all - <Ctrl + Shift + K>) and try the LoV again.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Could it be that one query is using bind variables and the other is using literals thus leading to different execution plans?

    Alan

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Could it be that the LOV has to return all results and your seeing the start of data being returned in the sql select. How long does a CTAS take in sql plus?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Oct 2007
    Posts
    9
    Yes, 'beilstwh' u are right. I double checked by running the query in SQL+ yesterday and found that it was retrieving about 20 rows in batches with some time interval in between and the complete result set finished in the same time as the LOV in forms i.e. by about 2 minutes.

    So to make it more fast I have set the "filter before display" property to yes and prompt the user to entire partial data and this gives the result in about 200 milliseconds.

    HOwever, I am posting the actual query of the record group associated with the LOV, so that you learned members may like to see if it can be optimised.
    ================================================== ======
    select fes_order_no po_no , fes_order_date po_date , fes_srn_no rct_no,
    fes_srn_date rct_date, rct_chln_no rct_chln_no , rct_chln_date rct_chln_date,
    fes_party_code po_splr_code, prt_name prt_name, prt_type prt_type
    from

    fa_party,

    (select fes_srn_type,FES_SRN_NO, FES_SRN_DATE, FES_PARTY_CODE, fes_prt_name,
    FES_SRN_AMOUNT, FES_ORDER_NO, FES_ORDER_DATE, RCT_CHLN_NO, RCT_CHLN_DATE
    from (
    select 'P' fes_srn_type, FES_SRN_NO, FES_SRN_DATE, FES_PARTY_CODE ,prt_name fes_prt_name ,
    FES_SRN_AMOUNT ,FES_ORDER_NO, FES_ORDER_DATE,RCT_CHLN_NO, RCT_CHLN_DATE
    from FA_PREV_SRN, fa_party
    where prt_code = FES_PARTY_CODE
    and FES_SRN_AMOUNT <> 0
    union
    select 'C' fes_srn_type ,RCT_NO,RCT_DATE,po_splr_code,splr_name,RCT_AMT,po _no,
    po_date,RCT_CHLN_NO,RCT_CHLN_DATE
    from MMS_SUPPLIERS, mms_rct_dtls, mms_rcts, mms_pos
    where po_id = rct_po_id(+)
    and PO_SPLR_CODE=splr_code
    and rcd_rct_id=rct_id
    and rct_no is not null
    and RCT_STATUS ='Y' )
    where fes_srn_no not in (select SBD_SRN_NO from fa_sup_bill, FA_SUP_BILL_DTLS
    where SBD_SCI_BILL_SL_NO = SCI_BILL_SL_NO
    and SCI_BILL_PASS_STTUS = 'Y'
    union
    select spl_srn_no from fa_srn_prev_liability)
    )
    pending

    where pending.fes_party_code = fa_party.prt_code (+)
    ================================================== ===

    Descriptions of the tables used (not all columns are show due to space constraint):

    SQL> desc fa_party; The vendor laster as in Accounts deptt
    Name Null? Type
    ------------------------------- -------- ----
    PRT_CODE NOT NULL VARCHAR2(10)
    PRT_TYPE NOT NULL VARCHAR2(25)
    PRT_NAME VARCHAR2(100)

    SQL> desc mms_suppliers; The vendor laster as in Ordering deptt
    Name Null? Type
    ------------------------------- -------- ----
    SPLR_CODE NOT NULL VARCHAR2(10)
    SPLR_NAME NOT NULL VARCHAR2(50)

    SQL> desc fa_prev_srn; The orders which have been delivered
    Name Null? Type
    ------------------------------- -------- ----
    FES_ID NOT NULL VARCHAR2(10)
    FES_ORDER_NO VARCHAR2(20)
    FES_ORDER_DATE DATE
    FES_SRN_NO VARCHAR2(20)
    FES_SRN_DATE DATE
    FES_PARTY_CODE VARCHAR2(10)
    FES_SRN_AMOUNT NUMBER(12,2)

    SQL> desc fa_srn_prev_liability; THe liability of orders
    Name Null? Type
    ------------------------------- -------- ----
    SPL_ID NOT NULL NUMBER(10)
    SPL_SRN_NO VARCHAR2(20)
    SPL_SRN_DATE DATE
    SPL_SRN_AMOUNT NUMBER(14,2)
    SPL_BILL_PASS_SLNO VARCHAR2(20)
    SPL_BILL_PASS_DATE DATE
    SPL_BILL_PASS_AMOUNT NUMBER(14,2)
    SPL_PRT_CODE VARCHAR2(20)
    SPL_PRT_TYPE VARCHAR2(20)

    SQL> desc mms_rcts; The recipt of materials
    Name Null? Type
    ------------------------------- -------- ----
    RCT_ID NOT NULL NUMBER
    RCT_NO VARCHAR2(20)
    RCT_DATE DATE
    RCT_AMT NUMBER(13,3)
    RCT_SEND_ON_FINANCE DATE
    RCT_TYPE VARCHAR2(10)
    RCT_STATUS VARCHAR2(1)
    RCT_SOURCE VARCHAR2(4)
    RCT_RCV_DATE DATE
    RCT_UPDATE_FLAG VARCHAR2(1)

    SQL> desc mms_rct_dtls;
    Name Null? Type
    ------------------------------- -------- ----
    RCD_CHLN_QTY NOT NULL NUMBER(11,3)
    RCD_CHLN_UOM VARCHAR2(3)
    RCD_RCV_QTY NUMBER(11,3)
    RCD_ACPT_QTY NUMBER(11,3)
    RCD_INSP_DATE DATE
    RCD_RATE NUMBER(20,6)
    RCD_RCT_ID NOT NULL NUMBER
    RCD_ITM_CODE NOT NULL VARCHAR2(11)
    RCD_AMOUNT NUMBER(20,4)
    RCD_ITM_NO VARCHAR2(20)

    SQL> desc mms_pos; The Purchase Orders issued to vendors as in Materials deptt
    Name Null? Type
    ------------------------------- -------- ----
    PO_ID NOT NULL NUMBER
    PO_NO NOT NULL VARCHAR2(30)
    PO_TYPE NOT NULL VARCHAR2(1)
    PO_DATE NOT NULL DATE
    PO_TOT NUMBER(10,2)
    PO_PO_ID NUMBER
    PO_QTN_ID NUMBER
    PO_SPLR_CODE VARCHAR2(10)
    PO_MPR_ID NUMBER
    PO_REM VARCHAR2(4000)
    PO_STATUS VARCHAR2(1)
    PO_DELV_PLACE VARCHAR2(40)
    PO_LQD_DMG NUMBER(6,2)
    PO_UNIT VARCHAR2(15)
    PO_CEILING NUMBER(6,2)
    PO_RC VARCHAR2(1)
    PO_AMT_WORDS VARCHAR2(200)
    PO_AMT_FIG NUMBER(15,2)
    PO_BRF_ITM_DESC VARCHAR2(100)

    SQL> desc fa_sup_bill; The bills raised by suppliers against the Purchase orders
    Name Null? Type
    ------------------------------- -------- ----
    SCI_BILL_SL_NO NOT NULL NUMBER(10)
    SCI_BILL_RECPT_DT DATE
    SCI_BILL_NO VARCHAR2(50)
    SCI_BILL_DATE DATE
    SCI_ORD_NO VARCHAR2(40)
    SCI_ORD_DATE DATE
    SCI_PASSED_AMT NUMBER(13,2)
    SCI_BILL_PASS_DATE DATE
    SCI_PRT_CODE VARCHAR2(10)
    SCI_PRT_TYPE VARCHAR2(25)
    SCI_SRN_BILL_AMT NUMBER(13,2)
    SCI_BILL_AMT NUMBER(13,2)
    SCI_REC_AMT NUMBER(13,2)
    SCI_BILL_PASS_STTUS CHAR(1)
    SCI_BILL_SERIAL_NO NOT NULL VARCHAR2(20)

    SQL> desc fa_sup_bill_dtls;
    Name Null? Type
    ------------------------------- -------- ----
    SBD_SRN_NO NOT NULL VARCHAR2(20)
    SBD_SRN_DATE NOT NULL DATE
    SBD_SRN_AMT NUMBER(13,2)
    SBD_SCI_BILL_SL_NO NOT NULL NUMBER(10)
    ================================================== ====

    All foreign keys have been indexed.

    The purpose of this LOV is to give a list of pending bills which have not been processed for payment so the user can choose one of them and send it for release of payment.

    The system is working on Oracle 9i and Forms 6i.

    I thank you all for your valuable time. Although the problem is solved as of now I am still thinking Y this query takes so much time when the max no of rows in any table is not more than 2000.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What does explain plan say?

  9. #9
    Join Date
    Oct 2007
    Posts
    9
    Hi, Thanks.

    I am posting the explain plan and statistics for this query:

    ----------------------------------------------------------
    431 rows selected.


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5190 Card=466 Bytes=53124)
    1 0 FILTER
    2 1 HASH JOIN (OUTER) (Cost=64 Card=466 Bytes=53124)
    3 2 VIEW (Cost=53 Card=466 Bytes=34950)
    4 3 SORT (UNIQUE) (Cost=53 Card=466 Bytes=57368)
    5 4 UNION-ALL
    6 5 HASH JOIN (Cost=13 Card=60 Bytes=5400)
    7 6 TABLE ACCESS (FULL) OF 'FA_PREV_SRN' (Cost=2 Card=60 Bytes=3840)
    8 6 TABLE ACCESS (FULL) OF 'FA_PARTY' (Cost=10 Card=6175 Bytes=160550)
    9 5 HASH JOIN (Cost=27 Card=406 Bytes=51968)
    10 9 HASH JOIN (Cost=16 Card=407 Bytes=39479)
    11 10 HASH JOIN (Cost=11 Card=164 Bytes=15252)
    12 11 TABLE ACCESS (FULL) OF 'MMS_RCTS' (Cost=4 Card=164 Bytes=8856)
    13 11 TABLE ACCESS (FULL) OF 'MMS_POS' (Cost=6 Card=623 Bytes=24297)
    14 10 INDEX (FAST FULL SCAN) OF 'MRCD_PK' (UNIQUE)(Cost=4 Card=1273 Bytes=5092)
    15 9 TABLE ACCESS (FULL) OF 'MMS_SUPPLIERS' (Cost=10 Card=3545 Bytes=109895)
    16 2 TABLE ACCESS (FULL) OF 'FA_PARTY' (Cost=10 Card=6175 Bytes=240825)
    17 1 SORT (UNIQUE) (Cost=11 Card=13 Bytes=165)
    18 17 UNION-ALL
    19 18 NESTED LOOPS (Cost=3 Card=1 Bytes=21)
    20 19 TABLE ACCESS (FULL) OF 'FA_SUP_BILL' (Cost=2 Card=1 Bytes=5)
    21 19 INDEX (RANGE SCAN) OF 'SBD_PK1' (UNIQUE) (Cost=1 Card=1 Bytes=16)
    22 18 TABLE ACCESS (FULL) OF 'FA_SRN_PREV_LIABILITY' (Cost=2 Card=12 Bytes=144)

    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    34764204 consistent gets
    0 physical reads
    0 redo size
    55468 bytes sent via SQL*Net to client
    3530 bytes received via SQL*Net from client
    30 SQL*Net roundtrips to/from client
    6004 sorts (memory)
    0 sorts (disk)
    431 rows processed

    SQL>

    --------------------------------------------------------------------

  10. #10
    Join Date
    Oct 2007
    Posts
    9
    I also find that the 'server stats' under 'tuning option' of TOAD there is a red mark on "high parse to execute ratio". I am trying to make it OK with a thought that this might improve the query performance. But is I try to increase the "session_cached_cursors " to 10 from zero through enterprise manager it return Ora-02017 error saying that integer value is required. I can maually do it for the session using "alter session set session_cached_cursors = 100" but this remains valid only for the current session. Do u suggest this could be done by the ora.ini file

    The screenshot of TOAD is attached for ref.

    THANKS again
    Attached Thumbnails Attached Thumbnails servertats.GIF  

  11. #11
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Have you tried replacing the UNIONS with UNION ALL as then you should elimanate the two sorts.

    Also you seem to be joining to fa_party twice, once inside the pending subquery and then you outer join to it again in the outer query. Do you need to outer join to it again or can you get the data from the peding subquery.

    Alan

  12. #12
    Join Date
    Oct 2007
    Posts
    9
    Hi,

    Thanks for the time and patience for going through this complicated query.

    On your suggestion I immediately tried the UNION ALL but it generated many more rows (duplicates).

    I think that using "not exists" instead on "not in" may produce the result faster.

    Any more suggestions.

    Thanks

  13. #13
    Join Date
    Aug 2010
    Posts
    1
    Did you run the same query (in Forms and TOAD) connected to the same schema? Is there, perhaps, a database link involved?

  14. #14
    Join Date
    Aug 2010
    Location
    London
    Posts
    1
    Thanks very much for posting this . I have seen an instance to get a query executed by LOv and pass the parameter you are passing to LOv and then check that query you are fetching record or not, if the same query isfetching the record from TOAd or other client then some professional needs to look into this!

Tags for this Thread

Posting Permissions

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