浅析Oracle 11g中对数据列默认值变化的优化


在日常的运维工作中,对生产数据表进行DDL操作是一件需要谨慎对待的事情。运维DBA们在进行数据DDL操作的时候,通常要全局考虑,诸如对生产影响、执行时间长度和影响存储数据等等。
 
数据列默认值的添加,是DBA们经常头疼的一个问题。传统的执行语句,消耗时间长、资源使用量大,对生产环境影响程度高。采用其他的一些变通方法,又存在操作步骤繁琐的问题。如何快速的添加一个有默认值的数据列,同时对现有生产环境影响最小,是我们希望达到的一个目标。
 
本文从操作入手,探讨添加default数据列的问题点,最后介绍Oracle 11g中对其进行的“革命性”优化。
 
1、从10g的数据列添加谈起
 
为了实现对比效果,我们首选选择10g版本的Oracle进行试验,构造一个相对较大的数据表。
 
 
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE   10.2.0.1.0     Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
 
SQL> create table t as select object_id from dba_objects;
表已创建。
 
SQL> select count(*) from t;
 COUNT(*)
----------
  3220352
 
 
数据表t只包括一个数据列,但是数据量大约为320万条。我们从体积上进行评估如下:
 
 
SQL> set timing on;
SQL> select bytes/1024/1024,blocks from dba_segments where wner='SYS' and segment_name='T';
 
BYTES/1024/1024    BLOCKS
--------------- ----------
            39      4992
 
已用时间: 00: 00: 00.03
 
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
PL/SQL过程已成功完成。
 
已用时间: 00: 00: 00.35
 
SQL> select blocks from dba_tables where wner='SYS' and table_name='T';
 
   BLOCKS
----------
     4883
 
已用时间: 00: 00: 00.01
 
 
Oracle分配给这个段segment的中空间为4992个数据块,高水位线HWM下的格式化过数据块为4883。总体积约40M。
 
下面进行两种方式的添加数据表默认值列方法,一起观察一下变化情况。首先是允许为空默认值列的操作。
 
 
SQL> alter table t add vc varchar2(100) default 'TTTTTTTTTTTT';
 
表已更改。
 
已用时间: 00: 34: 37.15
 
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
 
PL/SQL过程已成功完成。
 
已用时间: 00: 00: 03.86
 
 
SQL> select bytes/1024/1024,blocks from dba_segments where wner='SYS' and segment_name='T';
 
BYTES/1024/1024    BLOCKS
--------------- ----------
           208     26624
 
已用时间: 00: 00: 00.06
SQL> select blocks from dba_tables where wner='SYS' and table_name='T';
 
   BLOCKS
----------
    25864
 
已用时间: 00: 00: 00.01
 
 
果然是一个费时的操作,添加一个数据列默认值,总共消耗了近30分钟时间。原有数据表的体积也发生的膨胀,从原来的不到40M,上升到了208M。
 
这个现象告诉我们,当我们添加一个有default值的数据列,并且是直接添加的时候,一些数据被插入到了数据块中,引起空间膨胀。
 
在原有的结构下,数据添加到数据块上是必需的,只有这样才能将数据列default添加到里面去。
 
除了这个字句,我们是还可以提供数据列的not null选项,也是可以实现相同的功能的。
 
 
SQL> alter table t add vc2 varchar2(100) default 'TTTTTTTTTTTT' not null;
 
表已更改。
 
已用时间: 00: 15: 58.85
 
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
 
PL/SQL过程已成功完成。
 
已用时间: 00: 00: 36.87
 
SQL> select bytes/1024/1024,blocks from dba_segments where wner='SYS' and segme
nt_name='T';
 
BYTES/1024/1024    BLOCKS
--------------- ----------
           256     32768
 
已用时间: 00: 00: 00.14
SQL> select blocks from dba_tables where wner='SYS' and table_name='T';
 
   BLOCKS
----------
    32448
 
已用时间: 00: 00: 00.04
 
 
也是消耗了15分钟,空间发生了很大程度变化。新空间分配,同时数据行数没有发生变化,潜在的行迁移(Row Migration)和行链接(Row Chaining)是严重恶化的!
 
综合分析Oracle 10g下的操作:为了添加上数据字段的默认值,Oracle会去访问每个数据块上的每个数据行进行数据列拓展工作,这个过程中还伴随着新空间分配和多余数据行复制。
 
这类型操作对于生产环境是恐怖的,在整个作业过程中,数据表结构被锁定,相关业务处理操作阻塞或者缓慢。所以,运维DBA都是选择在维护窗口或者变通的方法进行处理。
 
在Oracle 11g环境下,事情有了一些不同。
  • 1
  • 2
  • 3
  • 下一页

相关内容