RE: Tuning Matter ==> Parameter PCT

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Fri, 23 Mar 2001 12:28:41 -0800
Message-ID: <F001.002D68FA.20010323114049@fatcity.com>

Harsh,
The columns size listed in the formula was for the maximum size of the number column. The remaining columns are fixed in size. The size of a row is determined by 3 factors:

1. Row header 3 bytes
2. column data varying on size requirements and datatype
3. length byte for each column.

Using the maximum for each column will give you the 142 bytes but using the actual values for the numeric field in the calculation produces: total=3+50+8=61 byte max in each row of data. The size between the average and the max is normal. The only way to have the max=avg is to use CHAR not VARCHAR.
It will also waste space and at the same time greatly reduce the change of chained rows.
I thank you for pointing out the lack of the wording (maximum) in the number sizing. I have updated my table size calculations sheet. Thanks again.
ROR mª¿ªm

>>> HARSHA_at_Amdocs.com 03/23/01 11:31AM >>> Hi Ron,
Let's take a example:

SQL> desc emp

 Name                    Null?    Type                BYTES
 ---------------------- -------- ------              ------     
 EMPNO                  NOT NULL NUMBER(4)               21
 ENAME                           VARCHAR2(10)            10
 JOB                             VARCHAR2(9)              9
 MGR                             NUMBER(4)               21
 HIREDATE                        DATE                     7
 SAL                             NUMBER(7,2)             21 
 COMM                            NUMBER(7,2)             21
 DEPTNO                          NUMBER(2)               21

TOTAL=3+(SUM OF COLUMN SIZES)+(# OF COLUMNS < 250)+(3* # OF COLUMNS > 250) So according to ur formula

TOTAL=3+131+8=142 BYTES Is this OK 'coz after analyzing the table with COMPUTE I got AVG_ROW_LEN 40.

Can there be such a HUGE difference ?

Pl. comment.

Thanks,
- Harsh

-----Original Message-----

Sent: Friday, March 23, 2001 3:16 PM
To: Multiple recipients of list ORACLE-L

max(row size) is the sum of all of the columns sizes in the table plus overhead.
The following is the column sizes and the sum formula to calculate the size of a row in a data block.

NUMBER =21
CHAR = COLUMN SIZE
VARCHAR = COLUMN SIZE
DATE = 7 TOTAL=3+(SUM OF COLUMN SIZES)+(# OF COLUMNS < 250)+(3* # OF COLUMNS > 250) This is part of the calculations used to determine the storage needed for a table.

OR: you could analyze you table and use the average row size is you need a general calculation of actual usage.

ROR mª¿ªm

>>> Bambang.Setiawan_at_Sidola.com 03/22/01 08:45PM >>> Dear Listers,

I've download a presentation talking about Calculating PCT_USED . the formula is quite simple :
PCT_USED + PCT_FREE < 100
PCT_USED = PCT_FREE + 1 row size

but I have no idea to calculate the size of 1 row in a table .

is there anyone who knows to calculate it ? or any alternative formula to calculate PCT_USED ?

Thanks a lot in advance : )

=bambang=

<> Bambang Setiawan <>

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Bambang Setiawan
INET: Bambang.Setiawan_at_Sidola.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Ron Rogers
INET: RROGERS_at_galottery.org

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

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

Author: Harsh Agrawal
INET: HARSHA_at_Amdocs.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

RE: Tuning Matter ==> Parameter PCT_USED in a Table