欄位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>