Can you think of any reason to have tsq$ handled differently during a = truncate? Regards, Waleed -----Original Message----- From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx] Sent: Thursday, May 06, 2004 2:29 PM To: oracle-l@xxxxxxxxxxxxx Subject: Re: truncate a table with many extents 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 -----=20 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 //www.freelists.org/archives/oracle-l/ FAQ is at //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 //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------