Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: truncate a table with many extents

RE: truncate a table with many extents

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Thu, 06 May 2004 12:56:59 -0500
Message-id: <001501c43393$8788b8f0$212f200a@rshamsudxp> 





Hi Marj


  Nope, it is a genuine, authentic, made in Texas,  comparison between
drop and truncate, each time populating data. Here is the script, if you
would like to review. Runstats_pkg is from Tom Kyte and run2 columns
from these two outputs were compared. Rows are inserted in a 'for loop'
so that the test results will somewhat mimic the application
functionality.


  Pctfree set to 99 so that I can consume extents quickly ;-).


-- script---



create table my_dict_trunc_tbl


  (n1 number,


   c1 varchar2(255))


tablespace test_tbs


pctfree 99 pctused 1


storage ( initial 128k  next 128k  pctincrease 0 minextents 1 maxextents
20000)


/


declare


begin


  for i in 1..20000

        loop
        insert into MY_DICT_TRUNC_TBL values (i, lpad (to_char(i),250,'
'));
        end loop;




end;


/


commit;


exec runstats_pkg.rs_start;


select count(*) total_extents from user_extents where
segment_name='MY_DICT_TRUNC_TBL';


exec runstats_pkg.rs_middle;


truncate table MY_DICT_TRUNC_TBL;


exec runstats_pkg.rs_stop;


declare


begin


  for i in 1..20000

        loop
        insert into MY_DICT_TRUNC_TBL values (i, lpad (to_char(i),250,'
'));
        end loop;




end;


/


commit;


exec runstats_pkg.rs_start;


select count(*) total_extents from user_extents where
segment_name='MY_DICT_TRUNC_TBL';

exec runstats_pkg.rs_middle;
drop table MY_DICT_TRUNC_TBL;
exec runstats_pkg.rs_stop;
   






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 Powell, Mark D
Sent: Thursday, May 06, 2004 12:15 PM


To: 'oracle-l_at_freelists.org'


Subject: RE: truncate a table with many extents




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.



    
  
  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_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







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.






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
-----------------------------------------------------------------





-- 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
-----------------------------------------------------------------


Received on Thu May 06 2004 - 13:04:18 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US