转载

v$lock之alter table drop column与alter table set unused column区别系列五

背景

之前文章：
http://blog.itpub.net/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
----------------------------------------- -------- ----------------------------
A NUMBER(38)
B NUMBER(38)

SQL> select * from t_dropcol;

A B
---------- ----------
1 1

查看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$ o2 where 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$ o2 where 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$ o2 where 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引入新的技术及机制，肯定是为了解决以前碰到一些问题