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
Oracle Performance
Moderators: chulett, rschirm, roy
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
"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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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?
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?
-
- Participant
- Posts: 11
- Joined: Tue Aug 10, 2004 12:59 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
Good luck.
Cheers,
Dan