Re: Table Sizing Estimation
Date: Fri, 10 Nov 2000 16:54:32 GMT
Message-ID: <8uh983$7i8$1_at_nnrp1.deja.com>
In our last gripping episode Mark D Powell <markp7832_at_my-deja.com>
wrote:
> In article <3A0B6D89.757F00C4_at_hket.com>,
> Rayfil Cheung <rayfilcheung_at_hket.com> wrote:
> > Hi,
> > How I estimate the table sizing when certain no. of rows are
expected.
> > Thanks.
> >
> There are several ways to do this. Here is a fairly quick formula.
>
> Abbreviations
>
> AVIL = Available space in block to hold rows
> OBS = Oracle block size
> RS = Row size
> Ovhd = Fixed plus variable block overhead
> TBR = Total blocks required
>
> Expected size = (( RS * number of rows) / AVIL ) * OBS) / K or M
> where K = 1024 and M = 1048576
>
> Figure RS as
> for varchar2 expected number of characters for column
> for number 1 + floor(num digits / 2) + 1
> for date use 7
> + 1 byte per column in row
> + 3 byte row overhead per row
>
> Figure number of bytes for block as
> pctfree = decimal value of pctfree parameter * OBS
>
> The variable area is mostly made up of 23 bytes per initran area and 2
> bytes per row for the row table entry. For 1 to 4 initrans I have
> calculated row overhead of 86 to 156 bytes so I just use a constant
for
> this value. Try 113 to start.
>
> Figure AVIL as OBS - ovhd - pctfree
>
> Total bytes = number of expected rows * RS
> TBR = Total Bytes / AVIL
> Expected Size = TBR * OBS / 1024 [for K]
>
> This is one way and it is fairly quick and works pretty well. The
> formula can be improved by adjusting the variable area size for the
> number of initrans and for the number of expected rows in the block,
> but using a constant works well for us.
>
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Maybe this package will help you:
rem "$Header: /usr/users/davidf/tuning/tables/RCS/table_size.sql,v 1.1
2000/03/24 15:56:27 davidf Exp $"
CREATE OR REPLACE PACKAGE space
AS
PROCEDURE table_size( owner_name_p VARCHAR2,-- schema of the table
table_name_p VARCHAR2,-- table name
table_rows_p NUMBER -- estimated number of rows
);
END;
/
CREATE OR REPLACE PACKAGE BODY space
AS
PROCEDURE table_size( owner_name_p VARCHAR2,-- schema of the table
table_name_p VARCHAR2,-- table name
table_rows_p NUMBER -- number of rows in table
)
AS
CURSOR columns_cur IS
SELECT column_name,
data_type,
decode
(data_length,'NUMBER',data_precision+data_scale,data_length)
FROM sys.dba_tab_columns
WHERE table_name = table_name_p
AND owner = owner_name_p;
DEBUG BOOLEAN := FALSE; -- set TRUE if you want to know what the procedure is doing
col_tmp_v NUMBER := 0; -- size of the column col_1_v NUMBER := 0; -- columns with size < 250 col_250_v NUMBER := 0; -- columns with size >= 250 col_type_v sys.dba_tab_columns.data_type%TYPE; -- column type x INTEGER := 0; t INTEGER := 0;
y INTEGER := 0;
z NUMBER := 0;
cursor_v INTEGER; -- cursor variable block_size_v INTEGER := 0; -- block size pct_free_v INTEGER := 0.10; -- pct_free of the table ignore_v INTEGER; -- for cursor err_v VARCHAR2(200); -- error number num_v INTEGER; -- error number row_size_v NUMBER(12,2); -- size of a row table_size_v NUMBER(20,2); -- size of the table vsizestmt_v VARCHAR2(2000); -- sql-clause grade_v VARCHAR2(10) := 'K'; -- grade (bytes,Kilobytes,Megabytes)colname_v sys.dba_tab_columns.column_name%TYPE; -- column_name
BEGIN
dbms_output.enable(1000000);
- PCTFREE --- select NVL(pct_free/100,0.10) into pct_free_v from sys.dba_tables WHERE table_name = table_name_p AND owner = owner_name_p;
- BLOCK_SIZE select value into block_size_v from sys.v_$parameter where name='db_block_size';
- sql-clause begins
- this clause will calculate the size of of each row in bytes
vsizestmt_v := 'SELECT AVG(';
OPEN columns_cur;
LOOP
FETCH columns_cur INTO colname_v,col_type_v,col_tmp_v;
EXIT WHEN columns_cur%NOTFOUND;
IF col_tmp_v >= 250 THEN
col_250_v := col_250_v + 1;
ELSE
col_1_v := col_1_v + 1;
end if;
col_tmp_v := 0;
IF DEBUG THEN
dbms_output.put(colname_v||' c1 '||col_1_v||'
c2 '||col_250_v);
dbms_output.new_line;
END IF;
vsizestmt_v := vsizestmt_v||'NVL(VSIZE('||colname_v||'),0)+1
+';
END LOOP;
CLOSE columns_cur;
- get rid of the last '+' vsizestmt_v := substr(vsizestmt_v,1,(length(vsizestmt_v)-1));
- end of the sql-clause
vsizestmt_v := vsizestmt_v||') AVERAGE_ROW_SIZE
FROM '||owner_name_p||'.'||table_name_p;
- execute the sql-clause cursor_v := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cursor_v,vsizestmt_v,1); DBMS_SQL.DEFINE_COLUMN(cursor_v,1,row_size_v); ignore_v := DBMS_SQL.EXECUTE(cursor_v); IF DBMS_SQL.FETCH_ROWS(cursor_v)>0 THEN DBMS_SQL.COLUMN_VALUE(cursor_v,1,row_size_v); END IF; DBMS_SQL.CLOSE_CURSOR(cursor_v);
- row size row_size_v := row_size_v + 3 + col_1_v + (3 * col_250_v);
- block row count (x) x := block_size_v/row_size_v; t := x;
- round x down to the nearest integer y := block_size_v -((block_size_v -(52+(4*x))) * pct_free_v);
IF DEBUG THEN
dbms_output.put('x:'||x||' y:'||y||' z:'||row_size_v);
dbms_output.new_line;
END IF;
- validate y >= x*z WHILE y < t * row_size_v LOOP t := t - 1; END LOOP;
- calculate the size of the table table_size_v := table_rows_p / x * block_size_v;
- if table is empty IF table_size_v is null then grade_v := '0 rows?'; table_size_v := -1; ELSE
- choose the grade
- IF table_size > 1048576 THEN table_size_v := table_size_v / 1048576; grade_v := 'M';
- ELSE IF table_size > 1024 THEN
- table_size := table_size/1024;
- grade := 'K';
- END IF; END IF;
- Show the result
dbms_output.put_line('Table Name Est. Rows Est.
Size');
dbms_output.put_line('------------------------- ------------ ----------
----');
dbms_output.put_line(rpad(table_name_p,25)||' '||
lpad(table_rows_p,12)||' '||
lpad(to_char(table_size_v,'99999990D99'),12)
||' '||grade_v);
- exceptions ------------------------------------------- --- EXCEPTION WHEN OTHERS THEN err_v := SQLERRM; num_v := SQLCODE; dbms_output.put_line(num_v); dbms_output.put_line(err_v); END; -- end of the procedure table_size END; -- end of the package size /
I use this to estimate table size based upon the number of rows expected. It does quite well with the estimates.
-- David Fitzjarrell Oracle Certified DBA Sent via Deja.com http://www.deja.com/ Before you buy.Received on Fri Nov 10 2000 - 17:54:32 CET