Truncate and checkpoint [message #445918] |
Thu, 04 March 2010 12:27  |
John Watson
Messages: 8979 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I have noticed that truncating a table generates a large amount of physical writes, equal to the number of dirty buffers of the segment. This example illustrates the issue: it creates a table of about 20MB, and calculates the write activity caused by truncating it. If I run the script repeatedly, I do get repeatable results: physical writes of about 24M, caused by the truncation. A bigger table causes more writes on truncation, proportional to the size of the table - unless you checkpoint first.
/* set up the user */
conn / as sysdba
drop user jon cascade;
grant dba to jon identified by jon;
conn jon/jon
set serveroutput on;
/* repeatable code starts here */
/* clean up after the last run */
drop table t1 purge;
/* fixed length rows */
create table t1(c1 char(1000)) cache;
/* insert 20MB of data */
begin
for i in 1..20000 loop
insert into t1 values('aaa');
end loop;
commit;
end;
/
var y number;
var z number;
/* save the physical writes before truncate */
begin
select value into :y from v$sysstat where name = 'physical write total bytes';
end;
/
/* do it */
truncate table t1;
/* save the physical writes after truncate */
begin
select value into :z from v$sysstat where name = 'physical write total bytes';
end;
/
/* and the answer is, */
exec dbms_output.put_line('bytes written by truncate: '||(:z - :y))
/* repeat as necessary...*/
I've tested this on 11.1.0.7 single instance Windows, and 11.1.0.6 RAC Linux.
Is this expected, documented, behaviour: that truncating a table checkpoints the segment? Or am I misunderstanding what is happening? This is disastrous on a RAC, where the checkpoint is global.
|
|
|
|
Re: Truncate and checkpoint [message #445920 is a reply to message #445918] |
Thu, 04 March 2010 12:36   |
John Watson
Messages: 8979 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Thank you for looking at this, BlackSwan.
If you checkpoint before the truncate, then there is no physical write. It is only dirty buffers which are written.
|
|
|
|
|
Re: Truncate and checkpoint [message #446015 is a reply to message #445925] |
Fri, 05 March 2010 02:57  |
John Watson
Messages: 8979 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Thank you for replying, Michel. If Tom says it, I guess it is documented! I can't work out yet why the segment checkpoint is needed for recovery, but I'm sure it will become clear with a bit more thought.
|
|
|