Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Anirban.

Asked: March 25, 2001 - 12:57 pm UTC

Last updated: March 15, 2005 - 9:17 pm UTC

Version: 8.0.5

Viewed 1000+ times

You Asked

Are there readily available guidelines to size database tables, assuming I know the growth pattern, updates vs inserts vs deletes etc?
Im am interested particularly in deriving PCTFREE and PCTUSED.
A excel like spreadsheet would be ideal, where I input some of the
known metrics and it computes the rest.

Thanks

and Tom said...

Really not possible using a spreadsheet or any sort of formula. Oh, there are formula's that try but due to the fact that:

o 999999999 takes three times the storage 10000000000000000000000
o indexes can be key compressed
o free space might not be used within a transaction but across transactions it
would be
o an IOT stores things very differently then a HEAP table.
o (any one of a thousand things)


The only way I know to accurately size a table and related structures is to load it on your system with data that is REPRESENTATIVE of the actual data you will be using. You don't have to load 100% of the data, anywhere from a fraction of 1% to 10% of the data (depending on the table size itself -- the bigger they are the less you need to sample percentage wise). Then you analyze the table and multiply. That'll give you your initial sizing.

As for pctfree and pctused -- they are a soft function of HOW you use the data -- there is no spreadsheet possible for them at all, not even a function. You set pctfree and pctused based on how the data will be manipulated. Some very very generalized rules of thumb might be:

o if you load a table and never update a row and never delete any rows the pctfree = 0 pctused = 1. pctfree = 0 because ROWS NEVER GROW, hence you want to pack the block all the way up. do not save any space for update. pctused = 1 because you really don't care what it is in this case. You never delete only add. When the block gets full it will be taken off of the free list (pctfree hit) and will never go back on (since you never delete)


o if you load a table with lots of rows at a point in time and expect that on average the rows will DOUBLE in size and you never delete you would set pctfree at say 55%. Now 55% of the block will be reserved for those updates. pctused is not relevant since once a block is half full, we want to reserve the rest of the space for updates so set pctused to 1 (block must be 99% empty to be a candidate for insertion)

o if you have no idea how the rows are going to grow and shrink over time, what effects deletes will have and so on, you would just leave then default and hope for the best.

and so on. Basically, you must take an understand of what pctfree and pctused do and see how they apply to YOUR data and the way YOU will be using your data. There are no forumlas for these.


Followup to the comment below regarding "...sometimes cause delays in projects like ...".

Let me rephrase it this way -- if you cannot come up with a small set of data that represents some percentage of the entire data set (representative set of numeric data for a field, varchars that represent how the actual data will be used and so on) at this point in time -- it does not MATTER what sizing you use, they will all be meaningless.

If you are going to a customer to tell them to buy such and such an amount of hardware and you do not know what the data is going to look like, the only thing you can do is give them the WRONG answer.

If you understand the data (a prerequisite for sizing), all you need is an Oracle instance somewhere with the appropriate block size you plan on using. Load it up with sample data and size it. You do not have to do it on their hardware, you just need to do it somewhere - and if you cannot do it, you could not size this with a formula either. That is because the only reason you could not do this is due to lack of knowledge of what the data looks like.


followup to comment 4 below

that is exactly the problem -- each DBA has their OWN spreadsheet with different ways of calculating values based on THEIR installation and their PAST experience with THEIR applications.

There is no generic solution to this. If the DBA switched jobs to a different company with a different set of applications -- they might as well through out their spreadsheets (they will have to modify them greatly at the very least).

Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 26, 2001 - 2:48 am UTC


Eric Grancher, March 28, 2001 - 5:35 pm UTC


Table Sizing

Sudip Sarkar from PricewaterHouseCoopers, March 30, 2001 - 5:00 am UTC

BEfore loading the dat into the database then to determine the sizing sometimes cause delays in projects like when the clients wants to order the h/w after the sizing is done.

reader, June 15, 2001 - 4:53 am UTC

I believe each of DBA has his own set of formula (either spreadsheet or programs), even though PCTUSED, PCTFREE are variables.

From DBA who does not use spreadsheet

Dusan, June 15, 2001 - 10:53 am UTC

I agree that the best way for table sizing is just populating of the table with sample data.
When using localy managed tablespaces (LMT) with uniform sizes, the whole issue (after identifying how 'big' the table really is) is simple - take decision to which tablespace put the table (index). To the 'small', 'medium' or 'big' tablespace. Thanks procedure show_space and LMT strategy, I calculated very precisely space needed for tables, indexes and tablespaces, and then rebuilt production database (saving a lot of GB!). The only inputs were number of rows and the real size of table or index, investigated by show_space.

From DBA who does not use spreadsheet

Dusan, June 15, 2001 - 12:14 pm UTC

I agree that the best way for table sizing is just populating of the table with sample data.
When using localy managed tablespaces (LMT) with uniform sizes, the whole issue (after identifying how 'big' the table really is) is simple - take decision to which tablespace put the table (index). To the 'small', 'medium' or 'big' tablespace. Thanks procedure show_space and LMT strategy, I calculated very precisely space needed for tables, indexes and tablespaces, and then rebuilt production database (saving a lot of GB!). The only inputs were number of rows and the real size of table or index, investigated by show_space.

Table Sizing

Jim Kirsch, August 30, 2001 - 2:56 pm UTC

Good, clear advice about the importance knowing the data before sizing.

hi folks

A reader, December 30, 2002 - 4:23 am UTC

Is there any utility or program which will populate sample data, by inspecting the tables.

Thanks

Table space

Chandra Mohan, January 14, 2004 - 11:06 am UTC

I cannot get the size of a table inside Oracle. Is there any special query is available ?

Tom Kyte
January 14, 2004 - 4:00 pm UTC

hmmm, look at user_segments

Diana, January 14, 2005 - 8:58 am UTC


Oracle 10g changes ?

Jignesh, March 15, 2005 - 3:14 pm UTC

Is there a change for Oracle 10g for calculating table sizing ?

Tom Kyte
March 15, 2005 - 9:17 pm UTC

Not from me, nope.

The tools still can guess, but if you want to know......