Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2017
  • Last Modified:

How do I determine my extent size on a table.

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

3 Solutions
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.

johnsoneSenior Oracle DBACommented:
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.
     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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now