OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Table Partition (Oracle 9i)

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

Table Partition [message #468422]

Fri, 30 July 2010 01:27

amit_kiran
Messages: 50
Registered: July 2006
Location: UK

Member

Hi,

Currently I am having large size table in Live environment. This table holds more than 12 Million records and is having 11 Indexes.

Due to this Insert of approx 0.3 Million records takes 3 hrs.

I need to reduce time taken for Insert.

Can you please help if Partitioning will improve performance of Inser statement.

if not, any other suggestions will be helpful.

Thanks in advance.
Amit.

Report message to a moderator

Re: Table Partition [message #468425 is a reply to message #468422]

Fri, 30 July 2010 01:30

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

Yes It will. Check the follwing link Table Partitioning

Report message to a moderator

Re: Table Partition [message #468445 is a reply to message #468425]

Fri, 30 July 2010 02:02

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

You need to do a lot of research when implementing partitioning because there are many options, and if you get it right it may help, but if you get it wrong it may be disastrous. Investigate the effect on wait events and execution plans of various partitioning strategies before committing to it, because it is expensive in effort, time, and money - so you must prove that it works before investing.
To take your example, I would begin with the wait events. If the 3 hours is because of wait events to do with redo generation, then partitioning will have no effect at all. If it is to with buffer busy waits on the table (the indexes are a different matter), hash partitioning might help. But then you need to think about the effect on execution plans for subsequent queries.

Report message to a moderator

Re: Table Partition [message #468463 is a reply to message #468445]

Fri, 30 July 2010 02:29

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

To add to John's answer.
In your case of massive insert, it is more likely that partitioning will not help you in any way. Maybe using ASSM might but not so much as the main problem is the 11 indexes.
If your bulk insert is 0.3 million of INSERT statements, you can decrease the time converting these statements to a CSV file and use SQL*Loader to parallel and direct load (if your hardware follows it, that is enough IO bandwidth and CPU but it is worth a try).

Regards
Michel

Report message to a moderator

Re: Table Partition [message #468590 is a reply to message #468463]

Fri, 30 July 2010 22:07

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

How are you doing the INSERT? Is it a single INSERT? Many INSERTs? SQL*Loader?

Using a direct-path load could be a good strategy if you have no Foreign Key constraints or triggers. Check out http://www.orafaq.com/tuningguide/direct%20path.html
You should also look into PARALLEL DML.

These approaches will have MUCH more impact on INSERT performance than partitioning the table.

Ross Leishman

Report message to a moderator

Re: Table Partition [message #468630 is a reply to message #468422]

Sat, 31 July 2010 05:32

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Why are you so confident that the problem is the indexes?
Do you have any triggers attached?
Run insert with sql trace and post TKPROF.

Report message to a moderator

Previous Topic:	An observation
Next Topic:	Query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 04 12:42:36 CDT 2024