Oracle Pro*C Bulk Merge of records in C using host arrays degrading performance with increased volume

Question

Using Oracle 11g Pro*C in C, I am using host arrays for managing bulk merge into DB table as per following example:

merge into TBL_NM A 
using (select 1 from dual) B 
on (col1 = to_date(:v,'YYMMDD') and col2 = :v and col3 = :v and col4 = :v) 
when matched then 
update set col5 = :v, col2 = col2+ :v, col3 = col3 + :v 
when not matched then  
insert (col1, col2, col3, col4, col5, col6, col7, col8) 
values (to_date(:v,'YYMMDD'),:v,:v,:v,:v,:v,:v,:v)

My first Question is: whether this way of bulk merge better than running query first to get the rowid and then if not found, insert and update otherwise. I found discussions here: Oracle Merge vs Select then Insert or Update and got the idea that merge is better than query then insert or update.

My second question is that I am observing degrading performance when volume increases. It takes more time to merge with higher volumes compared to DB having lower volume of data. Can this be fine tuned to make better performance and not to degrade performance with increased volume?

What are the possibilities of improvement within Pro*C boundary?

From http://www.dba-oracle.com/t_append_upsert_merge_sql.htm, it seems using the APPEND hint with the MERGE (upsert) SQL would be better? Please share your views.

Conative

This isn't really a bulk merge, this seems to be a single merge (run in a loop I suppose). A bulk merge would involve merging on a table (or at very least a pl/sql array). If you have large amount of data, it may be faster to load into a temp table then do the merge against temp. – Cesium

@tbone, it's indeed bulk merge using host arrays, not run in a loop, rather executing the statement through PRO*C using host arrays as per docs.oracle.com/cd/B10501_01/appdev.920/a97269/pc_08arr.htm – Conative

Con you provide some metrics. 1. How long does it take? 2. How many rows in the table? 3 How many rows are being updated? 4. How many rows inserted? 5. What is the size of your host array. 6. Can you share a SQL Monitor report. – Motherland

@Motherland I don't have any SQL Monitor report, but with increased volume performance seems to be degrading, my Question is Can the given MERGE query degrade performance with increased volume? – Conative

Can you answer the 5 other questions I asked? – Motherland

If you can get a Monitor report, please get at least an execution plan. – Motherland

@Motherland With about 18 Million data being passed through upsert/merge (about 30% update and 70% insert), it is taking approx 12 hours and situation is degrading with further attempt of merge. Size of the host array is 40K at a time, for more data, we execute MERGE multiple times each time with max 40K data passed through host arrays – Conative

Have you thought about an index on col1, col2, col3? – Pinkeye