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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

830 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