oracle 大表新增列 慢_Oracle DDL性能改进-大表新增列行为分析

生产过程中常常会遇到对表需要在线增加列，尤其是新增带缺省值的非空列，例如：

ALTER TABLE .. ADD COLUMN ( ... NOT NULL DEFAULT ... )

在Oracle 11g以前，执行这条DDL会涉及以下步骤：

在数据字典中中增加新列定义：列类型、长度、NOT NULL及DEFAULT等；

更新表上所有行，使用新列的缺省值增加新列的值。

对百万级以上的大表，该DDL可能会耗费较长时间及系统资源，同时也会对该表进行长时间加锁，影响业务的正常使用。

Oracle 11g起引入了新的隐藏参数：_ADD_COL_OPTIM_ENABLED。如果数据库参数：COMPATIBLE>=11时，则_ADD_COL_OPTIM_ENABLED缺省为TRUE。

_ADD_COL_OPTIM_ENABLED值为True时，新增带缺省值非空列的DDL会仅仅将新列添加到表定义中，但是不会更新实际的表行记录。 内部会在数据字典中存储一个标志，对于没有列值的行均会返回DEFAULT值。 对于大表，这会节省大量资源和加快执行速度。 在数据库内部，访问该表的任何代码路径都会查询数据字典设置，为该列返回正确的数据。该 参数的设置仅影响新的“ ADD COLUMN”命令。 一旦使用此种方式增加新的列，则数据字典中的标志将指示该特定列已被优化。可以通过以下查询获取已优化的列：

col object_name format a30

set lines 100

select owner, object_name, name

from dba_objects, col$

where bitand(col$.PROPERTY,1073741824)=1073741824

and object_id=obj#;

下面通过几个简单案例来分析下列值在数据块中的存储变化。

准备

测试数据库：11.2.0.4

compatible=11.2.0.4

_add_col_optim_enabled=true

SQL> create table test.t_obj(c1 varchar2(1));

Table created.

SQL> insert into test.t_obj values('1');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test.t_obj;

C

-

1

SQL> select dbms_rowid.rowid_object(rowid) obj#,

dbms_rowid.rowid_relative_fno(rowid) rfile#,

dbms_rowid.rowid_block_number(rowid) block#,

dbms_rowid.rowid_row_number(rowid) row#

from test.t_obj; 2 3 4 5

OBJ# RFILE# BLOCK# ROW#

---------- ---------- ---------- ----------

70623 4 134 0

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 134;

System altered.

查看trace文件：

Block header dump: 0x01000086

Object id on Block? Y

seg/obj: 0x113df csc: 0x00.95330 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x1000080 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.011.000010a8 0x00c028a4.00cb.26 --U- 1 fsc 0x0000.00095331

0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

bdba: 0x01000086

data_block_dump,data header at 0x7f96a71b3264

===============

tsiz: 0x1f98

hsiz: 0x14

pbl: 0x7f96a71b3264

76543210

flag=--------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0x1f93

avsp=0x1f7b

tosp=0x1f7b

0xe:pti[0] nrow=1 offs=0

0x12:pri[0] offs=0x1f93

block_row_dump:

tab 0, row 0, @0x1f93

tl: 5 fb: --H-FL-- lb: 0x1 cc: 1

col 0: [ 1] 31

end_of_block_dump

End dump data blocks tsn: 4 file#: 4 minblk 134 maxblk 134

案例一：新增带缺省值的非空列

SQL> alter table test.t_obj add c2 varchar2(1) default 'a' not null;

Table altered.

SQL> col binarydefval format a10

select ec.* from sys.ecol$ ec,sys.obj$ o where ec.tabobj#=o.dataobj#;

SQL>

TABOBJ# COLNUM BINARYDEFV

---------- ---------- ----------

70623 2 61

SQL> select c1,c2,dbms_rowid.rowid_object(rowid) obj#,

dbms_rowid.rowid_relative_fno(rowid) rfile#,

dbms_rowid.rowid_block_number(rowid) block#,

dbms_rowid.rowid_row_number(rowid) row#

from test.t_obj; 2 3 4 5

C C OBJ# RFILE# BLOCK# ROW#

- - ---------- ---------- ---------- ----------

1 a 70623 4 134 0

SQL> col object_name format a30

set lines 100

select owner, object_name, name

from dba_objects, col$

where bitand(col$.PROPERTY,1073741824)=1073741824

and object_id=obj#;

OWNER OBJECT_NAME NAME

------------------------------ ------------------------------ ------------------------------

TEST T_OBJ C2

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 134;

System altered.

查看trace文件：

Block header dump: 0x01000086

Object id on Block? Y

seg/obj: 0x113df csc: 0x00.95330 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x1000080 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.011.000010a8 0x00c028a4.00cb.26 --U- 1 fsc 0x0000.00095331

0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

bdba: 0x01000086

data_block_dump,data header at 0x7f96a71b3264

===============

tsiz: 0x1f98

hsiz: 0x14

pbl: 0x7f96a71b3264

76543210

flag=--------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0x1f93

avsp=0x1f7b

tosp=0x1f7b

0xe:pti[0] nrow=1 offs=0

0x12:pri[0] offs=0x1f93

block_row_dump:

tab 0, row 0, @0x1f93

tl: 5 fb: --H-FL-- lb: 0x1 cc: 1

col 0: [ 1] 31

end_of_block_dump

查询返回了期望的列值，但在数据块实际没有存储(根据Oracle内部机制，因为是NULL，所以未在数据块中存储)。

案例二：修改非空列的default

在案例一的基础上继续

SQL> alter table test modify (c2 default 'b');

Table altered.

SQL> select ec.* from sys.ecol$ ec,sys.obj$ o where ec.tabobj#=o.dataobj#;

TABOBJ# COLNUM BINARYDEFV

---------- ---------- ----------

70623 2 61

SQL> select column_name,data_default from dba_tab_columns where owner='TEST';

COLUMN_NAME DATA_DEFAULT

------------------------------ ---------------

C1

C2 'b'

SQL> insert into test.t_obj(c1) values('2');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test.t_obj;

C C

- -

1 a

2 b

SQL> select c1,c2,dbms_rowid.rowid_object(rowid) obj#,

dbms_rowid.rowid_relative_fno(rowid) rfile#,

dbms_rowid.rowid_block_number(rowid) block#,

dbms_rowid.rowid_row_number(rowid) row#

from test.t_obj; 2 3 4 5

C C OBJ# RFILE# BLOCK# ROW#

- - ---------- ---------- ---------- ----------

1 a 70623 4 134 0

2 b 70623 4 134 1

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 134;

System altered.

Block header dump: 0x01000086

Object id on Block? Y

seg/obj: 0x113df csc: 0x00.95330 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x1000080 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.011.000010a8 0x00c028a4.00cb.26 --U- 1 fsc 0x0000.00095331

0x02 0x000a.01b.0000109b 0x00c028aa.00cb.03 --U- 1 fsc 0x0000.0009558d

bdba: 0x01000086

data_block_dump,data header at 0x7f96a71b3264

===============

tsiz: 0x1f98

hsiz: 0x16

pbl: 0x7f96a71b3264

76543210

flag=--------

ntab=1

nrow=2

frre=-1

fsbo=0x16

fseo=0x1f8c

avsp=0x1f70

tosp=0x1f70

0xe:pti[0] nrow=2 offs=0

0x12:pri[0] offs=0x1f93

0x14:pri[1] offs=0x1f8c

block_row_dump:

tab 0, row 0, @0x1f93

tl: 5 fb: --H-FL-- lb: 0x1 cc: 1

col 0: [ 1] 31

tab 0, row 1, @0x1f8c

tl: 7 fb: --H-FL-- lb: 0x2 cc: 2

col 0: [ 1] 32

col 1: [ 1] 62

end_of_block_dump

新插入的行，非空列使用了修改后的default值，已有行继续使用原有的default值。

案例三：将非空列去除非空属性

在案例二的基础上继续

SQL> alter table test.t_obj modify c2 null;

Table altered.

SQL> select ec.* from sys.ecol$ ec,sys.obj$ o where ec.tabobj#=o.dataobj#;

no rows selected

SQL> select column_name,data_default from dba_tab_columns where owner='TEST';

COLUMN_NAME DATA_DEFAULT

------------------------------ ---------------

C1

C2 'b'

SQL> select c1,c2,dbms_rowid.rowid_object(rowid) obj#,

dbms_rowid.rowid_relative_fno(rowid) rfile#,

dbms_rowid.rowid_block_number(rowid) block#,

dbms_rowid.rowid_row_number(rowid) row#

from test.t_obj; 2 3 4 5

C C OBJ# RFILE# BLOCK# ROW#

- - ---------- ---------- ---------- ----------

1 a 70623 4 134 0

2 b 70623 4 134 1

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 134;

System altered.

Block header dump: 0x01000086

Object id on Block? Y

seg/obj: 0x113df csc: 0x00.95643 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x1000080 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.004.000010a3 0x00c028ab.00cb.04 --U- 2 fsc 0x0000.0009564a

0x02 0x000a.01b.0000109b 0x00c028aa.00cb.03 C--- 0 scn 0x0000.0009558d

bdba: 0x01000086

data_block_dump,data header at 0x7f96a71b3264

===============

tsiz: 0x1f98

hsiz: 0x16

pbl: 0x7f96a71b3264

76543210

flag=--------

ntab=1

nrow=2

frre=-1

fsbo=0x16

fseo=0x1f85

avsp=0x1f70

tosp=0x1f70

0xe:pti[0] nrow=2 offs=0

0x12:pri[0] offs=0x1f85

0x14:pri[1] offs=0x1f8c

block_row_dump:

tab 0, row 0, @0x1f85

tl: 7 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 1] 31

col 1: [ 1] 61

tab 0, row 1, @0x1f8c

tl: 7 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 1] 32

col 1: [ 1] 62

end_of_block_dump

此时可以看到数据块中的列值发生了变化，原有的NULL更新为具体值。对于大表来说，这个操作将较为耗时。

案例四：继续增加带缺省值的非空列和不带缺省值的空列

在案例三的基础上继续

SQL> alter table test.t_obj add c3 varchar2(1) default 'c' not null;

Table altered.

SQL> select c1,c2,c3,dbms_rowid.rowid_object(rowid) obj#,

dbms_rowid.rowid_relative_fno(rowid) rfile#,

dbms_rowid.rowid_block_number(rowid) block#,

dbms_rowid.rowid_row_number(rowid) row#

from test.t_obj; 2 3 4 5

C C C OBJ# RFILE# BLOCK# ROW#

- - - ---------- ---------- ---------- ----------

1 a c 70623 4 134 0

2 b c 70623 4 134 1

SQL> select ec.* from sys.ecol$ ec,sys.obj$ o where ec.tabobj#=o.dataobj#;

TABOBJ# COLNUM BINARYDEFV

---------- ---------- ----------

70623 3 63

SQL> alter system checkpoint;

System altered.

SQL> alter table test.t_obj add c4 varchar2(1);

Table altered.

SQL> insert into test.t_obj(c1,c4) values('3','3');

1 row created.

SQL> commit;

Commit complete.

SQL> select c1,c2,c3,c4,dbms_rowid.rowid_object(rowid) obj#,

dbms_rowid.rowid_relative_fno(rowid) rfile#,

dbms_rowid.rowid_block_number(rowid) block#,

dbms_rowid.rowid_row_number(rowid) row#

from test.t_obj; 2 3 4 5

C C C C OBJ# RFILE# BLOCK# ROW#

- - - - ---------- ---------- ---------- ----------

1 a c 70623 4 134 0

2 b c 70623 4 134 1

3 b c 3 70623 4 134 2

Block header dump: 0x01000086

Object id on Block? Y

seg/obj: 0x113df csc: 0x00.95643 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x1000080 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.004.000010a3 0x00c028ab.00cb.04 --U- 2 fsc 0x0000.0009564a

0x02 0x000a.01a.000010a8 0x00c028ac.00cb.1a --U- 1 fsc 0x0000.000957eb

bdba: 0x01000086

data_block_dump,data header at 0x7f96a71b3264

===============

tsiz: 0x1f98

hsiz: 0x18

pbl: 0x7f96a71b3264

76543210

flag=--------

ntab=1

nrow=3

frre=-1

fsbo=0x18

fseo=0x1f7a

avsp=0x1f63

tosp=0x1f63

0xe:pti[0] nrow=3 offs=0

0x12:pri[0] offs=0x1f85

0x14:pri[1] offs=0x1f8c

0x16:pri[2] offs=0x1f7a

block_row_dump:

tab 0, row 0, @0x1f85

tl: 7 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 1] 31

col 1: [ 1] 61

tab 0, row 1, @0x1f8c

tl: 7 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 1] 32

col 1: [ 1] 62

tab 0, row 2, @0x1f7a

tl: 11 fb: --H-FL-- lb: 0x2 cc: 4

col 0: [ 1] 33

col 1: [ 1] 62

col 2: [ 1] 63

col 3: [ 1] 33

end_of_block_dump

案例五：alter table move的影响

在案例四基础上继续：

SQL> alter table test.t_obj move;

Table altered.

SQL> alter system checkpoint;

System altered.

SQL> select c1,c2,c3,c4,dbms_rowid.rowid_object(rowid) obj#,

dbms_rowid.rowid_relative_fno(rowid) rfile#,

dbms_rowid.rowid_block_number(rowid) block#,

dbms_rowid.rowid_row_number(rowid) row#

from test.t_obj; 2 3 4 5

C C C C OBJ# RFILE# BLOCK# ROW#

- - - - ---------- ---------- ---------- ----------

1 a c 70624 4 139 0

2 b c 70624 4 139 1

3 b c 3 70624 4 139 2

SQL> alter system dump datafile 4 block 139;

System altered.

Block header dump: 0x0100008b

Object id on Block? Y

seg/obj: 0x113e0 csc: 0x00.9594b itc: 3 flg: E typ: 1 - DATA

brn: 0 bdba: 0x1000088 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.020.000010ad 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

bdba: 0x0100008b

data_block_dump,data header at 0x7f96a71b327c

===============

tsiz: 0x1f80

hsiz: 0x18

pbl: 0x7f96a71b327c

76543210

flag=--------

ntab=1

nrow=3

frre=-1

fsbo=0x18

fseo=0x1f63

avsp=0x1f4b

tosp=0x1f4b

0xe:pti[0] nrow=3 offs=0

0x12:pri[0] offs=0x1f77

0x14:pri[1] offs=0x1f6e

0x16:pri[2] offs=0x1f63

block_row_dump:

tab 0, row 0, @0x1f77

tl: 9 fb: --H-FL-- lb: 0x0 cc: 3

col 0: [ 1] 31

col 1: [ 1] 61

col 2: [ 1] 63

tab 0, row 1, @0x1f6e

tl: 9 fb: --H-FL-- lb: 0x0 cc: 3

col 0: [ 1] 32

col 1: [ 1] 62

col 2: [ 1] 63

tab 0, row 2, @0x1f63

tl: 11 fb: --H-FL-- lb: 0x0 cc: 4

col 0: [ 1] 33

col 1: [ 1] 62

col 2: [ 1] 63

col 3: [ 1] 33

end_of_block_dump

End dump data blocks tsn: 4 file#: 4 minblk 139 maxblk 139

结论

_ADD_COL_OPTIM_ENABLED=true生效后，可以实现大表的快速新增带缺省值的非空列，对于生产环境较有意义。

使用该特性增加列，需注意后期一些操作的影响，例如：not null改为null、alter table move等。

该参数优化仅对新增带缺省值的非空列有效，对带缺省值的空列无效。

Oracle 12c又引入了_add_nullable_column_with_default_optim参数，可以对新增带缺省值的空列也有效。