RE: truncate a table with many extents

  • From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 06 May 2004 11:13:20 -0500

I performed some testing on this few weeks ago. I couldn't measure the
undo blocks due to implicit commits from DDLs. Redo size and redo
entries were used instead. Oracle 8.1.7.4 64 bit on Solaris 8.

1. Dictionary managed: Table with 1001 extents 20000 rows 128k extent
size
Truncate : redo size :756k redo entries : 3031
Drop     : redo size :569k redo entries : 2183

2. Locally managed : Same code as above, but this generated 1251
extents. 128k uniform sized LMT
Truncate : redo size 1.46MB redo entries : 10,020
Drop     : redo size 367K   redo entries :  3,804

We decided to use LMTs with drop statements (of course, this table is
accessed through a procedure so no grants issue). Surprisingly, # of
recursive calls dropped from 15,110 to 307 between truncate and drop
statements in case of LMT.

It would be nice to see what happens with more # of extents.. Will try
that soon.

HTH
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis
Sent: Thursday, May 06, 2004 10:03 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: truncate a table with many extents



Shouldn't make a significant difference.
The undo and redo would only be about
the data dictionary.

There may be some versions of Oracle where 
the different actions produce a different number 
of updates on seg$ or tsq$, though, but that's
just a random thought.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


----- Original Message ----- 
From: "Powell, Mark D" <mark.powell@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, May 06, 2004 3:45 PM
Subject: RE: truncate a table with many extents


If I am not mistaken dropping the table should be much worse than using
truncate because the use of drop would greatly increase the amount of
undo that Oracle has to keep track of.

-- Mark D Powell --


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------



-- Attached file included as plaintext by Ecartis --
-- Desc: Signature

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: