RE: OT: Misinformation Ranting

249 wasn't the max for 8k blocks

121 for 2k
249 for 4k
505 for 8k

are you sure you have 8k blocks? :) or was this database rebuilt from an older one, with 4k blocks and the table storage clauses kept from the original version?

• "Jesse, Rich" <Rich.Jesse_at_qtiworld.com> wrote:
  > Oooo Oooo Ooooo! EXCELLENT info, Cary! And I just got a message
  > that a
  > table had reached max extents -- 249 because we had 8K blocks. :)
  >
  > As a side note, while we don't strictly regulate the max extents, it
  > is nice
  > to cap it somewhere, so when a rogue user tries to dump a whole
  > year's worth
  > of data into a table (instead of the month's worth as per design),
  > there is
  > a little extra safeguard that the TS won't fill up -- unless you're
  > using
  > autoextend. But that thread's been covered already... :)
  >
  > Thanks, Cary!
  > Rich Jesse System/Database Administrator
  > Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex,
  > WI USA
  >
  >
  > > -----Original Message-----
  > > From: Cary Millsap [mailto:cary.millsap_at_hotsos.com]
  > > Sent: Tuesday, September 10, 2002 5:28 PM
  > > To: Multiple recipients of list ORACLE-L
  > > Subject: RE: OT: Misinformation Ranting
  > >
  > >
  > > 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
  > --
  > Please see the official ORACLE-L FAQ: http://www.orafaq.com
  > --
  > Author: Jesse, Rich
  > INET: Rich.Jesse_at_qtiworld.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
  > (or the name of mailing list you want to be removed from). You may
  > also send the HELP command for other information (like subscribing).
  

  ---