很久以前就有个疑问，见过一些表设计时会留出几个reverse的字段，目的是为了以后扩展，但此时设计的字段类型、长度等都是预计的，未来是否可用，不好说，那为什么会这么做呢？可能的原因是：“我现在设定好字段，需要的时候直接用就行了，不需要新增字段的操作”。
那么，问题就是，新增字段的操作究竟有什么影响？增加表字段的时候，是否会锁表？对DML、DDL有什么影响？如果搞清楚这些，才能对上面的问题给出科学的答案。
为了证明增加字段的操作究竟做了什么，有什么影响，打算使用10046事件来看看。
SQL desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
TID NOT NULL NUMBER(38)
SQL alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL alter table t add (sex varchar2(1));
Table altered.
SQL alter session set events '10046 trace name context off';
Session altered.
使用tkprof格式化trace文件，关键的信息如下
SQL ID: fpur97hs1wpp3
Plan Hash: 0
LOCK TABLE "T" IN ROW EXCLUSIVE MODE NOWAIT
SQL ID: dwuf634dfa8hf
Plan Hash: 0
alter table t add (sex varchar2(1))
SQL ID: 3nkd3g3ju5ph1
Plan Hash: 2853959010
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1,
spare2
from
obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null
SQL ID: b1wc53ddd6h3p
Plan Hash: 1637390370
select audit$,options
from
procedure$ where obj#=:1
SQL ID: 3ktacv9r56b51
Plan Hash: 4184428695
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
nvl(property,0),subname,type#,d_attrs
from
dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
SQL ID: 8swypbbr0m372
Plan Hash: 893970548
select order#,columns,types
from
access$ where d_obj#=:1
SQL ID: 3k0c6241uw582
Plan Hash: 1964643588
select actionsize
from
trigger$ where obj# = :1
SQL ID: 07pcqtmt58zv9
Plan Hash: 1964643588
select action#
from
trigger$ where obj# = :1
SQL ID: 6mhctgagpvvhp
Plan Hash: 1964643588
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,
whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,
refprtname,rowid,actionlineno,trignameline,trignamecol,trignamecolofs,
actioncolno
from
trigger$ where obj# =:1
SQL ID: f91p5x1pzsmu1
Plan Hash: 1482114444
select tc.type#,tc.intcol#,tc.position#,c.type#, c.length,c.scale,
c.precision#,c.charsetid,c.charsetform, decode(bitand(c.property,8388608),
8388608, c.spare3, 0)
from
triggercol$ tc,col$ c, trigger$ tr where tc.obj#=:1 and tc.intcol#=c.intcol#
and tr.obj# = tc.obj# and (bitand(tr.property,32) != 32 or bitand(tc.type#,
20) = 20) and ( c.obj#=:2 and (bitand(tc.type#, 1024) = :3 or tc.type# =
0) or c.obj#=:4 and bitand(tc.type#, 1024) = :5) union select type#,
intcol#,position#,69,0,0,0,0,0,0 from triggercol$ where obj#=:6 and intcol#=
1001 union select tc.type#,tc.intcol#,tc.position#,121,0,0,0,0,0,0 from
triggercol$ tc,trigger$ tr where tr.obj# = tc.obj# and bitand(tr.property,
32) = 32 and tc.obj# = :7 and bitand(tc.type#,20) != 20
SQL ID: 1a8n1zgb7m90w
Plan Hash: 1457651150
select user#,password,datats#,tempts#,type#,defrole,resource$, ptime,exptime,
ltime, astatus, lcount, decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',
defschclass),spare1,spare4,ext_username,spare2
from
user$ where name=:1
SQL ID: ga9j9xk5cy9s0
Plan Hash: 1697022209
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece
from
idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#
SQL ID: cvn54b7yz0s8u
Plan Hash: 3246118364
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece
from
idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#
SQL ID: c6awqs517jpj0
Plan Hash: 1319326155
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece
from
idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece#
SQL ID: 39m4sx9k63ba2
Plan Hash: 2317816222
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece
from
idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#
...
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 212 0.07 0.09 2 27 0 0
Execute 573 0.13 0.16 16 34 39 9
Fetch 1269 0.05 0.09 105 2654 0 1973
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2054 0.26 0.35 123 2715 39 1982
Misses in library cache during parse: 81
Misses in library cache during execute: 65
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 123 0.00 0.07
asynch descriptor resize 1 0.00 0.00
Disk file operations I/O 1 0.00 0.00
23 user SQL statements in session.
502 internal SQL statements in session.
525 SQL statements in session.
********************************************************************************
Trace file: bisal_ora_3470.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
23 user SQL statements in trace file.
502 internal SQL statements in trace file.
525 SQL statements in trace file.
83 unique SQL statements in trace file.
11858 lines in trace file.
63 elapsed seconds in trace file.
总计将近1000行，就是一个alter table增加字段的操作，trace文件如此之长，真崩溃。。。
全部的信息，以现在的功力，不能都解释清楚，但针对这个问题，通过一些关键的点，应该可以看出端倪：
- 10046开始记录后的第一条语句：
SQL ID: fpur97hs1wpp3
Plan Hash: 0
LOCK TABLE "T" IN ROW EXCLUSIVE MODE NOWAIT
说明此时对T以NOWAIT方式，加了ROW EXCLUSIVE模式锁(关于ROW EXCLUSIVE，会在下篇中实验)。
- 接下来就是执行的新增字段的SQL语句：
SQL ID: dwuf634dfa8hf
Plan Hash: 0
alter table t add (sex varchar2(1))
接下来就是各种查，有数据字典表的，有PLSQL，虽然不是都明白，但感觉就一个字：乱，再加一字：晕。但大部分SQL执行的时间都在x毫秒。
我们直接看关闭10046事件之前的最后一句：
SQL ID: 6vqvn8ya0xybh
Plan Hash: 3870945217
update col$ set intcol#=:3,segcol#=:4,type#=:5,length=:6,precision#=decode(:5,
182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),scale=decode(:5,2,
decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,
183,:8,231,:8,null),null$=:9,fixedstorage=:10,segcollength=:11,col#=:12,
property=:13,charsetid=:14,charsetform=:15,spare1=:16,spare2=:17,spare3=:18,
deflength=decode(:19,0,null,:19),default$=:20
where
obj#=:1 and name=:2
执行的是col$表的更新语句。从表名看，col$是列的数据字典表，使用了绑定变量，那这些值是什么，就成了问题的关键。
- 为了查看绑定变量值，从网上学了一招：
使用v$sql_bind_capture可以查看仍在内存中的SQL绑定变量值，找了其中一些，有的已经查不到了，但上面10046前的最后一句SQL使用的绑定变量值如下：
SQL col sql_id format a20
SQL col name format a20
SQL col datatype_string format a14
SQL col value_string format a20
SQL select sql_id, name, datatype_string, last_captured, value_string
2 from v$sql_bind_capture where sql_id = '6vqvn8ya0xybh'
3 order by last_captured, position;
SQL_ID NAME DATATYPE_STRIN LAST_CAPT VALUE_STRING
-------------------- ---------- -------------- --------- --------------------
6vqvn8ya0xybh :1 NUMBER 30-APR-15 74592
6vqvn8ya0xybh :2 VARCHAR2(32) 30-APR-15 SEX
6vqvn8ya0xybh :3 NUMBER
6vqvn8ya0xybh :4 NUMBER
6vqvn8ya0xybh :5 NUMBER
6vqvn8ya0xybh :6 NUMBER
6vqvn8ya0xybh :5 NUMBER
6vqvn8ya0xybh :7 NUMBER
6vqvn8ya0xybh :7 NUMBER
6vqvn8ya0xybh :7 NUMBER
6vqvn8ya0xybh :7 NUMBER
6vqvn8ya0xybh :5 NUMBER
6vqvn8ya0xybh :8 NUMBER
6vqvn8ya0xybh :8 NUMBER
6vqvn8ya0xybh :8 NUMBER
6vqvn8ya0xybh :8 NUMBER
6vqvn8ya0xybh :8 NUMBER
6vqvn8ya0xybh :8 NUMBER
6vqvn8ya0xybh :8 NUMBER
6vqvn8ya0xybh :8 NUMBER
6vqvn8ya0xybh :8 NUMBER
6vqvn8ya0xybh :9 NUMBER
6vqvn8ya0xybh :10 NUMBER
6vqvn8ya0xybh :11 NUMBER
6vqvn8ya0xybh :12 NUMBER
6vqvn8ya0xybh :13 NUMBER
6vqvn8ya0xybh :14 NUMBER
6vqvn8ya0xybh :15 NUMBER
6vqvn8ya0xybh :16 NUMBER
6vqvn8ya0xybh :17 NUMBER
6vqvn8ya0xybh :18 NUMBER
6vqvn8ya0xybh :19 NUMBER
6vqvn8ya0xybh :19 NUMBER
6vqvn8ya0xybh :20 VARCHAR2(32)
看来可以解释许多问题了，
(1) update col$语句中一共有20个绑定变量，上面SQL显示的绑定变量值，同样是20个，说明是对应的。
(2) update col$的where条件是编号为1和2的绑定变量值，这里显示的是74592和SEX。
(3) 再来看看这个74592是什么，从上面SQL的条件obj#=:1，猜测是一个对象，
SQL col object_name for a5
SQL select object_name, object_id from dba_objects
2 where object_id = 74592;
OBJEC OBJECT_ID
----- ----------
T 74592
74592就是这张表T，SEX是新增字段名。
于是，可以猜测，之前已经将SEX字段加入了相应的数据字典表，最后一句就是更新col$中T表SEX字段的一些信息。
- trace文件的最后列出了所有递归调用语句的消耗统计：
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 212 0.07 0.09 2 27 0 0
Execute 573 0.13 0.16 16 34 39 9
Fetch 1269 0.05 0.09 105 2654 0 1973
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2054 0.26 0.35 123 2715 39 1982
Misses in library cache during parse: 81
Misses in library cache during execute: 65
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 123 0.00 0.07
asynch descriptor resize 1 0.00 0.00
Disk file operations I/O 1 0.00 0.00
23 user SQL statements in session.
502 internal SQL statements in session.
525 SQL statements in session.
********************************************************************************
Trace file: bisal_ora_3470.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
23 user SQL statements in trace file.
502 internal SQL statements in trace file.
525 SQL statements in trace file.
83 unique SQL statements in trace file.
11858 lines in trace file.
63 elapsed seconds in trace file.
可以看出，为了一个alter table新增字段的操作，总共执行了几百次的内部SQL，大部分是通过索引方式扫描，执行的时间是100多毫秒，很快，因此感觉不到，但实际Oracle自己做了这么多后台操作，感叹他的强大，一个简单的新增字段操作，就有如此复杂的实现，但性能上基本让你感觉不到，佩服得五体投地。
总结：
本篇文章主要说明了alter table新增字段操作，Oracle究竟做了什么，至于这操作有什么影响，其实就是ROW EXCLUSIVE会有什么影响，下篇文章会进行一些实验来说明ROW EXCLUSIVE的作用和影响。
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer，已开源在github ，欢迎star！
