alter table新增字段操作究竟有何影响?(上篇)

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> alter table新增字段操作究竟有何影响?(上篇)

很久以前就有个疑问,见过一些表设计时会留出几个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文件如此之长,真崩溃。。。

     

    全部的信息,以现在的功力,不能都解释清楚,但针对这个问题,通过一些关键的点,应该可以看出端倪:

    1. 10046开始记录后的第一条语句: 
  • SQL ID: fpur97hs1wpp3  
  • Plan Hash: 0  
  • LOCK TABLE "T" IN ROW EXCLUSIVE MODE NOWAIT  
  • 说明此时对T以NOWAIT方式,加了ROW EXCLUSIVE模式锁(关于ROW EXCLUSIVE,会在下篇中实验)。

    1. 接下来就是执行的新增字段的SQL语句:
  • SQL ID: dwuf634dfa8hf  
  • Plan Hash: 0  
  • alter table t add (sex varchar2(1))  
    1. 接下来就是各种查,有数据字典表的,有PLSQL,虽然不是都明白,但感觉就一个字:乱,再加一字:晕。但大部分SQL执行的时间都在x毫秒。
    1. 我们直接看关闭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$是列的数据字典表,使用了绑定变量,那这些值是什么,就成了问题的关键。

    1. 为了查看绑定变量值,从网上学了一招:

    使用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字段的一些信息。

    1. 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!

    转载声明:转载请注明出处,本技术博客是本人原创文章

    本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

    原文链接:blog.ouyangsihai.cn >> alter table新增字段操作究竟有何影响?(上篇)


     上一篇
    java.sql.SQLException—— 索引中丢失 IN或OUT 参数————x java.sql.SQLException—— 索引中丢失 IN或OUT 参数————x
    使用JDBC时,会有这么一个错误:java.sql.SQLException: 索引中丢失 IN或OUT 参数::x 如下示例中insertLog.execute();这行会抛出这个异常: span style="font-size:14
    下一篇 
    TO_DATE函数索引报错ORA-01743 TO_DATE函数索引报错ORA-01743
    开发同学有一个需求,如下这张表: CREATE TABLE TBL_EFFDATE (ROUTID NUMBER(20,0) NOT NULL, EFFDTE CHAR(7), EDDATE CHAR(7), ICID CHAR(1), F