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#

SQL ID: 3g7sxtj9d6zd3
Plan Hash: 742841275
select privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 and grantee#=:2 group by privilege#,nvl(col#,0)

eclare
TYPE attrs_cur IS REF CURSOR;
m_cur attrs_cur;
m_event varchar2(512);
m_user varchar2(512);
m_owner varchar2(512);
m_user1 varchar2(512);
m_type varchar2(512);
m_stmt varchar2(512);
m_name varchar2(5120);
m_column varchar2(5120);
m_cnt NUMBER;
m_stmt1 varchar2(512);
m_var varchar2(512);
m_o_stmt VARCHAR2(5120);
PRAGMA AUTONOMOUS_TRANSACTION;
begin
m_stmt:='select sys.dbms_standard.dictionary_obj_type from dual';
execute immediate m_stmt into m_type;
if(not (m_type='TABLE' or m_type='TRIGGER' or m_type='USER'))
then
return;
end if;
m_stmt:='select sys.dbms_standard.sysevent from dual';
execute immediate m_stmt into m_event;
m_stmt:='select SYS_CONTEXT(''USERENV'',''SESSION_USER'') from dual';
execute immediate m_stmt into m_user;
m_stmt:='select SYS_CONTEXT(''USERENV'',''CURRENT_USER'') from dual';
execute immediate m_stmt into m_user1;
m_stmt:='select sys.dbms_standard.dictionary_obj_owner from dual';
execute immediate m_stmt into m_owner;
m_stmt:='select sys.dbms_standard.dictionary_obj_name from dual';
execute immediate m_stmt into m_name;
m_stmt:='select sdo_geor_def.getSqlText from dual';
execute immediate m_stmt into m_o_stmt;
if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='ALTER')
then
m_stmt:='select column_name from dba_tab_columns where owner=:1 and table_name=:2';
open m_cur for m_stmt using m_owner,m_name;
loop
fetch m_cur into m_column;
exit when m_cur%NOTFOUND;
m_stmt:='select sdo_geor_def.isDropColumn(:1) from dual';
execute immediate m_stmt into m_stmt1 using SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_column);
if (trim(m_stmt1)='TRUE')
then
m_stmt:='begin sdo_geor_def.doAlterDropColumn(:1,:2,:3); end;';
execute immediate m_stmt using SYS.DBMS_ASSERT.SCHEMA_NAME(m_owner),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_name),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_column);
end if;
end loop;
end if;
if (m_event='DROP' and m_type='USER')
then
m_stmt:='insert into sdo_geor_ddl__table$$ values (1)';
EXECUTE IMMEDIATE m_stmt;
commit;
end if;
if (m_event='DROP' and m_type='TABLE')
then
m_stmt:='select count(*) from sdo_geor_sysdata_table where sdo_owner=:1 and RDT_TABLE_NAME=:2';
EXECUTE IMMEDIATE m_stmt into m_cnt using m_owner,m_name;
if(m_cnt!=0)
then
m_stmt:='select count(*) from sdo_geor_ddl__table$$';
EXECUTE IMMEDIATE m_stmt into m_cnt;
if((m_cnt=0)and (m_user!='SYS' and m_user!='SYSTEM' and m_user!='MDSYS'and m_owner!='MDSYS' and m_owner!='SYS'))
then
m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391, ''The referenced raster data table(RDT) cannot be dropped.'')';
execute immediate m_stmt;
end if;
end if;
m_stmt:='insert into sdo_geor_ddl__table$$ values (2)';
EXECUTE IMMEDIATE m_stmt;
commit;
end if;
if(m_user='SYS' or m_user='SYSTEM' or m_user='MDSYS'
or m_owner='MDSYS' or m_owner='SYS')
then
return;
end if;
if (m_event='RENAME' and m_type='TABLE')
then
m_stmt:='select count(*) from sdo_geor_sysdata_table where sdo_owner=:1 and RDT_TABLE_NAME=:2';
EXECUTE IMMEDIATE m_stmt into m_cnt using m_owner,m_name;
if(m_cnt!=0)
then
m_stmt:='select count(*) from sdo_geor_ddl__table$$';
EXECUTE IMMEDIATE m_stmt into m_cnt;
if(m_cnt=0)
then
m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391, ''The referenced raster data table(RDT) cannot be renamed directly.'')';
execute immediate m_stmt;
end if;
end if;
end if;
if (m_type='TRIGGER' and m_event='DROP')
then
m_stmt:='select REGEXP_SUBSTR(:1,''GRDMLTR_.+'',1,1,''i'') from dual';
EXECUTE IMMEDIATE m_stmt into m_var using m_name;
if(m_var is null)
then
return;
end if;
m_stmt:='select count(*) from sdo_geor_ddl__table$$';
EXECUTE IMMEDIATE m_stmt into m_cnt;
if(m_cnt=0)
then
m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391, ''GeoRaster DML triggers cannot be dropped.'')';
execute immediate m_stmt;
end if;
end if;
if (m_type='TRIGGER' and m_event='ALTER')
then
m_o_stmt:=upper(trim(m_o_stmt));
if(instr(m_o_stmt,' COMPILE ')>0 or instr(m_o_stmt,' ENABLE ')>0
or substr(m_o_stmt,length(m_o_stmt)-8,8)=' COMPILE' or substr(m_o_stmt,length(m_o_stmt)-7,7)=' ENABLE' )
then
return;
end if;
m_stmt:='select REGEXP_SUBSTR(:1,''GRDMLTR_.+'',1,1,''i'') from dual';
EXECUTE IMMEDIATE m_stmt into m_var using m_name;
if(m_var is null)
then
return;
end if;
m_stmt:='select count(*) from sdo_geor_ddl__table$$';
EXECUTE IMMEDIATE m_stmt into m_cnt;
if(m_cnt=0)
then
m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391, ''GeoRaster DML triggers cannot be altered.'')';
execute immediate m_stmt;
end if;
end if;
if (m_type='TRIGGER' and m_event='CREATE')
then
m_stmt:='select REGEXP_SUBSTR(:1,''GRDMLTR_.+'',1,1,''i'') from dual';
EXECUTE IMMEDIATE m_stmt into m_var using m_name;
if(m_var is null)
then
return;
end if;
m_stmt:='select count(*) from sdo_geor_ddl__table$$';
EXECUTE IMMEDIATE m_stmt into m_cnt;
if(m_cnt=0)
then
m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391, ''GeoRaster reserved names cannot be used to create regular triggers.'')';
execute immediate m_stmt;
end if;
end if;
Exception
when others then
if(sqlcode=-13391)
then
m_stmt:=sqlerrm;
m_stmt:=substr(m_stmt,11);
m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391,'''||m_stmt||''')';
execute immediate m_stmt;
end if;
end;

SQL ID: bcv9qynmu1nv9
Plan Hash: 1388734953
select sys.dbms_standard.dictionary_obj_type
from
dual

SQL ID: dcstr36r0vz0d
Plan Hash: 3798950322
select procedure#,procedurename,properties,itypeobj#
from
procedureinfo$ where obj#=:1 order by procedurename desc, overload# desc

SQL ID: 32hbap2vtmf53
Plan Hash: 3421168214
select position#,sequence#,level#,argument,type#,charsetid,charsetform,
properties,nvl(length, 0), nvl(precision#, 0),nvl(scale, 0),nvl(radix, 0),
type_owner,type_name,type_subname,type_linkname,pls_type
from
argument$ where obj#=:1 and procedure#=:2 order by sequence# desc

SQL ID: 8wutkrpf8j81q
Plan Hash: 1011998884
select max(procedure#)
from
procedurec$ where obj#=:1

SQL ID: 2xgubd6ayhyb1
Plan Hash: 3418045132
select max(procedure#)
from
procedureplsql$ where obj#=:1

SQL ID: 5km6gkvuh3n43
Plan Hash: 2075413643
select max(procedure#)
from
procedurejava$ where obj#=:1

SQL ID: 971vpk0tp7ahm
Plan Hash: 726556434
select procedure#,entrypoint#
from
procedurec$ where obj#=:1 order by procedure#

SQL ID: bgjhtnqhr5u9h
Plan Hash: 4040384485
select procedure#,entrypoint#
from
procedureplsql$ where obj#=:1 order by procedure#

SQL ID: 5j4c2v06qdhqq
Plan Hash: 423935871
select procedure#,ownerlength,classlength,methodlength,siglength, flagslength,
cookiesize
from
procedurejava$ where obj#=:1 order by procedure#

SQL ID: 83cq1aqjw5gmg
Plan Hash: 0
select ownername,classname,methodname,signature,flags
from
procedurejava$ where obj#=:1 and procedure#=:2 order by procedure#

SQL ID: 12kw3xcxv1qpg
Plan Hash: 1388734953
select sys.dbms_standard.sysevent
from
dual

SQL ID: d7y4tdacc7f3j
Plan Hash: 1388734953
select SYS_CONTEXT('USERENV','SESSION_USER')
from
dual

SQL ID: 721d7993vjur9
Plan Hash: 1388734953
select SYS_CONTEXT('USERENV','CURRENT_USER')
from
dual

SQL ID: ar9nmtmd28460
Plan Hash: 1388734953
select sys.dbms_standard.dictionary_obj_owner
from
dual

SQL ID: 4a3ccstvk0ssw
Plan Hash: 1388734953
select sys.dbms_standard.dictionary_obj_name
from
dual

SQL ID: cjk1ffy5kmm5s
Plan Hash: 1964104430
select obj#
from
oid$ where user#=:1 and oid$=:2

SQL ID: bcg7084jc4um6
Plan Hash: 1388734953
select sdo_geor_def.getSqlText
from
dual

SQL ID: 32hbap2vtmf53
Plan Hash: 0
select position#,sequence#,level#,argument,type#,charsetid,charsetform,
properties,nvl(length, 0), nvl(precision#, 0),nvl(scale, 0),nvl(radix, 0),
type_owner,type_name,type_subname,type_linkname,pls_type
from
argument$ where obj#=:1 and procedure#=:2 order by sequence# desc

SQL ID: 1mjd9xp80vuqa
Plan Hash: 3023518864
select node,owner,name
from
syn$ where obj#=:1

SQL ID: 1mkmz2c6nr80d
Plan Hash: 4171642546
select column_name
from
dba_tab_columns where owner=:1 and table_name=:2

SQL ID: g3wrkmxkxzhf2
Plan Hash: 749386351
select cols,audit$,textlength,intcols,property,flags,rowid
from
view$ where obj#=:1

SQL ID: 83taa7kaw59c1
Plan Hash: 3765558045
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
nvl(spare3,0)
from
col$ where obj#=:1 order by intcol#

SQL ID: grwydz59pu6mc
Plan Hash: 3684871272
select text
from
view$ where rowid=:1

SQL ID: 6aq34nj2zb2n7
Plan Hash: 2874733959
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#

SQL ID: 2q93zsrvbdw48
Plan Hash: 2874733959
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by
grantee#

SQL ID: 7nuw4xwrnuwxq
Plan Hash: 1720483994
select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#,
nvl(typidcol#, 0)
from
coltype$ where obj#=:1 order by intcol# desc

SQL ID: 9rfqm06xmuwu0
Plan Hash: 832500465
select intcol#, toid, version#, intcols, intcol#s, flags, synobj#
from
subcoltype$ where obj#=:1 order by intcol# asc

SQL ID: f3g84j69n0tjh
Plan Hash: 2335623859
select col#,intcol#,ntab#
from
ntab$ where obj#=:1 order by intcol# asc

SQL ID: 6qz82dptj0qr7
Plan Hash: 2819763574
select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk,
l.pctversion$, l.flags, l.property, l.retention, l.freepools
from
lob$ l where l.obj# = :1 order by l.intcol# asc

SQL ID: 9g485acn2n30m
Plan Hash: 2544153582
select col#,intcol#,reftyp,stabid,expctoid
from
refcon$ where obj#=:1 order by intcol# asc

SQL ID: 32bhha21dkv0v
Plan Hash: 3765558045
select col#,intcol#,charsetid,charsetform
from
col$ where obj#=:1 order by intcol# asc

SQL ID: 0fr8zhn4ymu3v
Plan Hash: 1231101765
select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, elemnum
from
opqtype$ where obj# = :1 order by intcol# asc

SQL ID: 3w4qs0tbpmxr6
Plan Hash: 1224215794
select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3
from
cdef$ where robj#=:1

SQL ID: gx4mv66pvj3xz
Plan Hash: 1932954096
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3
from
cdef$ where obj#=:1

SQL ID: 53saa2zkr6wc3
Plan Hash: 3954488388
select intcol#,nvl(pos#,0),col#,nvl(spare1,0)
from
ccol$ where con#=:1

SQL ID: db78fxqxwxt7r
Plan Hash: 3312420081
select /*+ rule */ bucket, endpoint, col#, epvalue
from
histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket

SQL ID: 8w5dxxmd9z3kt
Plan Hash: 1388734953
select sdo_geor_def.isDropColumn(:1)
from
dual

SQL ID: dadv22a1xru1y
Plan Hash: 1000871750
delete from compression$
where
obj#=:1

SQL ID: 1gu8t96d0bdmu
Plan Hash: 2035254952
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,
nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,
t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,
t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),
nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),
nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,
ts.logicalread
from
tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)

SQL ID: 7ng34ruy5awxq
Plan Hash: 3992920156
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,
i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,
i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,
ist.logicalread
from
ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4)))
valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where
i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#

SQL ID: 5n1fs4m2n2y0r
Plan Hash: 299250003
select pos#,intcol#,col#,spare1,bo#,spare2,spare3
from
icol$ where obj#=:1

SQL ID: 87gaftwrm2h68
Plan Hash: 1218588913
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname
from
obj$ o where o.obj#=:1

SQL ID: gsfnqdfcvy33q
Plan Hash: 2453887050
delete from superobj$
where
subobj# = :1

SQL ID: 3kywng531fcxu
Plan Hash: 2667651180
delete from tab_stats$
where
obj#=:1

SQL ID: b5cr4hhndmbuf
Plan Hash: 2918346288
update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=
decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),
audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,
rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,
analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,
null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,
flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,
spare6=:35
where
obj#=:1

SQL ID: 60uw2vh6q9vn2
Plan Hash: 0
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,
null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,
charsetid,charsetform,spare1,spare2,spare3)
values
(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,
null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,
180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12)
,:13,:14,:15,:16,:17,:18,:19,:20)

SQL ID: 4yyb4104skrwj
Plan Hash: 2683643009
update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,
dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14
where
owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname
is null and subname is null

SQL ID: dbcjnkpkvgy5w
Plan Hash: 511615611
update col$ set name=: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 intcol#=:2

SQL ID: by04k8p2ms8dd
Plan Hash: 1314400858
delete from idl_ub1$
where
obj#=:1

SQL ID: dn6xac8g73tmu
Plan Hash: 2807568952
delete from idl_char$
where
obj#=:1

SQL ID: dshhzm1vwgcpu
Plan Hash: 2238151678
delete from idl_ub2$
where
obj#=:1

SQL ID: fhguu20nbk7b3
Plan Hash: 1017615457
delete from idl_sb4$
where
obj#=:1

SQL ID: 1vpham7m95msd
Plan Hash: 2087301174
delete from error$
where
obj#=:1

SQL ID: 74anujtt8zw4h
Plan Hash: 3014793611
select o.owner#, o.name, o.namespace, o.obj#, d.d_timestamp,
nvl(d.property,0), o.type#, o.subname, d.d_attrs
from
dependency$ d, obj$ o where d.p_obj#=:1 and (d.p_timestamp=nvl(:2,
d.p_timestamp) or d.property=2) and o.owner#=nvl(:3,o.owner#) and
d.d_obj#=o.obj# order by o.obj#

SQL ID: 2faxux5uhbaaq
Plan Hash: 0
update dependency$ set p_timestamp=:1, p_obj#=:2
where
d_obj#=:3 and p_obj#=:4

SQL ID: 0muahtjw95f68
Plan Hash: 0
update dependency$ set d_reason = :1
where
d_obj# = :2 and p_obj# = :3

SQL ID: 1qub197bt5axw
Plan Hash: 0
update dependency$ set d_attrs = :1
where
d_obj# = :2 and p_obj# = :3

declare
TYPE attrs_cur IS REF CURSOR;
m_cur attrs_cur;
m_event varchar2(512);
m_user varchar2(512);
m_owner varchar2(512);
m_user1 varchar2(512);
m_type varchar2(512);
m_name varchar2(5120);
m_column varchar2(5120);
m_cnt NUMBER;
m_stmt varchar2(512);
m_ret varchar2(3000);
m_ret1 varchar2(512);
m_o_stmt VARCHAR2(5120);
begin
m_stmt:='select sys.dbms_standard.dictionary_obj_type from dual';
execute immediate m_stmt into m_type;
if(not (m_type='TABLE' or m_type='TRIGGER' or m_type='USER'))
then
return;
end if;
m_stmt:='select sys.dbms_standard.sysevent from dual';
execute immediate m_stmt into m_event;
m_stmt:='select SYS_CONTEXT(''USERENV'',''SESSION_USER'') from dual';
execute immediate m_stmt into m_user;
m_stmt:='select sys.dbms_standard.login_user from dual';
execute immediate m_stmt into m_user1;
m_stmt:='select sys.dbms_standard.dictionary_obj_owner from dual';
execute immediate m_stmt into m_owner;
m_stmt:='select sys.dbms_standard.dictionary_obj_name from dual';
execute immediate m_stmt into m_name;
if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='CREATE')
then
m_stmt:='select sdo_geor_def.listAllGeoRasterFieldsStr(:1,:2) from dual';
execute immediate m_stmt into m_ret using SYS.DBMS_ASSERT.SCHEMA_NAME(m_owner),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_name);
m_ret:=trim(m_ret);
while (length(m_ret)!=0) loop
if (instr(m_ret,' $$__## ')!=0)
then
m_ret1:=trim(substr(m_ret,1,instr(m_ret,' $$__## ')-1));
m_ret:=trim(substr(m_ret,instr(m_ret,' $$__## ')+8));
else
m_ret1:=trim(m_ret);
m_ret:='';
end if;
m_stmt:='begin SDO_GEOR_UTL.createDMLTrigger(:1,:2); end;';
execute immediate m_stmt using m_owner||'.'||m_name,SYS.DBMS_ASSERT.QUALIFIED_SQL_NAME(m_ret1);
end loop;
return;
end if;
if (m_name!='MDSYS' and m_type='USER' and m_event='DROP')
then
m_stmt:='call sdo_geor_def.doDropUserAndTable()';
execute immediate m_stmt;
end if;
if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='DROP')
then
m_stmt:='call sdo_geor_def.doDropUserAndTable()';
execute immediate m_stmt;
end if;
if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='TRUNCATE')
then
m_stmt:='call sdo_geor_def.doTruncateTable()';
execute immediate m_stmt;
end if;
if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='ALTER')
then
m_stmt:='call sdo_geor_def.doAlterRenameTable()';
execute immediate m_stmt;
end if;
if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='RENAME')
then
m_stmt:='call sdo_geor_def.doRenameTable()';
execute immediate m_stmt;
end if;
if (m_event='DROP' and m_type='TABLE')
then
m_stmt:='delete from sdo_geor_ddl__table$$ where id=2';
EXECUTE IMMEDIATE m_stmt;
end if;
if (m_type='USER' and m_event='DROP')
then
m_stmt:='delete from sdo_geor_ddl__table$$';
EXECUTE IMMEDIATE m_stmt;
end if;
Exception
when others then
if(sqlcode=-13391)
then
m_stmt:=sqlerrm;
m_stmt:=substr(m_stmt,11);
m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391,'''||m_stmt||''')';
execute immediate m_stmt;
end if;
end;

SQL ID: 7dwyqv9sawkyb
Plan Hash: 1388734953
select sys.dbms_standard.login_user
from
dual

SQL ID: bwyvhzgc956hw
Plan Hash: 0
call sdo_geor_def.doAlterRenameTable()

SQL ID: b94mm2v4pt594
Plan Hash: 1388734953
select REGEXP_SUBSTR(:1,'\s+rename\s+to\s+',1,1,'i')
from
dual

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

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增加欄位的操作，trace檔案如此之長，真崩潰。。。

全部的資訊，以現在的功力，不能都解釋清楚，但針對這個問題，透過一些關鍵的點，應該可以看出端倪：

1. 10046開始記錄後的第一條語句：

SQL ID: fpur97hs1wpp3
Plan Hash: 0
LOCK TABLE "T" IN ROW EXCLUSIVE MODE NOWAIT

說明此時對T以NOWAIT方式，加了ROW EXCLUSIVE模式鎖(關於ROW EXCLUSIVE，會在下篇中實驗)。

2. 接下來就是執行的新增欄位的SQL語句：

SQL ID: dwuf634dfa8hf
Plan Hash: 0
alter table t add (sex varchar2(1))

3. 接下來就是各種查，有資料字典表的，有PLSQL，雖然不是都明白，但感覺就一個字：亂，再加一字：暈。但大部分SQL執行的時間都在x毫秒。

4. 我們直接看關閉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$是列的資料字典表，使用了繫結變數，那這些值是什麼，就成了問題的關鍵。

5. 為了檢視繫結變數值，從網上學了一招：

使用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欄位的一些資訊。

6. 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的作用和影響。