FW: Performance of MERGE INTO statement

From: "Milen Kulev" <makulev@xxxxxxx>
To: <oracle-l-bounce@xxxxxxxxxxxxx>
Date: Sun, 18 May 2008 16:04:44 +0200
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@xxxxxxxxxxxxxxxxxxxx] 
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@xxxxxxx] 
Sent: Thursday, May 15, 2008 3:00 PM
To: oracle-l@xxxxxxxxxxxxx
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.
FW: Performance of MERGE INTO statement

Other related posts:
