Re: OT: Misinformation Ranting

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 <ORACLE-L_at_fatcity.com>
> 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: Jared.Still_at_radisys.com
>
> 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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
Received on Wed Sep 11 2002 - 16:29:31 CDT