Cary,
Great info, thanks. For some reason, I didn't finish reading this post when I replied to it earlier. Jared On Tuesday 10 September 2002 15:28, Cary Millsap wrote: > Just for fun, a little historical perspective: Does anyone remember > exactly why the number of extents *did* matter at one point in history? > Did it ever really? > > * * * > > The answer is that yes, it did matter for a while, but not for the > reasons that most people believed, and not at all for most types of > applications. Inserting, updating, deleting, and querying has *never* > been appreciably slower for multi-extent tables or indexes than for > single-extent ones. But for DROP statements, dictionary managed response > time is proportional to the square of the number of extents (minutes for > a few thousand extents, even on fast hardware). For locally managed > tablespaces, response time is *much* better, proportional only to the > number of extents (less than a second for tens of thousands of extents, > even on slow hardware). > > Another problem was a bug in how Oracle reused data blocks in clusters. > "Clusters?! We don't use clusters!" Sure you do. Oracle stores FET$ and > TS$ in a cluster called C_TS#. > > If you insert more than about 70 FET$ rows in a 2KB C_TS# cluster block, > then the cluster will chain (allocate a new block, and link to it). > That's no problem. The problem is that, once upon a time, there was an > Oracle bug that prevented good reuse of these blocks if you deleted rows > and then reinserted. For example, if you inserted 700 rows with TS#=7 > into FET$, then you'd drive the allocation of about 10 blocks to C_TS#. > Now, if you delete all 700 of those rows and insert a new row, guess how > many LIOs it would take to query that new row? Nope, not 1. Yes, 10. > > The symptom? If you ever let a table get thousands of extents in it, and > then try to drop and recreate it, both the drop and the recreate would > be really sloooow. The DROP would be slow because dictionary-managed > DROPs are O(n^2). The recreate would be slow because querying FET$ for > freespace information during the CREATE statements was doing far more > work than it should have needed to do. This bug was fixed in Oracle > 6.0.36. But the myth lives on through the magic of authors who either > (a) assume that it's safe to generalize upon the results of one > observation, or (b) believe that the benefits of sounding authoritative > exceed the costs of propagating incorrect information to thousands of > buying believers. > > "Any widely held myth can outlast a collection of mere facts." > --John H. White, Jr. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 > Honolulu > - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas > - Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark > > > > -----Original Message----- > Sent: Tuesday, September 10, 2002 4:45 PM > To: Multiple recipients of list ORACLE-L > > > <sympathy> > > I can't tell you how many times I've tried to explain to more junior > DBAs > that number of extents doesn't matter anymore. > > Then they'll point me to some official looking book where it says, "They > do > too matter." > > What's embarrassing is that for a time part of being a good DBA was > figuring out your INITIAL and NEXT so that you got only 1 or 2 extents > per > table or index. But that whole issue is now so 2nd millennium. > > </sympathy> > > > > > > Jared.Still > > @radisys.com To: Multiple recipients of > list ORACLE-L <[EMAIL PROTECTED]> > Sent by: root cc: > > Subject: OT: > Misinformation Ranting > > > 09/10/2002 > > 04:28 PM > > Please > > respond to > > ORACLE-L > > > > > > > > > > <RANT> > > I've just spent 30 minutes with our SAP administrator trying to > convince her that we really don't need to reorganize the tables > in our production SAP database. > > Due to some misinformation in an Oracle Press book, 'Oracle Unleashed' > I think, she is equating number of extents with fragmentation. > > The text she referred me to is in fact discussing 'migrated rows' though > that term is never used. She has become convinced that if the > extents allocated for tables are not all in contigous space, some > very nasty fragmentation will occur. > > I tried taking it down to disk and explaining that an OLTP system with > hundreds of users won't really see much benefit from this, but she > wasn't really ready for that. :) > > Her concern is that there are 29000 extents in an index tablespace. > This might have something to do with there being 3400 indexes in > said tablespace. > > Total 'wasted' ( honeycomb ) space in this 250 gig DB is < 20 meg. Not > much to gain there. > > The text of the book states that you should expect a '10 to 20 percent > performance increase' by reorganizing the tables/indexes. No data to > back it up of course. > > This is on a database that performs very well most of the time, outside > of a couple of custom reports that run too long. No complaints from > users about slowness. > > Arrghhh! > > I just had to vent to the list, cuz there's no one here that > understands. > > <\RANT> > > Jared > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
