Extremely slow Oracle Loading

gateleys · Post by **gateleys** » Mon Feb 20, 2006 1:02 pm

Hi, I have a very simple job which uses a flat-file source that passes through a transformer, where a couple of new fields are appended with some default values and then loaded into Oracle via OCI stage. I use DS7 on Windows, Oracle 9i. The problem is it starts loading at a rate of about 250 rows/sec and starts decreasing steadily, finally to 8 or 9 rows/sec. The total # of rows that need to be loaded are approximately 200,000 with about 12 fields. I tried to load it into a flat-file instead, and it loads in a few seconds. The oracle table definition does contain quite a few constraints. But, I have other tables with similar structure, and they do not exhibit this decrease in performance. Can anybody help me with the problem?

kcbland · Post by **kcbland** » Mon Feb 20, 2006 1:07 pm

Reasons inserting into a table progressively degrades, any number of the following: indexes, FK constraints, rollback, commit, triggers, tablespaces, partitions. Triggers are huge impactors of insert performance. Then again, what is your SQL? Insert only or are there inserts and updates?

Take your target table and make a copy of it: create table fred as select * from yourslowtable. Create the primary key index just like on yourslowtable so that INSERT else UPDATE is supported with the primary key index. Use this as a target and run your job. If it's wicked fast, then your issue is all of the junk hanging on the real table. If it's just as slow, then it's your job design.

ArndW · Post by **ArndW** » Mon Feb 20, 2006 1:08 pm

Can you disable the constraints or create a test dummy table in the same schema without the constraints and do a comparison run - this will help localize the cause of the slow load.

Also, can you use the bulk loader (if you are doing pure INSERTS) or are you doing upserts?

DeepakCorning · Post by **DeepakCorning** » Mon Feb 20, 2006 1:10 pm

1. Is the job running in to some kind of warnings. If yes then you need to remove them, as the warnings tend to reduce the performance significantly.
2. Try running the job by changing the options of the processing (to use performance tab in the job properties). In Process.
3. Request your DBA to observe is there any kind of DB activity going on or not?

gateleys · Post by **gateleys** » Mon Feb 20, 2006 1:30 pm

The job contains simple inserts, no updates. Will get my DBA to create a new dummy table without the constraints and check the performance again. Thanks.

ArndW · Post by **ArndW** » Mon Feb 20, 2006 1:32 pm

If you are doing pure inserts you really should look at the performance benefits of doing a bulk load - which can include turning off and subsequently re-enabling constraints for quicker loading. The downside is that if you load data that violates constraints you will have a table with a very upset stomach

kwwilliams · Post by **kwwilliams** » Mon Feb 20, 2006 1:33 pm

Do you have a primary key or unique index on the table? When I have seen loads slow down this much it was because we were getting primary key or unique index violations.

Does your log give you any useful information?

sjhouse · Post by **sjhouse** » Mon Feb 20, 2006 1:40 pm

Have you set your 'Array Size' and 'Rows per Transaction'? These 2 parameters in the stage affect our loads into Oracle.

Also, we disable indexes other than the primary key to improve performance.

gateleys · Post by **gateleys** » Mon Feb 20, 2006 1:46 pm

kwwilliams wrote:Do you have a primary key or unique index on the table? When I have seen loads slow down this much it was because we were getting primary key or unique index violations.

Does your log give you any useful information?

Yeah, there is a unique index on the table, apart from a few other indices. But, there are no unique index violations since I have had no rejects until about 80,000 rows (Note: the slow performance did not start here, i.e at 80,000 rows, but was there from the very beginning). The log is clean. But, when we tried to remove the constraints, Oracle said that the resource was busy--due to my process. It seems that since I had killed the job (a number of times) because of the slow performance, Oracle still maintained those processes. Would that be the reason for the degradation?

gateleys · Post by **gateleys** » Mon Feb 20, 2006 2:00 pm

sjhouse wrote:Have you set your 'Array Size' and 'Rows per Transaction'? These 2 parameters in the stage affect our loads into Oracle.

Also, we disable indexes other than the primary key to improve performance.

It has been set at 1000 rows. I was trying to load it without disabling any constraints since my previous loads to similar tables did not have the same issue. The only difference is that this table already has over a couple of million records. I guess I have to disable the indices and rebuild them later.

thanks
gateleys

I_Server_Whale · Post by **I_Server_Whale** » Mon Feb 20, 2006 5:43 pm

As Williams said, it is also likely that you are trying to load duplicate data, thereby violating the unique index, thereby slowing down the load performance.

Please check whether your flat-file has duplicate records or not.

Thanks,
Naveen.

rasi · Post by **rasi** » Mon Feb 20, 2006 6:08 pm

You have to engage your DBA to check what's happening in the system. For resource busy problem there is something running which avoids you to drop any objects on that table. Check with DBA what jobs are running or any locks in your table. Try to clear this off first and do a clean run. After that post your findings further...

rafidwh · Post by **rafidwh** » Mon Feb 20, 2006 8:24 pm

contact your DBA,some process might be running which needs to be cleaned up and ask the DBA to purge the log file.

No problem with your datastage job,it is the Database issue.

Saik

anujgarg · Post by **anujgarg** » Mon Feb 20, 2006 10:49 pm

Hi,

Can you try by collecting DBMS WRITEERROR records.That may also be the reason for slow performance as records may be getting rejected or some problems in writing the records.

kwwilliams · Post by **kwwilliams** » Tue Feb 21, 2006 6:37 am

If you have processes that are tying up the load job, your DBA should be able to see waits taking place. That may be one of the problems that you are having. If you have stopped and started your job multiple times, oracle would lock records and then when your next process starts and the two processes arrive at the same record then it would start cause a wait to take place. But typically those would not take very long to clear, leading me to think this is not the underlying cause of your problem. You still had the first job that had slow performance that would have caused you to stop the job in the first place.

Could you explain the entire layout of your job? Something like:

ODBC Stage --> Transformer --> Oracle Stage

Maybe there is something else there that we are not seeing. One of the more difficult things to debug has always been the handoff from DataStage to the Database and the performance problems that ensue. Is it the database or your job? Tricky question.

Another question do you clear the table before inserting into it? If not, are you sure that the primary key for that table does not exist before you insert into it?