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

提起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

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。

innodb_log_file_size设置 - 2016-03-08 14:03:54

innodb_log_file_size的大小设置将会影响MySQL数据库的写入性能,若设置的太小, 会增加checkpoint写的次数,以下测试在MySQL5.7版本中的如何修改innodb_log_file_size [root@endb local]# mysql Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.9-log MySQL

Oracle 12c PDB浅析(二) - 2016-03-08 14:03:50

之前写了第一篇Oracle 12c PDB浅析 http://blog.itpub.net/23718752/viewspace-1823792/?          在上次的基础上继续来学习学习。     首先关于多租户的架构设计来说,就好比在一座已经几十年的老房子上动地基一般,这个变化着实够大,如此重大的变化Oracle不遗余力的想引入进来,肯定有更深层次的原因,当然关于这种设计在SQLServer中确实已经早有实现,在Oracle中却被大家相传为一种略带神奇的架构设计。不过话说回来,这个和Oracle

cdb与pdb的一些常用查询命令 - 2016-03-07 14:03:13

[oracle@rhel59 ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 5 19:57:04 2016Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the

sql语句大全 - 2016-03-05 14:03:18

SQL语句大全 一、创建和删除数据库 1、创建用户 //创建用户且置密码,在MySQL中行,但在Oracle中行  ----必须在超级管理员身份下操作 create user hncu identified by '1234' 2、创建数据库 //创建数据库 并手动指定编码格式 //错,因为根据手册的查询,数据库名应该在 EXISTS后面 CREATE DATABASE hncu IF NOT EXISTS DEFAULT CHARACTER SET 'utf8'; //改正: CREATE DATABA
1.客户端使用plsqldeveloper登录,直接输入用户名密码即可 2.服务端登录,使用sqlplus,比如:密码123@123,用户名123 1)CMD-sqlplus 123/\"123@123\" 2)CMD-sqlplus /nolog - conn 123/"123@123"
我们有一个开发库,默认表空间是TEST_TBS,但今天查看开发库的时候,发现有些表和字段并不在用户默认使用的表空间中,而在USERS表空间,之所以可能是之前开发人员执行SQL是从其他库复制过来的,连通tablespace USERS名称一块复制了,为了规范,就需要将这些对象转移下表空间,期间碰见了几个常见的小问题,值得记录一下。 问题1:新建的一张表,为什么dba_segments视图中没有找到对应的表和索引记录 ? 实验: 创建一张表和一个索引: CREATE TABLE tbl_tbl(ID NUMB
MyBatis 实践 标签: Java与存储 动态SQL 动态SQL提供了对SQL语句的灵活操作,通过表达式进行判断,对SQL进行拼接/组装. if 对查询条件进行判断,如果输入参数不为空才进行查询条件的拼接. mapper select id= "selectUser" resultType= "com.fq.domain.User" parameterType= "com.fq.domain.User" SELECT * FROM user where if test= "id != null" AN

MyBatis 实践 -Mapper与DAO - 2016-03-04 14:03:30

MyBatis 实践 标签: Java与存储 MyBatis简介 MyBatis 前身是 iBatis ,是一个基于Java的 数据持久层/对象关系映射(ORM)框架 . MyBatis是对JDBC的封装,使开发人员只需关注SQL本身,而不需花费过多的精力去处理如 注册驱动 、 设置参数 、 创建 Connection / Statement 、 解析结果集 等JDBC过程性代码.MyBatis基于XML/注解的方式配置 Statement ,执行SQL,并将执行结果映射成Java对象, 大大降低了数据库

MyBatis 实践 -配置 - 2016-03-04 14:03:39

MyBatis 实践 标签: Java与存储 Configuration mybatis-configuration.xml 是MyBatis的全局配置文件(文件名任意),其配置内容和顺序如下: properties : 属性(文件)加载/配置 settings : 全局配置参数 typeAliases : 定义类型别名 typeHandlers : 类型处理器 objectFactory : 对象工厂 plugins : 插件 environments : 环境集合属性对象 environment tr
    在oracle 11g中,data guard的快照备用snapshot standby数据库特性比较适用于快速部署一个临时的与线上环境相同的测试数据库,建置方法可参考:http://blog.itpub.net/28539951/viewspace-1767427/.最近在使用过程中发现快照备用snapshot standby数据库的SCHEDULER JOBS没有执行,并且在DBA_SCHEDULER_JOBS中也查不到SCHEDULER JOB的信息.      通过参考文档Jobs are