Results 1 to 4 of 4

Thread: Slow response

  1. #1
    Join Date
    Oct 2005
    Posts
    39

    Unanswered: Slow response

    Hello,

    When I try to save the data through forms it is taking 2 minutes and 30
    Seconds for each save.

    I am using Oracle 8.1.7.3 and forms 6i on Suse Linux 7.1

    I enabled trace for the session where I was saving data through forms.
    In *.trf file I can see one section where I see some pointers for
    possible performance problems. That section from trf file is pasted
    below :


    ************************************************** *******************************


    SELECT A.FPL_ID,A.PZUG_ID,A.PZV_KEY,A.EINBRUCHZEIT,A.STRE CKE_ID,
    A.BST_REIHENFOLGE,COUNT(*),B.ZN
    FROM
    PZV_BSTKETTE A,PFPL_ZUG B WHERE (A.PZUG_ID = B.PZUG_ID ) GROUP BY
    A.FPL_ID,


    A.PZUG_ID,A.PZV_KEY,A.EINBRUCHZEIT,A.STRECKE_ID,A. BST_REIHENFOLGE,B.ZN
    HAVING COUNT(*) > 1


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 20 0.00 0.00 0 0 0 0
    Fetch 20 4.93 89.37 12388 157899 1400 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 41 4.93 89.37 12388 157899 1400 0


    Misses in library cache during parse: 0
    Optimizer goal: RULE
    Parsing user id: 40 (recursive depth: 1)
    ************************************************** *******************************


    Then I tried to execute the above mentioned query manually and enabled
    the trace for this session. That section (from *.trf file)
    corresponding to that query is pasted below:


    ************************************************** *******************************


    SELECT A.FPL_ID,A.PZUG_ID,A.PZV_KEY,A.EINBRUCHZEIT,A.STRE CKE_ID,
    A.BST_REIHENFOLGE,COUNT(*),B.ZN
    FROM
    PZV_BSTKETTE A,PFPL_ZUG B WHERE (A.PZUG_ID = B.PZUG_ID ) GROUP BY
    A.FPL_ID,
    A.PZUG_ID,A.PZV_KEY,A.EINBRUCHZEIT,A.STRECKE_ID,A. BST_REIHENFOLGE,B.ZN
    HAVING COUNT(*) > 1


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 1 0.32 4.57 620 7895 70 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 3 0.32 4.57 620 7895 70 0


    Misses in library cache during parse: 1
    Optimizer goal: RULE
    Parsing user id: 24


    Rows Row Source Operation
    ------- ---------------------------------------------------
    0 FILTER
    44625 SORT GROUP BY
    44624 NESTED LOOPS
    3665 TABLE ACCESS FULL PFPL_ZUG
    44624 TABLE ACCESS BY INDEX ROWID PZV_BSTKETTE
    48288 INDEX RANGE SCAN (object id 6452)


    ************************************************** *******************************


    Please let me know your comments, why through froms it is taking more time. Also, where to check for possible problems..??

    Thanks, Rajeev

  2. #2
    Join Date
    Oct 2005
    Posts
    61
    what is the execution plan for the forms?
    It maybe using a different execution plan. You need to check the execution plan and also check on what optimizer settings that query is run by the forms.

    Hope it helps!

    Thanks,

  3. #3
    Join Date
    Oct 2005
    Posts
    39
    Srinivas,

    Thanks for the reply.

    Can you please suggest , how to find the execution plan of forms ? I am new to forms, thats why asking silly question.

    Also, about optimizer, I understand it will use RULE based optimizer which is the setting of Database.

    Regards,
    Raj

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    The exec plans you posted are using RULE.
    1) Check that the OPTIMIZER_MODE=CHOOSE in init${sid}.ora file
    2) Gather statistics for all tables/indexes you are using.



    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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