背景

之前文章：

/9240380/viewspace-1814709/，涉及过如何删除表的某列，本文我们讨论下不同的删除表列语法参数，持锁模式的不同，便于大家在OLTP操作

时选用不同的命令组合，减少对于业务的影响。

结论

1,alter table set unused column与alter table drop column持锁模式相同

2,二者皆持表级排它锁

3,前者消耗的时间短于后者，原因在于前者递归操作DML操作字典表的次数要少于后者

4,前者消耗的REDO及UNDO要高于后者

5,oracle引入每个新特性，皆是为了解决之前版本存在一些问题

6,影响并发有几种因素：之前只想到有持锁模式即v$lock.lmode不同，但现在也意识到还有一个因素，持锁时间的长短也会影响并发操作

直白一点就是说，虽然有些操作持锁模式相同，但相比之下，某些操作运行时间短，这样也不会很明显的阻塞并发操作，而相之，结果就显而易见了

7,在高并发OLTP环境，在业务峰期期间，最好使用alter table set unused column

测试

SQL> select pid,spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));

PID SPID

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

178 26169

SQL> select * from v$version where rownum=1;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> desc t_dropcol;

Name Null? Type

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

ANUMBER(38)

BNUMBER(38)

SQL> select * from t_dropcol;

AB

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

11

查看alter table drop column方式持锁情况

SQL> alter table t_dropcol drop column b;

Table altered.

[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_26169.trc|grep -i --color "lock table"

LOCK TABLE "T_DROPCOL" IN EXCLUSIVE MODE NOWAIT

可见alter table drop column方式会持表级排它锁，所以这种情况，会导致所有与此表的DML及DDL操作受到阻塞，这种操作在OLTP环境一定要慎用

再看alter table set unused column方式持锁情况

SQL> alter table t_dropcol set unused column b;

Table altered.

[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_30745.trc|grep -i --color "lock table"

LOCK TABLE "T_DROPCOL" IN EXCLUSIVE MODE NOWAIT

[oracle@seconary ~]$

可见alter table set unused column方式也会持表级排它锁

我们再换个思路，那么ORACLE为何要设计这种新命令方式指定某表列为不可用呢？肯定是可以减少某些消耗的消耗的，对吧，从这个思路，我们分析下2种不同命令方式DML操作的命令差异

先看alter table drop column

共计2个insert语句

[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_26169.trc|grep -i --color "insert"

m_stmt:='insert into sdo_geor_ddl__table$$ values (1)';

m_stmt:='insert into sdo_geor_ddl__table$$ values (2)';

共计7个UPDATE语句

[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_26169.trc|grep -i --color "update"

update sys.wri$_optstat_histhead_history h set intcol# = decode(intcol#, :2, 0, intcol# -1) where h.intcol# >= :2 and h.obj# in (select o2.obj#from sys.obj$ o1, sys.obj$ o2where o1.obj# = :1 and o1.type# = 2 and o1.owner# = o2.owner# and o2.name = o1.name)

STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE WRI$_OPTSTAT_HISTHEAD_HISTORY (cr=8 pr=0 pw=0 time=0 us)'

update sys.wri$_optstat_histgrm_history h set intcol# = decode(intcol#, :2, 0, intcol# -1) where h.intcol# >= :2 and h.obj# in (select o2.obj#from sys.obj$ o1, sys.obj$ o2where o1.obj# = :1 and o1.type# = 2 and o1.owner# = o2.owner# and o2.name = o1.name)

STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE WRI$_OPTSTAT_HISTGRM_HISTORY (cr=9 pr=0 pw=0 time=0 us)'

update dependency$ set p_timestamp=:1, p_obj#=:2 where d_obj#=:3 and p_obj#=:4

update dependency$ set d_reason = :1 where d_obj# = :2 and p_obj# = :3

update dependency$ set d_attrs = :1 where d_obj# = :2 and p_obj# = :3

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

STAT #4 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE TAB$ (cr=3 pr=0 pw=0 time=0 us)'

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

STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE OBJ$ (cr=3 pr=0 pw=0 time=0 us)'

共计34个delete语句

[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_26169.trc|grep -i --color "delete"

delete sdo_geor_sysdata_table where sdo_owner=:1 and GEORASTER_TABLE_NAME=:2 and GEORASTER_COLUMN_NAME=:3

STAT #8 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE SDO_GEOR_SYSDATA_TABLE (cr=1 pr=0 pw=0 time=0 us)'

delete mdsys.sdo_tin_pc_sysdata_table where sdo_owner=:1 and TABLE_NAME=:2 and COLUMN_NAME=:3

STAT #11 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE SDO_TIN_PC_SYSDATA_TABLE (cr=0 pr=0 pw=0 time=0 us)'

delete from sys.wri$_optstat_histhead_history h where h.obj# in (select o2.obj#from sys.obj$ o1, sys.obj$ o2where o1.obj# = :1 and o1.type# = 2 and o1.owner# = o2.owner# and o2.name = o1.name) and h.intcol# = :2

STAT #8 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE WRI$_OPTSTAT_HISTHEAD_HISTORY (cr=8 pr=0 pw=0 time=0 us)'

delete from sys.wri$_optstat_histgrm_history h where h.obj# in (select o2.obj#from sys.obj$ o1, sys.obj$ o2 where o1.obj# = :1 and o1.type# = 2 and o1.owner# = o2.owner# and o2.name = o1.name) and h.intcol# = :2

STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE WRI$_OPTSTAT_HISTGRM_HISTORY (cr=10 pr=0 pw=0 time=0 us)'

delete com$ where obj#=:1 and col#=:2

STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE COM$ (cr=2 pr=0 pw=0 time=0 us)'

delete from sys.col_usage$ where obj#= :1 and intcol#= :2

STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE COL_USAGE$ (cr=2 pr=0 pw=0 time=0 us)'

delete from objauth$ where obj#=:1 and col#=:2

STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE OBJAUTH$ (cr=2 pr=0 pw=0 time=0 us)'

delete from col$ where obj#=:1 and intcol#=:2

STAT #8 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE COL$ (cr=2 pr=0 pw=0 time=0 us)'

delete from compression$ where obj#=:1

STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE COMPRESSION$ (cr=1 pr=0 pw=0 time=0 us)'

delete from idl_ub1$ where obj#=:1

STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_UB1$ (cr=2 pr=0 pw=0 time=0 us)'

delete from idl_char$ where obj#=:1

STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_CHAR$ (cr=2 pr=0 pw=0 time=0 us)'

delete from idl_ub2$ where obj#=:1

STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_UB2$ (cr=2 pr=0 pw=0 time=0 us)'

delete from idl_sb4$ where obj#=:1

STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_SB4$ (cr=2 pr=0 pw=0 time=0 us)'

delete from error$ where obj#=:1

STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE ERROR$ (cr=1 pr=0 pw=0 time=0 us)'

m_stmt:='delete from sdo_geor_ddl__table$$ where id=2';

m_stmt:='delete from sdo_geor_ddl__table$$';

delete from superobj$ where subobj# = :1

STAT #11 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE SUPEROBJ$ (cr=1 pr=0 pw=0 time=0 us)'

delete from tab_stats$ where obj#=:1

STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE TAB_STATS$ (cr=1 pr=0 pw=0 time=0 us)'

[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_26169.trc|grep -i --color "delete"|wc -l

34

再看alter table set unused column

共计2个insert语句

[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_30745.trc|grep -i --color "insert"

m_stmt:='insert into sdo_geor_ddl__table$$ values (1)';

m_stmt:='insert into sdo_geor_ddl__table$$ values (2)';

共计6个update语句

[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_30745.trc|grep -i --color "update"

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

STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE COL$ (cr=2 pr=0 pw=0 time=0 us)'

update dependency$ set p_timestamp=:1, p_obj#=:2 where d_obj#=:3 and p_obj#=:4

update dependency$ set d_reason = :1 where d_obj# = :2 and p_obj# = :3

update dependency$ set d_attrs = :1 where d_obj# = :2 and p_obj# = :3

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

STAT #7 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE TAB$ (cr=3 pr=0 pw=0 time=0 us)'

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

STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE OBJ$ (cr=3 pr=0 pw=0 time=0 us)'

[oracle@seconary ~]$

共计28个delete语句

[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_30745.trc|grep -i --color "delete"

delete sdo_geor_sysdata_table where sdo_owner=:1 and GEORASTER_TABLE_NAME=:2 and GEORASTER_COLUMN_NAME=:3

STAT #7 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE SDO_GEOR_SYSDATA_TABLE (cr=1 pr=0 pw=0 time=0 us)'

delete mdsys.sdo_tin_pc_sysdata_table where sdo_owner=:1 and TABLE_NAME=:2 and COLUMN_NAME=:3

STAT #7 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE SDO_TIN_PC_SYSDATA_TABLE (cr=0 pr=0 pw=0 time=0 us)'

delete com$ where obj#=:1 and col#=:2

STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE COM$ (cr=2 pr=0 pw=0 time=0 us)'

delete from sys.col_usage$ where obj#= :1 and intcol#= :2

STAT #4 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE COL_USAGE$ (cr=2 pr=0 pw=0 time=0 us)'

delete from objauth$ where obj#=:1 and col#=:2

STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE OBJAUTH$ (cr=2 pr=0 pw=0 time=0 us)'

delete from compression$ where obj#=:1

STAT #4 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE COMPRESSION$ (cr=1 pr=0 pw=0 time=0 us)'

delete from idl_ub1$ where obj#=:1

STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_UB1$ (cr=2 pr=0 pw=0 time=0 us)'

delete from idl_char$ where obj#=:1

STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_CHAR$ (cr=2 pr=0 pw=0 time=0 us)'

delete from idl_ub2$ where obj#=:1

STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_UB2$ (cr=2 pr=0 pw=0 time=0 us)'

delete from idl_sb4$ where obj#=:1

STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE IDL_SB4$ (cr=2 pr=0 pw=0 time=0 us)'

delete from error$ where obj#=:1

STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE ERROR$ (cr=1 pr=0 pw=0 time=0 us)'

m_stmt:='delete from sdo_geor_ddl__table$$ where id=2';

m_stmt:='delete from sdo_geor_ddl__table$$';

delete from superobj$ where subobj# = :1

STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE SUPEROBJ$ (cr=1 pr=0 pw=0 time=0 us)'

delete from tab_stats$ where obj#=:1

STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE TAB_STATS$ (cr=1 pr=0 pw=0 time=0 us)'

[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_30745.trc|grep -i --color "delete"|wc -l

28

[oracle@seconary ~]$

所以说明了什么呢，表明alter table set unused column虽然和alter table drop column持锁模式相同，但是前者在递归操作即DML底层字典表时，DML的操作明显要少于后者，

进一步说，会减少REDO及UNDO的操作，大家知道UNDO及REOD与性能也有密切相关的关系，同时也会减少资源的消耗，从直观来看，前者消耗的时间要短于后者

这个造成的影响就是在高并发的OLTP环境下，可以减少长时间对于资源的占用，所以我总结下，有几点好处：

1，alter table set unused column虽然和alter table drop column持锁模式相同

2, 但前者持锁时间要短于后者，因为前者消耗的时间要少

3, 前者消耗的REDO及UNDO也要少于后者

再引申一点，从这个测试可知，ORACLE确实一直在进步，每个技术新特性引入，不是无缘无故的，皆是有背景及原因的，这个我认为是本篇文章最为重要的价值。

给我以后学习ORACLE带入新的思维，ORACLE引入新的技术及机制，肯定是为了解决以前碰到一些问题

个人简介：

8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。 服务过的客户： 中国电信 中国移动 中国联通 中国电通 国家电网 四川达州商业银行 湖南老百姓大药房 山西省公安厅 中国邮政 北京302医院 河北廊坊新奥集团公司

项目经验: 中国电信3G项目AAA系统数据库部署及优化 中国联通CRM数据库性能优化 中国移动10086电商平台数据库部署及优化 湖南老百姓大药房ERR数据库sql优化项目 四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化 四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化 北京高铁信号监控系统RAC数据库部署及优化 河南宇通客车数据库性能优化 中国电信电商平台核心采购模块表模型设计及优化 中国邮政储蓄系统数据库性能优化及sql优化 北京302医院数据库迁移实施 河北廊坊新奥data guard部署及优化 山西公安厅身份证审计数据库系统故障评估 联系方式： 手机：1815468 qq : 305076427 qq微博: wisdomone1 新浪微博：wisdomone9 qq群：275813900 itpub博客名称：wisdomone1 /9240380/

来自 “ ITPUB博客 ” ，链接：/9240380/viewspace-1816982/，如需转载，请注明出处，否则将追究法律责任。