How do I determine my extent size on a table.

Posted on 2008-06-16
Last Modified: 2013-12-19
I have two tables I have to set extents for - I am doing this as I am still a beginner.   I have on table with 500 hundred rows, and I calcualted that the average row size is 30 bytes, cloumns 41bytes.  

I am yet to understand how to predict growth rates in regards to extents and bytes.  

If one of table grows at 26000 or 660000 a month, how would I determine what intitial extent to set, as well as the next extent sizes.  It is obviously a table that requires a hire percent used like 50+ and percent used at around 10.

Any expert clarification to this would be helpful...  

Question by:Alinafe
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 48

Assisted Solution

schwertner earned 25 total points
ID: 21800607
Use locally managed tablespace.
Yes, extent size does matter, but not greatly. Nevertheless, all extents should be a multiple of the multiblock read size. Otherwise, when a full table or index scan is performed, an extra multiblock read will be required to read the last few blocks of each extent, except probably the last one. This is because multiblock reads never span extent boundaries, even if the extents happen to be contiguous, and even in a locally managed tablespace.
LVL 35

Assisted Solution

johnsone earned 25 total points
ID: 21801733
Locally managed tablespaces with uniform extent sizes.

Create multiple tablespaces with different extent sizes.  This seems to becoming the standard practice.  Try to avoid the small, medium, large tablespaces.  This doesn't leave room for when the database gets larger and there is a need for extent sizes bigger that the originally defined large.

If it seems a table is growing more than the other tables in the tablespace, you can easily move it to a tablespace with larger extent size with an ALTER TABLE ... MOVE ... command.
LVL 22

Accepted Solution

DrSQL earned 200 total points
ID: 21814027
     I will add some clarification to the other part of your question.  The growth of the table and the setting of pctused and pctfree are also impacted by the volatility and access profile of the table.  Just saying that a table grows by a certain number of rows (or percentage of the total) each month isn't enough.  You also have to determine how the individual rows might grow and whether or not there are deletes.

    Oracle only stores the number of bytes necessary for the values provided when you insert a row.  It doesn't reserve space for growth (via updates) in the row, it does so in the block.  That is what the PCTFREE is for.  Oracle will reserve that portion of the block for updates that would grow any rows stored in that block.  And, the block will stay at the top of the list of available places to put data (FREELIST) until it has reached PCTFREE.  So, if you set PCTFREE too low (to a value that can never be reached), the block is always scanned for any new insert - even though it may not have enough room for a new row.  But, if you set it too high you'll waste space (not such a big deal nowadays).  If you don't have enough PCTFREE and you want to update a row (maybe you insert "stub" data and later update it to provide a record of activity), you may end up with "chaining" where a row is stored in more than one block - you can imagine what that does to I/O.

      PCTUSED is less understood by most designers.  It is the point at which a block will return to the list of available places to put data (actually moves it to the top).  If you don't have any deletes, or you never update records in a way that would make them smaller, then it doesn't really matter WHAT you set this to.  But, if you have a lot of deletes, or "shrinking" updates, then the wrong setting here could have blocks pinging on and off the free list.

    PCTINCREASE should almost always be zero.  It was an interesting idea, but it just rarely works out to be helpful.

Good luck!

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup
Suggested Courses

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question