Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2003
    Posts
    6

    Red face Unanswered: Performance Issue in Oracle8i

    Same query in Oracle8i runs much slower than in Oracle9i. Platform is Linux. The amount of data in the table is exactly same.

    The following query when run in Oracle9i(Linux Server) takes 0.3 seconds to execute while in Oracle8i(Linux Server) takes 47.017 seconds to execute.

    select count(distinct(m_itemcode)) from itemattpart partition(P_444) where m_itemcode in (Select m_itemcode from itemattpart partition(P_444) where m_attributeid =272 and M_LANGUAGEID=1 and upper(chardata)='HIGH SPEED STEEL') AND m_itemcode in (Select m_itemcode from itemattpart partition(P_444) where m_attributeid =503 and M_LANGUAGEID=1 and upper(chardata)='TIN') Order By m_itemcode asc

    I don't know what to do.

    Any help would be very much appreciated.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    - Do both servers have the same configuration (SGA size etc).
    - Are they both using the same optimiser goal ("choose" I would expect)
    - Are the table statistics up to date on the 8i (analyze tables....)
    - Are the tablespaces/extent sizes the same?

    Hth
    Bill

  3. #3
    Join Date
    Aug 2003
    Posts
    6
    They have the same configuration

    Both are using the same optimizer goal

    Moreover the Execution Plan window shows that:

    The Oracle9i DB is using Hash Join to retrieve the results while Oracle8i DB is using Merge Cartesian Join to get the results.

    Could that be the reason for Oracle8i performance degradation?

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Yes, absolutely. Could you post the explain plans for the queries from both servers.

    Cheers
    Bill

  5. #5
    Join Date
    Aug 2003
    Posts
    6
    The execution play for Oracle9i is as follows:

    SELECT STATEMENT, GOAL=CHOOSE
    HASH JOIN
    TABLE ACCESS BY GLOBAL INDEX ROWID
    NESTED LOOPS
    TABLE ACCESS BY GLOBAL INDEX ROWID
    INDEX RANGE SCAN
    INDEX RANGE SCAN
    TABLE ACCESS BY GLOBAL INDEX ROWID
    INDEX RANGE SCAN

    The execution plan for Oracle8i is as follows:

    SELECT STATEMENT, GOAL=CHOOSE
    SORT GROUP BY
    NESTED LOOPS
    MERGE JOIN CARTESIAN
    TABLE ACCESS BY GLOBAL INDEX ROWID
    INDEX RANGE SCAN
    SORT JOIN
    TABLE ACCESS BY GLOBAL INDEX ROWID
    INDEX RANGE SCAN
    TABLE ACCESS BY GLOBAL INDEX ROWID
    INDEX RANGE SCAN


    This is the exact hierarchy in which it is showing in Explain Plan Window of PL/SQL Developer.

  6. #6
    Join Date
    Aug 2003
    Posts
    6
    I am again posting the hirearchy for both 9i and 8i. This would be much clearer.

    Oracle9i Explain Plan

    SELECT STATEMENT, GOAL=CHOOSE
    ---SORT GROUP BY
    -----HASH JOIN
    -------TABLE ACCESS BY GLOBAL INDEX ROWID
    ---------NESTED LOOPS
    -----------TABLE ACCESS BY GLOBAL INDEX ROWID
    -------------INDEX RANGE SCAN
    -----------INDEX RANGE SCAN
    -------TABLE ACCESS BY GLOBAL INDEX ROWID
    ---------INDEX RANGE SCAN


    Oracle8i Explain Plan

    SELECT STATEMENT, GOAL=CHOOSE
    ---SORT GROUP BY
    -----NESTED LOOPS
    -------MERGE JOIN CARTESIAN
    ---------TABLE ACCESS BY GLOBAL INDEX ROWID
    -----------INDEX RANGE SCAN
    ---------SORT JOIN
    -----------TABLE ACCESS BY GLOBAL INDEX ROWID
    -------------INDEX RANGE SCAN
    -------TABLE ACCESS BY GLOBAL INDEX ROWID
    ---------INDEX RANGE SCAN

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Wierd, are you sure the statistics are up to date on both instances - and that they have the same analyze conditions (estimates, histograms etc). Check in USER_TAB_HISTOGRAMS and USER_TAB_COL_STATISTICS for this table.

    The explain plan you have posted doesn't show the actual indexes used, or the expected number of rows etc returned - while the structure looks similar they could be on different indexes. Need to see the full plan to be sure.

    Also, this is a long shot but could you check the OPTIMIZER_FEATURES_ENABLE parameter on both instances. To eliminate actual 8i/9i optimiser efficiency, I believe you could set your 9i instance to the same value as your 8i instance, if the difference is still there then atleast it's not caused by the versions.

    select * from v$parameter where name like 'optim%'

    Cheers
    Bill

  8. #8
    Join Date
    Aug 2003
    Posts
    6

    Performance Issue in Oracle8i

    The following information could be of much use:

    TABLE NAME: ITEMATTPART

    COLUMN NAMES:
    M_CLASSIFICATIONID
    M_ITEMCODE
    M_ATTRIBUTEID
    CHARDATA
    M_LANGUAGEID
    UOM
    DECIEQUI


    INDEXES USED:
    Index Name ---------------------- Columns Used
    IDX_ATTLANG_ITMATTPART ---------- M_ATTRIBUTEID,M_LANGUAGEID
    IDX_ITMCDATTLANG_ITMATTPART ----- M_ITEMCODE,M_ATTRIBUTEID,M_LANGUAGEID


    The following query when fired returns 17 rows in 0.14 seconds.
    select * from CMS.itemattpart partition(P_444)


    FOLLOWING DESCRIBES THE EXPLAIN PLAN ON BOTH OF THE SERVER ALONG WITH THEIR INDEX NAMES

    Oracle9i Explain Plan

    DESCRIPTION======================================= =========OBJECT NAME

    SELECT STATEMENT, GOAL=CHOOSE
    ---SORT GROUP BY
    -----HASH JOIN
    -------TABLE ACCESS BY GLOBAL INDEX ROWID=================itemattpart
    ---------NESTED LOOPS
    -----------TABLE ACCESS BY GLOBAL INDEX ROWID=============itemattpart
    -------------INDEX RANGE SCAN=============================idx_attlang_itmat tpart
    -----------INDEX RANGE SCAN===============================idx_itmcdattlan g_itmattpart
    -------TABLE ACCESS BY GLOBAL INDEX ROWID=================itemattpart
    ---------INDEX RANGE SCAN=================================idx_attlang_i tmattpart


    Oracle8i Explain Plan

    DESCRIPTION======================================= ==========OBJECT NAME

    SELECT STATEMENT, GOAL=CHOOSE
    ---SORT GROUP BY
    -----NESTED LOOPS
    -------MERGE JOIN CARTESIAN
    ---------TABLE ACCESS BY GLOBAL INDEx ROWID=================itemattpart
    -----------INDEX RANGE SCAN=================================idx_attlang_i temattpart
    ---------SORT JOIN
    -----------TABLE ACCESS BY GLOBAL INDEX ROWID===============itemattpart
    -------------INDEX RANGE SCAN===============================idx_attlang_ite mattpart
    -------TABLE ACCESS BY GLOBAL INDEX ROWID===================itemattpart
    ---------INDEX RANGE SCAN===================================idx_itmcdat tlang_itmattpart

  9. #9
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Nital,

    Are the estimates for rows returned in both plans the same?

    Cheers
    Bill

  10. #10
    Join Date
    Aug 2003
    Posts
    6
    Hi Bill,

    The estimate of rows returned in both the plan are exactly the same.

  11. #11
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Nital,

    The last thing I can think of is stored outlines (basically saved execution plans which override any statistics).

    Try checking the USER_OUTLINES view on both instances. If there are no outlines being used then I'm stumped, I would probably go through the table creation scripts, index scripts, db params etc with a fine tooth comb if there is still nothing to explain it you might consider opening a support call at Oracle.

    Sorry I can't be of more help.

    Cheers
    Bill

Posting Permissions

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