Oracle Enterprise Load-Append - Not inserting in Parallel

mkiru23 · Post by **mkiru23** » Mon Jun 29, 2009 10:54 am

Hi

I tried multiple options to use Enterprise Oracle load with append to do inserts in parallel.

1.OPTIONS(DIRECT=FALSE, PARALLEL=TRUE) with out rebuild index

2.OPTIONS(DIRECT=TRUE, PARALLEL=TRUE,SKIP_INDEX_MAINTENANCE=YES) with rebuild index

The table has unique index and range partition on date with 3 million rows on each date. I searched the forum too.

Control file :

OPTIONS(DIRECT=TRUE,PARALLEL=TRUE,SKIP_INDEX_MAINTENANCE=YES)
LOAD DATA length semantics byte INFILE 'ora.659462.895537.fifo.0' "FIX 973"
APPEND INTO TABLE daily_agg
(
.......

Please let me know if we can do inserts in parallel.

Thanks
Kumar

dsuser_cai · Post by **dsuser_cai** » Mon Jun 29, 2009 1:52 pm

try using upsert method. Insert/Update.

mkiru23 · Post by **mkiru23** » Mon Jun 29, 2009 2:00 pm

dsuser_cai wrote:try using upsert method. Insert/Update.

Thanks Karthick, This is also loading in sequential only ( 1 row at a time).

dsuser_cai · Post by **dsuser_cai** » Mon Jun 29, 2009 2:07 pm

Hi
did you try changing the execution mode to parallel.

mkiru23 · Post by **mkiru23** » Mon Jun 29, 2009 2:29 pm

dsuser_cai wrote:Hi
did you try changing the execution mode to parallel.

That's the default mode, I didn't change that. The explain plan in DB shows 1 row at a time.

keshav0307 · Post by **keshav0307** » Mon Jun 29, 2009 7:06 pm

how many nodes you have in configuration file?

mkiru23 · Post by **mkiru23** » Mon Jun 29, 2009 8:35 pm

Keshav,
8 nodes. please let me know if we can change/update the options to load/insert in parallel.

ray.wurlod · Post by **ray.wurlod** » Mon Jun 29, 2009 11:29 pm

Is the table partitioned?

mkiru23 · Post by **mkiru23** » Tue Jun 30, 2009 8:03 am

ray.wurlod wrote:Is the table partitioned?

Thanks Ray, yes it is range partition on date column with 6 months of data daily.

-Kumar

mkiru23 · Post by **mkiru23** » Wed Jul 01, 2009 2:34 pm

ray.wurlod wrote:Is the table partitioned?

Does any one has similar situation, how to achieve parallel inserts in oracle enterprise for range partitioned on date and unique index table.

Thanks
Kumar

sanjay · Post by **sanjay** » Mon Jul 06, 2009 10:43 pm

Kumar

Please try

$APT_ORACLE_LOAD_OPTIONS=OPTIONS(SKIP_INDEX_MAINTENANCE = TRUE,bindsize=8250000,readsize=8250000,rows=2000)

u can can insert in parallel from datastage based on configuration file

Thanks
Sanjay

mkiru23 wrote:
ray.wurlod wrote:Is the table partitioned?
Does any one has similar situation, how to achieve parallel inserts in oracle enterprise for range partitioned on date and unique index table.

Thanks
Kumar

ray.wurlod · Post by **ray.wurlod** » Mon Jul 06, 2009 10:45 pm

The site where U is currently working does not use Oracle.

The second person personal pronoun in English is spelled "you", not "u". Please strive for a professional standard of written English on DSXchange, not least to assist those whose first language is not English.

mkiru23 · Post by **mkiru23** » Thu Jul 09, 2009 7:59 am

sanjay wrote:Kumar

Please try

$APT_ORACLE_LOAD_OPTIONS=OPTIONS(SKIP_INDEX_MAINTENANCE = TRUE,bindsize=8250000,readsize=8250000,rows=2000)

u can can insert in parallel from datastage based on configuration file

Thanks
Sanjay

Sanjay,

I tried the job with this change but no difference. I planned a workaround to break the job in one to create a file with SKG and load that file in parallel with parallel option.

Thanks
Kiran

devanars · Post by **devanars** » Tue Jul 14, 2009 7:52 pm

I am also trying to insert records in the oracle target which has index. Job is running such a long time with out any throughput. can any one provide help on this.

chulett · Post by **chulett** » Tue Jul 14, 2009 8:37 pm

devanars - please start your own post.