oracle insert slow

[reachmexyz]

Hello all

I have searched the forum, but couldnt find an answer which solves my problem.

I have two test jobs like

OCI---XFM----File (server job) & OCI---XFM----Dataset (parallel job)
Running on a 2 node processor. Pulling 2 million records from oracle.
Server job ran at 8000 rows/sec and parallel at 12000 rows/sec.

In the next phase, i am feeding file and datasets to oracle table. JObs like

File----xfm---OCI(server) & Dataset --- XFM ---- OCI (Parallel).

Now server job is loading the table at 3000 rows/sec and parallel at 1000 rows/sec.

parallel job is performing slower than server job.
Table has no constraints, No Indexes. I have truncated the table before loading the table.
All the DS JObs are on the same server and accessign same tables.

How can i make sure parallel jobs performs better than Server. Am i missing any details in Parallel Job? Please advice

[chulett]

OCI is not a parallel stage, do you mean OE? What settings are you using for the Oracle side, both jobs? Particularly wondering what the PX 'Method' is.

[reachmexyz]

OCI is not a parallel stage, do you mean OE? What settings are you using for the Oracle side, both jobs? Particularly wondering what the PX 'Method' is.

Its OE.
On Server side i am using "Insert rows without clearing"
On Paralle Sie i am using write method "Upsert".In the updatesql clause i have written the INsert statement. (Jobs do plain inserts only).
I cannot use Write method as Load as inserts will be out of my control adn there are some not null constraints on the columns.

[reachmexyz]

OCI is not a parallel stage, do you mean OE? What settings are you using for the Oracle side, both jobs? Particularly wondering what the PX 'Method' is.

Its OE.
On Server side i am using "Insert rows without clearing"
On Paralle Sie i am using write method "Upsert".In the updatesql clause i have written the INsert statement. (Jobs do plain inserts only).
I cannot use Write method as Load as inserts will be out of my control adn there are some not null constraints on the columns.

Please advice

[chulett]

Please be patient. Advice will come.

[bollinenik]

try by selecting 'insert arry size' option and provide value as 1000

I think this will help you deffinetly.

[reachmexyz]

try by selecting 'insert arry size' option and provide value as 1000

I think this will help you deffinetly.

But i am using "update only" as the write method. In the update sql, i have written the insert sql statement. As i am using "update only" as the write method, i cannot see "insert array size" option.

[Kryt0n]

Why would you do an upsert if you have truncated the table? Do a load append.

If your data has potential duplicates, deal with them first, don't expect the Oracle inserts to handle them.