点评Oracle11g新特性：在线操作效能增强
【IT168 Oracle】11g中在线处理功能得到了很大增强，其中包括在线修改表结构，在线创建或重建索引，建立不可见索引，表增加非空字段，在线DDL以及对象依赖性细化等。
在11g以前，DDL操作都要获取对象的排他锁，如果当时无法获取，则会马上报错：
SQL> conn yangtk/yangtk@ytk102已连接。
SQL> create table t (id number);
表已创建。
SQL> insert into t values (1);
已创建 1 行。
在当前会话不提交，保持对t表的锁。然后在另外的session登陆，尝试对t表进行ddl操作：
<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ -->SQL> conn yangtk/yangtk@ytk102已连接。 SQL> set sqlp 'SQL2> ' SQL2> truncate table t; truncate table t *第 1 行出现错误: ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源 SQL2> drop table t; drop table t *第 1 行出现错误: ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源 SQL2> alter table t add name varchar2(10); alter table t add name varchar2(10) *第 1 行出现错误: ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源 SQL2> 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
这就是11g以前的DDL处理方式，在11g中，用户可以为DDL设置一个等待时间，这样DDL在获取不到排他锁时，会根据设置等待相应的时间，如果到时仍然获取不到锁，那么才会报错。
登陆11g进行同样的处理：
SQL> conn yangtk/yangtk@ora11g已连接。
SQL> create table t (id number);
表已创建。
SQL> insert into t values (1);
已创建 1 行。
登陆第二个会话，发出DDL操作：
SQL2> conn yangtk/yangtk@ora11g已连接。
SQL2> set timing on
SQL2> truncate table t;
truncate table t
*第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
已用时间: 00: 00: 00.07
DDL仍然马上报错，这是由于控制DDL等待时间的初始化参数DDL_LOCK_TIMEOUT默认值是0，下面修改这个默认值：
SQL2> alter session set ddl_lock_timeout = 5;
会话已更改。
已用时间: 00: 00: 00.06
SQL2> truncate table t;
truncate table t
*第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
已用时间: 00: 00: 05.07
如果在等待时间内第一个会话进行提交，那么DDL就可以获取到排他锁，从而开始操作。
SQL2> alter session set ddl_lock_timeout = 60;
会话已更改。
已用时间: 00: 00: 00.04
SQL2> truncate table t;
返回会话1进行提交：
SQL> commit;
提交完成。
会话2在会话1提交后，得到T表的锁，开始TRUNCATE操作：
表被截断。
已用时间: 00: 00: 13.03
SQL2> select * from v$version;
BANNER
--------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
已用时间: 00: 00: 00.21
注意，将DDL_LOCK_TIMEOUT参数设置过大也可能造成潜在的问题，虽然DDL还没有获取到排他锁，不能进行操作，但是它已经在T上增加了锁信息，这会导致后续的DML都会被锁住，直到DDL等待超时或锁住DDL的会话提交或回滚使得DDL顺利完成。
这对于繁忙的系统可能造成很严重的问题，设置之前需要谨慎考虑。
在11g以前，表中新增一个NOT NULL的字段是十分痛苦的事情，尤其是表很大的情况。不光是执行速度慢，而且由于现有数据长度的变化，很容易造成表中大量的行链接情况。
在11g中，这种情况得到了彻底的改善，Oracle通过在数据字典中记录DEFAULT值，避免了繁重的更新操作，增加非空字段的时间和增加一个可空字段的时间完全一样。
先来看看10g中添加一个包含DEFAULT值的非空字段的情况：
<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ -->SQL> CONN YANGTK/YANGTK@YTK102已连接。 SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS; 表已创建。 SQL> SELECT COUNT(*) FROM T; COUNT(*) ---------- 50357 SQL> SET TIMING ON SQL> ALTER TABLE T ADD NEW_COL CHAR(1000) DEFAULT 'LARGE COLUMN' NOT NULL; 表已更改。 已用时间: 00: 00: 17.04 SQL> SET TIMING OFF SQL> SELECT SUM(BYTES)/1024/1024 FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'T'; SUM(BYTES)/1024/1024 -------------------- 72 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
在10g中，5万条记录添加一个CHAR(1000)的新增NOT NULL列，需要17秒，而且更新后表的大小为72M。
下面看看11g中的表现：
<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ -->SQL> CONN YANGTK/yangtk@ORA11G已连接。 SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS; 表已创建。 SQL> SELECT COUNT(*) FROM T; COUNT(*) ---------- 68376 SQL> SET TIMING ON SQL> ALTER TABLE T ADD NEW_COL CHAR(1000) DEFAULT 'LARGE COLUMN' NOT NULL; 表已更改。 已用时间: 00: 00: 00.15 SQL> SET TIMING OFF SQL> SELECT SUM(BYTES)/1024/1024 FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'T'; SUM(BYTES)/1024/1024 -------------------- 9 SQL> SELECT * FROM V$VERSION; BANNER ---------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS for Linux: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production
将近7万的数据，添加字段仅用了0.15秒。性能完全没有可比性。从更新后表的大小也可以看到明显的区别。这里只看了执行时间和占用空间两个方面，从生成REDO和UNDO的大小看，前者同样远远超过了后者。
Oracle采用了何种方法实现了如此的性能提升，说起来也很简单。Oracle11g中，在添加一个包含DEFAULT值的NOT NULL字段，Oracle不会去更新现有的数据，Oracle需要做的不过是将默认值以及对应的表信息、列信息一起存储在一个新增数据字典表ecol$中。
这张表利用BLOB字段存储ALTER TABLE添加的DEFAULT值：
SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = 'T';
OBJECT_ID
----------
70482
SQL> SELECT * FROM SYS.ECOL$ WHERE TABOBJ# = 70482;
TABOBJ# COLNUM BINARYDEFVAL
---------- ---------- ----------------------------------------
70482 16 4C4152474520434F4C554D4E
简单转换一下：
SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_BLOB VARCHAR2(32767) DEFAULT '4C4152474520434F4C554D4E';
3 BEGIN
4 FOR I IN 1..LENGTH(V_BLOB)/2 LOOP
5 DBMS_OUTPUT.PUT(CHR(TO_NUMBER(SUBSTR(V_BLOB, (I - 1) * 2 + 1, 2), 'XXX')));
6 END LOOP;
7 DBMS_OUTPUT.NEW_LINE;
8 END;
9 /
LARGE COLUMN
PL/SQL procedure successfully completed.
Oracle在读取数据时，发现COLUMN 16列为非空，但是在存储的数据中找不到该列，于是就会从ECOL$中读取该列的默认值。
这个功能不但提高了添加非空字段的速度，而且节省了大量的磁盘空间。相比之下，在数据字典中查找DEFAULT VALUE的代价小的几乎可以忽略。