ITPub博客

首页 > 数据库 > Oracle > alter table新增字段操作究竟有何影响?(上篇)

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

Oracle 作者:bitifi 时间:2015-10-23 14:13:16 0 删除 编辑

很久以前就有个疑问,见过一些表设计时会留出几个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的作用和影响。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30496894/viewspace-1815865/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 分区表 总结
请登录后发表评论 登录
全部评论

注册时间:2015-09-21

  • 博文量
    211
  • 访问量
    275997