FW: Performance of MERGE INTO statement

Helo Jay,
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 :  

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3779680732446#15740265481549 http://jonathanlewis.wordpress.com/2006/12/12/plans-in-memory/  

"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)  

begin
  INSERT into FACT select * ....
exeption when UNIQUE_INDEX_VIOLATION
then
UPDATE FACT
end;  

You should not have so many duplicates, though, because internal processing of EXCEPTION clause is expensive.  

Best Regards, Milen    

-----Original Message-----
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.  

Thank you,
Jay

From: Milen Kulev [mailto:makulev_at_gmx.net] Sent: Thursday, May 15, 2008 3:00 PM
To: oracle-l_at_freelists.org
Cc: Hostetter, Jay M
Subject: RE:Performance of MERGE INTO statement  

Hello Jay,
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.    

HTH. MIlen  

**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.

--
http://www.freelists.org/webpage/oracle-l