I like the way when you responded to the question and attached links that related to the similar situation in the question. Reading the attached links expanded the knowledge of topic we are concerning.

Tony

Isn't there a difference between a full table scan of one extent vs many, with the possibility of scattered extents causing increased seek time? Or is this another of my closely-held truths being de-bunked?

Or how about this one? If the extent size is not coordinated with db_file_multiblock-read_count, you will have an 'inefficient' full table scan?

Sorry, but it's just hard for an old-timer like me to grasp this conceptually...

I can only guess that the only time this becomes a problem is when there are lots of tables with many extents.
When the database parses and executes a query it has to access base tables(x$tables) to identify the extents, which are part of the table being accessed. In doing so, these base tables are being FTScanned (no indexes on these base tables). When you have thousands of tables and they all have 100's of extents then it may cause some performance issues.
Which means that the access performed implicitly by the database during parse and execution of statement will impact performance not the actual statement be executed.
Oh! by the way if you use PQO, having lots of extents can often be of a benefit where 1 extent doesn't take advantage of PQO.

Thanks...

"you assume that an extent is contigous on disk. They
are not."
I beleive it is true for 'an extent is contigous on disk' and segment may not. That is why we may have segment fragmentation not extent fragmentation. I may wrong, if so, please show example. Thanks

Since 'extent size' and 'db_file_multiblock_read_count' are only logical concepts, it appears we lose some control is making these specs!

However, isn't it still better to try to allocate the DB files in contiguous blocks rather than have them scattered all over the disk? Or does the hardware compensate?

And if you had a large table that required frequent scans wouldn't it be better to segregate to its own (or infrequently accessed) disk to keep that seek time down?

Just trying to understand. Is the hardware that makes all of these concepts obsolete in the end?

The PDF Suggested by u is excellent. This sorts of attachments really helps.

The only exception to this argument is the nice gains you can get by putting raw redo logs on their disk - Nothing else moves the head and your logs scream along...The bummer with this is nowadays you're putting 200m logs on a 35G disk - hard to convince the bean counters on that one.

...isn't it true that there is considerable overhead involved in CREATING extents? So, if you define your next extent too small and are inserting a large number of rows into the table, won't it slow it down to have to keep stopping to create new extents?

Well, it looks like my original question has generated a few thoughts, so I wanted to take a second to put in my $0.02...

Keep in mind that I am using *only* Locally Managed Tablespaces (except for system, of course) with UNIFORM allocation (I can't find a good use for AUTOALLOCATE). I notice no appreciable performance decrease during a "full" load where lots of extents are being allocated/deallocated. For example, I have one table that during a "full" load process grows to 2,500 extents. (This is the extreme, most tables are between 1 and 200 extents.) It only stays at that number of extents for an hour or so, as another process comes along and removes the records shortly after being loaded into this "staging" table. (This is third-party app, by the way, so I have no control over the way it works. bummer.) Could I move that table to a tablespace with a bigger extent size to keep it from growing to 2,500 extents? Sure. Should I? Not as far as I am concerned - it is only one table, and it is not worth it to me. This process happens once a month. I have timed the process with a dictionary managed tablespace using 1 extent versus the LMT and the 2500 extents. The LMT is usually very slightly faster on average. Remember that since these are LMT's, Oracle does not have to access sys.uet$ and sys.fet$ for extent allocation/deallocation (which would be protected by undo and redo log generation, if I am not mistaken). It only has to read through the bitmap in the datafile and set a bit accordingly. Besides, most accesses to that table are full table scans, and the number of blocks in the table are the same regardless of the number of extents.

Anyway, the biggest benefit for me is that I can not have any tablespace free space fragmentation. period. With a dictionary managed tablespace, I can set the defaults for a tablespace, but the application setup (which is a binary in this case) may think it knows better and set the storage parameters differently resulting in objects with different initial and next settings.

I know DBA's who spend countless hours exporting with compress=y and importing so that an object is in only one extent. It is a vicious cycle, because the tablespace now has a bunch of objects with different extent sizes. These objects invariably extend again, and the whole process is repeated. Hello tablespace fragmentation.

My parting comments (which I know no one asked for):
Read the Oracle documentation - it has 20,000+ pages of very useful information (and is available online, so if you can read this, you can read the documentation), buy Tom's book, buy Jonathan Lewis' book, "Practical Oracle8i", and, finally, have every DBA and developer read them.

Sorry for the rambling. Thank you, and good night.

I find the extent debate somewhat humorous. Other RDBMS have been using bitmaps for space management since the 80's and early 90's. Having worked with DB2 and its segmented tablespaces, the number of extents was never an issue.

It's about time that the Oracle community finally caught up to the rest of the database world in space management methodology.

Tom,
Thanks a lot for all your help,
Can you please tell me which file system is best for Oracle database and why?

Regards
Tony

With dictionary managed tablespace, the number of extents shouldn't reach over 1024. How about LMT? Is it ok to have 3000, 5000, etc?

I have created a database with LMT. This is a 3rd party application. I leave all tables in their original tablespaces except for size 40M - 1G, I put them in medium size tablespace and for over 1G, I put them in Large size tablespace. For all tablespaces beside medium and large, I have uniform size of 160k. The problem is a lot of tables are empty and therefore they take a lot of space and database size increase 60%. If I set uniform size of 16k then some tables would have 2500 extents. Is it ok to have that many extents even in LMT?

Thank you.

Besides Tools, is there a certain number of extents that if exceeds, it's going to hurt?

Another question. If I specify storage for creating object in LMT, would this cause fragmentation. Example, I have first extent 1m next 2m minimum 4 extents for uniform size of 2m tablespace. This is going to create 1+2+2+2=7 for first extent - an odd number. If I drop the object, there's 1m may not be reused for future.

No, that is the beauty of LMTs: they don't let you mess up things, hard as you may try. Once you set up an LMT with 2M uniform extents, there is no way to create anything bu 2M extents. If you ask for (initial 1M next 2M minextents 4) you will get 4 extents of 2M. The "next" setting in the storage clause is ignored alltogether and the initial and minextents settings provide a lower boundary. Oracle will create as many of the uniform extents as needed to meet or exceed the requested space. If you'd ask for inital 11M minextents 4, you'd get 6 2M extents, which is the smallest number of extents to satisfy both your minextents AND initial requests.

Excellent!

How about catalog and catproc scripts? I still need to create all dictionary views when create database, don't I?

I created an empty database with LMT so that I can import my data from Dictionary MT to this. I use uniform size of 8k as thousands of our tables are empty (3rd party application). However, after creating all the tablespaces, the dba_extents shows 16k instead of 8k. Is this the minimum size for uniform extent? Because of this we will have a lot of space wasted. (abou 20G).
Anyway to work around?

Thanks so much.

Kelly,

Don't forget about minextents. That'll get you as well:
06:42:51 winkid1@MYOWN:HA52667> create tablespace test
06:43:29 2 datafile 'd:\oracle\oradata\myown\test01.dbf' size 1M autoextend off
06:43:37 3 extent management local uniform size 40k
06:43:46 4 segment space management auto
06:43:50 5 logging
06:43:52 6 online
06:43:52 7 /

Tablespace created.

Elapsed: 00:00:00.05
06:47:50 winkid1@MYOWN:HA52667> create table a (a number) tablespace test;

Table created.

Elapsed: 00:00:00.00
06:48:09 winkid1@MYOWN:HA52667> select extents from dba_segments where segment_name = 'A';

EXTENTS
----------
1

Elapsed: 00:00:00.07
06:48:16 winkid1@MYOWN:HA52667> select blocks from dba_extents where segment_name = 'A';

BLOCKS
----------
5

Elapsed: 00:00:01.02
06:48:19 winkid1@MYOWN:HA52667> drop table a;

Table dropped.

Elapsed: 00:00:00.00
06:48:27 winkid1@MYOWN:HA52667> create table a (a number) tablespace test storage(minextents 2);

Table created.

Elapsed: 00:00:00.00
06:48:42 winkid1@MYOWN:HA52667> select extents from dba_segments where segment_name = 'A';

EXTENTS
----------
2

Elapsed: 00:00:00.08
06:48:47 winkid1@MYOWN:HA52667> select blocks from dba_extents where segment_name = 'A';

BLOCKS
----------
5
5

Elapsed: 00:00:01.01
06:48:51 winkid1@MYOWN:HA52667>


Additionally, there is this requirement if you are using segment space management (this one took me by surprise...I always use 64k on my systems, so I guess I've been lucky):
06:41:43 winkid1@MYOWN:HA52667> create tablespace test
06:41:48 2 datafile 'd:\oracle\oradata\myown\test01.dbf' size 100k
06:41:50 3 extent management local uniform size 8k
06:41:58 4 segment space management auto
06:42:10 5 logging
06:42:15 6 online
06:42:17 7 /
create tablespace test
*
ERROR at line 1:
ORA-03249: Uniform size for auto segment space managed tablespace should have
atleast 5 blocks


And lastly, check the initial sizes. Even though your extents are of uniform size (unless you do the autoallocate), it will allocate the number of extents needed to fulfill your initial, plus some if initial isn't divisible by extent size:
06:48:51 winkid1@MYOWN:HA52667> drop table a;

Table dropped.

Elapsed: 00:00:00.00
07:04:07 winkid1@MYOWN:HA52667> create table a (a number) tablespace test storage(initial 120k);

Table created.

Elapsed: 00:00:00.00
07:04:25 winkid1@MYOWN:HA52667> select extents from dba_segments where segment_name = 'A';

EXTENTS
----------
3

Elapsed: 00:00:00.07
07:04:30 winkid1@MYOWN:HA52667> select blocks from dba_extents where segment_name = 'A';

BLOCKS
----------
5
5
5

Elapsed: 00:00:01.03
07:04:36 winkid1@MYOWN:HA52667>

ok, we asked for 120k initial, which is three extents of 40k. Makes sense. Now let's ask for 100K, and you'll still see it give three extents, because that's the minimum number to give you 100K.

07:04:36 winkid1@MYOWN:HA52667> drop table a;

Table dropped.

Elapsed: 00:00:00.00
07:07:21 winkid1@MYOWN:HA52667> create table a (a number) tablespace test storage(initial 100k);

Table created.

Elapsed: 00:00:00.00
07:07:30 winkid1@MYOWN:HA52667> select extents from dba_segments where segment_name = 'A';

EXTENTS
----------
3

Elapsed: 00:00:00.08
07:07:34 winkid1@MYOWN:HA52667> select blocks from dba_extents where segment_name = 'A';

BLOCKS
----------
5
5
5

Elapsed: 00:00:01.02
07:07:38 winkid1@MYOWN:HA52667>

Hope that tells you where the 16k is coming from.

Dennis

This discussion, and it's associated links, was extremely usefull. Not just because it told me what I wanted to here and provided me with some "ammunition" but also because it has really helped me to formulate a strategy for reorganizing tables.

As stated by others, this thread and associated links have been extremely helpful to me. My Oracle experience dates back to about 1990, when the "Minimize Number of Extents!!" philosophy was preached adamantly by everyone, including Oracle instructors. I'm glad to finally have this myth dispelled, and I can step confidently into the (new to me) 9i world knowing I don't have to worry so much about space management.

Hi Tom,

Using LMT with Uniforme Size which is the advantage of having only 100 extents in a segment?
Which is the disadvantage of having a segment with 10000 (or more) extents?
With a partitioned table does it matter how much extents the table has or does it only matter on partition/segment level?
Does it matter how much extent are in a tablespace?

Regards
Maurice

thanks for explaining of this topic.

Tom:

After DBA migrated an 8i objetcs to 9i database, I ran a schema check using TOAD to verify if both developer schema are equal. TOAD created a synch script with many statments like this:

ALTER TABLE MATERIAL_ITEMS PCTUSED 40;
ALTER TABLE MATERIAL_ITEMS (NEXT 104K);

ALTER index PK_shipment
storage (Next 40K Max Extents 505)

ALTER index shipped_item rebuild storage (Freelist 1 Freelist groups 1 initial 40K);

1. Shall I run all these statements under developer account? Should those match between 8i and 9i?

2. How Can I verify that DBA has allocated correct size for storage. Is there a way to verify that?

correct size meaning initial extent,next extent and max extent size

Hi Tom,

We know that we should always prefer the use LMT instead of DMT, because of reasons like less SYSTEM tablespace access, less fragmentation of datafiles ....

But for the sake of curiosity I would like to know, if asked to create a DMT/LMT with manual segment space management and create objects in it then what all factors need to be taken into account before deciding on the INITIAL, NEXT EXTENT sizes.

Please help me in getting my concepts clear on this.

Hi Tom,

Great tips... I salute your knowledge.

Can you please explain how to reach that UNIFORM SENSIBLE SIZE?

What are the ingredients needed to arrive at that size. >Does the row size come into picture here?
>Shall the extent size be always more than the average rowsize?

Hi Tom,

Thanks a lot for your answer.

So, it seems that the number of extents for a tablespace is not an issue, as long as it's in the 1,000s of extents (maybe even 10,000s).

Does that imply that big extent sizes are okay?

For example, we have a table with currently 32 million rows, and will grow to 100s million rows. Current tablespace size is 30 GB, with uniform extent size set to 3 GB.

3 GB extent size seems to be running okay. Is there an upper limit on how big an extent size can be, or should not be?

Thanks!

Hello Tom,
There are 3 questions regarding the dropping of big table which has many extents.

1. One of the table in test database which was 100GB in size was dropped (which was not required). Luckily everything is working fine on that database but they are seeing the TEMPORARY object of 60GB. How do to get rid of this?

2. Some time back I had faced a similar when I dropped a 5GB table on a test database. As a result no DDL or DML comamnds were going through. Then I realized the tablespace was created as DICTIONARY MANAGED TABLESPACE. When I converted that to LOCALLY MANAGED TABLESPACE it solved the issue.

Can you please explain why changing it to LMT solved the issue?

3. I searched in metalink to drop a big table (of size 115GB which has 2 LOB segments) and I found this

1. Truncate the table, specifying the REUSE STORAGE clause:

TRUNCATE TABLE BIGTAB REUSE STORAGE;

2. If it takes 3 days (72 hours) to drop the table, spread this out over
      6 nights i.e. drop 1/3 Gb per night. This can be achieved in 6 (nightly)
      steps as follows:
      Night 1: 
        SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 1707M; (2Gb*5/6)
      Night 2: 
        SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 1365M; (2Gb*4/6)
      Night 3: 
        SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 1024M; (2Gb*3/6)
      Night 4: 
        SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 683M; (2Gb*2/6)
      Night 5: 
        SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 341M; (2Gb*1/6)
      Night 6: 
        SQL> DROP TABLE BIGTAB;

The same method can be applied if LOB segments or indexes are involved.

SQL> ALTER TABLE <table_name> MODIFY LOB (<lob_column>) DEALLOCATE UNUSED KEEP <value>M;
 
SQL> ALTER INDEX <index_name> DEALLOCATE UNUSED KEEP <value>M;
 
What factors I should I consider while calculating the size as mentioned in step 2 (The ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP)?

Do you have any other methoof dropping a big table? 

I suggest to rebuild the table with storage (INITIAL 1M NEXT 1M) as the current extent size is 64K which will limit one i/0 to 64K extent size even if increase db_file_multiblock_read_count =128.
In FTS oracle can do one I/O of (128*8k) =1MB. But as extent size is 64K (whether locally managed or dictionary managed). The Oracle I/O cannot go beyond extent size, so it will be limited by extent size of 64K.

As per my knowledge Oracle I/O cannot go beyond extent size even the db_file_multiblock_read_count=128.

The other reason to increase the size of extent is:
Managing large number of extents has overhead.
The ideal number of extents on Oracle table should be less than 512 and if number of extents is more than 1024 it may cause performance degradation

Hi Tom,
you said November 30, 2006:

you would have to figure out
a) how big the segment will be
b) how many extents you think it too many (say a couple of thousand)
c) divide a by b, use that.

and you said in last review August 28, 2009:
"PROVE IT OR GO AWAY" to that guy that said "if number of extents is more than 1024 it may cause performance degradation"

10g: we use locally managed tablespace uniform size, we've got 4 or 5 tables which size is 2G, extent size 1M, so ve've just reached 2000 number of extents for each table.
My question is, is it usefull to reorganize so get less extents on these table? do you think we'll have better performance on these tables? or just waist of time.
Thanks!

Dear Tom.

Pls, see below example:

SQL> create tablespace myts1 datafile '/oracle/product/oradata/db1/myts1_1.dbf' size 512k extent management local;

Tablespace created.

SQL> create table myt1 (x int) storage(initial 256k next 256k) tablespace myts1;

Table created.

SQL> select extent_id, bytes, blocks from dba_extents where segment_name = 'MYT1';

EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 65536 8
1 65536 8
2 65536 8
3 65536 8

SQL> alter table myt1 allocate extent;

Table altered.

SQL> /

Table altered.

SQL> /

Table altered.

SQL> /
alter table myt1 allocate extent
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.MYT1 by 8 in tablespace MYTS1


SQL> select extent_id, bytes, blocks from dba_extents where segment_name = 'MYT1';

EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 65536 8
1 65536 8
2 65536 8
3 65536 8
4 65536 8
5 65536 8
6 65536 8

7 rows selected.

7 extenst * 8 blocks * block_size (8K) = 448 K
And datafile is 512k
Logically there should be 512k - 448k = 64K free space

Why Oracle can not allocate extent?

Thanks for your time.

Thanks, Tom.

Great!

I dont use initial and so on.

Just investigation Oracle internals :)

Thanks again.

Hi Tom;

Suppose I have blocks in freelist or bitmap,
When a table need an extent, does oracle combine these free blocks in freelist to make an extent
or
allocate untouched new extent from the datafile.

hello tom
Suppose table1 has some blocks in freelist or bitmap.
Do these blocks only belong to table1 ?
I mean can table2 use these blocks?

Thanks Tom;
Last question :)
Consider standard heap tables,
does a block contain rows of only one single table ?

say I have table1,table2,table3
and I have block1

Can table1,table2,table3 both have rows in block1?

If so, their freelist must have the info of this block.

Sir;
In above you said that:
...64k is used by Oracle to manage the tablespace right off of the bat

I know that this 64k is used by the extent management for LMT.
How about for tables?
For every table I create , is there a overhead like this?
(freelist or bitmap)

Tom;
Apologies for this easy question.

I want to know the fragmentation that most people used to deal with.

Assume table a has extents 1m,1.1m,1.2m,..
table b has extents 10m,11m,12m,..
table b can only use the extents that is more than its next extent size
In this case it will look 13m, therefore the free extents of table a will never be used.
Is it correct regarding the fragmentation that people used to deal with during old days.

..if table a HAD extents 1, 1.1, 1.2, .... 
AND you dropped table a 
we would coalese them if possible 


Sir, 
You said that, coalesce would happen once we drop table.
Why automatic coalesce doenst happen in this scenerio?


create tablespace tun_tbs datafile '/oracle/tun.dbf' size 5m;

create table tun (a number) tablespace tun_tbs;

alter table tun allocate extent;
alter table tun allocate extent;
alter table tun allocate extent;
alter table tun allocate extent;
..
..


drop table tun;


SQL> select  block_id,blocks,BYTES from dba_free_space where TABLESPACE_NAME='TUN_TBS';

  BLOCK_ID     BLOCKS      BYTES
---------- ---------- ----------
        73        568    4653056
         9          8      65536
        17          8      65536
        25          8      65536
        33          8      65536
        41          8      65536
        49          8      65536
        57          8      65536
        65          8      65536

sir,

What I dont understand is, since these blocks are adjacent, why oracle doesnt combine them to make one bigger one?

I was expecting something like this:

BLOCK_ID BLOCKS BYTES
---------- ---------- ----------
73 568 4653056
9 64 524288

Thanks Sir;

Is this true?
Wheter it is DMT or LMT, contigues blocks are not combined automatically once I drop table.
They are only combined when there is a large extent request...

Hello

Consider LMT with system allocated extends.

Suppose my extent sizes are all 64k and 1mb chunks in dba_free_space. (Suppose they are not adjacent)

If my table is not a new table and needs to extend 8mb, these free chunks will never used. Am I right?

System tablespace is DMT and objects has diffrent next extent size and pct_increase.
If I migrate the System tablespace to LMT using "DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM'); "
Does the next extent size or pct_increase of the objects change? (since in LMT it can be either uniform or system allocated)

Hi, I have taken over a role as APPS DBA at a company. They are running apps 11i on 10g database.
They have various tables that have lots of extents, one such table is GL.GL_JE_LINES with almost 60000 extents.
I have not heard any complaints from the users about the system being slow.
I want to move this table into a new tablespace, is it worth it?

DB_FILE_MULTIBLOCK_READ_COUNT' AND EXTENTS MANAGEMENT [ID 181272.1]
Modified 21-JUL-2004 Type TROUBLESHOOTING Status PUBLISHED

PURPOSE
-------
This article discusses the db_file_multiblock_read_count and its
performance impact on Extents Management.

SCOPE & APPLICATION
-------------------
Advices for DBAs who have to tune a database.
Skill Level: Novice

DB_FILE_MULTIBLOCK_READ_COUNT And Extents Management.
-----------------------------------------------------

The Parameter 'db_file_multiblock_read_count' specifies the number
of Oracle Blocks to be read in Single I/O Operation.
DB_FILE_MULTIBLOCK_READ_COUNT should generally be made as large
as possible.
The value differs between platforms and versions, and usually
depends on the settings of DB_BLOCK_SIZE and the maximum OS I/O Size.

EXTENT SIZING
===============

This parameter is normally used for multiblock reads for Full Table
Scans and Fast Full Index Scans and normally can be used with CBO.
The Oracle Extent Management can be tuned with this Parameter to get
optimal benefit for I/O operation. Normally it is recommended that the
entire Segment should be contained in Single Extent so that Full Table
Scan or Fast Full Index Scan just have to traverse a single Extent.
This will contribute to fast Query Operations.But as such, it is
not a must.Even if the Segment is spread over Hundred or Thousands
Of Extents and if the extents are of even size with proper
‘db_file_multiblock_read_count’ defined than also the Full Table Scan
or Fast Full Index Scan can improve performance.

The following Example will illustrate the above-mentioned fact

Suppose a Table X currently occupies eight extents with the
HWM (High Water Mark) in the eight extents.
Each extent is evenly sized with 50blocks
(db_block_size = 8K and Extent size 400k).
The db_file_multiblock_read_count is set to 16.



-------------------------------------------------------------------------
| | | | | | | | |
------ ------ ------ -------- -------- ---------- -------- <---HWM--->
| | | | | | | | |
-------------------------------------------------------------------------
E1 E2 E3 E4 E5 E6 E7 E8

<-------------------- 50 blocks per extent ---------------------------->

Here E1 - E8 represents Extents each with 50 blocks.
The HWM is in 389 th Block so 10 blocks are above the HWM since the
first block is the Segment header block and total 400 blocks.

The Multiblock I/O never spans Extent Boundaries even if the Extents
are continuous and also if using Locally Managed Tablespaces.
Assuming that none of the above blocks are in the Buffer Cache if we
do Full Table Scan, then total 31 i/o operations will be required.
Since each extent comprises of 50 blocks and 16 blocks read in single
I/O operation.
This means that 48-blocks/per extents will be read in 3 I/O operations
whereas for the last remaining blocks one extra I/O has to be performed,
in all making 4 I/O calls per Extent.This amounts to 28 I/O operations
up to 7 th Extent.
The 8 th Extent has 10 blocks above the HWM, so there will be only
3 i/o operations in the 8th Extent.
(32 blocks in 2 i/o’s and remaining 8 blocks in 1 i/o )

*** The Extents size should always be a Multiple of
db_file_multiblock_read_count ****

The Extents size should always be a Multiple of
db_file_multiblock_read_count, to take the advantage of Full Table
Scans and Fast Full Index Scans.
IF the extents of the Table X are made multiple of
db_file_multiblock_read_count (80 blocks per extent) then only
5 Extents are required to manage the entire segment instead of
8 extents.
Also since the extents are multiple of db_file_multiblock_read_count,
the I/O operations are reduced from 31 to 25. There will only
be 5 i/o operations per extent resulting in Total 25 I/O calls.
Since each extent comprises of 80 blocks and multiblock_read_count=16
Hence (16 * 5 = 80) each extent will require 5 I/O operations.


-------------------------------------------
| | | | | |
------- ------- ------- -------- ---------
| | | | | |
------- ------- ------- -------- ----------
| | | | |<--HWM-->|
--------------------------------------------
E1 E2 E3 E4 E5

<----------- 80 blocks/Extent ------------>


Thus the i/o operations are reduced from 31 to 25 just by proper
management of Extents.i.e if the Extents size are a Multiple of
db_file_multiblock_read_count then i/o operations required will be
less compared to when the Extents are not Multiple of
db_file_multiblock_read_count.

Here, reducing the number of Extents have not reduced the I/O Calls
but the multiple factor of Blocks per Extent with respect to
Multiblock Read, have contributed to lessen the I/O calls.

Hence, even if the Segment comprises of Multiple Extents and if the
extent size is a multiple of db_file_multiblock_read_count, the FTS
will be faster.
Remember here that even if the Table X was composed of Single Extent
of 400 blocks, then also if would take 25 i/o’s to read upto FTS.
So the number of Extents don’t matter if there are properl




Related
Products
Oracle Database Products > Embedded Database > Oracle Lite > Oracle Lite
Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
Keywords
DB_FILE_MULTIBLOCK_READ_COUNT

Dear Sir,

I could not open the page mentioned in the following link that is mentioned in this page.

" http://technet.oracle.com/deploy/availability/pdf/defrag.pdf"

Thanks,
Girish

Tom
Thanks for pointing the above article by quickly googling it.

Further, this is a good article on Oracle 7 and Oracle 8 days.
9i onwards many features have been added on space management.
So , any similar article on on 21st century databases?

Regards

yes Tom.
I agree.

:)

Hi Tom,

I know the parameters 'db_file_multiblock_read_count' - so if I set the uniform extent size multiples of the parameter db_file_multiblock_read_count - My Full table scan will be faster - if I opt for 'autoallocation of extents' by oracle - then there MAY BE chance that the Full scans may take longer time - and also we could avoid fragmentation by setting uniform extents ..

Nowadays you are suggesting 'auto-allocation of extents' instead of uniform extent allocation -

How can we decide whether to go for 'Uniform extent sizes' (or) 'Autoextent allocation' by Oracle for a table space ?