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" <[EMAIL PROTECTED]> 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 > [EMAIL PROTECTED] Quad/Tech International, Sussex, > WI USA > > > > -----Original Message----- > > From: Cary Millsap [mailto:[EMAIL PROTECTED]] > > 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: [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). __________________________________________________ Yahoo! - We Remember 9-11: A tribute to the more than 3,000 lives lost http://dir.remember.yahoo.com/tribute -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
