| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: extent allocation
Re:
On the other hand if the tablespace extent size
is small and the table extents are large and numerous there most likely
will be a lot of disk thrashing to get the data as the extents could be
all over the disk and not contiguous.
Contiguous extents do not lend themselves to improved performance in most cases due to the random access prevalent in most databases. Even if user A is performing a sequential scan which may benefit from contiguous layout, users B-Z are also using the database and the disk(s) will be accessing data in a random manner.
I too advocate the use of common extent sizes in tablespaces (even when Dict Mgmt was all we had) as it ensures efficient allocation and re-use of freed space.
With AUTOALLOCATE the smallest possible extent size is 64k and the first 16 extents are set to this size, the next 63 are 1M, then it jumps to 8M for more disk space than I have available but I guess if it follows a trend then it would have to be 255 before jumping up to 32M in size, then 1023 before jumping to 64M by which stage your table would be coming close to 300GB.
So for tables <2GB for example, you will have a mix of three different extent sizes per tablespace and de-allocation of smaller extents may leave pockets of space that cannot be re-used by large extent tables.
<DIGRESSION>
Too much focus on AUTO-thingies these days. Automatic Undo, Automatic Storage Management, Auto Allocate, etc.
It was with great amusement that I read the agenda for Oracle Openworld in Melbourne, Australia this year.
Among the presentations were the following:
Oracle 10g The self-managing database
The self-managing database: Automatic SQL Tuning
The self-managing database: Automatic Performance Tuning
Automatic Storage Management: The Best New Practice
and then there is
Managing the self-managing database: Part 1
followed by
Managing the self-managing database: Part 2
It seems there is a lot of management involved in managing the self-managing database, eh?
</DIGRESSION>
Cheers,
Graeme.
-----Original Message-----
From: Ron Rogers [mailto:RROGERS_at_galottery.org]
Sent: Wednesday, 23 June 2004 10:22 PM
To: oracle-l_at_freelists.org
Subject: RE: extent allocation
I would have to say that extent size does matter but depends on what you
are placing in the tablespace. Why would you want to have 100 M extents
when the tablespace will contain table that are small such as 4 K. When
the tablespace has to increase in size it will consume a lot of space
just for a small table. On the other hand if the tablespace extent size
is small and the table extents are large and numerous there most likely
will be a lot of disk thrashing to get the data as the extents could be
all over the disk and not contiguous.
I think that it is better to plan the database layout and keep like
sized object in a like sized tablespace as much as possible.
I prefer to use the uniform extent size method as it allows me to plan
the disk layout and future growth capabilities for the disks. With
autoallocate the growth pattern looks logarithmic and increases in size
dramatically as the number of extend increases.
Ron
>>> Jacques.Kilchoer_at_quest.com 06/22/2004 9:20:18 PM >>>
I don't think anyone is going to argue AGAINST uniform extent
allocation. However, some people have argued AGAINST autoallocate.
If you made all tablespace uniform no one would be able to say you
made
the wrong choice.
The next question is, what size should you make your uniform extents? A: Since number of extents is irrelevant (this also seems to be the consensus on this list), then I would say that the extent size doesn't really matter either.
-----Original Message-----
Harvinder Singh
It will be great if you can point me to articles/white papers
explaining
advantages/disadvantages of using autoallocate/uniform extent
allocation
for LMT
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
--
This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please notify the sender and delete the transmission. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Limited unless expressly stated otherwise.
 |
 |