Re: Opinions on multiple extents on tables/indexes

In article <5knbrj$cqa$2_at_news2.alpha.net>, scott.overby_at_sdsdata.com says...
>
>What is the conventional wisdom on having multiple extents on tables/indexes
>and the effect on performance?
>

I am aswering my own question.

Taken from:

http://tiburon.us.oracle.com/odp/archive/library/pdf/30433.pdf

Oracle7 Space Management Rev. 1.4b (95/10/31) 53

7.4.6 Real Benefits of Multiple Extents
There are actually several very good reasons to use more than one extent in an Oracle data- base segment. To summarize:
· Using more than one extent in a table that is never full-scanned bears absolutely no im-pact on
the performance of queries on that table. · Using more than one extent in an index bears absolutely no impact on the performance of   searches performed using that index.
· Using more than one extent in a table, cluster, or temporary segment does not materially   impact the performance of full-scans on an operational multi-user system. · Using more than one extent in a table, cluster, or temporary segment does not materially   impact the performance of full-scans on a dedicated single-user batch processing system if the   extents are well-sized and if the application is written to avoid expensive DDL op-erations. · If you match your extent sizes appropriately to your multi-block read batch size, you further   minimize the alleged performance cost of having many extents in a segment. · You should prefer many extents to few extents for rollback segments, because using multiple   extents for rollback segments can help reduce recursive SQL calls to do dy-namic extent   allocations on the segments.
· The real constraint on the number of extent allocations you want for a segment in ver-sions of   Oracle prior to 7.3 is the segment’s effective maxextents value, which perma-nently limits the   growth of a segment, and which has an upper bound that is a function of your database block   size.
· It isn’t possible to put very large segments into single extents because of file size and file   system size limitations.
· You should want to put your largest segments into many extents, because it gives you the   opportunity to stripe parts of those segments across different disk drives. · You should want segments to allocate new extents over time, to allow you to take advan-tage   of the market’s faster, less expensive disks.

-- 
==========================================================================
Scott Overby                    E-mail  scott.overby_at_sdsdata.com
Sr. Systems Analyst
Strategic Data Systems          FAX     (414) 459-9123
615 Penn Ave.                   
Sheboygan, WI 53082
===========================================================================