Message-ID: <F001.005863D0.20030421125143@fatcity.com>
Date: Mon, 21 Apr 2003 12:51:43 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Bob Metelsky" <bmetelsky@cps92.com>
Subject: RE: Calculate PCTFREE and PCTUSED
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain;	charset="us-ascii"
Content-Transfer-Encoding: 8bit

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