Home » RDBMS Server » Performance Tuning » How to improve the performance of "creating partition". (oracle 10g R1 on Red Hat linux)
icon4.gif  How to improve the performance of "creating partition". [message #364430] Mon, 08 December 2008 07:38 Go to next message
abs_amit
Messages: 59
Registered: January 2006
Location: delhi
Member
hi,
i have a table with huge partition of 200GB. and now we are splitting this partition in to new partitions (by month).
But its taking lot of time to perform that.
we are planning to create around 10 partition and creating first partition itself is taking around 10 hours.
just wanted to know, is that normal?

Database performance looks fine and Server too as well.
there is no blocking or locking session on the same object.

my server has four CPU's. should i use Parallelism to enhance the performance or any other suggestion to boostup the performance ?

any comments on the same are highly appreciated?

=============================================
alter table <table_name> split partition <parition_name>
at (to_date('2008-09-01', 'yyyy-mm-dd'))
into (partition <new_partition>, partition <partition_name)
===================================================

[Updated on: Mon, 08 December 2008 08:22]

Report message to a moderator

Re: How to improve the performance of "creating partition". [message #364505 is a reply to message #364430] Mon, 08 December 2008 22:00 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Splitting partition containing a huge amount of data will be always slow, as data belonging to different new partitions have to be separated physically.
Much more better way would be:
1) Create the empty table with the same structure as the original table
2) Exchange splitted partition with the new table
3) Split the partition on the original table (as it is empty, it will be fast)
4) INSERT SELECT into the original table from the new table (use APPEND hint, you may play with parallelism).
5) Drop or truncate (if it needs to be done more times) the new table
Re: How to improve the performance of "creating partition". [message #364842 is a reply to message #364430] Wed, 10 December 2008 04:11 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
I would do it a bit different:
1. CREATE ... NOLOGGING AS SELECT ...
-- To generate a number of tables (according to a number of needed partitions).
2. Create indexes (nologging) for all new tables.
3. DROP old table.
4. Create it as partitioned.
5. EXCHANGE partitions with tables from #1.

HTH.
Re: How to improve the performance of "creating partition". [message #364851 is a reply to message #364842] Wed, 10 December 2008 04:53 Go to previous messageGo to next message
abs_amit
Messages: 59
Registered: January 2006
Location: delhi
Member
Thanks for the really good suggestions.

But there are few constraints in implementing them:
1. the partition which we are planning to split is MAXVALUE partition.
2. partition size is around 210GB. and i have only 150GB free on the diskgroup. it means i can not go for the option (Creating new table).

any other idea keeping above two things in focus ?
Re: How to improve the performance of "creating partition". [message #365652 is a reply to message #364430] Thu, 11 December 2008 19:12 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
Split your maxvalue group into 2 and do it half at a time then!
Previous Topic: Keep buffer pool after restart of the server
Next Topic: Converting Trace (.TRC) file to readable(HTML) file in forms 10g
Goto Forum:
  


Current Time: Wed Dec 07 20:21:07 CST 2016

Total time taken to generate the page: 0.13303 seconds