slow performance of MERGE statement
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: slow performance of MERGE statement

  1. #1
    Join Date
    Apr 2001
    Posts
    125

    slow performance of MERGE statement

    Hi

    I have a merge statement which is used to INSERT/UPDATE table A from another table B, table A and table B has exactly same structure, I expect Oracle to use the primary key of table A, but it doesn't, instead, Oracle choose to use a foreign key on table A - FK_A_TO_C, the table this foreign key pointing to is an empty table - table C, I checked the 10046 trace of the statement, it shows:

    ...
    0 TABLE ACCESS BY INDEX ROWID A(cr=29511977 pr=0 pw=0 time=1184298365 us)
    1041617280 INDEX RANGE SCAN FK_A_TO_C (cr=2859856 pr=0 pw=0 time=142514 us)(object id 101659)

    Although table C is empty, table A contains around 100000 rows, table B contains 10000 rows, but Oracle use FK_A_TO_C index to gather 1041617280 rows, and it takes 1184298365/1000000=1184 seconds, why Oracle is doing this? Should I add /*+ INDEX(A PK_A) */ hint to MERGE statement to force it to use PK_A or use a stored outline?

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Are you saying you have a merge in between tables A and B and, Oracle is reading from a third table?

    If Yes... look at your Synonyms
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Apr 2001
    Posts
    125
    No, what I am saying is MERGE statement use incorrect index and gather wrong number of rows by using that index

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Would you mind in posting actual MERGE statement?
    Can you eventually drop the incorrect index and check the execution plan?
    Have you checked your execution plan after including the hint?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Apr 2001
    Posts
    125
    For security reason, I can't post the statement. I plan to try the hint first and see if it solves the problem.

  6. #6
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Yes
    HTML Code:
    table A contains around 100000 rows, table B contains 10000 rows, but Oracle use FK_A_TO_C index to gather 1041617280 rows, and it takes 1184298365/1000000=1184 seconds, why Oracle is doing this? Should I add /*+ INDEX(A PK_A) */ hint to MERGE statement to force it to use PK_A or use a stored outline?
    Yes, it could help you. But need to be tested. Some times oracle picks wrong indexes, reason could be insufficient stats or bind variable...
    http://www.perf-engg.com
    A performance engineering forum

  7. #7
    Join Date
    Apr 2001
    Posts
    125
    Malay,

    we gather stats on all index and columns with estimated size = 100 every night, what do you mean "bind variable" is a reason?
    Oracle 8, 8i, 9i OCP DBA
    Oracle 6/6i OCP DEV
    Sun Solaris8 SCSA
    MCDBA 2000

  8. #8
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Is this post a question or just a conclusion? Seems u know everything and know that the index is wrong and that you have done the things fine and CBO is wrong. Then if you hint it it should be better

  9. #9
    Join Date
    Apr 2001
    Posts
    125
    I just want to konw if anyone else experienced the same issue before and know in and out of it.

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Once again...

    Can you eventually drop the incorrect index and check the execution plan?
    Can you eventually rebuild both the incorrect and the expected index and check the execution plan?
    Have you checked your execution plan after including the hint?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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