Re: table sizing

From: Chris Hamilton <chrish_at_cmprime.att.com>
Date: 1997/04/07
Message-ID: <01bc435a$5530a420$2d89b687@chrish-pc.cis.att.com>

> Mark McWilliams wrote:

> >
> > Does anyone have an SQL stmt, stored procedure, etc to calculate
> > the estimated size of tables and indexes. I know the steps are
> > in the DBA guide, but I don't want to recreate the wheel.

Here is a script that I wrote to do table size estimations.

rem *****************************************************************
rem Name:     calc_tab.sql
rem Author:   Chris Hamilton
rem Date:     10-Dec-92.  Revised CHH, 23-May-94.  Added prompt for
rem           Block Size, changed ttitle to include table name, added
rem           NVL support for block size.
rem           20-Dec-1997, widened some columns.
rem Purpose:  This script (based on formula in DBA guide) calculates the
rem           space allotment required for an Oracle table.
rem NOTE:     Average Length refers to defined length of columns.
rem NOTE:     Critical parameter in this script is blk_size.  Change this
rem           for your blocksize when prompted at runtime.  If you don't
rem           know the blocksize, check the db_block_size parameter in your
rem           init.ora file, or run the following query:
rem           select name, value
rem           from   sys.v_$parameter
rem           where  name = 'db_block_size'

rem Usage: sqlplus -s un/pw @calc_tab.sql

rem *****************************************************************

set verify off;
set linesize 80;
set pagesize 58;
set space 1;
set trimspool on;

prompt -------------------------------------------------;

prompt Calculate Table Space Required Program;

prompt -------------------------------------------------;

accept table_name char prompt 'Table Name: '; accept n_rows number prompt '# of Rows: '; accept n_cols number prompt '# of Columns: ';

accept avg_len number prompt 'Average Column Length:  ';
accept pct_free number prompt 'PCTFREE value:  ';
accept blk_size number prompt 'Block Size in Bytes:  ';

accept filename char prompt 'Filename to spool to: ';

prompt -------------------------------------------------;

prompt Working...;

set verify off;

column blocks_req format 999,999,990 heading "# of Blocks Req"; column bytes_req format 999,999,999,999,990 heading "# of Bytes Req"; column table_name format a24 heading "Table Name"; column n_rows format 999,999,990 heading "# of Rows"; column n_cols format 990 heading "# of|Cols";

column avg_len format 99,990.0 heading "Avg Len";
column blk_size format 9990 heading "Block|Size";
column pct_free format 90 heading "Pct|Free";

column table_name new_value tname noprint; column today new_value report_date noprint;

ttitle skip 1 report_date right "Page" sql.pno skip 2 -

       center "Estimated Space Required to Create a Table" -
       skip 2 center "Table Name:  " tname skip 2;

spool &&filename;

select initcap(sysdate) today,

       upper('&&table_name') table_name,
       &&n_rows n_rows,
       &&n_cols n_cols,
       &&avg_len avg_len,
       nvl(&&blk_size,2048) blk_size,
       &&pct_free pct_free,
       (round
       (
       (&&n_rows * (5 + &&n_cols * (1 + &&avg_len)))
       /
       ((nvl(&&blk_size,2048) - 90) * (1 - &&pct_free/100))
       )
       ) blocks_req,
       (
       round(
       ((&&n_rows * (5 + &&n_cols * (1 + &avg_len)))
       /
       ((nvl(&&blk_size,2048) - 90) * (1 - &&pct_free/100))))
       * nvl(&&blk_size,2048)
       ) bytes_req

from dual;

spool off;

pause Press <Return> to continue...;

Here is a script to do index size estimations . . .

rem *****************************************************************
rem Name:     calc_ndx.sql
rem Author:   Chris Hamilton
rem Date:     10-Dec-92.  Revised CHH, 05-May-94.  Added prompt for
rem           Block Size, changed ttitle to include table and column names.
rem           20-Feb-97, widened some columns.
rem Purpose:  This script (based on formula in DBA guide) calculates the
rem           space allotment required for an Oracle index.
rem NOTE:     # of Columns and Average Length refers to defined length
rem           of Indexed columns only.
rem NOTE:     Critical parameter in this script is blk_size.  Change this
rem           for your blocksize when prompted at runtime.  If you don't
rem           know the blocksize, check the db_block_size parameter in your
rem           init.ora file, or run the following query:
rem           select name, value
rem           from   sys.v_$parameter
rem           where  name = 'db_block_size'

rem Usage: sqlplus -s un/pw @calc_ndx.sql

rem *****************************************************************

set verify off;
set linesize 80;
set pagesize 58;
set space 1;
set feedback off;
set trimspool on;

prompt -------------------------------------------------;

prompt Calculate Index Space Required Program;

prompt -------------------------------------------------;

accept table_name char prompt 'Table Name: '; accept column_name char prompt 'Column Name(s): '; accept n_rows number prompt '# of Rows: '; accept n_cols number prompt '# of Columns: ';

accept avg_len number prompt 'Average Column Length:  ';
accept pct_free number prompt 'PCTFREE value:  ';
accept blk_size number prompt 'Block Size in Bytes:  ';

accept filename char prompt 'Filename to spool to: ';

prompt -------------------------------------------------;

prompt Working...;

column blocks_req format 999,999,999,990 heading "# of Blocks Req"; column bytes_req format 999,999,999,999,990 heading "# of Bytes Req"; column n_rows format 999,999,990 heading "# of Rows"; column n_cols format 990 heading "# of|Cols";

column avg_len format 999,990.0 heading "Avg Len";
column blk_size format 9990 heading "Block|Size";
column pct_free format 90 heading "Pct|Free";

column table_name format a24 heading "Indexed Table"; column table_name new_value tname noprint; column column_name new_value cname noprint; column today new_value report_date noprint;

break on today on table_name on column_name on report;