(direct-path insert)研究

直接路径插入使用直接写(direct write)操作,在要修改的段的高水位(high watermark)以上直接加载数据,这个事实产生以下重要的结果:

1. 由于直接写的缘故,高速缓存被辟开了.

2. 不允许在被修改的表上同步的执行DELETE,INSERT,MERGE,UPDATE语句,以及索引的创建(重建)操作.

3.在高水位以下的包含空闲空间的块也不会被利用.

直接路径插入之所以可以获得更好的性能,其中一个原因是,只为这个段生成了最少量的回滚.实际上,只为空间管理操作做了回滚.

 

有以下两种方式可以使一个INSERT INTO...SELECT...语句使用直接路径插入.

1.在SQL语句中指定append提示:INSERT /*+ append*/ INTO...SELECT...

2.并行地执行SQL语句.注意,在这里,插入(INSERT)与查询(SELECT)都可以独立地被并行处理.要利用直接路径插入,至少INSERT部分必须被并行执行.

测试如下:

1.以常规方式插入数据(noarchivelog mode,logging mode, no direct-path insert)

LIBIN@dextrys>DROP TABLE t;
DROP TABLE t
           *
第 1 行出现错误:
ORA-00942: 表或视图不存在

已用时间:  00: 00: 00.15
LIBIN@dextrys>
LIBIN@dextrys>CREATE TABLE t (id NUMBER, pad VARCHAR2(1000));

表已创建。

已用时间:  00: 00: 00.06

为表添加主键约束:
LIBIN@dextrys>ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);

表已更改。

已用时间:  00: 00: 00.21
LIBIN@dextrys>TRUNCATE TABLE t;

表被截断。

已用时间:  00: 00: 00.12
LIBIN@dextrys>SELECT value
  2  FROM v$mystat NATURAL JOIN v$statname
  3  WHERE name = 'redo size';
     VALUE
----------
     29332

已用时间:  00: 00: 00.07
LIBIN@dextrys>ALTER TABLE t LOGGING;

表已更改。

已用时间:  00: 00: 00.01
LIBIN@dextrys>INSERT INTO t
  2  SELECT rownum AS id, rpad('*',1000,'*') AS pad
  3  FROM dual
  4  CONNECT BY level <= 1000000;

已创建1000000行。

已用时间:  00: 03: 11.98

检查生成的已用回滚信息.
LIBIN@dextrys>SELECT t.used_ublk, t.used_urec
  2  FROM v$transaction t, v$session s
  3  WHERE t.addr = s.taddr
  4  AND s.audsid = userenv('sessionid');
 USED_UBLK  USED_UREC
---------- ----------
      4320     378280

已用时间:  00: 00: 00.12
LIBIN@dextrys>SELECT value
  2  FROM v$mystat NATURAL JOIN v$statname
  3  WHERE name = 'redo size';
     VALUE
----------
1192914920

已用时间:  00: 00: 00.03

说明:常规方式下,插入100万条数据,花费大约192秒,生成大量回滚信息,生成(1192914920-29332)字节redo log;

2.以(direct-path insert)方式插入数据(noarchivelog mode,logging mode,  direct-path insert)
LIBIN@dextrys>TRUNCATE TABLE t;

表被截断。

已用时间:  00: 00: 01.43
LIBIN@dextrys>ALTER TABLE t LOGGING;

表已更改。

已用时间:  00: 00: 00.07
LIBIN@dextrys>INSERT /*+ append */ INTO t
  2  SELECT rownum AS id, rpad('*',1000,'*') AS pad
  3  FROM dual
  4  CONNECT BY level <= 1000000;

已创建1000000行。

已用时间:  00: 00: 37.04
LIBIN@dextrys>SELECT t.used_ublk, t.used_urec
  2  FROM v$transaction t, v$session s
  3  WHERE t.addr = s.taddr
  4  AND s.audsid = userenv('sessionid');
 USED_UBLK  USED_UREC
---------- ----------
      1070       7490

已用时间:  00: 00: 00.03
LIBIN@dextrys>SELECT value
  2  FROM v$mystat NATURAL JOIN v$statname
  3  WHERE name = 'redo size';
     VALUE
----------
1251788232

已用时间:  00: 00: 00.01

说明:(direct-path insert)方式下,插入100万条数据,花费大约37秒,生成少量回滚信息(回滚信息是为维护索引而产生的),生成(1251788232-1192914920)字节redo log;

3.以(direct-path insert)方式插入数据(noarchivelog mode,nologging mode,  direct-path insert)
LIBIN@dextrys>TRUNCATE TABLE t;

表被截断。

已用时间:  00: 00: 02.82
LIBIN@dextrys>ALTER TABLE t NOLOGGING;

表已更改。

已用时间:  00: 00: 00.03
LIBIN@dextrys>INSERT /*+ append */ INTO t
  2  SELECT rownum AS id, rpad('*',1000,'*') AS pad
  3  FROM dual
  4  CONNECT BY level <= 1000000;

已创建1000000行。

已用时间:  00: 00: 36.18
LIBIN@dextrys>SELECT t.used_ublk, t.used_urec
  2  FROM v$transaction t, v$session s
  3  WHERE t.addr = s.taddr
  4  AND s.audsid = userenv('sessionid');
 USED_UBLK  USED_UREC
---------- ----------
      1071       7496

已用时间:  00: 00: 00.03
LIBIN@dextrys>SELECT value
  2  FROM v$mystat NATURAL JOIN v$statname
  3  WHERE name = 'redo size';
     VALUE
----------
1311002048

已用时间:  00: 00: 00.01

说明:(direct-path insert)方式下,插入100万条数据,花费大约36秒,生成少量回滚信息(回滚信息是为维护索引而产生的),生成(1311002048-1251788232)字节redo log;

2和3的比较说明了:即使没有使用最小日志模式,在非归档(noarchivelog)模式下运行的数据库也不会为直接路径插入操作生成重做日志.

 

 

4.以(direct-path insert)方式插入数据(noarchivelog mode,nologging mode,  direct-path insert),且删除表上的索引

 

 

LIBIN@dextrys>TRUNCATE TABLE t;

表被截断。

已用时间:  00: 00: 03.07
LIBIN@dextrys>alter table t drop constraint t_pk ;

表已更改。

已用时间:  00: 00: 00.48
LIBIN@dextrys>SELECT value
  2  FROM v$mystat NATURAL JOIN v$statname
  3  WHERE name = 'redo size';
     VALUE
----------
1311588524

已用时间:  00: 00: 00.07
LIBIN@dextrys>ALTER TABLE t NOLOGGING;

表已更改。

已用时间:  00: 00: 00.01
LIBIN@dextrys>INSERT /*+ append */ INTO t
  2  SELECT rownum AS id, rpad('*',1000,'*') AS pad
  3  FROM dual
  4  CONNECT BY level <= 1000000;

已创建1000000行。

已用时间:  00: 00: 25.68
LIBIN@dextrys>SELECT t.used_ublk, t.used_urec
  2  FROM v$transaction t, v$session s
  3  WHERE t.addr = s.taddr
  4  AND s.audsid = userenv('sessionid');
 USED_UBLK  USED_UREC
---------- ----------
         1          1

已用时间:  00: 00: 00.03
LIBIN@dextrys>SELECT value
  2  FROM v$mystat NATURAL JOIN v$statname
  3  WHERE name = 'redo size';
     VALUE
----------
1312384532

已用时间:  00: 00: 00.01

说明:删除表上的索引以后,(direct-path insert)方式下,插入100万条数据,花费大约26秒,生成少量回滚信息为1,生成(1312384532-1311002048)字节redo log;以上说明了若表上有索引,在执行DML时,索引段上会生成回滚信息,如果要加快操作速度,避免生成索引相关的回滚,可以在加载数据之前将索引改为不可用,并在加载之后来重建这些索引.

 

在执行直接路径插入的过程中,高水位不会被提高.只有到提交事务的时候才会执行这个操作.因此,在加载之后提交(或回滚)事务之前,执行直接路径插入的会话无法访问被修改的表.在提交(或回滚)之前执行的SQL语句会终止执行,并报一个"ORA-12838"错误.

LIBIN@dextrys>select count(*) from t;
select count(*) from t
                     *
第 1 行出现错误:
ORA-12838: 无法在并行模式下修改之后读/修改对象

已用时间:  00: 00: 00.06
LIBIN@dextrys>commit;

提交完成。

已用时间:  00: 00: 00.06
LIBIN@dextrys>select count(*) from t;
  COUNT(*)
----------
   1000000

已用时间:  00: 00: 22.09