| How can I Improve Performance [message #64760] |
Thu, 22 January 2004 20:25  |
Shesh
Messages: 16
Registered: July 2003
|
Junior Member |
|
|
Hi,
I am writing a PL/SQL for Migrating data from a schema of 4 tables to a schema of 10 tables.
I have to transfer around 150000 Records from one schema to another.
It is a one time job and I don't want any redo/undo to be generated.
Can you please tell me how can I Improve the performance for this.
Can you please tell me what changes should I make to my database/schema for Improving the performance.
Thanks in advance
Shesha
|
|
|
|
| Re: How can I Improve Performance [message #64765 is a reply to message #64760] |
Fri, 23 January 2004 12:49  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Hi,
Quote "It is a one time job and I don't want any redo/undo to be generated."
is almost not possible under normal conditions! Some amount of undo/redo is inevitable.
You can minimize it though.
Tables/Indexes can be created in NOLOGGING mode, which will skip(greatly reduce) redo & undo generation when direct load operations(ie INSERT /*+ APPEND */ , SqlLoader Direct load) are performed against it.
Indexes can be dropped before the load and created after the load , in parallel (if PQ is configured).
Buffer cache/Sort_area_size(or PGA_AGGREGATE_TARGET) can be sized up during this process,to avoid unnecessary disk I/O.
-Thiru
|
|
|
|
| Re: How can I Improve Performance [message #64788 is a reply to message #64760] |
Fri, 30 January 2004 13:36 |
ilver
Messages: 50
Registered: January 2004
|
Member |
|
|
Hi,
In addition to performance you should be conserned about "what if source data doesn't fit the transformation algoritm"
Disabling logging while transforming data will not allow for rollback.
I suggest you allocate sufficient RBS to make the operation safe.
Btw. You have exellent commit control while writing PL/SQL, making the needs for RBS minimal.
/ilver
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
