RE: truncate a table with many extents

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Thu, 6 May 2004 13:15:12 -0400
Message-ID: <564DE4477544D411AD2C00508BDF0B6A1CE0AB1C@usahm018.exmi01.exch.eds.com>

Well maybe I won't need to test. Riyak, too make sure I understand what I am reading I take it this is a comparison of truncate verse drop and not a truncate followed by a drop?

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Riyaj Shamsudeen Sent: Thursday, May 06, 2004 12:13 PM
To: oracle-l_at_freelists.org
Subject: RE: truncate a table with many extents

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.

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
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Thursday, May 06, 2004 10:03 AM
To: oracle-l_at_freelists.org
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_at_eds.com> To: <oracle-l_at_freelists.org> 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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Thu May 06 2004 - 12:42:02 CDT