SQL Script

Show Average Row Length

Updated : 06-June-2000
Version : 1.3

Description

This script prompts for a table and then calculates the average row length. This is needed to size a table accurately. The usual method would be to run this against a small test database for each table you want to size and then use the calculation to size the production table. The 3 constants (UB1,UB4,SB2)are platform specific so double check then by querying table v$type_size on your platform.

If the tables statistics are available (from the ANALYZE) command, these statistics are used instead. This allows the script to be used on existing production databases.

This script also reports the average row length for indexes associated with the table.

NOTE : This script will not work for a table with columns defined as LONG

Parameters

& tab - The table name
&owner - The Table Owner

SQL Source

set serveroutput on size 100000
set verify off

ACCEPT own PROMPT 'Enter value for owner (RETURN for ALL) > '
ACCEPT tab PROMPT 'Enter value for table name (RETURN for ALL) > '

DECLARE

c1 integer;
rows_processed integer;
colsize number DEFAULT 0;
avgrow number DEFAULT 0;
ind_avgrow number DEFAULT 0;
l_sql varchar2(2000);

UB1 CONSTANT number :=  1; /* Get from v$type_size for your platform */
UB4 CONSTANT number :=  4; /* Get from v$type_size for your platform */
SB2 CONSTANT number :=  2; /* Get from v$type_size for your platform */

CURSOR get_tabs IS SELECT owner,table_name,avg_row_len FROM all_tables 
		   WHERE table_name like UPPER('%&tab%')
		   AND OWNER NOT IN ('SYS','SYSTEM')
		   AND OWNER LIKE UPPER('%&own%')
 		   ORDER BY table_name;

CURSOR get_ind (p_tab_name all_indexes.table_name%TYPE
			,p_owner all_indexes.owner%TYPE) IS
		SELECT * 
		FROM all_indexes
		WHERE table_name = p_tab_name
		AND owner = p_owner;

CURSOR get_ind_cols (p_index all_ind_columns.index_name%TYPE
			,p_tab_name all_ind_columns.table_name%TYPE
			,p_owner all_ind_columns.table_owner%TYPE) IS
		SELECT * 
		FROM all_ind_columns
		WHERE table_name = p_tab_name
		AND table_owner = p_owner
		AND index_name = p_index;

CURSOR get_cols (p_tab_name all_tables.table_name%TYPE
		,p_owner all_tab_columns.owner%TYPE) IS 
		SELECT * 
                FROM all_tab_columns 
                WHERE table_name = p_tab_name
		AND owner = p_owner;

BEGIN

FOR tab_rec IN get_tabs LOOP

-- Do the INDEXES First
FOR ind_rec IN get_ind(tab_rec.table_name,tab_rec.owner) 
LOOP

  ind_avgrow := 0;

   FOR get_ind_col IN get_ind_cols (ind_rec.index_name
					,tab_rec.table_name,tab_rec.owner)
   LOOP

     c1 := dbms_sql.open_cursor;
     l_sql := 'select round(avg(nvl(vsize(' || get_ind_col.column_name ||
               '),0)))' || ' from ' || 
                   get_ind_col.index_owner||'.'||get_ind_col.table_name
                   || ' where rownum < 1000000';
     dbms_sql.parse(c1,l_sql,dbms_sql.v7);
     dbms_sql.define_column(c1, 1, colsize);

     rows_processed := dbms_sql.execute_and_fetch (c1);

     dbms_sql.column_value(c1, 1, colsize);
     dbms_sql.close_cursor (c1);

     ind_avgrow := ind_avgrow + colsize;

    END LOOP;

     dbms_output.put_line ('Index '||ind_rec.index_name
                            ||' Avg Row Size = '||CEIL(to_char(ind_avgrow)) );
END LOOP;

avgrow := UB1*3; /* row header */

IF tab_rec.avg_row_len IS NULL THEN

   -- Try and calculate the average row length using VSIZE 

   FOR get_col_rec IN get_cols (tab_rec.table_name,tab_rec.owner)
   LOOP

     c1 := dbms_sql.open_cursor;
     l_sql := 'select round(avg(nvl(vsize(' || get_col_rec.column_name ||
               '),0)))' || ' from ' || 
                   get_col_rec.owner||'.'||get_col_rec.table_name;
     dbms_sql.parse(c1,l_sql,dbms_sql.v7);
     dbms_sql.define_column(c1, 1, colsize);

     rows_processed := dbms_sql.execute_and_fetch (c1);

     dbms_sql.column_value(c1, 1, colsize);

--Uncomment this to display the column vsize
--dbms_output.put_line (' Column '||get_col_rec.column_name
--                              ||' Col Size = '||to_char(colsize));

     dbms_sql.close_cursor (c1);
   
     avgrow := avgrow + colsize + SB2;

   END LOOP;

 ELSE
  
   avgrow := tab_rec.avg_row_len;

 END IF;

   dbms_output.put_line ('Table '||tab_rec.table_name
                                ||' Avg Row Size = '||CEIL(to_char(avgrow)));

END LOOP;

END;
/