OraFAQ Forum: Performance Tuning » How can I Improve Performance

Home » RDBMS Server » Performance Tuning » How can I Improve Performance

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

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

Report message to a moderator

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

Report message to a moderator

Previous Topic:	statspack
Next Topic:	How to solve latch Promble?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 05 06:08:08 CDT 2025