-
LMT. Pros/Cons of Uniform Extent Vs. Auto Extents
Can someone please explain me and/or redirect me where I can find information on, Which Extent allocation to be used, UNIFORM or AUTO ALLOCATE.
Is it drived by nature of database, like Data warehouse/OLTP/Hybrid? Your valuable suggestions are highly appreciated.
Thanks!
-
If you are going to use mixed sized objects in the same tablespace, I would use automatic mangement.
If you will place similar sized objects in the same talbespace, I would use uniform extent sizes following the 160/5/160 rule (tables <5M in uniform 160K, tables under 160M in 5M uniform size, tables < 160M in tablespace with uniform size 160M)
Jeff Hunter
-
Regarding the smallest extent size, in this case 160K -- this ought to be equal to, or an exact multiple of, the block size multiplied by db_file_multiblock_read_count, no? 160 seems a peculiar number in that respect. I would expect to see 128, or 256 maybe.
-
Smallest extent size is DB_BLOCK_SIZE * 5, for UNIFORM.
OCP 8i, 9i DBA
Brisbane Australia
-
Originally posted by grjohnson
Smallest extent size is DB_BLOCK_SIZE * 5, for UNIFORM.
yes, the smallest possible is 5*db_block_size, but i was meaning the smallest desirable extent size.
-
-
Ah ha, I thought I remembered the 160K number from somewhere, however the authors are referring to Oracle 7 when they recommend this size.
Here's a quote ...
These extent sizes are chosen to be a multiple of five blocks since Oracle7 will round all extent sizes to a multiple of five blocks. In Oracle8 extents won’t be rounded to a multiple of five blocks if they are a multiple of the minimum extent size for the tablespace. For Oracle8 databases, choosing the following extent sizes is a little simpler.
1) Segments smaller than 128M should be placed in 128K extent tablespaces.
2) Segments between 128M and 4G should be placed in 4M extent tablespaces.
3) Segments larger than 4G should be placed in 128M extent tablespaces
When the authors note later that ...
By using large extents, scan operations can issue large contiguous disk reads. Extent sizes of 160K already capture most of this speedup. 5120K extent sizes capture all of this speedup. Most segments are only ever accessed through indexes, so the table scan time is totally irrelevant anyway.
... it seems to be an indirect reference to db_file_multiblock_read_count, although they don't mention the parameter anywhere.
As a side issue, let us just note that the minimum extent size of (DB_BLOCK_SIZE*5) only applies to uniform allocation with automatic segment space management -- manual ssm has a minimum of DB_BLOCK_SIZE. It seems odd that "5" should crop up in both 9i LMT's with auto SSM, and in Oracle 7 -- maybe it's just coincidence.
-
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Re: LMT. Pros/Cons of Uniform Extent Vs. Auto Extents
Originally posted by Sharma
Can someone please explain me and/or redirect me where I can find information on, Which Extent allocation to be used, UNIFORM or AUTO ALLOCATE.
Is it drived by nature of database, like Data warehouse/OLTP/Hybrid? Your valuable suggestions are highly appreciated.
Thanks!
http://www.dbazine.com/burleson11.html
http://www.dbazine.com/jlewis8.html
HTH
Amar
"There is a difference between knowing the path and walking the path."
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|