Skip to Main Content
  • Questions

  • Truncating Table Partition "prevents" Inserts into a Different Partition

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Aminu.

Asked: March 03, 2025 - 10:07 am UTC

Last updated: March 04, 2025 - 1:29 pm UTC

Version: 12.2

Viewed 1000+ times

You Asked

Hi,

We have a partitioned table that is very critical to the operation of a 24/7 OLTP application. Data is mainly only INSERTED into this table and every transaction that happens writes to this table. Data in this table is usually not required after a month or so so we usually TRUNCATE a partition every month.

This table is range partitioned (MONTHLY) by a DATE column. We "thought" the fact that this table was partitioned would mean we could truncate a previous month's partition while INSERTS into the current partition will still happen without a problem, but we were wrong.

Anytime we have to truncate this table with the application still running, INSERTS into this table will literally freeze until the truncation is completed, and due to the size of this table this usually takes an excruciating 20+ minutes. And... we always do this during off-peak times. This means 20+ minutes of application downtime. Top wait event during partition truncation are:

- buffer busy waits
- cursor: pin S wait on X
- row cache lock
- library cache: mutex X ... etc

We know for sure that this operation will be much faster if we simply shut down the application before truncation but that is not something the application team wants. We also think there may be a better approach.

I want your opinion on this approach we are contemplating, as well as any other opinion you may have that can help:

- If we delete all rows in the partition we want to truncate (yes, this will generate A LOT of redo) and truncate after the partition is empty, will this likely help?

- Is there a way to generate less redo and/or improve the deletion speed of such a large number of rows (600 million)?

- How are situations like this handled for zero-downtime applications?


Database Version: 12.2
Average Partition Size per month: 300GB
Average Row Count per month: 600 Million


Thanks for your time.


and Chris said...

Truncate partition should be "instant", so if this 20+ minutes something's up.

From 12.1 maintenance of any global indexes is deferred to a background job. But there are restrictions on this feature, so if the table has global indexes and you use the UPDATE INDEXES clause this may explain the wait.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/maintenance-partition-can-be-performed.html#GUID-087B87A6-959A-40C6-82AF-36E401FD089B

If this isn't the problem, you may be hitting a bug; various support notes mention partition maintenance operations taking a long time. Check with support - though note 12.2 is out of support now, so you may need to prioritise upgrading.

If we delete all rows in the partition we want to truncate (yes, this will generate A LOT of redo) and truncate after the partition is empty, will this likely help?

Maybe? We'd need to know why the truncate is slow to answer that. Tracing the session that is truncating the partition will help us see what exactly this operation is doing.

Is there a way to generate less redo and/or improve the deletion speed of such a large number of rows (600 million)?

You can do a filtered partition move from 12.2. This only keeps the rows where the condition is true. So something like:

alter table ... move partition ...
  including rows where 1=0
  online


will delete all the rows in the partition. As you're moving nothing this should be fast. Though this may hit the same problem as truncate.

How are situations like this handled for zero-downtime applications?

Like I said at the top, this should be a fast operation. If it's not, something's wrong.

Rating

  (1 rating)

Comments

Thanks Chris

Aminu, March 04, 2025 - 10:24 am UTC

Thanks a lot Chris. The bug option looks more possible. We will trace the next operation and open a ticket with Support.

I have come across (Doc ID 2177233.1) but this applies to cases with Global Indexes. Since we are dealing with a single Local Index I doubt that applies to our situation. Do you know of any other support notes related to such behavior?

Thanks

Chris Saxon
March 04, 2025 - 1:29 pm UTC

A search for "truncate slow" on MOS returns several documents.
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.