Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Kapil.

Asked: October 14, 2007 - 2:16 pm UTC

Answered by: Tom Kyte - Last updated: February 11, 2020 - 6:23 pm UTC

Category: Database - Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom

We got a table which is partitioned on Range (date), Each partition holds approximately 5 Million records, there are 60 partitions. There are 4 Indexes on this table and they are Global Indexes. To remove old data we truncate partition.

Truncate partition is very slow, while truncating partition other seesions which are trying to SELECT/DML from this table goes into some kind of wait and takes longer time.

Could you please explain why truncate partition takes more time (some time it takes more than 10 minutes). How we can spped up this process.


Thanks

Kapil

and we said...

in order to truncate, we checkpoint the data in the affected partition.

You are likely waiting for this to finish.

And that additional IO is affecting the runtime of the other processes (naturally)

To see if this is true, checkpoint AND THEN truncate. The checkpoint should have the effect of slowing down other things (because of the IO involved), but the truncate will be fast.

and you rated our response

  (14 ratings)

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

Reviews

Checkpoint Alone?

September 26, 2009 - 6:29 pm UTC

Reviewer: Student from NJ USA

When we try to truncate a set (20 + Tables in a Row), a particular table is taking 40+ Minuted while rest of the 19 takes only seconds...Please explain.


We rerun this test and in that specific table alone the wait is more and that table is 2nd in the list.

Table has 2 Columns and has a composite index on oth the columns.



Tom Kyte

Followup  

September 30, 2009 - 6:57 am UTC

the table that takes a long time has tons of dirty blocks in the cache.

when you truncate - we do segment level checkpoints first - and then truncate. You are waiting for the IO to complete.

September 30, 2009 - 8:27 am UTC

Reviewer: A reader

Dear Sir,

Is your below reply is true for non-partition table truncate?


<qoute>
the table that takes a long time has tons of dirty blocks in the cache.

when you truncate - we do segment level checkpoints first - and then truncate. You are waiting for the IO to complete.
<qoute>

Thanks

Regard's
Tom Kyte

Followup  

October 07, 2009 - 8:32 am UTC

yes.

Similar Problem

October 01, 2009 - 3:06 pm UTC

Reviewer: Mike from KATL

One of our developers just said that a table that used to take seconds to truncate (weekly ETL process) now takes about 10 minutes. One of your replies said that there could be blocks from this table in the buffer cache that need to be cleared before the truncate happens. Out of curiosity, I traced the session that truncated a similar table.

The truncate took 10 minutes performing 97,030 LIO's. These are our top waits:

db file sequential read: 540.531
local write wait: 49.532
CPU service, execute calls: 20.190
rdbms ipc reply: 16.982

Am I correct when I assume that the actual truncation takes 36 seconds (CPU Service + ipc reply), and the checkpointing process is taking 589 seconds (the remainder of the waits)?

What would cause the checkpoint to take so long? Is there anything we could do to speed this up?

Thanks!
Tom Kyte

Followup  

October 07, 2009 - 2:57 pm UTC

make checkpointing happen more often - using any of the many parameters that do that such as

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams068.htm#REFRN10058

downside: you will have a cleaner buffer cache, dbwr will be more aggressive in keeping it clean

upside: time to recover if you crash is bounded, small


and you may well consider using delete instead of truncate as delete will take place in the buffer cache, truncate is not fast = true, truncate is DDL that commits and has downsides to it. You might find delete to be much faster.

How is delete and truncate implemented internally.

March 18, 2010 - 8:42 am UTC

Reviewer: Dev

All along, I have read, heard and told that Truncate is faster than delete command as it does not create logs.
Your statement however confuses me.
and you may well consider using delete instead of truncate as delete will take place in the buffer cache, truncate is not fast = true, truncate is DDL that commits and has downsides to it. You might find delete to be much faster.

1) Can you please elaborate on how these are implemented internally.
2) In which case will truncate be slower than delete

Tom Kyte

Followup  

March 18, 2010 - 9:02 am UTC

delete does a delete, it goes row by row and deletes the data, updating the indexes in turn. It generates undo and redo for these operations (and the generate of redo does NOT imply "it is slow" by the way, but anyway)....


truncate will flush the buffer cache of any blocks related to the segment(s) (table and index). It must wait for this to finish. Then it can reset the table and "lose" the data.



So, what if in the cache there are 10,000 dirty blocks associated to the table and it's indexes. And furthermore, in the table there are just 1,000 rows right now.

If you delete, we'll delete 1,000 records (pretty fast) and maintain the indexes (pretty fast) and do it all in the buffer cache (it is a small delete).

If you truncate, we'll first have to flush 10,000 blocks to disk (pretty slow, takes a while). Then we'll reset the table storage (pretty fast)

outcome: likely the delete is many times faster than truncate.



So, what if in the cache there are 10,000 dirty blocks .... and furthermore, in the table there are 100,000,000 rows.

<left blank, exercise for reader to finish the logical outcome...>


Recreaste table

November 06, 2014 - 11:01 pm UTC

Reviewer: Sergio from São Paulo SP Brazil

Hi Tom,
I am thinking if it is better to rename old table and their indexes, creste New table And then Drop old table in some time in the future.
If i have i window time to maintance, mas be this aproache will be faster than others
Best regards,
Sergio

December 03, 2014 - 12:28 pm UTC

Reviewer: Anand Ramesh from India

Hi Tom

We have a table which is having ~175 records .
we are doing a daily truncate load on this table and we are noticing that the truncate is taking a long time to complete and the disk IO is high .

What could be the possible reasons for this and how do we solve this problem . Can we use a delete on the table instead ?

Tom Kyte

Followup  

December 03, 2014 - 12:44 pm UTC

it sounds like this table is highly active and even though there are just 175 records in there - it has a ton of blocks associated with it. I'm guessing, because you don't give a lot to go on.


when you truncate, we have to checkpoint that segment - meaning we have to write out all of the dirty blocks in the buffer cache and it sounds like there are a lot.

A delete would not have to do that - it would let the blocks remain in the buffer cache.


However, that said, when you truncate, you reset the high water mark - so the table which is apparently big becomes very small. But then it seems to grow rapidly back to a big size again. If that is the case, the delete would probably be the way to go - growing the table takes work, if the table has already grown let's just keep that around for tomorrow. Also, we would not have to checkpoint and wait for the checkpoint to complete.

Alternatively, say this table is called T, you could

a) load a new table T2 that looks like T
b) index it
c) rename t to t_old
d) rename t2 to t
e) drop t_old

now the drop would do what the truncate does (checkpoints) but it doesn't matter if it takes time - T is online except for the brief moment of (c) (d).


September 08, 2017 - 12:41 pm UTC

Reviewer: Priyanka from Kerala, India

While truncating partitionm, other sessions that try to SELECT/DML can be made faster by ENABLE PARALLEL DML to 'yes' in map or in db, change Parallel degree from 1 to some higher value, say, 8

Truncate table duration

January 22, 2020 - 1:47 pm UTC

Reviewer: A reader

Hi,
Just a follow question to this; we have a data warehouse - data is loaded during an ETL and then not updated until the following evening when the changes from the source system are pulled in. Now and again (for various reasons) we do a "full" load, in which the table is truncated and the full set of data is brought in.
We have little control over the functionality unfortunately as this is performed by the Oracle ODI tool.

We have found that in some instances the truncate of some table is taking a very long time - (a couple of hours or more). There are no blocking locks from other sessions performing DDL etc, and there are no user queries running against that table.

Given there is no major "activity" on the database, would the above still be applicable? If we did a load say a week ago, and then did a truncate today, is it possible that the checkpoint being performed before our truncate would be flushing dirty blocks from a week back? There should have been no updates (or at least no major updates) to any data in the warehouse since then.

If this is the case, what options do I have to try and get the truncate time(s) down?
Connor McDonald

Followup  

January 23, 2020 - 4:02 am UTC

Dirty blocks get flushed out from the buffer cache periodically anyway, and hence an "old" load should have had its blocks flushed out at some point.

And normally, when we talk about a "slow" truncate, we're talking tens of seconds not tens of minutes...

I would look at doing a trace to see where the time is being lost, ie

- turn on trace (dbms_monitor.session_trace_enable(waits=>true)
- run the truncate
- turn off trace (dbms_monitor.session_trace_enable(waits=>true)

I know that can sometimes be hard to inject that into an automated tool, but alternatives are keeping an eye on v$active_session_history whilst the truncate is running

VASH

February 06, 2020 - 7:20 am UTC

Reviewer: A reader

Apologies for the delay in replying and thanks for the pointers. Yes; adding a trace appears to be a bit tricky...

Looking at V$ASH, all I see is a load of operations "DDL STATEMENT". However waits are:

       SID STATUS   OSUSER   EVENT                                                  TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO WAIT_CLASS           TOTAL_WAITS
---------- -------- --------------------------------------------------------------- ----------- ------------ ----------------- -------------------- -----------
      1899 ACTIVE   oracle   local write wait                                            310789        12.21        3107891655 User I/O                   25452
      1899 ACTIVE   oracle   enq: RO - fast object reuse                                  64268        26.98         642677185 Application                 2382
      1899 ACTIVE   oracle   db file sequential read                                      43433          .94         434330305 User I/O                   45993
      1899 ACTIVE   oracle   db file scattered read                                        4134         1.99          41335338 User I/O                    2078
      1899 ACTIVE   oracle   events in waitclass Other                                     3488          .66          34883528 Other                       5321
      1899 ACTIVE   oracle   log file switch (checkpoint incomplete)                       1561       780.53          15610560 Configuration                  2
      1899 ACTIVE   oracle   SQL*Net message from client                                    521        40.07           5209597 Idle                          13
      1899 ACTIVE   oracle   log file switch (private strand flush incomplete)              397        36.06           3966740 Configuration                 11
      1899 ACTIVE   oracle   db file parallel read                                          183        15.25           1830508 User I/O                      12
      1899 ACTIVE   oracle   log file switch completion                                      91        18.25            912499 Configuration                  5
      1899 ACTIVE   oracle   library cache: mutex X                                           9          .35             87564 Concurrency                   25
      1899 ACTIVE   oracle   log file sync                                                    2          .39             15522 Commit                         4
      1899 ACTIVE   oracle   library cache lock                                               1          .04             12976 Concurrency                   29
      1899 ACTIVE   oracle   latch: shared pool                                               1          .09             11386 Concurrency                   12
      1899 ACTIVE   oracle   latch: cache buffers chains                                      0          .02              2097 Concurrency                   12
      1899 ACTIVE   oracle   Disk file operations I/O                                         0            0              1093 User I/O                      22
      1899 ACTIVE   oracle   buffer busy waits                                                0          .02               311 Concurrency                    2
      1899 ACTIVE   oracle   row cache read                                                   0            0                53 Concurrency                    4
      1899 ACTIVE   oracle   SQL*Net message to client                                        0            0                36 Network                       13

Also...

February 06, 2020 - 3:27 pm UTC

Reviewer: A reader

Also - When I query blocking locks, I see my truncating session is being blocked by another session - which is a BACKGROUND session.

resource_consumer_group = _ORACLE_BACKGROUND_GROUP_
program = oracle@{NAME} (DBW1)


So it does look like it's writing dirty blocks back to disk - but why are there so many (or more, why does it take so long?) to do this when nothing has changed in the warehouse for over a day, and they should be periodically flushed out?
Is there a possibility that the automated flush out isn't working? Is there a way to see "non-flushed dirty blocks" using an internal view?
Connor McDonald

Followup  

February 10, 2020 - 4:22 am UTC

We don't have version details, because this wasn't logged as a new question, but you might be hitting this

Truncate Slow in 11.2.0.3 and Higher (Doc ID 1667223.1)

Partitioned but higher version

February 10, 2020 - 3:07 pm UTC

Reviewer: A reader

Hi,
Thanks for the pointer to the note. We are using partitioning on the table in the example, however I'm not sure the note is applicable?

Our version:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
"CORE 12.2.0.1.0 Production"
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production


This is a DBaaS cloud instance, and according to the note:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.


So does "Version N/A" mean it's not applicable, or does it mean it's applicable to all versions?
Connor McDonald

Followup  

February 11, 2020 - 3:53 am UTC

however I'm not sure the note is applicable?


Neither are we, because we don't have all your details, which is why we like people to ask *new* questions on AskTOM rather than tack them onto historical ones :-)

Trace

February 10, 2020 - 3:09 pm UTC

Reviewer: A reader

Also, I did manage to get a trace - just by doing it manually. Key sections from the TKP:

SQL ID: 26g6sb1js148k Plan Hash: 1021102044

truncate table w_ap_holds_f


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.05       0.05          0          0          0           0
Execute      1    108.75    5657.71     189499     183942    1134572           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    108.80    5657.76     189499     183942    1134572           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 123  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  library cache lock                              1        0.14          0.14
  Disk file operations I/O                       21        0.00          0.00
  db file scattered read                       2064        0.13          2.77
  reliable message                            24169        0.93         31.41
  enq: RO - fast object reuse                 27200        1.43       1395.40
  db file sequential read                    159902        0.09         89.46
  local write wait                           149443        1.92       4056.39
  latch: cache buffers chains                    38        0.00          0.00
  buffer busy waits                               1        0.00          0.00
  db file parallel read                          12        0.00          0.07
  log file switch completion                      2        0.22          0.39
  latch: redo allocation                          1        0.00          0.00
  latch free                                      5        0.00          0.00
  PGA memory operation                            6        0.00          0.00
  latch: cache buffers lru chain                  1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       20.55         20.55
********************************************************************************


********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.05       0.05          0          0          0           0
Execute      3    108.76    5657.72     189500     184004    1134572           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5    108.81    5657.77     189500     184004    1134572           2

Misses in library cache during parse: 2
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2       20.55         28.18
  PGA memory operation                           53        0.00          0.00
  library cache lock                              1        0.14          0.14
  Disk file operations I/O                       21        0.00          0.00
  db file scattered read                       2064        0.13          2.77
  reliable message                            24169        0.93         31.41
  enq: RO - fast object reuse                 27200        1.43       1395.40
  db file sequential read                    159902        0.09         89.46
  local write wait                           149443        1.92       4056.39
  latch: cache buffers chains                    38        0.00          0.00
  buffer busy waits                               1        0.00          0.00
  db file parallel read                          12        0.00          0.07
  log file switch completion                      2        0.22          0.39
  latch: redo allocation                          1        0.00          0.00
  latch free                                      5        0.00          0.00
  latch: cache buffers lru chain                  1        0.00          0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    81534      1.53       1.92          0          0          0           0
Execute 141514     14.23      14.67         69     291923     297235       87152
Fetch       15      0.00       0.00          0         56          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   223063     15.77      16.59         69     291979     297235       87162

Misses in library cache during parse: 20
Misses in library cache during execute: 18

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PGA memory operation                          379        0.00          0.00
  db file sequential read                        69        0.02          0.68
  latch: shared pool                              1        0.01          0.01

    4  user  SQL statements in session.
   34  internal SQL statements in session.
   38  SQL statements in session.
********************************************************************************
Trace file: DEV_ora_29168.trc
Trace file compatibility: 12.2.0.0
Sort options: default

       1  session in tracefile.
       4  user  SQL statements in trace file.
      34  internal SQL statements in trace file.
      38  SQL statements in trace file.
      26  unique SQL statements in trace file.
  739588  lines in trace file.
    5702  elapsed seconds in trace file.

Connor McDonald

Followup  

February 11, 2020 - 3:59 am UTC

Give this a try for me:

Replace

"truncate table w_ap_holds_f"

with

"truncate table w_ap_holds_f drop all storage"

and see how you go.

Similar

February 11, 2020 - 11:30 am UTC

Reviewer: A reader

That was a good bit quicker - however it still took 18 minutes which seems quite a long time? There's only 3M rows in the table.

SQL> truncate table w_ap_holds_f drop all storage;

Table truncated.

Elapsed: 00:18:47.25


Though it must be related to the table being partitioned I think. The only other difference of course is that the original table contains indexes (mainly bitmaps), which I know are expensive to maintain, but I'd have thought for a truncate there wouldn't be any significant maintenance because they'll be empty afterwards.

SQL> create table xxjk_w_ap_holds_f as select * from w_ap_holds_f;

Table created.

Elapsed: 00:02:10.79
SQL> truncate table xxjk_w_ap_holds_f;

Table truncated.

Elapsed: 00:00:04.41
SQL>



(yes I did re-populate w_ap_holds_f after the original truncate! :) )
Chris Saxon

Followup  

February 11, 2020 - 1:21 pm UTC

How many partitions are there?

Although truncate is an "instant" metadata operation, when you truncate a partitioned table the database has to update the data dictionary for every partition.

This can take a while!

e.g. the following takes ~8s to truncate a basic table with 100 partitions:

create table t (
  c1 int, c2 int
) partition by range ( c1 ) 
  interval ( 1 ) (
    partition p1 values less than ( 2 )
  );

insert into t 
with rws as (
  select level x from dual
  connect by level <= 100
)
  select x, x from rws;

commit;

select count(*) from user_tab_partitions
where  table_name = 'T';

COUNT(*)   
        100 

set timing on
truncate table t;

Table T truncated.

Elapsed: 00:00:07.849


Can you reduce the number of partitions at all?

If this is an ongoing process, speak with support.

February 11, 2020 - 2:00 pm UTC

Reviewer: A reader

There aren't a huge number of partitions - 1045 in total, with between 1000 and 5000 rows per partition on average. A row in the table isn't particularly huge either, 55 columns, mostly numbers and dates. avg_row_len=313.

Yes, apologies, I would have started a new question if I'd have known upfront it wouldn't have been a simple answer. Just wanted to check here first before raising a support ticket as we need to get it sorted this year ideally. :)

thanks
Chris Saxon

Followup  

February 11, 2020 - 6:23 pm UTC

Well my test took ~7s with just 100 partitions and two columns...

Still 18 minutes does sound excessive. Speak with support!

That said - with just ~5k rows/partition, this table seems a little too small for partitioning to be worth it. Certainly with that number of partitions.