Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Unanswered: Query Optimization

    Hello Gurus,
    I have one update query.

    UPDATE CODE_MASTER SM
    SET F_CURRENT = 'N',
    CHG_CURR_DATE = SYSDATE
    WHERE DB_CODE IN
    (SELECT DISTINCT DB_CODE
    FROM LOG_INFORMATICA
    WHERE DWH_BEG IS NOT NULL
    AND DWH_END IS NULL
    INTERSECT
    SELECT DB_CODE
    FROM DB_MASTER
    WHERE REGION_CODE = Region
    )
    AND F_CURRENT = 'Y'
    AND (DB_CODE, ITEM_NUMBER) NOT IN
    (SELECT IM.DB_CODE, ITEM_NUMBER
    FROM DATAP_AM.CODE_MASTER IM, LOG_INFORMATICA LI
    WHERE DWH_BEG IS NOT NULL
    AND DWH_END IS NULL
    AND IM.DB_CODE = LI.DB_CODE
    UNION
    SELECT IM.DB_CODE, ITEM_NUMBER
    FROM DATAP_AP.CODE_MASTER IM, LOG_INFORMATICA LI
    WHERE DWH_BEG IS NOT NULL
    AND DWH_END IS NULL
    AND IM.DB_CODE = LI.DB_CODE
    UNION
    SELECT IM.DB_CODE, ITEM_NUMBER
    FROM DATAP_EU.CODE_MASTER IM, LOG_INFORMATICA LI
    WHERE DWH_BEG IS NOT NULL
    AND DWH_END IS NULL
    AND IM.DB_CODE = LI.DB_CODE
    );


    I ran this query in test as well as production DB.
    Explain on test database

    OPERATION OPTIONS OBJECT_NAME
    ------------------------------ --------------- --------------------
    UPDATE STATEMENT
    UPDATE CODE_MASTER
    TABLE ACCESS BY INDEX ROWID CODE_MASTER
    NESTED LOOPS
    VIEW VW_NSO_1
    INTERSECTION
    SORT UNIQUE
    TABLE ACCESS FULL LOG_INFORMATICA
    SORT UNIQUE
    TABLE ACCESS FULL DB_MASTER
    INDEX RANGE SCAN I_CODE_MASTER_DBCODE

    OPERATION OPTIONS OBJECT_NAME
    ------------------------------ --------------- --------------------
    _ITEMNO

    SORT UNIQUE
    UNION-ALL
    NESTED LOOPS
    INDEX UNIQUE SCAN PK_CODE_MASTER
    TABLE ACCESS FULL LOG_INFORMATICA
    NESTED LOOPS
    INDEX UNIQUE SCAN PK_CODE_MASTER
    TABLE ACCESS FULL LOG_INFORMATICA
    NESTED LOOPS

    OPERATION OPTIONS OBJECT_NAME
    ------------------------------ --------------- --------------------
    INDEX UNIQUE SCAN PK_CODE_MASTER
    TABLE ACCESS FULL LOG_INFORMATICA

    Explain Plan for Production database

    OPERATION OPTIONS OBJECT_NAME
    ------------------------------ --------------- --------------------
    UPDATE STATEMENT
    UPDATE CODE_MASTER
    FILTER
    HASH JOIN
    VIEW VW_NSO_1
    INTERSECTION
    SORT UNIQUE
    TABLE ACCESS FULL LOG_INFORMATICA
    SORT UNIQUE
    TABLE ACCESS FULL DB_MASTER
    TABLE ACCESS FULL CODE_MASTER

    OPERATION OPTIONS OBJECT_NAME
    ------------------------------ --------------- --------------------
    SORT UNIQUE
    UNION-ALL
    NESTED LOOPS
    INDEX UNIQUE SCAN PK_CODE_MASTER
    TABLE ACCESS FULL LOG_INFORMATICA
    NESTED LOOPS
    INDEX UNIQUE SCAN PK_CODE_MASTER
    TABLE ACCESS FULL LOG_INFORMATICA
    NESTED LOOPS
    INDEX UNIQUE SCAN PK_CODE_MASTER
    TABLE ACCESS FULL LOG_INFORMATICA

    In production database CODE_MASTER is having full table scan it is not using index I_CODE_MASTER_DBCODE_ITEMNO.
    Same index in used in Test database. I also tried to force the query the used the index I_CODE_MASTER_DBCODE_ITEMNO
    but it is not using it. i.e

    UPDATE /*+INDEX(DATAP.CODE_MASTER I_CODE_MASTER_DBCODE_ITEMNO)*/ CODE_MASTER SM
    SET F_CURRENT = 'N',
    CHG_CURR_DATE = SYSDATE
    ---- --- --- ---
    -- ----- --
    Please help me.

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Query Optimization

    1) Analyze all tables involved
    2) Remember, full table scan is not "bad", it might be right for the production database.
    3) Your EXPLAIN PLAN results are almost unreadable without any indentation, and aren't showing useful information such as cost, cardinality.
    4) Don't use hints; instead, fix the problem (if there is one)

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,
    Sorry for the inconvience. Please have a look on the attachment
    All tables are analyzed on 21-FEB-04.

    We are using oracle 9.2.0.3.0 for Datware housing.Lot of huge queries are not runing properly infact i think oracle is not making proper decision for execution plan (hope) . But when i useds hints queries are runing fast

    Can u please tell what are the things to looked up to make the queries run faster

    Thanks
    Attached Files Attached Files
    Pagnint
    (No need to search web before posting new question)

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

    Your incomplete EXPLAIN_PLAN suggests that the optimizer mode is set to rule, rather than COST or CHOOSE.

    Run this query and post the result.
    select * from v$parameter where name = 'optimizer_mode'

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Has a lot of data been loaded into those tables since 21 Feb? According to the explain plan output it is expecting to obtain only a few rows from these tables - i.e. 1000 rows from CODE_MASTER, 56 from LOG_INFORMATICA, ... If Oracle thought that was "most" of the rows, it would go for a full table scan.

    How big is CODE_MASTER, and what indexes are defined on it?

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Oops,

    I was looking at the plans from your original post, not from the later attachment. Ignore my ramblings...
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  7. #7
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Total number of rows in CODE_MASTER= 2031820
    Rows inserted after 21-feb-2004 =1052

    What wrong in the query why it is taking time to execute.?

    Thanks
    Pagnint
    (No need to search web before posting new question)

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by paginit
    Total number of rows in CODE_MASTER= 2031820
    Rows inserted after 21-feb-2004 =1052

    What wrong in the query why it is taking time to execute.?

    Thanks
    Don't know. You haven't stated what indexes exist yet.

Posting Permissions

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