Home » RDBMS Server » Performance Tuning » How can I Improve Performance
How can I Improve Performance [message #64760] Thu, 22 January 2004 20:25 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: statspack
Next Topic: How to solve latch Promble?
Goto Forum:
  


Current Time: Thu Mar 28 03:36:39 CDT 2024