Hint的常见错误使用方式.pdf

百分

128

10页

0次

2024-02-23

免费下载

Hint 的常见错误使用方式

提起 Oracle 数据库的 Hint，几乎每一个 DBA 都知道这一强大工具。在 Oracle 中，Hint 可以

用来改变 SQL 的执行计划、固定 SQL 的执行计划。Oracle 数据库内部的很多特性也依赖于

Hint，比如 Outline、Profile 等。

但是在日常工作中，很多开发人员或 DBA，对 Hint 的使用仍然存在一些错误的方式。下面

将列举主要的 2 种。（本文不讨论 Hint 的滥用即过度使用问题）。

1. NOLOGGING 的不正确使用。

很多人知道，在进行数据处理时，如果不产生日志或只产生少量的日志，将会有明显的、甚

至是巨大的效率提升。下面有几条不同的 SQL：

view plain copy to clipboard print ?

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 plain copy to clipboard print ?

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 plain copy to clipboard print ?

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 plain copy to clipboard print ?

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）：

view plain copy to clipboard print ?

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 plain copy to clipboard print ?

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.

hint

Address: http://www.laoxiong.net/common-incorrect-using-hints.html

? DRM 引起的问题解决一例

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

Trackback

7 comments untill now

bamboo @ 2012-01-10 21:00

INSERT INTO T1 SELECT * FROM T2 NOLOGGING;

这个 sql 语句是不会产生 redo 日志，以下是我做的实验

（1）这个 sql 是用 nologging，在执行计划中，redosize 是 0

of 10

免费下载

hint常见错误

关注

评论