RE: Calculate PCTFREE and PCTUSED

Bob

   The rule of thumb I've used in the past is to ask the developer for an idea of how volatile the data is. When I find a table experiencing significant row migration (a.k.a. chaining), I take care of those rows, then increase PCTFREE by 5 percent.

   If you are really concerned about row migration (your statement "I allow for the full length of the row"), bear in mind that the NUMBER type can increase is size slightly if more digits are stored. Also, if calculating the maximum size as 2 rows/block, be sure Oracle doesn't store 3 or 4 rows in there for you. The developers of one application we run here were particularly concerned about row migration at sites without an Oracle DBA, so they made all the data types NOT NULL and use CHAR instead of VARCHAR2.

   Two weeks ago I tried several formulas to predict the table size if I rebuilt a table. Then I rebuilt the table. Each of the formulas calculated high. But I suppose it is better for a formula to overshoot than undershoot.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Monday, April 21, 2003 3:52 PM
To: Multiple recipients of list ORACLE-L

Dennis thanks for the reply

Yes, I did research it some and I think I have a clearer understanding of how it works

The way I understand it is
You need to get the "average" row length (of actual data not total bytes)-- but lets say my data length
(sum of all columns) = 1344 bytes

Some documentation suggests to allow 6 bytes overhead per column 6 x 64 =384 + 1344 actual full length of the row Which would be 1728 bytes which is just about 1/2 of one block (based on a 4k block size)
So in this case I could get 2 rows in one block

The PCTFREE from my understanding.. Is "how much your rows will expand horizontally , within the overall row length/block (sum of all columns) Obliviously, the data cant grow larger than the length of the field. So, I don't see how the chaining could occur, particularly if I allow for the full length of the row, not "average" row length. After all the table is not created yet and I am trying to anticipate the data.

I not sure how one could guess how much rows will expand or contract If there isn't really any history per-sea particularly if there are *a lot* of columns which is my case 64 columns (no its not normalized ;-) )

I was hoping someone could refer a general formula or more enlightenment (as you've provided)

Thanks !
bob

> Bob
> The big factor on PCTFREE is your data volatility. If the
> data is never updated (static), you can reduce PCTFREE to a
> very small value. But if the data is often updated, chances
> are you will end up with row migration (usually called row
> chaining, but that is something different) unless you go with
> a larger PCTFREE.
> PCTUSED controls how quickly a block will be returned to
> the free list after some data is removed from the block.
> If you are looking for a calculation for how many blocks a
> table will take up, you can do a web search and find several.
> But I've found it isn't a very precise calculation. Today
> with disk space easier to come by and DBA time being more
> scarce, it usually isn't worth going to too much trouble.
> I looked in Kevin Loney's book Oracle9i DBA Handbook and
> he says to allow 90 bytes in each block for overhead, in
> addition to your PCTFREE.
>
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Monday, April 21, 2003 11:52 AM
> To: Multiple recipients of list ORACLE-L
>
>
> All
>
> Im looking for a straight forward calculation for
> PCTFREE and PCTUSED
>
> I can total the bytes for all columns or get the average
> length after running statistics on the table however this is
> a new (empty) table that will have say 100K rows populated
> via sqlldr and then an estimated growth of 5000 rows per mon.
>
> I like to know how to set the PCTFREE and PCTUSED manually.
> Ive read some documents but none to straightforward
>
> Eg col length x col_num * blocksize...?????
>
> TIA!
> bob
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Bob Metelsky
> INET: bmetelsky_at_cps92.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in the message BODY, include a line containing: UNSUB
> ORACLE-L (or the name of mailing list you want to be removed
> from). You may also send the HELP command for other
> information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in the message BODY, include a line containing: UNSUB
> ORACLE-L (or the name of mailing list you want to be removed
> from). You may also send the HELP command for other
> information (like subscribing).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bob Metelsky
  INET: bmetelsky_at_cps92.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).