FW: Performance of MERGE INTO statement
Date: Sun, 18 May 2008 16:04:44 +0200
no, CARDINALITY hint is not so new. I have used it many times on 9i and 10g, but I think the hint is (still) not officially documented ;(
Some udeful links for fast-start :
"I think I might try using an INSERT statement for the majority of the records, then possibly PL/SQL to update remaining
records. I've had a least one reply where I was told that it is unusual to update fact table records. Yet in my case, I don't see how I can avoid it.
do you have an unique index on the fact table ? If yes, you can use (just as rough scratch)
INSERT into FACT select * ....
exeption when UNIQUE_INDEX_VIOLATION
You should not have so many duplicates, though, because internal processing of EXCEPTION clause is expensive.
Best Regards, Milen
From: Hostetter, Jay M [mailto:JHostetter_at_decommunications.com] Sent: Friday, May 16, 2008 2:07 PM
To: Milen Kulev
Subject: RE: Performance of MERGE INTO statement
Thanks for the reply Milen.
I can get better performance if I specify all of the indexes, but I thought that seemed a bit extreme. Is the CARDINALITY hint new for 10g? I can't find any docs on it.
I think I might try using an INSERT statement for the majority of the records, then possibly PL/SQL to update remaining records. I've had a least one reply where I was told that it is unusual to update fact table records. Yet in my case, I don't see how I can avoid it.
From: Milen Kulev [mailto:makulev_at_gmx.net]
Sent: Thursday, May 15, 2008 3:00 PM
Cc: Hostetter, Jay M
Subject: RE:Performance of MERGE INTO statement
recently I had athe same problem with MERGE statement (again AIX 5.2, Oracle 10.2.0.3). I had a similar (as a structure) execution plan (with no so many dimention tables and without SORT GROUP BY ). I have found that:
1) So some reason Oracle was reading the soure and targer tables (FTS) only blobk by block (instead in chunks -> db_block_size(16K)* db_multiblock_read_count(64)= 1MB). Performance of the storage box was OK (~ 4-6 ms per access, 64 blocks were accesed in 7-10 ms).
Is this the case by you too.
2) I think the problem is in the early stages of the execution plan
|* 11 | HASH JOIN | | 1 | 169 | 1547 | | | | 12 | TABLE ACCESS FULL| TRUNK_DIM | 675 | 36450 | 3 | | | | 13 | TABLE ACCESS FULL| CALL_STAGING | 563K| 61M| 1367 | | |
If you manually join the first two tables does the hash join really filter out only one row ? I believe NESTED LOOPS OUTER is overly optimistic in this case. Can you try using CARDINALITY hint with the view ? Specify a much bigger value for the number of rows coming from the view e.g. MERGE ... select /*+ CARDINALITY(V 100000) */ from V WHEN... etc. The execution plan should then switch to HASH JOIN OUTER join method.
**DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business.Received on Sun May 18 2008 - 09:04:44 CDT