Re: Autoallocate vs Uniform extent performance

Rachel,

At a recent past job, under 8.1.6 on Win2k we had tables with out-of-line CLOB segments of 30,000 extents (1MB each). Every month we dropped one to make room for another (6 months of CLOB documents online). It always just took a few seconds for the drop. These were in DMTs.

Later we switched servers and I changed to LMTs of 100MB Uniform Extents for the CLOB segments. Going from 30,000 to 300 extents for those hulks made no noticeable difference in query or interMedia indexing performance, nor did it noticeably change the time it took to drop the tables.

Here at AISD, our student information database (SASI, for those in Education who know this 3rd party app) has over 47,000 tables and 70,000 indexes (typical abysmal design for a 3rd party app, eh?), many of them empty or with very few rows. A few months ago I rebuilt it under 8.1.7.4.6
(Win2k - it was previously at 8.1.7.0.0) with LMTs of 8KB Uniform Extents
to save space. Surprisingly, only 40 or so segments have over 1000 extents. One, a consolidated Student table, has a little over 10,000 extents. We've noticed no problem at all with performance, etc.

I've not been concerned about extent counts for several years now, and I've seen nothing convincing that I should be. Maybe I've just not hit the situation where it matters. That is not to say that extents don't matter, but it's only if they obey the stupid directives of uninformed duhvelopers, such as those of our 3rd party Financials system, where they used PctIncrease of 50. Like children and dogs, there are no bad extents, just bad designers. ;-)

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
JApplewhite_at_austin.isd.tenet.edu

                                                                                                           
                      Rachel Carmichael                                                                    
                      <wisernet100_at_yaho        To:       Multiple recipients of list ORACLE-L              
                      o.com>                    <ORACLE-L_at_fatcity.com>                                     
                      Sent by:                 cc:                                                         
                      root_at_fatcity.com         Subject:  Re: Autoallocate vs Uniform extent performance    
                                                                                                           
                                                                                                           
                      04/04/2003 07:01                                                                     
                      AM                                                                                   
                      Please respond to                                                                    
                      ORACLE-L                                                                             
                                                                                                           
                                                                                                           

rumor hath it (as I've never actually had an object hit that high a number) that when you exceed 4K extents it's time to resize. This came from one of the instructors in Oracle University, one who is well-known to actually have more than a clue. He said this at the Data Internals class, before 9i was released.

I have not seen his test results but.... I do know that tests done with DMTs have shown that large numbers of extents (I believe Kevin Loney tested with 60K extents, and I vaguely remember a conversation with Cary where he said he had also tested large numbers) are a problem during operations that empty a lot of extents (think large deletes) because of thrashing on FET$ and UET$. Since an LMT doesn't access those tables by design, I would think that that problem goes away.

--
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.com




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: JApplewhite_at_austin.isd.tenet.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).