深入解析：由SQL解析失败看开发与DBA的性能之争

深入解析：由SQL解析失败看开发与DBA的性能之争

李华

云和恩墨高级技术顾问

编者注：在很多生产系统中，程序员经意不经意写下的一条SQL都可能带来性能上的巨大隐患，正确的、不正确的。而DBA就要不断在这些问题中出生入死，本案例描述的那些不正确的SQL可能给我们带来的麻烦，而这类错误SQL往往为大家所忽视。这样的问题在最近的客户案例中不断涌现，在12c中同样为我们带来麻烦，而很多DBA可能并不了解，转引这篇文章与大家为警示。

以下案例来自大讲堂的一次分享，从这个案例中我们可以了解“错误的SQL”可能对数据库产生的种种影响。如何找到这些错误的、解析失败的SQL呢？我们先把方法列举在这里：

  1. 通过关联 x$kglcursor x$kglcursor_child_sqlid  视图；
  2. 通过使用 Oracle 10035 Event 事件可以找到解析失败的SQL；
  3. 通过 oracle systemdump 也可以找到解析失败 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 。

 

 

                   

                     

        

 

 

 

 

 

 

  1.   

 

    
时间： 2024-08-01 17:50:21

深入解析：由SQL解析失败看开发与DBA的性能之争的相关文章

Oracle中的游标、硬解析、软解析、软软解析、解析失败

Oracle中的游标.硬解析.软解析.软软解析.解析失败 一. 游标的分类及共享游标 游标(Cursor)是Oracle数据库中SQL解析和执行的载体,它可以分为共享游标(Shared Cursor)和会话游标(Session Cursor).共享游标可以细分为父游标(Parent Cursor)和子游标(Child Cursor),可以通过视图V$SQLAREA和V$SQL来查看当前缓存在库缓存(Library Cache)中的父游标和子游标,其中V$SQLAREA用于查看父游标,V$SQL用

Oracle SQL的硬解析和软解析

我们都知道在Oracle中每条SQL语句在执行之前都需要经过解析,这里面又分为软解析和硬解析.在Oracle中存在两种类型的SQL语句,一类为 DDL语句(数据定义语言),他们是从来不会共享使用的,也就是每次执行都需要进行硬解析.还有一类就是DML语句(数据操纵语言),他们会根据情况选择要么进行硬解析,要么进行软解析. DML:INSERT,UPDATE,DELETE,SELECT DDL:CREATE,DROP,ALTER 一.QL 解析过程 Oracle对此SQL将进行几个步骤的处理过程:

SQL解析过程详解

作者:一帅 简介 SQL任务是ODPS中使用最频繁的一类作业,大部分用户开始使用ODPS时要做的第一件事情就是学习怎么写ODPS的SQL.ODPS SQL是一种非常灵活的语言,兼容大部分的SQL92规范,也对大规模计算场景做了一些特别的定制.有些用户写出的SQL让人看了之后茅塞顿开的感觉,也有一些神级用户经常写一些1000多行的SQL,让人看的只想撞墙.本文会介绍一下SQL是如何分析解析,并拆解成分布式飞天任务的一些实现原理. ps.由于一些历史包袱和工程实现的原因,ODPS某些内部实现细节可能

SqlParser 一个利用正则表达式解析单句SQL的类_正则表达式

先看要解析的样例SQL语句: 复制代码 代码如下: select * from dual SELECT * frOm dual Select C1,c2 From tb select c1,c2 from tb select count(*) from t1 select c1,c2,c3 from t1 where condi1=1 Select c1,c2,c3 From t1 Where condi1=1 select c1,c2,c3 from t1,t2 where condi3=3

SQL 软解析和硬解析详解

当客户端进程,将SQL语句通过监听器发送到Oracle时, 会触发一个Server process生成,来对该客户进程服务.Server process得到SQL语句之后,对SQL语句进行Hash运算,然后根据Hash值到library cache中查找,如果存在,则直接将library cache中的缓存的执行计划拿来执行,最后将执行结果返回该客户端,这种SQL解析叫做软解析:如果不存在,则会对该SQL进行解析parse,然后执行,返回结果,这种SQL解析叫做硬解析. 1.硬解析的步骤硬解析一

Sharding-JDBC 1.5.0.M1 正式发布，全新的 SQL 解析引擎

经过了长达几个月的紧张开发,Sharding-JDBC 1.5.0.M1终于正式发布.Sharding-JDBC 1.5.0.M1版本是一次里程碑式升级,工作量巨大,Sharding-JDBC截止到1.4.2之前所有的提交次数为385次,而1.5.0.M1一个版本的提交次数为517次. Sharding-JDBC从这个版本开始明确定位为"水平扩展以及inline事务数据库中间件",将全力专注于OLTP以及内联事务的处理. 本次里程碑版本的主要更新是: 全新的SQL解析引擎,去掉了对Dr

简单对比MySQL和Oracle中的一个sql解析细节

SQL的语法解析器是一个很强大的内置工具集,里面会涉及到很多的编译原理的相关知识,语法分析,词法分析..一大堆看起来很理论的东东,不过看起来枯燥之余,它们的价值也更加明显. 借用一下网络中的原话:如果我们考究一下历史,就会发现很多被称为程序设计大师的人都是编译领域的高手.写出第一个微型机上运行的Basic语言的比尔盖茨,设计出Delphi的Borland的"世界上最厉害的程序员", Sun的JAVA之父, 贝尔实验室的C++之父 起点提得有些高了,今天和大家分享的案例是一个很简单的sq

SqlParser 一个利用正则表达式解析单句SQL的类

先看要解析的样例SQL语句: 复制代码 代码如下: select * from dual SELECT * frOm dual Select C1,c2 From tb select c1,c2 from tb select count(*) from t1 select c1,c2,c3 from t1 where condi1=1 Select c1,c2,c3 From t1 Where condi1=1 select c1,c2,c3 from t1,t2 where condi3=3

自己实现一个SQL解析引擎

自己实现一个SQL解析引擎 功能:将用户输入的SQL语句序列转换为一个可执行的操作序列,并返回查询的结果集. SQL的解析引擎包括查询编译与查询优化和查询的运行,主要包括3个步骤: 查询分析: 制定逻辑查询计划(优化相关) 制定物理查询计划(优化相关) 查询分析: 将SQL语句表示成某种有用的语法树. 制定逻辑查询计划: 把语法树转换成一个关系代数表达式或者类似的结构,这个结构通常称作逻辑计划. 制定物理查询计划:把逻辑计划转换成物理查询计划,要求指定操作执行的顺序,每一步使用的算法,操作之间的