RE: Calculate PCTFREE and PCTUSED

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