Re: Does the number of extents affect the performance?

Assumptions :
DB_FILE_MULTIBLOCK_READ_COUNT =16
DB_BLOCK_SIZE=4K
Table 192M total size
Case 1: 1 Extent 192M

             Number of reads required to perform full scan = ceiling(192*1024/(16*4))=3072 Reads
Case 2: 8 Extents 24M each

             Number of reads =ceiling(24*1024/(16*4)) * 8=3072 Reads Case 3: 2048 Extent 96K each

             Number of reads = ceiling(96/(16*4))*2048=4096 Reads
             It needs 2 reads (64K each) to read one extent (96K each)
             There is extra 4096-3072=1024 reads (each 64K)
             I do not think that this is micro seconds, we are talking
relatively now
             in this case there is 1024/3072%=33% overhead reads

Best Regards

--
Mohamed El-Mallah
San Diego Data Processing Corp.
5975 Santa Fe Street
San Diego, CA 92109
Tel. (619) 581-7813
Fax (619) 581-9606

Thomas Kyte wrote:



> 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.

>

> 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

> 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