前几日晚,某核心应用负责人准备进行上线操作前打来一个确认电话,咨询了一个Oracle 11g 表添加default值的问题,询问是否有何影响,听完他的描述后,简单跟他描述完原理后,建议他取消此上线操作,否则会造成核心应用严重的性能问题,但是此问题再Oracle 12c新特性中已得到很大改进,对性能的影响已经降到最低。
11g情况下,可以看到,增加一个带有default值的并且没有添加非空约束的列,需要分钟级别(与表数据量有直接关系),完成时间会随着数据量的增加而耗时变长。
会话2,我们在执行alter table时会造成读写阻塞,等待library cache lock
SID=916会话被阻塞
SID=346会话被阻塞:
所以在此期间内产生的应用事务,查询均会被阻塞,如果TABLE量级比较大,会造成更严重的性能问题,因为11g在添加DEFAULT值且没有非空约束时,在更新数据字典的同时仍要更新表中全部已有记录值,我们从segment大小与10046 event trace文件中可以得到论证。
但是11g新提供了一种新的方式,如果添加一个带有默认值的非空列,则会很快完成,因为此方式不会更新现有行,只更新现有数据字典,我们在10046 event Trace文件中是找不到update table_name set语句的,只会看到针对sys.col基表中的数据进行插入,已有数据默认值从数据字典中获取,不更新现有列数据,随后数据才会写入具体segment中的block中,从而效率也有着非常大的提升。
我们再看一下Oracle 12c版本中,对不存在非空约束的列添加默认值时,采用同样的方式,只会更新数据字典,之后新数据才会更新,不会update之前现有列数据,效率非常高。
从执行计划角度来看,如果不更新列,读取数据时会有nvl或者decode函数的开销
(1) 11g add defualt
(2) 11g add default not null,会有NVL函数进行转换,
(3)12c add defualt,同样会有DECODE函数进行转换,是否从数据字典中获取还是从具体含有值的block中读取。