FW: Oracle 8.1.17 extent management

Some how this came up to 22K in the transmittal (must be html garbage), so it didn't go to the list the first time around.  

From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Friday, June 20, 2008 10:49 AM
To: 'JDunn_at_sefas.com'; 'oracle-l'
Subject: RE: Oracle 8.1.17 extent management  

Unless the next extent sizes are small compared to the actual multi-block read sizes frequently attempted by your queries, you frequently drop objects incurring a costly algorithm to put the dropped extents back on the available extents list, or you allocated new extents so frequently that you queued on the latch allocating extents, it is unlikely performance issues were due to extent fragmentation to a degree that would justify reloading the data again into local management. How long soon is may modify that notion, and if it is very cheap operationally for you to simply move to locally managed tablespaces, you need little justification.  

But probably it is sufficient to run across your user objects making pctincrease 0 and making the next extent size uniform in each tablespace and large enough so you are unlikely to experience breaks in access through a multi-block read. Unless you have a specifically engineered disk farm allocation and positioning to match boundaries AND make monolithic large queries behave as if you were operating a single user system, it is unlikely that the next extent should be smaller than 1 MB (or the closest amount near 1 MB that matches your hardware's natural clustering amounts and boundaries.) A laugh test check is that you have enough acreage of storage to handle +1 MB each for all your users objects, should they extend at all. If not, you might need to consider a smaller, but still substantial uniformly applied next size for the tablespaces containing many of these presumably smaller objects. (Notice that you would need that same amount of storage to move to uniform 1M local storage.)  

If the actual problems were due to low data density (which in aged implementations has many causes, the most frequently observed of which is many block having experienced many row deletes but either insufficient row deletes to put the block back on the freelist or despite being back on the freelist, never reaching the head of the free list to be used again due to the huge number of blocks returned to the freelist(s)). If soon is soon enough to prevent that, you should be okay without future rebuilds, but that is a problem that can recur with age even in 10 and 11 whether with freelists or ASSM. (And it is not related to extent fragmentation in any way.)  

Another interesting problem is when completely emptied out blocks at the beginning of a given table are postitioned on the freelist (or an ASSM bitmap) so they do not become candidates for new inserts, combined with an application that tests for any row existence or scans a table with a stopkey implicitly relying on that being a cheap operation. If many empty blocks must be scanned before the first row or set of rows meeting the stopkey size, then the operation can become quite expensive. The elimination of this problem (and other beneficial side effects) is often attributed to the elimination of "fragmentation" of extents achieved in a rebuild effort such as your export/import. (Using any leading edge indexed column in place of the * effectively removes this problem, if you can repair the application cheaply.) Heavy fragmentation of extents is a symptom that is sometimes associated with sparse blocks. Before we had reasonable diagnostics (and when a 7 GB database qualified as a VLDB), it was often cheaper to simply rebuild the database to rule out those problems than to figure out which tables had problems. Likewise, you need to consider the cost of moving to locally managed tablespaces against what you are likely to experience remaining with dictionary managed tablespaces. If you had the import to do over again, getting immediately to locally managed tablespaces would have preempted the question, and I would certainly build anything new with locally managed tablespaces.  

Regards,  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of John Dunn
Sent: Friday, June 20, 2008 7:37 AM
To: oracle-l
Subject: Oracle 8.1.17 extent management  

In have a customer still runing 8.1.7. They were having performance issues which appeared to be due to frgmentation. An export and import solved the performance issue.  

They are due to move to Orcale 10 soon, but in the mean time what can they do to minimise fragmentation on 8.1.7.  

Move to locally managed tablespaces? Change the next extent values(which appear to be very small) ?  

<snip>

--
http://www.freelists.org/webpage/oracle-l