Oracle Performance

vasu77 · Post by **vasu77** » Wed Jan 26, 2005 12:45 pm

Hi,
We have a Parellel job which insert data from Dataset to Oracle database using OracleEnterprise stage. Till last week we have seen very good performance(nearly 3000 rows/sec).
But now it is loading at the rate of 400 rows/sec.
Even we have did some performance testing on Database server.Every thing looks fine.
Even we have tested the same job in Devolpment environment.
Its looks good.
I will be very much thanksful if you give some kind advises in fixing this problem.

Thanks
Vas

kcbland · Post by **kcbland** » Wed Jan 26, 2005 1:11 pm

Please describe the nature of the tables, the nature of the data load process (inserts vs. updates), the hardware involved. I'm not even beginning to ask about competing processes on the server.

vasu77 · Post by **vasu77** » Wed Jan 26, 2005 2:02 pm

Nature of the Table:The table has one primary key constraint and one foreign key.It do has some indexes.
Nature of Load: Insert ( Direct Insert - No Validations)
Hardware:Both ETL and Oracle are on different Servers(Unix AIX).

Thanks
Vas

ray.wurlod · Post by **ray.wurlod** » Wed Jan 26, 2005 2:54 pm

What has changed between when you were getting 3000 rows/sec and 400 rows/sec.

"Nothing" is not an acceptable answer. Nor is "the row rate" an especially useful answer.

Is there some other load now occurring? What else is happening in the database? Is the network now more active, so that less bandwidth is available to your processing? Were the 3000 rows/sec achieved starting with an empty table? And so on. Track down what's changed, and you will gain insight into possible reasons for the slower rate.

kcbland · Post by **kcbland** » Wed Jan 26, 2005 3:04 pm

If you started from an empty table and things slow down over time doing straight inserts, it points to the underling table architecture. You can be encountering growth inefficiencies across the board.

Your tables could be partitioned, and PX is loading them according to partition, which is optimal. However, if you have global indexes, you could be incuring significant overhead as the global index spans partitions and takes longer to update. You could be dealing with slow extents growth, or disk i/o bottlenecking because of a host of reasons.

See your DBA.

vasu77 · Post by **vasu77** » Wed Jan 26, 2005 3:07 pm

Thanks for your Quick response,
Actually we ran the same job today early morning as nothing is running any thing on the database.Still we are getting same problem.
We have checked with the DBA's they said that they dont find any problem in that database.
Actually we are facing that problem while loading data from ETL server to Oracle server only.
Is there any thing we can do with the connections?

danthony74 · Post by **danthony74** » Wed Jan 26, 2005 5:06 pm

Hi - Check out the thread Updates 100 times slower than inserts. Sounds like you may be having the same problem as I am: The ETL server spends a lot of time waiting for acknowledgements on updates. Your network folks should be able to help analyze that: Our actual updates are lightning fast - we had the DBA's trace them (they do a unique primary key lookup and update) What's happening is we are spending an inordinate amount of time after each update waiting for an ACK. (6.48 seconds of every 8 seconds processing). You can see this in the DB as sessions that appear to be inactive.

Good luck.

Cheers,

Dan