Home » RDBMS Server » Performance Tuning » Table Partition (Oracle 9i)
Table Partition [message #468422] Fri, 30 July 2010 01:27 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8960
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
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 Go to previous messageGo to next message
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 Go to previous message
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Nov 25 05:50:59 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.