【重磅推荐】从Library Cache等待事件深入剖析SQL解析

编辑手记:在很多生产系统中,程序员经意不经意写下的一条SQL都可能带来性能上的巨大隐患,而DBA就要不断在这些问题中出生入死,这些不正确的SQL可能给我们带来哪些麻烦,如何识别和处理,本文将通过真实案例的分析深入解答。

作者介绍:李华 云和恩墨高级技术顾问

错误的SQL可能对数据库产生的种种影响。如何找到这些错误的、解析失败的SQL呢?我们先把方法列举在这里:

  1. 通过关联 x$kglcursor x$kglcursor_child_sqlid 视图;
  2. 通过使用 Oracle 10035 Event 事件可以找到解析失败的SQL;
  3. 通过 oracle systemdump 也可以找到解析失败 SQL;

接下来我们通过一个真实的案例来分析SQL的内存结构和SQL的解析过程,以及SQL解析失败的原因和处理方式。

案例背景描述:

客户的一套重要生产系统,出现了性能问题。这个问题涉及的信息如下:

月底时候数据库主机的 CPU 利用率长期在100%左右。

数据库中出现大量的 latch: library cache 竞争

系统概况


该系统为 OLAP OLTP 混合系统,平时为交易型数据库。每个网点实时数据上传,月底会有统计类报表产生。

以下为数据库负载曲线,可以看到在月底复杂急剧上升,导致业务不能正常操作。

以下为故障时间点部分 AWR 截图。

从 LOAD PROFILE 看当前数据库每秒有158次的硬解析,总的解析在1082次。

这个时间点的 TOP 5 等待事件中 latch: library cache 与 kksfbc child completion 排在前列,library cachelatch 占到将近有 70%。

Latch: Oracle 用于控制内存并发的串行锁机制

共享池 latch 竞争一般导致的原因有以下集中:

  1. literal SQL 所谓的 literalSQL 就是没用使用绑定变量值的 SQL 比如 select * from enmo where id=100;
  2. 硬解析比如一个新执行的 SQL 没有在共享池中,那么就要经历一个硬解析的过程,关于过程这里就不在多讲
  3. SQL 不能共享,不能共享的原因有很多比如没有在同一个用户下面执行
  4. SQL VERSION 大量高版本 SQL 也会导致共享池的竞争
  5. 另外就是主机出现大量换页,比如在 AIX 环境下大量计算内存使用了 SWAP 会导致类似的问题
  6. 还有就是查询一些底层的视图比如 x$ksmsp 在某些版本下高并发的系统中直接查询这些视图会出现大量的 latch 竞争
  7. 还有就是 SGA 大量抖动或者模拟调整的时候也会导致此问题
  8. Oracle 各个版本上也存在相关的 BUG 会导致

根据以上几点我们去分析到底此问题出现在什么地方。

首先数据库等待事件除了 library cache latch 之后就是 kksfbc

K[Kernel]K[Kompile]S[Shared]F[Find]B[Best]C[Child]

该函数用以在软解析时找寻合适的子游标,是否该故障是由于大量 VERSION COUNT 引起呢?

从这个时间点 AWR 来看没有看到大量 version count 的SQL出现。

分析 latch 的时候 AWR 有一个非常重要的数据。

从 Latch Miss Source 的数据可以看到,绝大多数都是对于 shared pool latch 的 sleeps

从 AWR Sleep 来看 shared pool 排在了第一位。从调用的函数来看都是发生在硬解析这个过程中。

以下为一些常见函数的功能:

Kghfrunp: KGH: Ask client to freeunpinned space Kghdmp : x$ksmsp is a fixed table based onkgh metadata. The number of latch sleeps for "kghdmp" will increaseif x$ksmsp if an installation selectsfrom this fixed table too frequently. kghupr1 : un-pin recreatable kghalo KGH: main allocation entry point kghgex KGH: Get a new extent kghalf KGH: Non-recoverably allocate afreeable chunk of memory

有很多函数这里就不一一列举。

当前现在也可以排除人为查询底层视图导致的 latch 竞争因为没有看到相关函数出现,插播一个类似的案例。

像这种情况很明显就是有人查询了底层的视图导致的 shared pool 竞争。

从主机最早的信息来看也是没有 SWAP 竞争出现的。

SGA 没有大量的 resize 也可以排除掉由于 SGA 组件抖动引起的。

从以上信息,我们没有找到想要的结果,那么问题出现在哪里。

把上面几个原因都排除掉了,难道真是遇到 Oracle BUG 了么。

有的时候分析问题会陷入一些误区,比如一个数据库出现大量的 latch 竞争导致会话飙升然后把 process 撑满,从 time mode 里面来看的话可以发现 95%都是花费在了连接上面,那么到底是大量不正常的连接(比如连接泄漏)导致了数据库出现竞争呢,还是数据库出现问题导致会话不能等了然后不停的重连导致了问题呢。

从这个库这个时间点的 time mode 可以发现 75%的 db time 都是花费在了解析上面,这也是没有问题的因为这个时间点数据库竞争就出现在解析上面,但是为什么其中有 38%的 db time 发生在解析失败上面呢,也就是总共解析的一般时间都是错误的解析。硬解析只有5%左右。

我们来看一张正常时间点的 time mode 。

从这个趋势图库看到解析失败一直是跟着硬解析的次数而增加,并且每天都在上班之后开始发生。

数据库正常时间点硬解析也只有不到 5%左右,也就是硬解析没有大的变化,但是解析失败确认翻了几倍。是什么原因导致这么多的解析失败呢?另外解析失败的 SQL 是否会导致大量 latch 竞争?解析失败的 SQL 是否会在共享池中存储?怎么查询到解析失败的 SQL?

很多时候我们会有这样一个误区,既然语法错误或者对象不存在应该在语法语义检查这个步骤就挂了怎么还好存在共享吃里面呢?带着这个几个问题我们做几个简单的测试。

我们先了解下什么是解析失败的 SQL。

那么怎么证明就是解析失败的 SQL 存在共享池中并且在解析的时候持有 library cache latch 呢?

做下面测试之前我们先回顾一个 Oracle 一些基本概念。

Library cache 是 shared pool 中的一块内存区域,主要作用就是缓存执行过的 SQL 语句所对应的执行计划信息等信息。当同样的 SQL 再次执行时候可以直接利用已经缓存的相关对象不需要再从头解析。

Library cache 对象句柄是以 hashtable 的方式存储的,存储方式如下图:

当 sql 执行时候,首先会对 sql 文本进行 hash 运算然后根据 hash 值去相关 hash bucket 中遍历,如果找到了就直接用该 sql 缓存的执行计划等,如果找不到则从头解析,并把解析后执行计划等缓存在 hash bucket 中。

下面这几张图片展示了一个 SQL 解析的过程。

SQL 的内存结构

我们知道 SQL 语句必须至少是一个父游标一个子游标存在的,当然生产中很多情况下都是一父多子的情况。

父游标与子游标结构是一样的,区别在于 sql 文本存储在父游标对应的对象句柄中,而 sql 的执行计划等信息存储在子游标对应的库缓存对象句柄 heap 6 中。另外父游标的 heap 0 中存储着子游标的句柄地址。如果解析错误的 SQL 在共享池中存储的话那么必然要产生一个父游标然后父游标里面存储的有 SQL 文本之类的信息,但是子游标的?既然解析失败那么就没有产生执行计划。

关于 heap 0 中信息可以参考如下图:

父游标句柄对地址可以在 x$kglob 视图中查询到,KGLHDPAR=KGLHDADR 的记录为父游标

X$KGLOB

该视图定义为 [K]ernel[G]eneric [L]ibrary Cache Manager

KGLHDADR RAW(4|8) Address of kglhd for this object

该地址 000007FF11937C90 为 select * from enmo SQL 的父游标的句柄地址。

可以看到:

KGLOBHD0 RAW(4|8) Address of heap 0 descriptor KGLOBHD6 RAW(4|8) Address of heap 6 descriptor

上面查到的就是该 SQL 父游标的信息,父游标的 kglobhd0 的地址为 0000000075489AE8

该句柄地址记录的信息很多包含了子游标的信息。

找下该 SQL 子游标的信息:

子游标 heap 6 的地址为 000000007625FBF8 句柄中存储的也就是执行计划相关的信息。

通过以上测试我们很容易找到 sql 的父游标的句柄还有子游标的句柄在内存中的地址。

下面做另外一个简单的测试解析错误的 SQL 是否有父游标还有子游标生成。

可以看到是可以查询到信息的,也就是有父游标的句柄为 00000000754453B8 heap 0 的地址为 0000000075485620.

可以看到是有错误的文本信息的内存地址,但是子游标呢?

可以看到是没有子游标产生的,因为该 SQL 执行错误不会有执行计划相关信息出现。

从 x$kglob 也可以查到 kglobhd0 kglobhd6 都为空。

在 x$kglcursor_child 视图也查不到任何信息的,v$sql v$sqlare 类似的视图也就查不到解析错误的 SQL 了。

关于解析错误的 SQL 是否需要获取 latch 其实从上面的测试已经证明了还是要获取 shared pool 的 latch 的因为生成了父游标。

回顾一下,SQL 硬解析过程中需要获取的latch.

首先持有 library cache lath,在 library cache 相关 hash bucket 中扫描已经缓存的对象句柄,查找是否有匹配的父游标,没有找到释放 library cache latch.

接着持有 library cache latch 然后不释放情况下持有 shared pool latch 从 shared pool 中申请分配内存成功后是否 shared pool latch 再是否 library cache latch.

还以上面那个错误的 SQL为例做一个简单的测试。

首先获取 library cache latch 然后运行 sql 查询。

这个时候会话已经 hang 了。

怎么找到解析失败的 SQL?

  1. 通过关联 x$kglcursor x$kglcursor_child_sqlid 这两个视图是可以找到解析失败的 SQL
  2. 通过使用 Oracle 10035 event 事件也是可以找到解析失败的SQL
  3. 通过 oracle systemdump 也可以找到解析失败 SQL

当然最后该问题定位到了相关解析失败的 SQL,该 SQL 主要是在月底某一模块批量跑的时候大量的执行,最后修改应用程序代码解决了问题。

通过这个简单的案例可以看到不规范的开发习惯给数据库带了严重的性能影响。像类似这种解析出错的 SQL 在很多客户核心系统中比比皆是但是由于种种原因不能及时去除类似的 SQL 最终将带来灾难性的影响。

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

原文发表时间:2017-07-07

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏玄魂工作室

Hacker基础之Linux篇:进阶Linux命令二

发音类似<砰>,对黑客而言,这就是成功实施黑客攻击的声音,砰的一声,被<黑>的电脑或手机就被你操纵了

1072
来自专栏互联网杂技

总结Web应用中常用的各种Cache

cache是提高应用性能重要的一个环节,写篇文章总结一下用过的各种对于动态内容的cache。 文章以Nginx,Rails,Mysql,Redis作为例子,换...

3444
来自专栏喔家ArchiSelf

老曹眼中的缓存技术

缓存是系统快速响应中的一种关键技术,是一组被保存起来以备将来使用的东西,介于应用开发和系统开发之间,是产品经理们经常顾及不到的地方,算是技术架构中的非功能性约束...

862
来自专栏大前端开发

从编程小白到全栈开发:寻找代码中的问题

很少有人能一下子就写出完全没有问题的代码。工作良好的程序,都是经过一遍遍的反复测试运行、发现问题、剔除问题(也就是我们所说的找Bug和修Bug)过后的产物,经过...

823
来自专栏Java面试通关手册

关于MySQL的知识点与面试常见问题都在这里

我自己总结的Java学习的一些知识点以及面试问题,目前已经开源,会一直完善下去,欢迎建议和指导欢迎Star: https://github.com/Snailc...

1673
来自专栏轮子工厂

关于操作系统的一些事,这些你应该要知道~

894
来自专栏北京马哥教育

Linux 基础快速入门教程:全栈必备基础知识

Linux 几乎无处不在,不论是服务器构建,还是客户端开发,操作系统的基础技能对全栈来说都是必备的。

930
来自专栏程序员宝库

Golang 大杀器之性能剖析 PProf

想要进行性能优化,首先瞩目在 Go 自身提供的工具链来作为分析依据,本文将带你学习、使用 Go 后花园,涉及如下:

793
来自专栏web前端教室

Vue2.0,lifeCycle ['laɪfˌsaɪkl] -- 生命周期大白话~

生命周期,这词太屌了,头一次在前端相关文章中看到这个词的时候,我真是被唬住了。心里想,这前端还跟生命周期搞一块了,是不是还带转生投胎啊,跪着看了一半,我就站起来...

2128
来自专栏沈唁志

Windows下iPython的安装与报错解决方法

1393

扫码关注云+社区