Hint的常见错误使用方式

60 阅读 0 评论 40 点赞

我是靠谱客的博主迷路期待，最近开发中收集的这篇文章主要介绍Hint的常见错误使用方式，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

转载于：http://www.laoxiong.net/common-incorrect-using-hints.html

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

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

1. NOLOGGING的不正确使用。

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

 
    view plaincopy to clipboardprint? 
   
 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的一些正确用法：

 
    view plaincopy to clipboardprint? 
   
 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操作中，只有下面一种方式能够在大数据量时仍然只会产生极少量的日志：

 
    view plaincopy to clipboardprint? 
   
 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会生效：

 
    view plaincopy to clipboardprint? 
   
 INSERT /*+ append,parallel(t1) */ INTO T1 SELECT * FROM T2;  
 INSERT /*+ parallel(t1), append */ INTO T1 SELECT * FROM T2;  
 INSERT /*+ this is append */ INTO T1 SELECT * FROM T2;  
 INSERT /*+ this append */ INTO T1 SELECT * FROM T2;  

要回答这个问题，请先看下面的测试（测试环境：10.2.0.1 for Windows）：

 
    view plaincopy to clipboardprint? 
   
 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也是不起作用的：

 
    view plaincopy to clipboardprint? 
   
 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.

« DRM引起的问题解决一例

在存储过程中的clob数据类型 »

6 Responses to “Hint的常见错误使用方式”

bamboo Says:
1月 10th, 2012 at 9:00 下午
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月 10th, 2012 at 10:22 下午

的确是个很奇怪的现象。下面是我测试的结果，第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统计值。
wangxy Says:
2月 13th, 2012 at 2:35 下午
我启用了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反而是累赘？