Re: Table Sizing Estimation

From: David Fitzjarrell <oratune_at_aol.com>
Date: Fri, 10 Nov 2000 16:54:32 GMT
Message-ID: <8uh983$7i8$1@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 - 10:54:32 CST