Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2010
    Posts
    6

    Unanswered: Oracle sql performance issue

    Hi -

    The below query is taking time to execute (1 hour 40 minutes). Could you please help me to fine tune it?

    UPDATE TEST_LINE TL
    SET REL_CURR =
    (SELECT REL_CURR
    FROM SCH_LINE SL
    WHERE SL.TEST_ID = TL.TEST_ID
    AND SL.P_NUM = TL.P_NUM
    AND SL.P_LINE = TL.P_LINE
    )
    WHERE REL_CURR <>
    (SELECT REL_CURR
    FROM SCH_LINE SL
    WHERE SL.TEST_ID = TL.TEST_ID
    AND SL.P_NUM = TL.P_NUM
    AND SL.P_LINE = TL.P_LINE
    )


    I tried with not exists clause but still it is taking time.



    Info:
    Version:Oracle10r1
    Test_line= 10 Million rows
    sch_line= 50 thousand records
    Test_line is having index on (test_id,p_num,p_line)
    sch_lines is having index on (test_id,p_num,p_line)

    Thanks in Advance
    Parag

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post FORMATTED EXPLAIN PLAN
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2010
    Posts
    6
    Kindly find the enclosed explain plan for the update query
    Attached Files Attached Files

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    you need an index on TEST_LINE (rel_curr)
    or
    index maybe all of these columns together:
    TEST_LINE (test_id, p_num, p_line, rel_curr)


    I would experiment with both and test results.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Nov 2010
    Posts
    6
    Hi-

    Thanks!!

    As suggest, I created both the indexes...

    TEST_LINE (rel_curr)

    Update query used this index (confirmed by explain plan). But query took longer time and hang for around 2 hours.

    Same was the result for the 2nd index TEST_LINE (test_id, p_num, p_line, rel_curr)

    I guess, it took longer time, as Oracle also has to update the index column (rel_curr)


    Can this be tune by writing any plsql stored procedure?

    Thanks in Advance,
    -Parag

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >As suggest, I created both the indexes...
    were statistics gathered for the new indexes?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Nov 2010
    Posts
    6
    I have not explicitly created statitics on Indexes as statistics were automatically gerenarated after index were created.

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I guess it might improve if you remove the correlated subquery i.e.
    Code:
    UPDATE TEST_LINE TL
    SET REL_CURR =
    (
    SELECT REL_CURR
    FROM SCH_LINE SL
    WHERE SL.TEST_ID = TL.TEST_ID
    AND SL.P_NUM = TL.P_NUM
    AND SL.P_LINE = TL.P_LINE
    )
    WHERE rowid in
    (
    SELECT t.rowid
    FROM SCH_LINE SL, TEST_LINE T
    WHERE SL.TEST_ID = T.TEST_ID
    AND SL.P_NUM = T.P_NUM
    AND SL.P_LINE = T.P_LINE
    AND SL.REL_CURR<>T.REL_CURR
    )
    Alan

  9. #9
    Join Date
    Nov 2010
    Posts
    6
    Sorry for replying late. Thanks for the query. I am in the process for testing the update query. I will keep you posted for the result.

  10. #10
    Join Date
    Feb 2011
    Posts
    1

    Question hii

    Anyone could help me to find out the query execution time in sql comands .....

  11. #11
    Join Date
    Feb 2011
    Posts
    1

    try another syntax

    Maybe you can try the "merge into" syntax.

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Anyone could help me to find out the query execution time in sql comands .....

    SQL> SET TIME ON
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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