Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.ision.net!newsfeed2.easynews.net!ision!newsfeed.freenet.de!newspeer1.nwr.nac.net!border2.nntp.dca.giganews.com!nntp.giganews.com!news.glorb.com!nntpserver.com!zeus.nntpserver.com!10.1.1.41.MISMATCH!pfilter-v0.1!not-for-mail Date: Wed, 04 Apr 2007 11:02:15 -0500 From: Brian Peasland User-Agent: Thunderbird 1.5.0.10 (Windows/20070221) MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server Subject: Re: Are multiple extents still a problem with LMT? References: <1175685870.650528.217830@y80g2000hsf.googlegroups.com> <1175691774.500456.177760@q75g2000hsh.googlegroups.com> <1175693409.545408.143540@e65g2000hsc.googlegroups.com> In-Reply-To: <1175693409.545408.143540@e65g2000hsc.googlegroups.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Lines: 81 Message-ID: <4613bfa7$0$16336$88260bb3@free.teranews.com> NNTP-Posting-Date: 04 Apr 2007 15:09:27 GMT X-Complaints-To: abuse@teranews.com Xref: news.f.de.plusline.net comp.databases.oracle.server:195754 Andy Kent wrote: >> I can't understand why 'extent fragmentation' is a problem as you do not >> have any odd-sized extrents which cannot be reused by other objects > > You seem to be misunderstanding how extent allocation works. Extent allocation has very little to do with performance of your application. This has been proven time and time again. > If two > tables are grabbing extents alternately then their extents will be > interleaved and there is no way you can avoid having a whole load of > extents of the default size for the table or tablespace. Even if > extents are contiguous 8i doesn't automatically figure that out. You > can reduce the problem with COALESCE but that will make no difference > where interleaving has happened. I'm not sure why this is a problem. See more on that below... > Having too many extents creates two distinct problems: > 1. Access to the dictionary increases with the number of extents and > the dictionary itself may fragment if the number is large. This can be > a considerable overhead (especially in sub-optimal situations where > the system and data tablespaces are on the same disk). Are you assuming that Oracle consults the Data Dictionary to read each and every extent? This is not normally the case. Most access to the data is done by a ROWID value which contains the file and block within that file. When reading data from a table, Oracle does not read the Data Dictionary, read a block from the table, go back and read the Data Dictionary for the next block location, repeat. > 2. Large queries and especially full scans are compromised because the > data blocks do not occupy contiguous space so seek time is increased. > However where datafiles use filesystem space the filesystem is likely > to add its own fragmentation overhead so making everything [logically] > contiguous within a datafile may be a lost cause. This type of thinking is not correct. Let's assume that you have a 1GB table and you are reading all data in that table. What are the chances that you'll be able to actually read all 1GB of that data without interruption? Remember that you only have one disk unit at play here. Other user's I/O requests are interleaved with your I/O request. And even if you are the only user in the database, there are still requests for access to the control files, temp space, undo, etc. If the user could guarantee that their I/O request would be satisfied without interruption from start to finish, then having the data physically contiguous on disk could speed things up. But that is very, very rare in today's environments. > Now, I know that moving from DMT to LMT sorts out 1. What I'm trying > to establish is whether table rebuild should be added to the equation > in order to mitigate 2. As has been said many times in these (and other) forums, defragmenting for the sole purpose of performance gains unncessary. Oracle performs quite well with many, many extents. > Rest assured I am also tuning SQL (ANALYZE had never been run!), > indexes, SGA and undo/ redo. I sure hope you aren't running ANALYZE to gather your statistics! If you are on a version of Oracle that supports LMT's, then you should be using DBMS_STATS for your statistics gathering. Cheers, Brian -- =================================================================== Brian Peasland dba@nospam.peasland.net http://www.peasland.net Remove the "nospam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three" - Unknown -- Posted via a free Usenet account from http://www.teranews.com