Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kashif.

Asked: January 20, 2021 - 5:06 pm UTC

Last updated: March 01, 2024 - 1:55 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Hello,

I have a production problem for which I need your inputs.
Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

1) There is a monthly Partitioned Oracle table with monthly Partitions created at during table creation.
2) The application ran fine for many years but admin forgot the create new Partitions in time and now we have a very heavy MAX partition having 200 million rows.
There are a number of tables affected with this problem.
3) The application software provider gave us the standard PARTITION SPLIT command based scripts to split MAX partition into new monthly partition which can run recursively to run until the whole MAX partition is empty and future partitions are created.

ISSUE: For some heavy tables, SPLITTING even a single month with DOP 16 is taking almost 20 hours on Prod like Copy.
Such down time is not possible for this application and I would like to avoid that.


Is there any alternate approach possible which can help in faster Partition split.
--> we want minimum downtime.
--> The activity can be performed in a longer duration(like a month) as there is no application performance issue at the moment but no downtime is critical for the application. Maybe just 1 downtime for 4-5 hours.


Any input will be highly appreciated.

---
One approach I can think of is:-
1) Detach this MAX Partition (I tried using EXCHAHE Partition in my POC but This requires an un-partitioned shadow/target table)
2) Add New Partitions in orig tables.
3) Re-attach MAX Partition (how to re-attach?)
4) Later enable ROW MOVEMENT and UPDATE PARTITIOING key to make rows move into correct Partitions.-- this can be done slowly over few weeks

Will this approach work?




and Chris said...

I'm unsure what you mean by "detach" the max partition. Some form of partition exchange process where you gradually split the partition into smaller chunks is possible, but this will be fiddly.

Here's a couple of other options I'd choose over this:

Upgrade to 12.2 (or higher) where you can do partition splitting online :)

You've said there's no application performance issue at the moment. So if you prioritise upgrading (to be honest as you're on 11.2 this should be a high priority task anyway) you may be able to live with the situation as-is until then.

If you're not able to wait until after an upgrade, you could use DBMS_redefinition to change the partitioning method online.

https://oracle-base.com/articles/11g/online-table-redefinition-enhancements-11gr1

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for your input, FAST SPLIT

Kashif Akhtar, January 25, 2021 - 9:19 pm UTC

Thanks for your inputs.

We are now actually considering the speedup of DB upgrade for our project.
The IT has EXAData in pipeline which should be available in prod in 6 months.

1) Do you think having EXAData will help us further in Online Partition SPLIT or 12C VS 12C + ExaData will have same performance. AT the moment I am not sure whic oracle version will come with EXAData, but assuming at least it will be 12c.

2) In order to Stop further growth of MAX partition in next 6 months I am evaluating below option. Any feedback on this will be great.

a) Some rows in MAX Part has PARTITION KEY , way too far in future (due to bad functional input), but volume is very less, less like 5K rows in whole table.

Enable ROW MOVEMENT,
UPDATE these records to bring them into PARTITIONs which are in near future. (all current data are inserted with PARTITIONKEY( a date) 6 month in future.
DISBALE ROW MOVEMENT

b) SPLIT MAX partition into a PARTITION that will contain all data of MAX partition and a New empty MAX partition.
THis should be fast due to Oracle's FAST SPLIT optimization.

c) SPLIT MAX into future dated partitions so that all future traffic are inserted into Correct partition.

d) In future when Oracle Upgrade/ExaData is available
Online SPLIT the heavy partition created in step c.


I hope I am not trying to do something stupid.
Currently we are try to get a prod like db setup to be able to test this.

Your feedback is welcome.

Chris Saxon
January 27, 2021 - 11:13 am UTC

1. I'm not aware of Exadata features that will make this specific operation faster

2. You're basic strategy sounds good

After splitting the max partition so it's empty, I'd look into switching to interval partitioning to avoid this problem happening again.

To do this, drop the max partition and set the interval:

alter table t drop partition pmax;
alter table t partition by interval ( ... );

Tony, February 29, 2024 - 7:00 pm UTC

I had same issue, except my bulged partition had billions of rows on a date range partition type.

here is how I fixed:

1. find max value in bulged partition.

2. add few values to max value, like max_val=300, then define splitting point at 305. the second partition will have the MAXVALUE anchor but will be empty. This partition after the split can be dropped. No data is lost because it is empty.

3. switch partition creation to interval. Partitions are created peacefully.

Connor McDonald
March 01, 2024 - 1:55 am UTC

I think the aim here to divide the max partition, not simply keep it in the mix as a normal partition.


But hey, thanks for the 1 star :-)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database