Re: My Whole World Fell Apart!!

>have a table called scpard it was created with an initial extent of >620 Kb and a next extent size of 124 Kb. > >My problem is with rate at which extents are being added. Currently the >table is adding extents at a rate of 5 per week. There are 38 extents >and the calculated max extents is 225. As we're a 24x7 site I only have >a couple of scheduled downtimes a year. > >I ran the following SQL Query, but did not get the results I expected. >Baring in mind that PE_SIZE is in 2 Kb Pages. I thought the initial was >going to be 310, which it was. Then my whole world fell apart when the >next extent was not 62 and all other extent sizes seemed to be randomly >sized. I really do need to know why this is, in order to recalculate >the next extent size correctly. > >Is there some sort of weighted factor like Oracle's PCTINCREASE and >furthermore what is the rationale behind this bizarre extent allocation >sizes. Or why even specify a next extent as it seems to ignore it >anyway! > 1) If there is space next to the last extent when more space is needed, then that extent will be expanded instead of allocating another extent. 2) The "next extent" size is doubled every 16th extent. 3) If contigeous space can not be found for an extent request within a database space, then a smaller space will be allocated. What it sounds like is happening is that when the request is made, there are not any contigeous space within the db-space that can handle that request. Since you are getting some large extents mixed in with some smaller ones, my guess is that this extent request is probably is in the same db-space that tempory tables are being allocated within. That is causing the db-space to be fragmented so that a single large extent request can not be made. You might want to do some oncheck -pe's during your production time, and see how the dbspace in question is being used. If it appears that there are some "holes" in the extent list for that db-space, then temporary tables are being allocated within that same db-space. If that is the situation, then you might need to "force" an extent allocation during non-production time by inserting a number of dummy records into the table until the extent allocation is done, then removing those dummy records. Madison Pruet