| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Does the number of extents affect the performance?
On Wed, 10 Sep 1997 21:53:18 GMT, tkyte_at_us.oracle.com (Thomas Kyte) wrote:
>On Tue, 09 Sep 1997 22:25:37 -0700, Mohamed El-Mallah <mie_at_mainruis.sannet.gov>
>wrote:
>
>>
>>The number of extents in table does not affect the performance of full
>>table scans as long as
>>the size of the extents is a multiple of the sizeof each read performed
>>(DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE)
>>Mohamed El-Mallah
>>SDDPC
>
>And if it isn't, it still won't affect the performance of a full table scan of a
>table in multiple extents. We are talking micro-milli seconds if we don't get a
>full db_file_multiblock_read_count number of blocks in a read.
>
Ok, yes, for some very worst case scenarios, yes, having many (hundreds or thousands) extents that are not some multiple of db_file_multiblock_read_count could cause a slow down of a full table scan. The slow down would ocurr due to the fact that for every extent that cannot hold a multiple of db_file_multiblock_read_count, we would have to issue additional read. The worst case would be an extra read/extent in the table.
sorry.
>Let your tables go into as many extents as you want. It will not affect the
>performance of inserts/updates/deletes/selects.
>
>The ONLY statements that are affected by a table in many extents are certain DDL
>command (eg: it takes longer to drop a table with 200 extents then 1 extent
>becase we need to remove 200 rows from uet$ instead of just 1).
>
>>
>>Thomas Kyte wrote:
>>
>>> >After reading several articles, I get an imprsssion that the fewer
>>> >extents in a table's data segment, the better the performance would
>>> >be. But I can't find this statement in any official document.
>>> >So I want to ask the experienced DBA about the relation between the
>>> >size of extent and the performance.
>>> >
>>>
>>> No, performance for a 1 extent table will be no different then a 100
>>> extent
>>> table. this (few extents = better performance) is a rumor.
>>>
>>> On many databases, you can run export with compress=y and see a
>>> measurable
>>> performance improvement in the resulting imported database. The
>>> serious flaw
>>> with using this data point as a "proof" that few extents is good
>>> is that you can use compress=n and achieve identical performance
>>> gains. The
>>> performance gains come not from reduction in the number of extents in
>>> database
>>> segments, but from the block packing and row fragmentation removal
>>> implicitly in
>>> any import.
>>
>>
>
>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Government
>Bethesda MD
>
>http://govt.us.oracle.com/ -- downloadable utilities
>
>----------------------------------------------------------------------------
>Opinions are mine and do not necessarily reflect those of Oracle Corporation
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |