Loading records takes long time:Oracle enterprise stage

[RAJARP]

Hi,

We have one parallel job which loads data into an oracle table from a dataset(as designed below)
Dataset-->Transformer---->Oracle Enterprise Stage

The transformer doesn't have any major transformations apart from doing setnull() for a few fields.
This job took 11 hours to load 40 million records into a partitioned table.This table has 250 columns and indexed on 5 columns.

Settings of Oracle enterprise stage:

Code: Select all

Write Method=Load
Write Mode=append

Disable constraints =true

Execution mode is Default(Parallel) in Stage->advanced tab

Also got a warning

Code: Select all

APT_ORACLE_LOAD_OPTIONS has been set by user. Therefore, Orchestrate assumes that the DIRECT and/or PARALLEL options have been set to FALSE, and that it is okay to load table 'Schema1.Table',  even though it is indexed, and an index option (rebuild or maintenance)  has not been included. APT_ORACLE_LOAD_OPTIONS has been set by the user to 'OPTIONS (DIRECT=TRUE, PARALLEL=FALSE)'.

What could be the issue here?Is there some setting which can be modified to increase the performance?
Any suggetions would be appreciated.

Thanks in advance,
Raja R P

[chulett]

You can't do a DIRECT (i.e. bulk) load on a table with indexes so it switched to a conventional load - inserts. You would need to add the index options it needs to allow the bulk load to happen.

[SURA]

1) Read the Connectivity Guide for Oracle Databases pdf will give you a good idea.

2) The Connectivity Guide for Oracle Database will guide for APT_ORACLE_LOAD_OPTIONS warning too!

In regards with load performance, i saw in some projects, they will disable the index and get it back once the load is done.

It would be great if you involve you dba and find the best way to do it!

[RAJARP]

Thanks Craig and Sura,
As Suggested I have added,

Code: Select all

 Index mode= rebuild
        Add COMPUTE STATISTICS=True
        Add NOLOGGING =True

But still, the job ran for 9.45 hrs(same job completed in 11 hrs without index option ) to load the same number of records.

Actually, In The transformer in between the source and target, I am doing setnull() for 80 columns.

Dataset -->Transformer---->oracle enterprise stage

My question is,Whether doing setnull() for large number of columns is hindering the performance somehow ?


Thanks again,

Raja R P

[chulett]

Conduct a test. Write your records to a flat file, note the time it takes. Remove all of the 'set null' handling, rerun.

Did you confirm it actually did a BULK load? (DIRECT=TRUE)

[ray.wurlod]

Is there any reason you're not using an Oracle Connector stage? Do some comparitive tests for this also.

[RAJARP]

Thanks Craig and Ray!!!!

Conduct a test. Write your records to a flat file, note the time it takes. Remove all of the 'set null' handling, rerun.

Did you confirm it actually did a BULK load? (DIRECT=TRUE)

I did this.Not much difference between the two runs( In fact, the job with all setnull() was 3 minutes faster then the other one) And, Yes.It is Direct=True

Is there any reason you're not using an Oracle Connector stage?

I used connector.The time got reduced to 4 hrs 50 minutes.But , when I try 'Write mode =Bulk load' I am getting a error message 'Table or view doesn't exist which is weird because the table is present.

So I am doing only 'write mode = Insert,Table action=append' because of which I am not able to disable the indexes.

Any idea why I am getting the above error message when i try Bulk load??I believe If I could do Bulk load with disabling indexes, the performance might increase??

Thanks again,
Raja R P

[RAJARP]

Hi Gurus,

Any of you had a chance to look at my previous post?Please do reply.

Thanks,
Raja R P