DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: performance problem in 8.1.7.2

  1. #1
    Join Date
    Aug 2000
    Posts
    52

    Angry

    We have migrated to Oracle ver 8.1.7 from 8.0.6 .
    We have done this migration through full export .
    I have one sql query which was performaing well in 8.0.6 but the same query is not performing well in 8.1.7.
    SQL plus hangs. The explain plan fpr the query shows NESTED LOOP AND NESTED LOOP OUTER..But this query was performing very well in 8.0.6.ALL the indexes are also same.Other resources are not a problem .
    OS IS SUN SOLARIS

    Can any body help me to resolve this problem ?

    Thanks.

    Sanjay

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Do you have the tables and indices analyzed?

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Aug 2000
    Posts
    52

    Angry

    Yes !
    Tables and indexes both are analyzed.

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    What is the optimizer mode?

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Aug 2000
    Posts
    52

    Angry


    Optimizer_mode is CHOOSE..This is peoplesoft application

    Sanjay

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Could you post both the query and the explain plan for 8.0.6 and 8.1.7

    -Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    Aug 2000
    Posts
    52

    Angry

    SELECT B.IESG_BROKER_ID, B.NAME, K.DESCR, L.DESCR, ' ', ' ', SUM( decode( A.ANALYSIS_TYPE,'RJE', A.RESOURCE_AMOUNT,0)), D.IESG_RANK, D.RESOURCE_AMOUNT, F.IESG_RANK, F.RESOURCE_AMOUNT, H.IESG_RANK, H.RESOURCE_AMOUNT, J.IESG_RANK, J.RESOURCE_AMOUNT
    FROM PS_PROJ_RESOURCE A, PS_IESG_BROKER_TBL B, PS_IESG_BROKER_TBL C, PS_IESG_RANK_TBL D, PS_IESG_BROKER_TBL E, PS_IESG_RANK_TBL F, PS_IESG_BROKER_TBL G, PS_IESG_RANK_TBL H, PS_IESG_BROKER_TBL I, PS_IESG_RANK_TBL J, PS_IESG_OFFICE_TBL K, PS_IESG_BROK_TITLE L
    WHERE A.ANALYSIS_TYPE(+) = 'RJE'
    AND A.ACCOUNTING_DT(+) BETWEEN TO_DATE('2001-01-01','YYYY-MM-DD') AND TO_DATE('2001-11-08','YYYY-MM-DD')
    AND A.IESG_BROKER_ID(+) = B.IESG_BROKER_ID
    AND B.EFFDT =
    (SELECT MAX(B_ED.EFFDT) FROM PS_IESG_BROKER_TBL B_ED
    WHERE B.SETID = B_ED.SETID
    AND B.IESG_BROKER_ID = B_ED.IESG_BROKER_ID
    AND B_ED.EFFDT <= SYSDATE)
    AND EXISTS (SELECT 'X' FROM PSTREESELECT06 B3_4 WHERE B3_4.SELECTOR_NUM=6380 AND B3_4.TREE_NODE_NUM BETWEEN 894736817 AND 905263132 AND B3_4.RANGE_FROM_06 = B.IESG_OFFICE_ID)
    AND B.SETID = C.SETID
    AND B.IESG_BROKER_ID = C.IESG_BROKER_ID
    AND C.EFFDT =
    (SELECT MAX(C_ED.EFFDT) FROM PS_IESG_BROKER_TBL C_ED
    WHERE C.SETID = C_ED.SETID
    AND C.IESG_BROKER_ID = C_ED.IESG_BROKER_ID
    AND C_ED.EFFDT <= B.EFFDT)
    AND C.IESG_BROKER_ID = D.IESG_BROKER_ID(+)
    AND D.BUSINESS_UNIT(+) = 'IESG'
    AND D.FISCAL_YEAR(+) = '2000'
    AND C.SETID = E.SETID
    AND C.IESG_BROKER_ID = E.IESG_BROKER_ID
    AND E.EFFDT =
    (SELECT MAX(E_ED.EFFDT) FROM PS_IESG_BROKER_TBL E_ED
    WHERE E.SETID = E_ED.SETID
    AND E.IESG_BROKER_ID = E_ED.IESG_BROKER_ID
    AND E_ED.EFFDT <= C.EFFDT)
    AND E.IESG_BROKER_ID = F.IESG_BROKER_ID(+)
    AND E.SETID = G.SETID
    AND E.IESG_BROKER_ID = G.IESG_BROKER_ID
    AND G.EFFDT =
    (SELECT MAX(G_ED.EFFDT) FROM PS_IESG_BROKER_TBL G_ED
    WHERE G.SETID = G_ED.SETID
    AND G.IESG_BROKER_ID = G_ED.IESG_BROKER_ID
    AND G_ED.EFFDT <= E.EFFDT)
    AND G.IESG_BROKER_ID = H.IESG_BROKER_ID(+)
    AND G.SETID = I.SETID
    AND G.IESG_BROKER_ID = I.IESG_BROKER_ID
    AND I.EFFDT =
    (SELECT MAX(I_ED.EFFDT) FROM PS_IESG_BROKER_TBL I_ED
    WHERE I.SETID = I_ED.SETID
    AND I.IESG_BROKER_ID = I_ED.IESG_BROKER_ID
    AND I_ED.EFFDT <= G.EFFDT)
    AND I.IESG_BROKER_ID = J.IESG_BROKER_ID(+)
    AND F.BUSINESS_UNIT(+) = 'IESG'
    AND H.BUSINESS_UNIT(+) = 'IESG'
    AND J.BUSINESS_UNIT(+) = 'IESG'
    AND F.FISCAL_YEAR(+) = '1999'
    AND H.FISCAL_YEAR(+) = '1998'
    AND J.FISCAL_YEAR(+) = '1997'
    AND B.SETID = K.SETID
    AND B.IESG_OFFICE_ID = K.IESG_OFFICE_ID
    AND K.EFFDT =
    (SELECT MAX(K_ED.EFFDT) FROM PS_IESG_OFFICE_TBL K_ED
    WHERE K.SETID = K_ED.SETID
    AND K.IESG_OFFICE_ID = K_ED.IESG_OFFICE_ID
    AND K_ED.EFFDT <= SYSDATE)
    AND B.SETID = L.SETID
    AND L.EFFDT =
    (SELECT MAX(L_ED.EFFDT) FROM PS_IESG_BROK_TITLE L_ED
    WHERE L.SETID = L_ED.SETID
    AND L.IESG_BROKER_TITLE = L_ED.IESG_BROKER_TITLE
    AND L_ED.EFFDT <= SYSDATE)
    AND B.IESG_BROKER_TITLE = L.IESG_BROKER_TITLE
    GROUP BY B.IESG_BROKER_ID, B.NAME, K.DESCR, L.DESCR, ' ', ' ', D.IESG_RANK, D.RESOURCE_AMOUNT, F.IESG_RANK, F.RESOURCE_AMOUNT, H.IESG_RANK, H.RESOURCE_AMOUNT, J.IESG_RANK, J.RESOURCE_AMOUNT
    ORDER BY 7 DESC;

    Explain Plan


    Query Plan
    ----------------------------------------------------------------------------------------------------
    SORT ORDER BY
    SORT GROUP BY
    FILTER
    NESTED LOOPS OUTER
    NESTED LOOPS OUTER
    NESTED LOOPS
    NESTED LOOPS OUTER
    NESTED LOOPS
    NESTED LOOPS OUTER
    NESTED LOOPS
    NESTED LOOPS OUTER
    NESTED LOOPS
    NESTED LOOPS
    NESTED LOOPS
    TABLE ACCESS FULL PS_IESG_BROKER_TBL
    TABLE ACCESS FULL PS_IESG_BROK_TITLE
    TABLE ACCESS FULL PS_IESG_OFFICE_TBL
    INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
    TABLE ACCESS BY INDEX ROWID PS_IESG_RANK_TBL
    INDEX UNIQUE SCAN PS_IESG_RANK_TBL
    INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
    TABLE ACCESS BY INDEX ROWID PS_IESG_RANK_TBL
    INDEX UNIQUE SCAN PS_IESG_RANK_TBL
    INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
    TABLE ACCESS BY INDEX ROWID PS_IESG_RANK_TBL
    INDEX UNIQUE SCAN PS_IESG_RANK_TBL
    INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
    TABLE ACCESS BY INDEX ROWID PS_IESG_RANK_TBL
    INDEX UNIQUE SCAN PS_IESG_RANK_TBL
    TABLE ACCESS BY INDEX ROWID PS_PROJ_RESOURCE
    INDEX RANGE SCAN PSYPROJ_RESOURCE
    SORT AGGREGATE
    INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
    INDEX RANGE SCAN PS_PSTREESELECT06
    SORT AGGREGATE
    INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
    SORT AGGREGATE
    INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
    SORT AGGREGATE
    INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
    SORT AGGREGATE
    INDEX FAST FULL SCAN PS_IESG_BROKER_TBL
    SORT AGGREGATE
    INDEX FAST FULL SCAN PS_IESG_OFFICE_TBL
    SORT AGGREGATE
    FIRST ROW
    INDEX RANGE SCAN (MIN/MAX) PS_IESG_BROK_TITLE

  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    First of all, could you try these parameters:
    Code:
    ALTER SESSION/SYSTEM SET OPTIMIZER_FEATURES_ENABLED=8.0.6
    Try the quey. If it doesn't perform well, then set
    Code:
    ALTER SYSTEM/SESSION SET OPTIMIZER_MODE=FIRST_ROWS
    Also please check your sort area size too...

    -Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  9. #9
    Join Date
    Aug 2000
    Posts
    52

    Angry

    SQL> alter system set optimizer_features_enable=8.0.6;
    alter system set optimizer_features_enable=8.0.6
    *
    ERROR at line 1:
    ORA-02095: specified initialization parameter cannot be modified


    optimizer_mode = first_row is also not working.

    Sort_area_size is 25600 which is same as an 8.0.6 database.

    The problem is This query is working very well oracle 8.0.6.

    Very strange ..Pl help......

    Sanjay

  10. #10
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Oops that was a wrong suggestion. Its that you have to specify the parameter in the init.ora file and bounce the instance ....


    Sorry about that.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


Posting Permissions

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


Click Here to Expand Forum to Full Width