背景
之前文章:/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/,如需转载,请注明出处,否则将追究法律责任。