ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 浅析Oracle 11g中对数据列默认值变化的优化

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

原创 Linux操作系统 作者:golden_zhou 时间:2013-01-10 10:34:09 0 删除 编辑
浅析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环境下,事情有了一些不同。
2、11g下的默认值配置
我们在11g上进行相似操作。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE       11.2.0.1.0        Production
构建相似规模的数据表。
SQL> set timing on;
SQL> create table t as select object_id from dba_objects;
Table created
 
SQL> select count(*) from t;
 COUNT(*)
----------
  3323167
 
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
 
SQL> select segment_name, bytes/1024/1024, extents,blocks from user_segments where segment_name='T';
 
SEGMENT_NA BYTES/1024/1024   EXTENTS    BLOCKS
---------- --------------- ---------- ----------
T                      40        55      5120
SQL> select NUM_ROWS, BLOCKS from dba_tables where wner='SCOTT' and table_name='T';
 
 NUM_ROWS    BLOCKS
---------- ----------
  3323167      5041
11g下我们准备了约330万数据,进行添加非空带默认值的数据列。
SQL> alter table t add vc2 varchar2(100) default 'TTTTTTTTTTTT' ;
alter table t add vc2 varchar2(100) default 'TTTTTTTTTTTT'
ORA-01013:用户请求取消当前的操作
在添加defalut列,不指定not null的时候,数据持续时间超过了我们的想象。笔者主动将其断开了。下面试试添加not null时候。
 
--1s不到完成操作;
SQL> alter table t add vc varchar2(100) default 'TTTTTTTTTTTT' not null;
Table altered
Executed in 0.047 seconds
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
SQL> select NUM_ROWS, BLOCKS from dba_tables where wner='SCOTT' and table_name='T';
 NUM_ROWS    BLOCKS
---------- ----------
  3323167      5041
Executed in 0 seconds
SQL> select segment_name, bytes/1024/1024, extents,blocks from user_segments where segment_name='T';
SEGMENT_NA BYTES/1024/1024   EXTENTS    BLOCKS
---------- --------------- ---------- ----------
T                      40        55      5120
 
SQL> select * from t where rownum<10;
 OBJECT_ID VC
---------- --------------------------------------------------------------------------------
       20 TTTTTTTTTTTT
       46 TTTTTTTTTTTT
       28 TTTTTTTTTTTT
       15 TTTTTTTTTTTT
(篇幅原因,有省略……)
9 rows selected
我们发现,当执行not null的时候,Oracle以超乎想象的速度完成了过程。并且注意:数据表的体积没有发生任何变化!!但是,我们检查数据表的时候,却发现了对应列的默认值已经添加。
这个事情是比较奇怪的,有一个道理必然是可以说通:就是这个默认值在执行过程中,是绝对没有真正添加到数据块中的,因为只有这样才不会影响数据段的体积。
 
3、11g默认值处理的优化
 
那么,11g这个过程中是如何处理的呢?而且为什么只有添加Not null的时候才会有这个特点。我们从select数据行的trace进行入手。
 
我们选择10046跟踪一下select的全过程,看看显示出来的默认值从哪里来。
 
SQL> select value from v$diag_info where name='Default Trace File';
 
VALUE
-----------------------------------------------------------------------
/u01/diag/rdbms/wilson/wilson/trace/wilson_ora_6177.trc
 
SQL> alter session set events '10046 trace name context forever, level 12';
会话已更改。
 
SQL> select * from t where rownum<10;
 
 OBJECT_ID
----------
VC
-----------------------------------------------------------------------------
 
SQL> alter session set events '10046 trace name context off';
会话已更改。
 
对生成的trace文件进行处理,获取到tkprof结果。
 
D:\des>tkprof wilson_ora_6177.trc
output = res.txt
 
TKPROF: Release 10.2.0.1.0 - Production on星期五8月24 22:07:10 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
 
在分析的结果中,我们发现很多的recursive语句,也就是Oracle为了执行这个SQL,连带运行了很多的语句,其中我们发现了一个“可疑”对象。
 
***********************************************************************
 
select binaryDefVal, length(binaryDefVal)
from
 ecol$          where tabobj# = :1 and colnum = :2

call    count      cpu   elapsed      disk     query   current       rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse       1     0.00      0.00         0         0         0          0
Execute     1     0.00      0.00         0         0         0          0
Fetch       1     0.00      0.00         2         2         0          1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total       3     0.00      0.00         2         2         0          1
 
ecol$是sys用户下的一个新添加的字典基表,其中内容如下:
 
SQL> desc ecol$;
Name        Type  Nullable Default Comments
------------ ------ -------- ------- --------
TABOBJ#     NUMBER Y                       
COLNUM      NUMBER Y                       
BINARYDEFVAL BLOB  Y                       
 
SQL> select * from ecol$;
 
  TABOBJ#    COLNUM BINARYDEFVAL
---------- ---------- ------------
    76046         2
Executed in 0.031 seconds
 
SQL> col owner for a10;
SQL> col object_name for a10;
SQL> select owner, object_name, object_id from dba_objects where object_id in (76046);
 
OWNER     OBJECT_NAM OBJECT_ID
---------- ---------- ----------
SCOTT     T              76046
 
Executed in 0 seconds
 
 
从ecol$数据表中,我们发现了对数据表T对象第二列(column=2)的一个对象引用,引用的值binarydefval是一个blob类型。从直观上,我们已经可以猜出这个就是记录了数据表vc列的默认值。
 
此处,我们说一个问题,在Oracle中,默认值都是通过大对象类型进行保存。在数据字典col$中,默认值是通过long类进行保存。而进入11g的ecol$表,这个值是使用blob类型进行保存。
 
另一个需要注意的,就是这个数据表中只有一个数据行,也就是只有我们创建数据表T的默认值。这说明什么呢?
 
此时,我们已经可以猜出Oracle的良苦用心。首先,Oracle注意到了在生产online的时候,添加带默认值列数据的困难。但是,从现有的体系结构和存储结构下,将默认值逐行插入、从而引起行迁移的情况是不能避免的。所以,Oracle采用了一种“障眼法”。
 
如果我们在创建数据表的时候就指定了数据列的默认值、或者没有要求将所有数据空值一次性全都变成默认值的时候,Oracle还是按照原有的存储策略进行管理。如果出现了要求添加数据列,并且一次性将所有默认值列都加入的情况,Oracle索性就不进行插入数据和挪行的操作,而是将这个默认值保存在ecol$中。
 
接下来,如果要进行检索数据,首先oracle会利用recursive call的方法,保存提取出默认值。在检索数据的过程中,如果遇到默认值列为空的情况(没有插值),就将取出的默认值输出到界面上进行显示。其实,数据行对应的默认值列是没有这个值的。
 
这就解释了为什么只有在添加not null默认值列的时候,才会有这个优化。因为Oracle需要确认这个列不会有空值,才会将出现的空值全都进行“障眼法”匹配。
 
4、结论
 
借助了11g这个特性,我们说在online生产环境下,临时加入默认值列就不是一件恐怖的工作了。不过,处于谨慎的考虑,还是希望有条件的时候,将该数据表进行重构。这种特性属于应急环境下考虑使用。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25198367/viewspace-752433/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-09

  • 博文量
    238
  • 访问量
    308336