Hi Tom, I'm turning to You, because I do not found any answer for my question:
Question: Why I do not see the right AVG_ROW_LENGTH in the DBA_TABLES Dictionary Table? Why is it not correct? How will it be calculated in the background?
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 23 11:18:05 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
SQL> CREATE TABLE BDA.BDA_TEST
2 TABLESPACE BDA_DATA --> ASSM
3 AS SELECT owner, object_name, subobject_name, object_id, created, timestamp, status
4 FROM ALL_OBJECTS;
Table created.
SQL> SELECT COUNT(*) FROM BDA.BDA_TEST;
COUNT(*)
----------
63796
SQL> ANALYZE TABLE BDA.BDA_TEST COMPUTE STATISTICS;
Table analyzed.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'BDA', TABNAME => 'BDA_TEST', ESTIMATE_PERCENT => 100);
PL/SQL procedure successfully completed.
SQL> SELECT MIN(rowlength) AS MIN_ROW_LEN
2 , MAX(rowlength) AS MAX_ROW_LEN
3 , SUM(rowlength) AS SUM_ROW_LEN
4 , ROUND(AVG(rowlength), 2) AS AVG_ROW_LEN
5 , ROUND(SUM(rowlength)/COUNT(1), 2) AS AVG_ROW_LEN_1
6 FROM (SELECT NVL(VSIZE(owner ), 0)
7 + NVL(VSIZE(object_name ), 0)
8 + NVL(VSIZE(subobject_name), 0)
9 + NVL(VSIZE(object_id ), 0)
10 + NVL(VSIZE(created ), 0)
11 + NVL(VSIZE(timestamp ), 0)
12 + NVL(VSIZE(status ), 0) AS ROWLENGTH
13 FROM BDA.BDA_TEST
14 )
15 ;
MIN_ROW_LEN MAX_ROW_LEN SUM_ROW_LEN AVG_ROW_LEN AVG_ROW_LEN_1
----------- ----------- ----------- ----------- -------------
39 100 4205195 65.92 65.92
SQL> SELECT table_name, num_rows, blocks, empty_blocks, chain_cnt, avg_row_len, sample_size
2 FROM DBA_TABLES
3 WHERE table_name LIKE 'BDA_TEST';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE
----------- -------- ------ ------------ --------- ----------- -----------
BDA_TEST 63796 705 63 0 72 63796
SQL> Could You tell me please why I get 72 for the AVG_ROW_LEN, while I calculate 66 ?
Thank You in advance!
you are missing null byte indicators and length bytes in your calculation.
ops$tkyte%ORA10GR2> create table t ( x varchar2(30), y varchar2(30) );
Table created.
ops$tkyte%ORA10GR2> insert into t values ( '12345', '12' );
1 row created.
ops$tkyte%ORA10GR2> select vsize(x), vsize(y) from t;
VSIZE(X) VSIZE(Y)
---------- ----------
5 2
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select avg_row_len from user_tables where table_name = 'T';
AVG_ROW_LEN
-----------
9
obviously - the average row length is 7 right? wrong, each of the varchar2's have a leading byte length field - adding 2 bytes.
In your example, only one field - created - is of 'known length' (7 bytes for the date). The rest ALL have varying length byte indicators to tell us how long they are.
The rest are "six". 66+6 = 72.