Checkpoint Alone?
Student, September 26, 2009 - 6:29 pm UTC
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.
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.
A reader, September 30, 2009 - 8:27 am UTC
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
October 07, 2009 - 8:32 am UTC
yes.
Similar Problem
Mike, October 01, 2009 - 3:06 pm UTC
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!
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.
Dev, March 18, 2010 - 8:42 am UTC
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
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
Sergio, November 06, 2014 - 11:01 pm UTC
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
Anand Ramesh, December 03, 2014 - 12:28 pm UTC
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 ?
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).
Priyanka, September 08, 2017 - 12:41 pm UTC
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
A reader, January 22, 2020 - 1:47 pm UTC
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?
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
A reader, February 06, 2020 - 7:20 am UTC
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...
A reader, February 06, 2020 - 3:27 pm UTC
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?
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
A reader, February 10, 2020 - 3:07 pm UTC
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?
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
A reader, February 10, 2020 - 3:09 pm UTC
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.
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
A reader, February 11, 2020 - 11:30 am UTC
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! :) )
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.849Can you reduce the number of partitions at all?
If this is an ongoing process, speak with support.
A reader, February 11, 2020 - 2:00 pm UTC
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
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.