Home » SQL & PL/SQL » SQL & PL/SQL » Truncate and checkpoint (11.1.0.x Linux / Windows)
Truncate and checkpoint [message #445918] Thu, 04 March 2010 12:27 Go to next message
John Watson
Messages: 6403
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 #445919 is a reply to message #445918] Thu, 04 March 2010 12:33 Go to previous messageGo to next message
BlackSwan
Messages: 24914
Registered: January 2009
Senior Member
What changes, if anything, if immediately after COMMIT the following is done?

ALTER SYSTEM CHECKPOINT;

& done prior to

select value into :y from v$sysstat where name = 'physical write total bytes';
Re: Truncate and checkpoint [message #445920 is a reply to message #445918] Thu, 04 March 2010 12:36 Go to previous messageGo to next message
John Watson
Messages: 6403
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 #445922 is a reply to message #445920] Thu, 04 March 2010 12:42 Go to previous messageGo to next message
BlackSwan
Messages: 24914
Registered: January 2009
Senior Member
http://www.jlcomp.demon.co.uk/cleanout.html

One can argue chicken & egg with regard to TRUNCATE.

I believe what you measured was delayed block cleanout.

If others disagree, so be it.

It is was it is, & we just have to deal with reality as best we can
Re: Truncate and checkpoint [message #445925 is a reply to message #445918] Thu, 04 March 2010 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 63814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is this expected, documented, behaviour: that truncating a table checkpoints the segment?

Yes.
A couple of years ago I asked this on AskTom and Tom answered me this is needed for recovery purpose.

Regards
Michel

Re: Truncate and checkpoint [message #446015 is a reply to message #445925] Fri, 05 March 2010 02:57 Go to previous message
John Watson
Messages: 6403
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.
Previous Topic: concat
Next Topic: call to function in SYS schema
Goto Forum:
  


Current Time: Fri Sep 30 14:04:20 CDT 2016

Total time taken to generate the page: 0.11226 seconds