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 条评论
登录 后参与评论

相关文章

来自专栏北京马哥教育

Linux 组调度浅析

cgroup 与组调度 linux内核实现了control group功能(cgroup,since linux 2.6.24),可以支持将进程分组,然后按组来...

2774
来自专栏jeremy的技术点滴

InnoDB与MyISAM的区别

2535
来自专栏技术博文

从MyISAM转到InnoDB需要注意什么

转自 MySql中文网 http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=200910426&idx=1...

33514
来自专栏云成本管理

云成本管理方法论(三)——云优化管理之判定规则

云优化管理四个管理维度中管理时点在通用管理模型基础上不需要额外补充,所以主要说明其他三个维度(管理对象、判定规则和管理措施)。另外,为了贴近我们熟悉的优化概念,...

46419
来自专栏Golang语言社区

几种服务器端IO模型的简单介绍及实现(上)

一些概念: 同步和异步 同步和异步是针对应用程序和内核的交互而言的,同步指的是用户进程触发I/O操作并等待或者轮询的去查看I/O操作是否就绪,而异步是指用户进程...

3327
来自专栏程序猿

MySQL优化方案(一)优化SQL脚本与索引

MySQL的优化方案有哪一些? 本文记录MySQL优化方案 ,梗概如下: 优化SQL 优化索引 (一)优化SQL 1、通过MySQL自有的优化语句 优化SQL语...

3797
来自专栏LanceToBigData

MySQL(十一)之触发器

上一篇介绍的是比较简单的视图,其实用起来是相对比较简单的,以后有什么更多的关于视图的用法,到时候在自己补充。接下来让我们一起了解一下触发器的使用! 一、触发器概...

1948
来自专栏纯洁的微笑

MySQL 大表优化方案

除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑、部署、运维的各种复杂度,一般以整型值为主的表在 千万级以下,字符串为主的表在 五百万以...

874
来自专栏枕边书

PHP中的数据库一、MySQL优化策略综述

前些天看到一篇文章说到PHP的瓶颈很多情况下不在PHP自身,而在于数据库。我们都知道,PHP开发中,数据的增删改查是核心。为了提升PHP的运行效率,程序员不光需...

2138
来自专栏python3

python3--进程

进程的概念起源于操作系统,是操作系统最核心的概念,也是操作系统提供的最古老也是最重要的抽象概念之一。操作系统的其他所有内容都是围绕进程的概念展开的

492

扫描关注云+社区