Originally posted by nagarjuna
It's no big deal to have large no. of extents as long as your queries use indexes. It DOES matter when table goes for FTS (full table scan). When a query goes for FTS, Oracle has to check for next block which may not be continous in the case of many smaller sized extents, but there are chances of finding the continous blocks in the case of few big sized extents.
It DOES NOT matter even for FTS. This is one of those old myths that symply wouldn't die, no matter how many time it has been proved that they are wrong. The number of extents does not have any practical impact on FTS performance as long as your extents are sized properly (in accordance with block size and db_file_multiblock_read_count). For further reading, check an old, but still excelent and totaly valid whitepaper by Cary V. Millsap "Oracle7 Server Space management" (http://www.orapub.com/papers2//doc104.pdf), particulary the chapter: 7.4.4 Drive Head Motion.

For those of you who don't want to go through the whole paper, here is a quote from chapter 7.4.4 "Drive Head Motion" that talk about this myth:

"7.4.4 Drive Head Motion
Another popular argument against having multiple extents is that a disk drive head execut-ing a full-table scan naturally tends to “sweep” over a disk in its task of reading data. The argument goes, if the head reaches the end of an extent, not only does the Oracle Server software have a job to do (calculate the next extent’s address), the drive head also has to pack up and physically move to a different location from the one at which it happens to be, a wasted mechanical motion that “obviously could have been prevented.” This argument is frequently validated in tests by authors who measure full-scan performance of single- and multi-extent tables.

Unfortunately for the applicability of the experimental results in OLTP environments, almost all such tests are performed in a carefully controlled environment on a single-user computing system. To understand the profound influence of this assumption on the argument, let’s re-view the mechanics of a full-table scan (we’ll hit the high spots).
1. A server process will read a batch of blocks from a database file, and those blocks are pinned into the database buffer cache of the SGA.
2. The server process sequentially reads information from the blocks in memory, processes that information, returns rows, and un-pins the blocks in the SGA.
Step 2 requires non-zero time, and especially if db_file_multiblock_read_count is large, finishing step 2 gives plenty of time for a competing process on the system to have queued an I/O request of the disk drive that our experimenters assume to be patiently awaiting our next I/O request. Fact is, our disk drive head is just as likely to be close to the first block of a new extent as it is to be close to the next block in our current extent. Experiments on heavily-loaded, operational OLTP computing systems show there to be no measurable difference between full-table scans on large tables with one extent and full-table scans on large tables with many extents."

See also http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_CRITERIA:730289259844,%7Bnumber%7D%20and%20%7Bof%7D%20and%20%7Bextents%7D

Originally posted by Shestakov
if we have many extents in some table, and these extents place on different tracks of HDD we increase number of mechanical movements of heads but this operation dramaticaly slower then usual read data from HDD. This is one of the problem with number of extents.
Doesn't matter we use raids or hdd cache or not.
this is degradation of perfomance in any way.
See the above quote (or better yet, the quoted document). Besides, even if you have the whole table in one single extent, this doesn't neccesserily mean that the data of that table is stored in contigues space on disk! See http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_CRITERIA:1056598515561,%7Bnumber%7D%20and%20%7Bof%7D%20and%20%7Bextents%7D

So once again: the number of extents is irelevant for performance of the queries. Why we realy don't want to have segments with extremely large number of extents is because of the managebility of such segments and because of the possible negative performance impact on some DDL operations (DROP, TRUNCATE).



[Edited by jmodic on 08-31-2002 at 10:37 AM]