提起Oracle数据库的Hint,几乎每一个DBA都知道这一强大工具。在Oracle中,Hint可以用来改变SQL的执行计划、固定SQL的执行计划。Oracle数据库内部的很多特性也依赖于Hint,比如Outline、Profile等。

但是在日常工作中,很多开发人员或DBA,对Hint的使用仍然存在一些错误的方式。下面将列举主要的2种。(本文不讨论Hint的滥用即过度使用问题)。

1. NOLOGGING的不正确使用。

很多人知道,在进行数据处理时,如果不产生日志或只产生少量的日志,将会有明显的、甚至是巨大的效率提升。下面有几条不同的SQL:

INSERT INTO T1 NOLOGGING;
INSERT INTO T1 SELECT * FROM T2 NOLOGGING;
INSERT /*+ NOLOGGING */ INTO T1 VALUES ('0');
INSERT /*+ NOLOGGING */ INTO T1 SELECT * FROM T2;
DELETE /*+ NOLOGGING */ FROM T1;
UPDATE /*+ NOLOGGING */ T1 SET A='1';

实际上,上述所有的SQL没有一个能够实现“不产生”日志的数据更改操作。第1-2条SQL语句虽然没有将NOLOGGING写为Hint的形式,但是也是很多人的错误写法,一并列在此处。事实上,NOLOGGING并不是Oracle的一个有效的Hint,而是一个SQL关键字,通常用于DDL语句中。这里NOLOGGING相当于给SELECT的表指定了一个别名为“NOLOGGING”。下面是NOLOGGING的一些正确用法:

CREATE TABLE T1 NOLOGGING AS SELECT * FROM T2;
CREATE INDEX T1_IDX ON T1(A) NOLOGGING;
ALTER INDEX T1_IDX REDUILD ONLINE NOLOGGING;
ALTER TABLE T1 NOLOGGING;

上述SQL中,最后一条SQL只是将表的LOGGING属性改为"NO"。而之前的几条SQL能够有效地减少DDL操作时减少的日志量。

在DML操作中,只有下面一种方式能够在大数据量时仍然只会产生极少量的日志:

INSERT /*+ APPEND */ INTO T1 SELECT * FROM T2;

也就是使用append hint。但是这个hint要达到目的,需要以下几个条件:

  • 使用INSERT /*+ APPEND */ INTO .. SELECT .. FROM形式的INSERT SQL。
  • 如果是在归档模式下,需要将表的LOGGING属性置为NO。
  • 表空间或数据库的FORCE LOGGING属性为NO。注意在非归档模式下也是可以设置FORCE LOGGING的。

这里提到的insert语句中的append hint,对于索引,仍然会产生日志,也就是说append hint对索引是没有效果的。
另外,DDL中使用的nologging关键字和inset语句中使用的append hint,并不是说完全不产生日志,只是对表的数据块的数据部分的更改不会有日志产生,但是SQL执行过程中数据字典的更改、空间分配等递归SQL、段头和位图块的更改、将数据块标记为unrecoverable等仍然会产生少量日志。

2. Hint的不正确写法。

这是一个比较不容易发现的问题。下面几条SQL,哪一条SQL的append hint会生效:

1. INSERT /*+ append,parallel(t1) */ INTO T1 SELECT * FROM T2;
2. INSERT /*+ parallel(t1), append */ INTO T1 SELECT * FROM T2;
3. INSERT /*+ this is append */ INTO T1 SELECT * FROM T2;
4. INSERT /*+ this append */ INTO T1 SELECT * FROM T2;

要回答这个问题,请先看下面的测试(测试环境:10.2.0.1 for Windows):

SQL> INSERT /*+ append,parallel(t1) */ INTO T1 SELECT * FROM T2;
已创建55640行。
统计信息
----------------------------------------------------------
      12304  redo size
SQL> COMMIT;

SQL> INSERT /*+ parallel(t1), append */ INTO T1 SELECT * FROM T2;
已创建55640行。
统计信息
----------------------------------------------------------
    5739584  redo size
SQL> COMMIT;

SQL> INSERT /*+ this is append */ INTO T1 SELECT * FROM T2;
已创建55640行。
统计信息
----------------------------------------------------------
    5746604  redo size
SQL> COMMIT;

SQL> INSERT /*+ this append */ INTO T1 SELECT * FROM T2;
已创建55640行。
统计信息
----------------------------------------------------------
      12052  redo size
SQL> COMMIT;

从上面的输出可以看到,通过insert语句执行产生的redo size判断,4条SQL语句中,1和4这2条SQL中的append hint起了作用,而2和3这2条SQL中的append hint没有起作用。我们看看第1和第2条SQL,只不过是parallel和append换了个位置,结果就截然不同;而第3和第4条SQL,只是一个多了"is"这个词,另一个没有,其结果也完全不同。这里有什么玄机吗?

这里就需要了解Oracle在解析SQL时,是怎样解析hint的。
Oracle在解析hint,从左到右进行,如果遇到一个词是oracle关键字或者说是保留字,将忽略这个词以及之后的所有词。如果遇到的一个词即不是关键字也不是hint,就忽略该词。如果遇到的一个词是有效的hint,那么就会保留该hint。

Oracle的保留字或者说是关键词(虽然二者在意义不一样,但这里不将其区分),可以通过视图v$reserved_words来查询。"is"正是一个关键词,甚至连","(逗号)也是一个关键词。这样,上面的第2和第3条SQL,Oracle解析时当遇到","和"is"时,就忽略了后面的所有hint。在第4条SQL中,this并不是一个关键词,所以append hint有效。基于这个原理,下面的一条SQL中的hint也是不起作用的:

INSERT /*+ NOLOGGING APPEND */ INTO T1 SELECT * FROM T2;

在9.2.0.8和11.2.0.2这2个版本下进行同样的测试,结果完全一样。
为了避免这样的情况,在SQL中书写hint时,在/*+ */--+这2种结构内只写hint,而不要写逗号,或者是其他的注释。如果要对SQL写注释,在专门的注释结构中写入。比如/* test comment */。如果与hint混写注释,虽然当时没有关键词在里面,但随着版本升级,很可能会加入新的关键词。

另外,一些很常见的hint形式,比如/*+ parallel(t,8) */,/*+ index(t,t_idx) */,虽然当前没有问题,但标准的写法应该是:
/*+ parallel(t 8) */,/*+ index(t t_idx) */

--end end.

Trackback

7 comments untill now

  1. INSERT INTO T1 SELECT * FROM T2 NOLOGGING;
    这个sql语句是不会产生redo日志,以下是我做的实验
    (1)这个sql是用nologging,在执行计划中,redosize是0
    SQL> set autotrace on;
    SQL> insert into emp01 select * from emp nologging;

    14 rows created.

    Execution Plan
    ———————————————————-
    Plan hash value: 3956160932

    ————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————–
    | 0 | INSERT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
    | 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
    ————————————————————————–

    Statistics
    ———————————————————-
    461 recursive calls
    5 db block gets
    123 consistent gets
    16 physical reads
    0 redo size
    671 bytes sent via SQL*Net to client
    581 bytes received via SQL*Net from client
    4 SQL*Net roundtrips to/from client
    8 sorts (memory)
    0 sorts (disk)
    14 rows processed
    (2)下面sql不添加nologging,redo size是不为0的
    SQL> insert into emp01 select * from emp;

    14 rows created.

    Execution Plan
    ———————————————————-
    Plan hash value: 3956160932

    ————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————–
    | 0 | INSERT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
    | 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
    ————————————————————————–

    Statistics
    ———————————————————-
    1 recursive calls
    3 db block gets
    8 consistent gets
    0 physical reads
    940 redo size
    675 bytes sent via SQL*Net to client
    571 bytes received via SQL*Net from client
    4 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    14 rows processed

    老熊 Reply:

    的确是个很奇怪的现象。下面是我测试的结果,第1次带有nologging,redosize为0,但是第2次insert仍然带有nologging,redosize就变成了1100,第3次insert不带有nologging,redosize仍然是1100。

    SQL> set autot on
    SQL> insert into t2 select * from t1 nologging;

    已创建10行。

    执行计划
    ———————————————————-
    Plan hash value: 3617692013

    ————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————–
    | 0 | INSERT STATEMENT | | 11402 | 946K| 40 (0)| 00:00:01 |
    | 1 | TABLE ACCESS FULL| T1 | 11402 | 946K| 40 (0)| 00:00:01 |
    ————————————————————————–

    统计信息
    ———————————————————-
    266 recursive calls
    8 db block gets
    40 consistent gets
    0 physical reads
    0 redo size
    1767 bytes sent via SQL*Net to client
    1149 bytes received via SQL*Net from client
    6 SQL*Net roundtrips to/from client
    2 sorts (memory)
    0 sorts (disk)
    10 rows processed

    SQL> insert into t2 select * from t1 nologging;

    已创建10行。

    执行计划
    ———————————————————-
    Plan hash value: 3617692013
    ————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————–
    | 0 | INSERT STATEMENT | | 11402 | 946K| 40 (0)| 00:00:01 |
    | 1 | TABLE ACCESS FULL| T1 | 11402 | 946K| 40 (0)| 00:00:01 |
    ————————————————————————–

    统计信息
    ———————————————————-
    0 recursive calls
    1 db block gets
    7 consistent gets
    0 physical reads
    1100 redo size
    908 bytes sent via SQL*Net to client
    950 bytes received via SQL*Net from client
    4 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    10 rows processed

    SQL> insert into t2 select * from t1 ;

    已创建10行。

    执行计划
    ———————————————————-
    Plan hash value: 3617692013

    ————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————–
    | 0 | INSERT STATEMENT | | 11402 | 946K| 40 (0)| 00:00:01 |
    | 1 | TABLE ACCESS FULL| T1 | 11402 | 946K| 40 (0)| 00:00:01 |
    ————————————————————————–

    统计信息
    ———————————————————-
    1 recursive calls
    1 db block gets
    7 consistent gets
    0 physical reads
    1100 redo size
    908 bytes sent via SQL*Net to client
    941 bytes received via SQL*Net from client
    4 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    10 rows processed

    我们再做另一个测试,第1次插入不带nologging,而第2次插入带nologging,这一次测试不带nologging的redosize反而是0了。

    SQL> truncate table t2;

    表被截断。

    SQL> set autot on
    SQL> insert into t2 select * from t1;

    已创建10行。

    执行计划
    ———————————————————-
    Plan hash value: 3617692013

    ————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————–
    | 0 | INSERT STATEMENT | | 11402 | 946K| 40 (0)| 00:00:01 |
    | 1 | TABLE ACCESS FULL| T1 | 11402 | 946K| 40 (0)| 00:00:01 |
    ————————————————————————–

    统计信息
    ———————————————————-
    1 recursive calls
    8 db block gets
    13 consistent gets
    0 physical reads
    0 redo size
    1769 bytes sent via SQL*Net to client
    1143 bytes received via SQL*Net from client
    6 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    10 rows processed

    SQL> insert into t2 select * from t1 nologging;

    已创建10行。

    执行计划
    ———————————————————-
    Plan hash value: 3617692013

    ————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————–
    | 0 | INSERT STATEMENT | | 11402 | 946K| 40 (0)| 00:00:01 |
    | 1 | TABLE ACCESS FULL| T1 | 11402 | 946K| 40 (0)| 00:00:01 |
    ————————————————————————–

    统计信息
    ———————————————————-
    1 recursive calls
    1 db block gets
    7 consistent gets
    0 physical reads
    1100 redo size
    909 bytes sent via SQL*Net to client
    954 bytes received via SQL*Net from client
    4 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    10 rows processed
    看起来这应该是autotrace的问题,准确的结果应该是看v$sesstat里面的redosize统计值。

  2. 我启用了2个会话,第一个会话sid:133,且使用nologging,redosize
    为1856;
    SQL> insert into empt select * from scott.emp nologging;

    已创建14行。

    SQL> select * from v$sesstat t where t.STATISTIC#=134 and sid=133;

    SID STATISTIC# VALUE
    ———- ———- ———-
    133 134 1856

    SQL> insert into empt select * from scott.emp nologging;

    已创建14行。

    SQL> select * from v$sesstat t where t.STATISTIC#=134 and sid=133;

    SID STATISTIC# VALUE
    ———- ———- ———-
    133 134 1856

    第一个会话sid:143,且没使用nologging,redosize
    为0;
    SQL> insert into empt select * from scott.emp;

    已创建14行。

    SQL> select * from v$sesstat t where t.STATISTIC#=134 and sid=143;

    SID STATISTIC# VALUE
    ———- ———- ———-
    143 134 0

    SQL> insert into empt select * from scott.emp;

    已创建14行。

    SQL> select * from v$sesstat t where t.STATISTIC#=134 and sid=143;

    SID STATISTIC# VALUE
    ———- ———- ———-
    143 134 0
    为什么啊?难道说明sql语句加上nologging反而是累赘?

  3. hi wangxy, 老熊

    我想跟nologging这个关键字无关, 可能是In Memory undo(IMU)的作用, 可以观察一下IMU的使用情况, 还有会话的redo size, redo entries, IMU Flush. 刚刚写了一篇博客, 见 http://sid.gd/in-memory-undo/

    老熊 Reply:

    @Sidney, 有时间我研究一下这个问题。

  4. yangang_q @ 2012-04-12 19:30

    有结果了没,老熊

  5. netbanker @ 2013-05-07 08:09

Add your comment now