9Million rec in Oracle tbl, Update or Insert 7rows/sec!

vinaymanchinila · Post by **vinaymanchinila** » Wed Jul 13, 2005 3:30 pm

Hi,
I have a table with 9 million records(Initial load) , and when we run the daily it is incremental and I have like 250K records every day, but it is writing at 7 recs/sec.
Is there any way around this , because I need to run this load more than once a day , probably 6 times !

Any help !
Thanks

ketfos · Post by **ketfos** » Wed Jul 13, 2005 4:33 pm

Hi,
Have you thought on lines of table partition? How big is the Oracle table?
Are there any lookups in the job?

Ketfos

vinaymanchinila · Post by **vinaymanchinila** » Wed Jul 13, 2005 4:36 pm

No, but i built a hash onthe target and using two links to write to it, one just for update and the other just for insert, it islot better this way now.
Thanks,

logic · Post by **logic** » Wed Jul 13, 2005 6:32 pm

Hi,
Did you check if there are improper indexes on the table. Also i guess looking into transaction size and array might help..I think.

wnogalski · Post by **wnogalski** » Thu Jul 14, 2005 12:58 am

First of all You should partition your table f.e monthly, then when loading the data remove all indexes for the partition, delete all data for the reloaded day, insert the data and rebuild indexes - this should be faster than 7 rows/sec

ray.wurlod · Post by **ray.wurlod** » Thu Jul 14, 2005 5:53 am

Assuming they're all inserts, use a bulk loader. SQL*Load (command is sqlldr) for Oracle.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jul 14, 2005 6:26 am

The solution will depend upon the level of change you are preared to do and the authorisation level you are granted.

If it is a full table reload, you can download the full table and repopulate it later.

You can partition using something like batch id or load id

You can load into a temp table an then use SQL to merge them.

mdan · Post by **mdan** » Sat Jul 16, 2005 4:33 am

Hi,
You may use partitioner stage to split file (round-robin) and pump data in parallel. We started from 800 rows, sending via 5 connections we got 4000 - but still very slow for us

. We did some test to find the maximum: it looks like over 6 upload branches we don't get any improvements.
Probably you need also a very good dba - we don't have one ... oracle gets into trouble when we have large updates - we used update then insert. With only inserts we got an aggregate performance of 8000 per second - still slow ... no indexes only a pk

Any other idea ?

vinaymanchinila wrote:Hi,
I have a table with 9 million records(Initial load) , and when we run the daily it is incremental and I have like 250K records every day, but it is writing at 7 recs/sec.
Is there any way around this , because I need to run this load more than once a day , probably 6 times !

Any help !
Thanks

ukyrvd · Post by **ukyrvd** » Sat Jul 16, 2005 12:00 pm

vinaymanchinila wrote:No, but i built a hash onthe target and using two links to write to it, one just for update and the other just for insert, it islot better this way now.
Thanks,

As mdan pointed out, splitting the stream into 3/4 inserts and updates would be fast. In essence thats what you have done by seperating inserts/update streams.

Checkout the job monitor and see exactly at what stage you are seeing the drop in rows/second .. and if your logic permits.. try partitioning stream before that stage .. it could help.

Another option would be sperate all updates to anew job if their percentage is low (grab them in a seq file .. ) and use sql*loaded as ray pinted out for all inserts.

DSXchange

9Million rec in Oracle tbl, Update or Insert 7rows/sec!

9Million rec in Oracle tbl, Update or Insert 7rows/sec!

Re: 9Million rec in Oracle tbl, Update or Insert 7rows/sec!