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
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 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 35

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

719 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