-
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?
-
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.
-
No, what I am saying is MERGE statement use incorrect index and gather wrong number of rows by using that index
-
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.
-
For security reason, I can't post the statement. I plan to try the hint first and see if it solves the problem.
-
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...
-
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
-
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
-
I just want to konw if anyone else experienced the same issue before and know in and out of it.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|