背景

  之前文章：
  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引入新的技術及機制，肯定是為了解決以前碰到一些問題

個人簡介：