Compute a table's rowsize
This script computes the average rowsize of an existing Oracle table to help you determine required table space requirements.
This is a utility script that computes the average rowsize of an existing Oracle table. It can help you determine how many rows can fit into a block size, so you can properly plan the space requirements for a table. It should be run in SQL*PLUS. Anyone can use this script so long as the SELECT privilege is selected on the ALL_TAB_COLUMNS table and the table being computed. You must supply the table name to be computed for the rowsize.
set serverout ON size 1000000
set ver off
ACCEPT tbnm PROMPT 'Enter name of table to be computed: '
ACCEPT tbown PROMPT 'Enter table owner: '
DEF dot = "."
spool &tbnm.lst
DECLARE
V_SIZE NUMBER;
V_TOT_SIZE NUMBER(5,2) := 0;
V_COLUMN VARCHAR2(255);
V_INIT_SIZE NUMBER;
CURSOR SLC_TAB_COL_CURSOR IS
SELECT column_name
FROM all_tab_columns
WHERE table_name=upper('&tbnm')
AND owner = upper('&tbown');
SLC_TAB_COL_REC SLC_TAB_COL_CURSOR%ROWTYPE;
CURSOR AVG_ROW_SIZE_CURSOR IS
SELECT avg(vsize(V_COLUMN))
FROM &tbown&dot&tbnm;
BEGIN
dbms_output.put_line(chr(10));
FOR SLC_TAB_COL_REC IN SLC_TAB_COL_CURSOR LOOP
V_COLUMN := SLC_TAB_COL_REC.COLUMN_NAME;
OPEN AVG_ROW_SIZE_CURSOR;
FETCH AVG_ROW_SIZE_CURSOR INTO V_SIZE;
V_TOT_SIZE := V_TOT_SIZE + V_SIZE;
CLOSE AVG_ROW_SIZE_CURSOR;
dbms_output.put_line('Col. Name: '||V_COLUMN||' '||' Avg. Size: '||V_SIZE);
END LOOP;
dbms_output.put_line(chr(10)||'Average Table Rowsize is : '||V_TOT_SIZE||' bytes');
END;
/
Reader Feedback:
Janardanam B. writes: I see a few issues with this code.
1. The total size, V_TOT_SIZE, is not incremented with the column size retrieved.
2. The second cursor, meant for finding the average size of column, just returns the size of the column name instead of the size of the column value. For example: Col. Name: SEQ_NUM Ave. Size: 7 , where 7 is the size of "SEQ_NUM" (7 characters).
Please correct me if I am wrong. I have written a procedure, based on your tip, with a solution for the first issue. But I have not found a solution for the second issue. Please let me know if you have any solution for the second issue.
CREATE OR REPLACE PROCEDURE test2205 (tbname in String, tbowner in String)
IS
V_SIZE NUMBER;
V_TOT_SIZE NUMBER := 0;
V_COLUMN VARCHAR2(255);
V_INIT_SIZE NUMBER;
CURSOR COL_SIZE_CUR IS
SELECT column_name
FROM all_tab_columns
WHERE table_name=upper(tbname)
AND owner = upper(tbowner);
COL_SIZE_REC COL_SIZE_CUR%ROWTYPE;
CURSOR AVG_COL_SIZE IS
SELECT avg(vsize(V_COLUMN))
FROM (select tbowner || '.' || tbname from dual);
BEGIN
dbms_output.put_line(chr(10));
FOR COL_SIZE_REC IN COL_SIZE_CUR LOOP
V_COLUMN := COL_SIZE_REC.COLUMN_NAME;
OPEN AVG_COL_SIZE;
FETCH AVG_COL_SIZE INTO V_SIZE;
CLOSE AVG_COL_SIZE;
dbms_output.put_line('Col. Name: '||V_COLUMN||' '||' Ave. Size: '||V_SIZE);
select V_TOT_SIZE + V_SIZE into V_TOT_SIZE
from dual;
END LOOP;
dbms_output.put_line('Average Table Rowsize is : '||V_TOT_SIZE||' bytes');
END;
/
Author's Response:
With regards to the issues being posted, here are my comments:
1. The total size, V_TOT_SIZE, is not incremented with the column size retrieved. The V_TOT_SIZE variable is increased with the average value, in bytes, of the columns being retrieved as specified in the line:
V_TOT_SIZE := V_TOT_SIZE + V_SIZE;
2. The second cursor, meant for finding the average size of column, just returns the size of the column name instead of the size of the column value.
Please test it on tables with varying record LENGTH values on a particular column. Of course, if you will test it on a column with a fixed record length, it will return the same value. Try running it on the EMP table and you will get the difference.
Thank you for your feedback.
Reader Feedback:
Beth S. writes: Why not just compute statistics on the table check the AVG_ROW_LEN in USER_TABLES/DBA_TABLES?
Steve S. writes: The idea of the tip is most welcome, but I think the reader Janardanam B. is right in his comment for the second point. In order to get the average length of a column whose name is known only at run time and not at pl/sql compilation time, some kind of dynamic sql should be used. This could be done either by using the DBMS_SQL package (for versions earlier than Oracle8i) or by using the EXECUTE IMMEDIATE statement for Oracle8i and later. It could look like this (this is just a partial code, using the author's variable names):
DECLARE
sql_stmt VARCHAR2(2000);
v_table VARCHAR2(100);
BEGIN
v_table := '&tbown&dot&tbnm'
sql_stmt := 'SELECT avg(vsize' || V_COLUMN || ')) FROM ' || V_TABLE ;
EXECUTE IMMEDIATE sql_stmt INTO v_size ;
END;
Steve S. writes: The reader (Janardanam B.) feedback on this tip is correct. I ran the author's code against my tables, and it returned the length of the column name, not the average length of the column values. The author is attempting to do a dynamic SQL query where only static SQL is allowed, so the column name is being interpreted as a character literal and not a database object name.
Using the DBMS_SQL package to do a dynamic query, as described in the Oracle8 PL/SQL Programming book published by Oracle Press, I was able to modify the author's program to work as intended:
set serverout ON size 1000000
set ver off
ACCEPT tbnm PROMPT 'Enter name of table to be computed: '
ACCEPT tbown PROMPT 'Enter table owner: '
DEF dot = "."
spool &tbnm.lst
DECLARE
V_SIZE NUMBER(8,2) := 0;
V_TOT_SIZE NUMBER(8,2) := 0;
V_COLUMN VARCHAR2(255);
V_INIT_SIZE NUMBER;
V_CURSOR_ID INTEGER;
V_DYNAMIC_SQL VARCHAR2(500);
V_DUMMY INTEGER;
CURSOR SLC_TAB_COL_CURSOR IS
SELECT column_name
FROM all_tab_columns
WHERE table_name=upper('&tbnm')
AND owner = upper('&tbown');
SLC_TAB_COL_REC SLC_TAB_COL_CURSOR%ROWTYPE;
BEGIN
dbms_output.put_line(chr(10));
FOR SLC_TAB_COL_REC IN SLC_TAB_COL_CURSOR LOOP
V_COLUMN := SLC_TAB_COL_REC.COLUMN_NAME;
-- open dynamic cursor for average column size
V_CURSOR_ID := DBMS_SQL.OPEN_CURSOR;
-- build query
V_DYNAMIC_SQL := 'SELECT NVL(AVG(VSIZE(' || V_COLUMN || ')), 0) FROM &tbown&dot&tbnm';
-- parse the query
DBMS_SQL.PARSE(V_CURSOR_ID, V_DYNAMIC_SQL, DBMS_SQL.V7);
-- define the output variable
DBMS_SQL.DEFINE_COLUMN(V_CURSOR_ID, 1, V_SIZE);
-- execute the statement
V_DUMMY := DBMS_SQL.EXECUTE(V_CURSOR_ID);
-- fetch the result row (if non-zero return code, bail out)
IF DBMS_SQL.FETCH_ROWS(V_CURSOR_ID) = 0 THEN
EXIT;
END IF;
-- retrieve the output variable
DBMS_SQL.COLUMN_VALUE(V_CURSOR_ID, 1, V_SIZE);
-- close the dynamic cursor
DBMS_SQL.CLOSE_CURSOR(V_CURSOR_ID);
V_TOT_SIZE := V_TOT_SIZE + V_SIZE;
dbms_output.put_line('Col. Name: '||V_COLUMN||' '||' Avg. Size: '||V_SIZE);
END LOOP;
dbms_output.put_line(chr(10)||'Average Table Rowsize is : '||V_TOT_SIZE||' bytes');
END;
/
Mike B. writes: Below is the original script rewritten to calculate the average column size correctly (noted as issue #2 by Janardanam B.) and some sample output. Keep in mind that if the table has user defined datatypes like the oe.customers table in the second output example, this script will not work.
===================== begin script code ==============================
set serverout ON size 1000000
set ver off
ACCEPT tbnm PROMPT 'Enter name of table to be computed: '
ACCEPT tbown PROMPT 'Enter table owner: '
DEF dot = "."
spool &tbnm.lst
DECLARE
AVG_COL_SIZE VARCHAR2(2000) ;
V_TAB_NAME VARCHAR2(128) := '&tbown&dot&tbnm';
V_SIZE NUMBER;
V_TOT_SIZE NUMBER(5,2) := 0;
V_COLUMN VARCHAR2(255);
V_INIT_SIZE NUMBER;
CURSOR SLC_TAB_COL_CURSOR IS
SELECT column_name
FROM all_tab_columns
WHERE table_name=upper('&tbnm')
AND owner = upper('&tbown');
SLC_TAB_COL_REC SLC_TAB_COL_CURSOR%ROWTYPE;
BEGIN
dbms_output.put_line(chr(10));
FOR SLC_TAB_COL_REC IN SLC_TAB_COL_CURSOR LOOP
V_COLUMN := SLC_TAB_COL_REC.COLUMN_NAME;
AVG_COL_SIZE := 'select round(avg(vsize('||V_COLUMN||')),6) from '||V_TAB_NAME ;
EXECUTE IMMEDIATE AVG_COL_SIZE INTO V_SIZE ;
V_TOT_SIZE := V_TOT_SIZE + V_SIZE;
dbms_output.put_line('Col. Name: '||rpad(V_COLUMN,32)||' '
||' Avg. Size: '||V_SIZE);
END LOOP;
dbms_output.put_line(chr(10)||'Average Table Rowsize is : '||V_TOT_SIZE||' bytes');
END;
/
--===================== END Script Code =============================
## Output 1
SQL> @get_rowsize.sql
Enter name of table to be computed: emp
Enter table owner: scott
Col. Name: EMPNO Avg. Size: 2.928571
Col. Name: ENAME Avg. Size: 5
Col. Name: JOB Avg. Size: 6.857143
Col. Name: MGR Avg. Size: 3
Col. Name: HIREDATE Avg. Size: 7
Col. Name: SAL Avg. Size: 2.428571
Col. Name: COMM Avg. Size: 1.75
Col. Name: DEPTNO Avg. Size: 2
Average Table Rowsize is : 30.97 bytes
PL/SQL procedure successfully completed.
## Output 2
SQL> desc oe.customers
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTOMER_ID NOT NULL NUMBER(6)
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(20)
CUST_ADDRESS OE.CUST_ADDRESS_TYP
PHONE_NUMBERS OE.PHONE_LIST_TYP
NLS_LANGUAGE VARCHAR2(3)
NLS_TERRITORY VARCHAR2(30)
CREDIT_LIMIT NUMBER(9,2)
CUST_EMAIL VARCHAR2(30)
ACCOUNT_MGR_ID NUMBER(6)
CUST_GEO_LOCATION MDSYS.SDO_GEOMETRY
SQL> @get_rowsize.sql
Enter name of table to be computed: customers
Enter table owner: oe
Col. Name: CUSTOMER_ID Avg. Size: 2.996865
Col. Name: CUST_FIRST_NAME Avg. Size: 5.695925
Col. Name: CUST_LAST_NAME Avg. Size: 6.523511
Col. Name: CUST_ADDRESS Avg. Size: 42.473354
DECLARE
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got OE.PHONE_LIST_TYP
ORA-06512: at line 28
Ofer H. writes: Correct me if I'm wrong, but analyzing the table return you also the average rowsize (in avg_row_len). Since most of the databases are CBO, you probably have an analyze of all tables.
Frank P. writes: The calulation for the column size should consider NULL values. If there is at least one column having no value in any record, the calculation of the total size will bring NULL.
The line
AVG_COL_SIZE := 'select round(avg(vsize('||V_COLUMN||')),6) from '||V_TAB_NAME ;
should be replaced by
AVG_COL_SIZE := 'select nvl(round(avg(vsize('||V_COLUMN||')),6),0) from '||V_TAB_NAME ;
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, PL/SQL, administrative and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions -- or help out your peers by answering them -- in our active forums.
- Best Web Links: Oracle tips, tutorials and scripts from around the Web.
Start the conversation
0 comments