Re: truncate a table with many extents




I did a truncate on a table with 10,000 (8k) extents in a LMT on 9.2.0.4 on
solaris, with 10046 level 8 trace enabled.  I didn't see tsq$ - am I
missing something?


                                                                                
                                  
                          "Jonathan Lewis"                                      
                                  
                          <jonathan@xxxxxxxxxxxxx  T                            
                                  
                          co.uk>                   To:   
<oracle-l@xxxxxxxxxxxxx>                                 
                                                   cc:                          
                                  
                          Sent by:                                              
                                  
                          oracle-l-bounce@freelis  bcc:                         
                                  
                          ts.org                   Subject:                     
                     Re: truncate 
                                                   a table with many extents    
                                  
                                                                                
                                  
                                                                                
                                  
                          05/06/2004 02:29 PM                                   
                                  
                          Please respond to                                     
                                  
                          oracle-l                                              
                                  
                                                                                
                                  
                                                                                
                                  





I think this is one that probably requires testing
for every platform and version of Oracle.

I've just run a quick test on 9.2.0.4 with 169 extents
in an LMT Uniform sized.

The most significant difference is that the TRUNCATE
updated tsq$ once per extent removed, DROP updated
tsq$ once at the end.  On the other hand, the DROP
command triggered various other (mostly small) actions,
including a call to a procedure called aw_drop_proc.

The most significant difference, though, was the handling
of tsq$, and it made the truncate much more expensive
in undo and redo.

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: "Riyaj Shamsudeen" <rshamsud@xxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, May 06, 2004 5:13 PM
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



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

Other related posts: