字段avg_row_len of dba_tables是如何被计算的！

困扰了很久的一个问题！[@more@]

SQL> create table tt(id int , name varchar2(20)) tablespace users;

表已创建。

SQL> insert into tt values(1,'a');

已创建 1 行。

SQL> insert into tt values(2,'aa');

已创建 1 行。

SQL> insert into tt values(2,'aaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaaaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaaaaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaaaaaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaaaaaaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaaaaaaaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaaaaaaaaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaaaaaaaaaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaaaaaaaaaaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaaaaaaaaaaaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaaaaaaaaaaaaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaaaaaaaaaaaaaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaaaaaaaaaaaaaaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaaaaaaaaaaaaaaaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaaaaaaaaaaaaaaaaa');

已创建 1 行。

SQL> insert into tt values(2,'aaaaaaaaaaaaaaaaaaaa');

已创建 1 行。
SQL> commit;

提交完成。

SQL> select * from tt;

ID NAME
---------- --------------------
1 a
2 aa
2 aaa
2 aaaa
2 aaaaa
2 aaaaaa
2 aaaaaaa
2 aaaaaaaa
2 aaaaaaaaa
2 aaaaaaaaaa
2 aaaaaaaaaaa

ID NAME
---------- --------------------
2 aaaaaaaaaaaa
2 aaaaaaaaaaaaa
2 aaaaaaaaaaaaaa
2 aaaaaaaaaaaaaaa
2 aaaaaaaaaaaaaaaa
2 aaaaaaaaaaaaaaaaa
2 aaaaaaaaaaaaaaaaaa
2 aaaaaaaaaaaaaaaaaaa
2 aaaaaaaaaaaaaaaaaaaa

已选择20行。

SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_numb
er(rowid) block from tt ;

FNO BLOCK
---------- ----------
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605

FNO BLOCK
---------- ----------
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605
4 22605

已选择20行。
SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_b
lock_number(rowid) block from tt ;

FNO BLOCK
---------- ----------
4 22605

SQL> analyze table tt compute statistics;

表已分析。

SQL> select avg_row_len from dba_tables where table_name='TT' and owner='SYS';

AVG_ROW_LEN
-----------
17

SQL>
--===============================
block_row_dump:
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] 61
tab 0, row 1, @0x1f87
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 2] 61 61
tab 0, row 2, @0x1f7d
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 61 61 61
tab 0, row 3, @0x1f72
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 4] 61 61 61 61
tab 0, row 4, @0x1f66
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 5] 61 61 61 61 61
tab 0, row 5, @0x1f59
tl: 13 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 6] 61 61 61 61 61 61
tab 0, row 6, @0x1f4b
tl: 14 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 7] 61 61 61 61 61 61 61
tab 0, row 7, @0x1f3c
tl: 15 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 8] 61 61 61 61 61 61 61 61
tab 0, row 8, @0x1f2c
tl: 16 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 9] 61 61 61 61 61 61 61 61 61
tab 0, row 9, @0x1f1b
tl: 17 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [10] 61 61 61 61 61 61 61 61 61 61
tab 0, row 10, @0x1f09
tl: 18 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [11] 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 11, @0x1ef6
tl: 19 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [12] 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 12, @0x1ee2
tl: 20 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [13] 61 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 13, @0x1ecd
tl: 21 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [14] 61 61 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 14, @0x1eb7
tl: 22 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [15] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 15, @0x1ea0
tl: 23 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [16] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 16, @0x1e88
tl: 24 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [17] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 17, @0x1e6f
tl: 25 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [18] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 18, @0x1e55
tl: 26 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [19] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 19, @0x1e3a
tl: 27 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [20] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 22605 maxblk 22605
--=================================
--上面的dump信息中我们发现每行都有一个tl，这个tl我猜测是total length,不知道是否准确，
也就是说tl代表的是row length，根据这个意思，可以计算：
SQL> select trunc((8+27)*10/20) from dual;

TRUNC((8+27)*10/20)
-------------------
17

下面是通过我们对row里面数据存储的理解来计算的：
A row fully contained in one block has at least 3 bytes of row header. After the row header information, each row contains column length and data. The column length requires 1 byte for columns that store 250 bytes or less, or 3 bytes for columns that store more than 250 bytes, and precedes the column data. Space required for column data depends on the datatype. If the datatype of a column is variable length, then the space required to hold a value can grow and shrink with updates to the data.

To conserve space, a null in a column only stores the column length (zero). Oracle does not store data for the null column. Also, for trailing null columns, Oracle does not even store the column length.

SQL> select vsize(id)+1+vsize(name)+1+3 row_len from tt;

ROW_LEN
----------
8
9
10
11
12
13
14
15
16
17
18

ROW_LEN
----------
19
20
21
22
23
24
25
26
27

已选择20行。
计算出来的8～27不正是我们dump出来的tl嘛！
--=================================
通过dbms_stats搜集统计信息和analyze计算统计信息有点差别，
dbms_stats搜集统计信息计算avg_row_len时不包括row header占用的3byte：

SQL> exec dbms_stats.gather_table_stats('SYS','TT');

PL/SQL 过程已成功完成。
SQL> select avg_row_len from dba_tables where table_name='TT' and owner='SYS';

AVG_ROW_LEN
-----------
14
SQL> select vsize(id)+1+vsize(name)+1 from tt;

VSIZE(ID)+1+VSIZE(NAME)+1
-------------------------
5
6
7
8
9
10
11
12
13
14
15

VSIZE(ID)+1+VSIZE(NAME)+1
-------------------------
16
17
18
19
20
21
22
23
24

已选择20行。
SQL> select trunc((5+24)*10/20) from dual;

TRUNC((5+24)*10/20)
-------------------
14

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1023466/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1023466/