ITPub博客

首页 > 数据库 > Oracle > 【HINT】Hint的常见错误使用方式(nologging)

【HINT】Hint的常见错误使用方式(nologging)

Oracle 作者:zhaohuan5116 时间:2016-03-10 23:05:41 0 删除 编辑

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

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

1. NOLOGGING的不正确使用。

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

  1. INSERT INTO T1 NOLOGGING;  
  2. INSERT INTO T1 SELECT * FROM T2 NOLOGGING;  
  3. INSERT /*+ NOLOGGING */ INTO T1 VALUES ('0');  
  4. INSERT /*+ NOLOGGING */ INTO T1 SELECT * FROM T2;  
  5. DELETE /*+ NOLOGGING */ FROM T1;  
  6. UPDATE /*+ NOLOGGING */ T1 SET A='1';  

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

  1. CREATE TABLE T1 NOLOGGING AS SELECT * FROM T2;  
  2. CREATE INDEX T1_IDX ON T1(A) NOLOGGING;  
  3. ALTER INDEX T1_IDX REDUILD ONLINE NOLOGGING;  
  4. ALTER TABLE T1 NOLOGGING;  

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

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

  1. 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. 1. INSERT /*+ append,parallel(t1) */ INTO T1 SELECT * FROM T2;  
  2. 2. INSERT /*+ parallel(t1), append */ INTO T1 SELECT * FROM T2;  
  3. 3. INSERT /*+ this is append */ INTO T1 SELECT * FROM T2;  
  4. 4. INSERT /*+ this append */ INTO T1 SELECT * FROM T2;  

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

  1. SQL> INSERT /*+ append,parallel(t1) */ INTO T1 SELECT * FROM T2;  
  2. 已创建55640行。  
  3. 统计信息  
  4. ----------------------------------------------------------  
  5.       12304  redo size  
  6. SQL> COMMIT;  
  7.   
  8. SQL> INSERT /*+ parallel(t1), append */ INTO T1 SELECT * FROM T2;  
  9. 已创建55640行。  
  10. 统计信息  
  11. ----------------------------------------------------------  
  12.     5739584  redo size  
  13. SQL> COMMIT;  
  14.   
  15. SQL> INSERT /*+ this is append */ INTO T1 SELECT * FROM T2;  
  16. 已创建55640行。  
  17. 统计信息  
  18. ----------------------------------------------------------  
  19.     5746604  redo size  
  20. SQL> COMMIT;  
  21.   
  22. SQL> INSERT /*+ this append */ INTO T1 SELECT * FROM T2;  
  23. 已创建55640行。  
  24. 统计信息  
  25. ----------------------------------------------------------  
  26.       12052  redo size  
  27. 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也是不起作用的:

  1. 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) */


原地址:http://www.laoxiong.net/common-incorrect-using-hints.html

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

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

注册时间:2016-01-24

  • 博文量
    5
  • 访问量
    8253