RE: extent allocation
- From: Graeme Farmer <graeme.farmer@xxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Jun 2004 23:29:22 +1000
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@xxxxxxxxxxxxx]
Sent: Wednesday, 23 June 2004 10:22 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx 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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
--
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.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
