Could NOT follow the Calculation too . Can any explain ?
-Original Message-
From: Tim Sawmiller [SMTP:[EMAIL PROTECTED]]
Sent: Friday, March 23, 2001 6:35 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Tuning Matter == Parameter PCT_USED in a Table
Ok, I'll
I think We can use average column a + average column (estimating), because
of VARCHAR2 in our table, then we determine can determine how many records
inside our database.
I think we can not ignore the overhead bit inside our tablespace, my Guru
said leave 1 row space between PCT_USED and PCT_FREE
Ok, I'll admit my ignorance. What the heck does "1 row" mean? It can't mean the
row length, or you'll end up with a negative PCT_USED.
[EMAIL PROTECTED] 03/22/01 09:35PM
Hi Bambang,
Analyze the table and you'll find the average row length in
USER_TABLES.AVG_ROW_LEN. Subtract 90 bytes
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
Hi Ron,
Let's take a example:
SQL desc emp
NameNull?TypeBYTES
-- -- --
EMPNO NOT NULL NUMBER(4) 21
ENAME VARCHAR2(10)10
JOB
Harsh,
I thought number would only generally take 4 bytes (I've checked a couple
using vsize on 7.3.4) and varchar will only take what it needs.
Cheers
Iain
-Original Message-
Sent: 23 March 2001 16:32
To: Multiple recipients of list ORACLE-L
Hi Ron,
Let's take a example:
SQL desc
Are all the columns completely filled? ie all the "ename" are 10 chars long
and all the "job" fields are 9 chars. When you use the varchar2 field it
only stores the length _used_.
Jim
-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Friday, March 23,
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.
Hi Bambang,
Analyze the table and you'll find the average row length in
USER_TABLES.AVG_ROW_LEN. Subtract 90 bytes from the block size before
expressing that as a percentage and round it up. Also, the formula
should be PCT_USED = 100 - (PCT_FREE + 1 row). There are scripts on the
Ixora web site
