性能分析:Oracle的CLOB使用与临时段访问及其性能优化

编辑手记:在系统测试、上线和优化的过程中,抓住核心环节、不放过任何可疑,这是DBA的基本要求之一,在这个案例中,高频度调用的存储过程引起了注意。

客户新上线的一套重要生产系统,某个存储过程每小时调用约11万次,每次调用的逻辑读超过了10,000,消耗的 CPU 占数据库 CPU Time 的25-30%。很显然,这样一个存储过程是值得优化的。

不幸的是,这个存储过程的业务逻辑很复杂,光是参数就有9个。而存储过程所在的包其代码超过了1万行。通过复查代码的方式,耗时耗力,还不一定能够找出问题。

诊断性能问题,首要的是了解这个存储过程所大概要完成的业务功能,然后通过 trace 或 instrument 收集足够详细的性能数据。从客户和开发商那里了解到,存在性能问题的存储过程,主要用于系统之间的数据查询接口,根据不同的参数输入查询不同的数据,那么,对于这种小数据量的存储过程,可以考虑使用10046事件来分析是哪些 SQL 产生了这么多的逻辑读。

但是通过10046事件,发现存储过程中实际执行的 SQL 并不多,同时并没有逻辑读高的 SQL语句。也许问题并没有出现在 SQL语句中,而是出现在存储过程中其他非 SQL 部分。虽然用10046没有找到 SQL 语句,但还是有重大发现,在过程执行时,大量的逻辑读来自于 current方式的读,这显示不是通常的 SELECT 语句所产生的。接下来我们用 dbms_profiler 来分析存储过程:

然后使用来自 MOS 文档 “Implementing and Using the PL/SQL Profiler [ID 243755.1]”中的 profiler.sql 脚本(通过文末原文链接可以下载这个脚本),生成一个profiler的结果文件,格式为 html。下面是部分的内容:

点击其显示的代码行(line),跳转到相应的源代码,发现大量的字符串拼接代码,很明显是用于拼接成 XML 格式。这很容易理解,因为现在系统之间的文本数据交互,xml 几乎成了标准。从上面的截图中也可以看到很多类型于 xml:=xml || ‘xxxx’ 这样的代码。是这样的代码引起的问题吗?

检查代码发现,这里用于拼接字符串的变量 xml,被定义为 clob 类型,这引起了我极大的关注。在 oracle 的标准数据类型中,lob 类型由于其能够存储大数据的本质,导致其内部格式和操作是最复杂的。有理由怀疑是 clob 的大量拼接引起的问题。

在有怀疑对象后,我们可以构造下面的测试来进行验证。首先创建下面3个不同的存储过程,但是实现的功能是一致的:

第1个存储过程,P1,完全使用 clob 类型来拼接字符串;第2个存储过程,P2,使用 varchar2 类型拼接字符串,只有在过程最后将 varchar2 转成clob;第3个存储过程,P3,先使用varchar2 类型拼接成较长的字符串,然后再用较长的字符串用 clob 类型来拼接。

下面看看P1和P2的差距:

从上面的数据来看,差距是巨大的,执行P1时产生的一致性读(consistent gets)和当前模式读(db block gets),都远远大于执行P2时产生的一致读和当前模式读。特别是大量的 db block gets 验证了之前使用10046事件跟踪存储过程执行得到的结果。

上面的数据中,甚至是会话消耗的内存都有很明显的差异。同时,还有其他对性能显著影响的地方,cache buffers chains latch,cache buffers lru chain latch,object queue header operation latch,这3种 latch 的获取次数是相当多的,在大并发时无疑将引起争用。实际上,在客户的这套系统中,这3种 latch 的争用经常出现,只是目前还没形成特别严重的后果。

再看看P1和P3的差异:

二者的差异同样很明显。

不仅如此,在存储过程中,执行 P1 过程之后,甚至在临时表空间中产生了临时段,而这个临时段是不会自动清除的,经测试,即使没有用于返回结果的 clob 参数,在过程内部生成的 clob 所占的临时段也不会自动清除,只有会话退出才会清除掉。如果更深入跟踪,也许可以发现大量的逻辑读来源于这个临时段。

通过验证,证明clob类型的数据的确是引起客户系统中存储过程大量逻辑读和Latch争用的原因,找到了这个原因,优化就相对简单了

在 PL/SQL 存储过程中,clob 是相当方便的一种数据类型,由于其能够存储超长字符数据的特性,使得在这种用于数据交换的存储过程中用得较多。然而,从上面的数据中,不难发现,如果大量使用 clob 运算,将普通的字符串拼接成 clob,其 CPU 消耗、逻辑读、甚至是latch的获取都是非常高的,对性能影响非常大。实际上在10g中,存储过程中的 varchar2类型,其长度最大可以达到 32767,所以如果返回的结果确保不超过这个长度,完全可以使用 varchar2 类型,只是在返回时再转换为 clob,正如上面的存储过程 P2 所做的那样。如果其长度超过了32767,也可以如存储过程 P3 一样,先将短小的字符串拼接成较大的字符串,然后将较大的字符串拼接到 clob 中。

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

原文发表时间:2016-06-29

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

一个执行计划解析的小问题分析(r9笔记第37天)

前几天,一个开发的同学让我帮忙做一个大查询,给了我一个数据列表,里面的ID有几万个,提供了一个SQL语句,看这情况还得我自己来解析生成相关的SQL了。 假设ID...

2856
来自专栏difcareer的技术笔记

Android智能指针

网上已经有很多分析智能指针的文章了,讲得不错的是:Android系统的智能指针(轻量级指针、强指针和弱指针)的实现原理分析。本文尽量从不分析代码的角度,将And...

574
来自专栏web前端教室

看过必懂!JavaScript的继承

最近刚把Js高级程序设计看了一遍,仔细的读了其中的第6章的第6.3节-继承。今天写出来跟大家分享一下,顺便也加深一下自己的理解。

631
来自专栏老马说编程

计算机程序的思维逻辑 (1)

程序大概是怎么回事 计算机就是个机器,这个机器主要由CPU、内存、硬盘和输入输出设备组成。计算机上跑着操作系统,如Windows或Linux,操作系统上运行着各...

18410
来自专栏程序员互动联盟

【一起学python】print 语句

联盟有个小伙伴,为了督促自己学习进步,决定把自己以前学的python重新梳理下,并且以文章的方式展示出来,联盟专门做一起学python系列专栏,鼓励这位小伙伴学...

3407
来自专栏企鹅号快讯

Java与C/C加加的区别

Java 是由 C++发展而来的,保留了 C++的大部分内容,其编程方式类似于 C++。但 Java 的句法更清晰、规模更小、更易学。Sun 公司对多种程序设计...

1896
来自专栏xingoo, 一个梦想做发明家的程序员

【AngularJS】—— 11 指令的交互

前面基本了解了指令的相关内容:   1 如何自定义指令   2 指令的复用 本篇看一下指令之间如何交互。学习内容来自《慕课网 指令3》 背景介绍 ...

1759
来自专栏web前端教室

vue.js的computed计算属性,表达式的“js另存为”

简单的讲,vue的模板是基于html的,就是html里加模板语法,所以模板里的js注定不能太复杂。如果业务逻辑很复杂,就不能放在模板里了,否则这模板就成了一个业...

1806
来自专栏HTML5学堂

面向对象系列讲解—认识对象

HTML5学堂:面向对象、原型、继承应该说是属于JavaScript最底层的知识和概念,对于这些知识,在我们没有触碰的时候的确觉得是比较困难的,包括在学习的过程...

2804
来自专栏WeaponZhi

轻松初探 Python 篇(五)—dict 和 set 知识汇总

这是「AI 学习之路」的第 5 篇,「Python 学习」的第 5 篇 dict dict 是 Python 内置的字典类型,熟悉 Java 的同学可以把它类比...

2889

扫描关注云+社区