-
Hi,
I'm working on Data porting from one database to another. Version of both the databases are same. We have created some predefined scripts which deletes,inserts & updates data to new database as selecting from older one.
We worked a lot on query optimizations and minimising network traffics which improved speed/performance to a better extents.
Now still we need to improve speed as we have to port one more database very soon which contains very heavy data. So I need more tips on improving performance while data porting.
can creating tables in nologging mode will help ?
can we ignore RBS so the internal workload will minimise?
Is there any other aspect to look at?
Thanks in advance.
- Milind
-
Sure nologging can be very much of a help.
But I don't think you can ignore the RBS.
Make small rbs for small transactions,
and big rbs for big transactions.
Other aspect to look at are:
Storage definition if more updates then
PCTFREE 15+ and PCTUSED 80, PCTINCREASE 0
if less updates more inserts then
PCTFREE 5 and PCTUSED 90, PCTINCREASE 0.
increase data buffer cache, sort_area_size
Indexes of the older db must at least be rebuild.
That's all I can think for now.
-
one last minor thing : create indexes after having imported data, it should be faster
-
Originally posted by skmilind
can creating tables in nologging mode will help ?
Definitely yes, but it's a bit tricky.
The target table must be set to nologging (ALTER TABLE NOLOGGING), it should have large extents (MB) and the worst thing: you must drop all indexes on it and then create them again (you can do that in nologging mode).
NOLOGGING only works for direct-load inserts (INSERT /*+ APPEND */ INTO ... SELECT ...) and for SQL*Loader.
I'd recommend to test nologging on small part of data for each table and watch redo ...
If you need more help just ask me, I spent the whole sunday with similar task :-)). But in the end it generated 200K of redo for 2,2 mil. rows. Total time: 30 min.
HTH,
Ales
[Edited by ales on 10-22-2001 at 09:32 AM]
-
Originally posted by ales
you must drop all indexes on it and then create them again
Stupid me, of course you do NOT have to drop indexes on populated tables, but they are logged and I haven't found any way how to avoid that. So if you want to minimize redo size you should drop them and after loading data recreate them with the nologging clause.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|