SQL优化误用'append'案例一则

编辑手记:SQL是数据库系统的核心,因SQL问题引发的系统蝴蝶效应屡见不鲜,今天继续学习SQL优化的技巧

这是某客户关键系统的一个TOP SQL:

根据下图sqlhc获取的信息,该SQL平均每次插入不到一条记录,每半小时执行10万次左右。

为什么cpu时间消耗很少,大部分等待时间是application等待?

sqlhc里面也有交待:

这些wait class是application的具体等待事件是enq:TM - contention,也就是表锁。在AWR报告的TOP 10事件中,也出现了这个事件,而且占DB time的4.6%,可见一个SQL就对系统造成了较大的影响:

这个表锁是如何生成的?罪魁祸首就是SQL中使用的append Hint。

append的Hint一般使用在insert select语句,插入大量结果集的时候,采用直接路径(direct path)在表的高水位线以上直接写入数据。在没有commit之前,sql会一直持有表锁。这个Hint在数据仓库的SQL中使用较多,一次插入记录几十万以上,执行频率低。

但是,在本例OLTP系统中,频繁执行而且插入少量记录的SQL也使用了append的hint,造成的后果就是:

1、sql执行效率低,大量的表锁等待,并发越多等待越严重。

2、插入的表TF_B_OCS_BATDEAL,有大量的空间被浪费,每插入一条记录都会占用一个block。而且即使有大量记录被delete,这些高水位线以下的空闲块不会被重新使用。

解决方法

这种频繁执行,每次插入少量记录的情况,不能使用append,必须马上去掉这个hint。

补充: 并行DML开启时,默认启用append插入模式。

insert /*+ parallel(4) */ into xxx select ....

这个SQL默认并没有开启并行DML,只是后面的select部分使用了并行,这种情况没有使用append。

alter session enable parallel dml; --启用并行DML

insert /*+ parallel(4) */ into xxx select

这时就启用了并行DML,不加append的hint,默认也是append插入模式。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-05-15

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏北京马哥教育

MySQL 超级入门教程(内含资源福利)

运维行业正在变革,推荐阅读:30万年薪Linux运维工程师成长魔法 MySQL简介 1、什么是数据库 ? 数据库(Database)是按照数据结构来组织、存储...

6618
来自专栏阿杜的世界

【转】Innodb中的事务隔离级别和锁的关系一次封锁or两段锁?事务中的加锁方式参考资料

因为有大量的并发访问,为了预防死锁,一般应用中推荐使用一次封锁法,就是在方法的开始阶段,已经预先知道会用到哪些数据,然后全部锁住,在方法运行之后,再全部解锁。这...

853
来自专栏性能与架构

HBase 的表结构

HBase 是一个NoSQL数据库,用于处理海量数据,可以支持10亿行百万列的大表,下面就了解一下数据是如何存放在HBase表中的 关系型数据库的表结构 为了更...

38713
来自专栏Java架构师历程

Mysql面试题目

5>.InnoDB不支持全文索引,而MyISAM支持。(X) (2) 问各种不同mysql版本的2者的改进 (3)2者的索引的实现方式

1743
来自专栏个人随笔

MySQL 关于存储过程那点事

存储例程是存储在数据库服务器中的一组sql语句,通过在查询中调用一个指定的名称来执行这些sql语句命令. 简介 SQL语句需要先编译然后执行,而存储过程(Sto...

4348
来自专栏深度学习之tensorflow实战篇

mysql几种存储引擎介绍

前言 在数据库中存的就是一张张有着千丝万缕关系的表,所以表设计的好坏,将直接影响着整个数据库。而在设计表的时候,我们都会关注一个问题,使用什么存储引擎。等一下...

2844
来自专栏Java架构师历程

sql必会基础3

对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加索引的话,那么查...

1162
来自专栏Java3y

Oracle总结【视图、索引、事务、用户权限、批量操作】

前言 在Oracle总结的第一篇中,我们已经总结了一些常用的SQL相关的知识点了…那么本篇主要总结关于Oralce视图、序列、事务的一些内容… 在数据库中,我们...

3744
来自专栏小怪聊职场

MySQL(一)|性能分析方法、SQL性能优化和MySQL内部配置优化

6509
来自专栏idba

漫谈死锁

一 前言 死锁是每个MySQL DBA 都会遇到的技术问题,本文是自己针对死锁学习的一个总结,了解死锁是什么,MySQL如何检测死锁,处理死锁,死锁的案例,...

1264

扫码关注云+社区