Path: news.easynews.com!easynews!hub1.nntpserver.com!headwall.stanford.edu!newsfeed.stanford.edu!canoe.uoregon.edu!logbridge.uoregon.edu!news-west.eli.net!not-for-mail Message-ID: <3C82AC99.4BFD214B@ci.seattle.wa.us> From: damorgan X-Mailer: Mozilla 4.79 [en] (Win95; U) X-Accept-Language: en MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server Subject: Re: Algorithm for calculating extent size in LMT References: <3C829FD7.51727A5B@ci.seattle.wa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 70 Date: Sun, 03 Mar 2002 23:07:00 GMT NNTP-Posting-Host: 156.74.250.7 X-Complaints-To: yvonne.tracy@ci.seattle.wa.us X-Trace: news-west.eli.net 1015196820 156.74.250.7 (Sun, 03 Mar 2002 16:07:00 MST) NNTP-Posting-Date: Sun, 03 Mar 2002 16:07:00 MST Organization: City of Seattle NewsReader Service Xref: easynews comp.databases.oracle.server:137318 X-Received-Date: Sun, 03 Mar 2002 16:04:54 MST (news.easynews.com) Thanks for adding the clarification to my posting. But I still see performance problems with large numbers of extents. Especially when doing things such as emptying a table with DELETE rather than TRUNCATE (something that is sometimes necessary). And to me a large number of exents is a sign that someone doesn't understand their schema or didn't take the time to calculate the size of a table and its data ... in short ... sloppy and unprofessional workmanship. Daniel Morgan "Howard J. Rogers" wrote: > There *is* such a thing, of course... there is an 'autoallocate' policy for > locally managed tablespaces, and as best I can tell it goes something like > this: > > The first 16 extents of a segment will be 64K in size. > The next 64 extents will be 1M in size > Then extents become 8M in size. > At the 200th extent, you get 64M extents. > After that, I can't tell you... because I ran out of disk space! > > What Daniel is hinting at, I guess, is that having odd-sized extents within > a tablespace is not a good idea, because it risks fragmentation. I agree > with him that 'autoallocate' is not a terribly good idea for your own > tablespaces, and that you should take charge of the extent allocation > policy. > > The essential feature of locally managed tablespace is that we no longer > really give a damn how many extents a segment acquires, because extent > allocation is now a trivial operation for the database (though I agree that > having the extent map for a segment fit into one block makes for some small > performance improvement, and therefore limiting the number to the old hard > limits (121 for 2K blocks, 504 for 8K blocks and so on) is still not a bad > idea). > > Regards > HJR > -- > ---------------------------------------------- > Resources for Oracle: http://www.hjrdba.com > =============================== > > "damorgan" wrote in message > news:3C829FD7.51727A5B@ci.seattle.wa.us... > > There is no such thing. Extent sizes are determined by developers and DBAs > > that do their homework and determine the idea size for a class of tables. > > Often ... creating multiple tablespaces with different extent sizes to > avoid > > both fragmentation and keep the number of segments small. > > > > Daniel Morgan > > > > > > > > EP wrote: > > > > > Hi > > > > > > Does anybody know the algorithm used by Oracle when calculating the > extent > > > size for LMT when autoallocate option is specified ??? > > > > > > TIA > > > EP > >