Improve company productivity with a Business Account.Sign Up


How do I determine my extent size on a table.

Posted on 2008-06-16
Medium Priority
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
LVL 48

Assisted Solution

schwertner earned 100 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 36

Assisted Solution

johnsone earned 100 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 800 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

601 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