专栏首页数据和云如何保持Oracle数据库SQL性能的稳定性

如何保持Oracle数据库SQL性能的稳定性

使用Oracle数据库的应用系统,有时出现SQL性能突然变差,特别是对于OLTP类型系统执行频繁的核心SQL,如果出现性能问题,通常会影响整个数据库的性能,进而影响整个系统的正常运行。这是常常遇到的问题,也是一些DBA的挑战。

SQL性能变差原因分析

SQL的性能变差,通常是在SQL语句重新进行了解析,解析时使用了错误的执行计划出现的。

下列情况是SQL会重新解析的原因:

  1. SQL语句没有使用绑定变量,这样SQL每次执行都要解析。
  2. SQL长时间没有执行,被刷出SHARED POOL,再次执行时需要重新解析。
  3. 在SQL引用的对象(表、视图等)上执行了DDL操作,甚至是结构发生了变化,比如建了一个索引。
  4. 对SQL引用的对象进行了权限更改。
  5. 重新分析(收集统计信息)了SQL引用的表和索引,或者表和索引统计信息被删除。
  6. 修改了与性能相关的部分参数。
  7. 刷新了共享池。
  8. 当然重启数据库也会使所有SQL全部重新解析。

SQL重新解析后,跟以前相比,性能突然变差,通常是下列原因:

1. 表和索引的优化统计信息被删除,或者重新收集后统计信息不准确。重新收集统计信息通常是由于收集策略(方法)不正确引起。比如对分区表使用analyze命令而不是用dbms_stats包、收集统计信息时采样比例过小等等。Oracle优化器严重依赖于统计信息,如果统计信息有问题,则很容易导致SQL不能使用正确的执行计划。

2. SQL绑定变量窥探(bind peeking),同时绑定变量对应的列上有直方图;或者绑定变量的值变化范围过大、分区数据分布极不均匀:

1) 绑定变量的列上有直方图:

假如表orders存储所有的订单,state列有3种不同的值:0表示未处理,1表示处理成功完成,2表示处理失败。State列上有一个索引,表中绝大部分数据的state列为1,0和2占少数。有下面的SQL:

select * from orders where state=:b1

这里:b1是变量,在大多数情况下这个值为0,则应该使用索引,但是如果SQL被重新解析,而第一次执行时应用传给变量b1值为1,则不会使用索引,采用全表扫描的方式来访问表。对于绑定变量的SQL,只在第一次执行时才会进行绑定变量窥探,并以此确定执行计划,该SQL后续执行时全部按这个执行计划。这样在后续执行时,b1变量传入的值为0的时候,仍然是第一次执行时产生的执行计划,即使用的是全表扫描,这样会导致性能很差。

2) 绑定变量的值变化范围过大: 同样假如orders表有一列created_date表示一笔订单的下单时间,orders表里面存储了最近1年的数据,有如下的SQL:

select * from orders where created_date >=:b1;

假如大多数情况下,应用传入的b1变量值为最近几天内的日期值,那么SQL使用的是created_date列上的索引,而如果b1变量值为5个月之前的一个值,那么就会使用全表扫描。与上面描述的直方图引起的问题一样,如果SQL第1次执行时传入的变量值引起的是全表扫描,那么将该SQL后续执行时都使用了全表扫描,从而影响了性能。

3) 分区数据量不均匀: 对于范围和列表分区,可能存在各个分区之间数据量极不均匀的情况下。比如分区表orders按地区area进行了分区,P1分区只有几千行,而P2分区有200万行数据。同时假如有一列product_id,其上有一个本地分区索引,有如下的SQL:

select * from orders where area=:b1 and produce_id=:b2;

这条SQL由于有area条件,因此会使用分区排除。如果第1 次执行时应用传给b1变量的值正好落在P1分区上,很可能导致SQL采用全表扫描访问,如前面所描述的,导致SQL后续执行时全部使用了全表扫描。

3. 其他原因,比如表做了类似于MOVE操作之后,索引不可用,对索引进行了更改。当然这种情况是属于维护不当引起的问题,不在本文讨论的范围。

综上所述,SQL语句性能突然变差,主要是因为绑定变量和统计信息的原因。注意这里只讨论了突然变差的情况,而对于由于数据量和业务量的增加性能逐步变差的情况不讨论。

如何保持SQL性能的稳定

为保持SQL性能或者说是执行计划的稳定性,需要从以下几个方面着手:

1. 规划好优化统计信息的收集策略。对于Oracle 10g来说,默认的策略能够满足大部分需求,但是默认的收集策略会过多地收集列上的直方图。由于绑定变量与直方图固有的矛盾,为保持性能稳定,对使用绑定变量的列,不收集列上的直方图;对的确需要收集直方图的列,在SQL中该列上的条件就不要用绑定变量。

统计信息收集策略,可以考虑对大部分表,使用系统默认的收集策略,而对于有问题的,可以用DBMS_STATS.LOCK_STATS锁定表的统计信息,避免系统自动收集该表的统计信息,然后编写脚本来定制地收集表的统计信息。脚本中类似如下:

2. 修改SQL语句,使用HINT,使SQL语句按HINT指定的执行计划进行执行。这需要修改应用,同时需要逐条SQL语句进行,加上测试和发布,时间较长,成本较高,风险也较大。

3. 修改隐含参数” _optim_peek_user_binds”为FALSE,修改这个参数可能会引起性能问题(这里讨论的是稳定性问题)。

4. 使用OUTLINE。对于曾经出现过执行计划突然变差的SQL语句,可以使用OUTLINE来加固其执行计划。在10g中DBMS_OUTLN.CREATE_OUTLINE可以根据已有的执行正常的SQL游标来创建OUTLINE。如果事先对所有频繁执行的核心SQL使用OUTLINE加固执行计划,将最大可能地避免SQL语句性能突然变差。

注:DBMS_OUTLN可以通过$ORACLE_HOME/rdbms/admin/dbmsol.sql脚本来安装。

5. 使用SQL Profile。SQL Profile是Oracle 10g之后的新功能,此处不再介绍,请参考相应的文档。

除此之外,可以调整一些参数避免潜在的问题,比如将"_btree_bitmap_plans"参数设置为FALSE(这个参数请参考互联网上的文章或Oracle文档)。

而在实际工作中,通过使用定制的统计信息收集策略,以及在部分系统上使用OUTLINE,系统基本上不会出现已有的SQL性能突然变差的情况。当然也有维护人员操作不当引起的SQL性能突然变差,比如建了某个索引而没有收集统计信息,导致SQL使用了新建的索引,而该索引并不适合于那条SQL;维护人员意外删除了表个索引的统计信息。

本文分享自微信公众号 - 数据和云(OraNews),作者:熊军

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2017-04-17

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Delphi 程序错误写法造成Oracle数据库负载异常

    作者介绍 ? 张洪涛 富士康 DBA 注意:本文涉及的问题在9i的环境中测试的,经验证,同样适用于11g. 在用Toad的SGA Trace工具监控我们的Or...

    数据和云
  • 验证GaussDB T 闪回事务查询功能;闪回表功能强劲闪回TRUNCATE

    总的来说,gaussdb100 T 是可以支持闪回事务查询。 二、GaussDB T 的 Flashback Table 功能非常强劲可以闪回TRUNCATE...

    数据和云
  • 执行计划:如何手工创建Profile维持SQL计划的稳定性

    在上一篇:《执行计划:Oracle的Profile特性与SQL执行计划的稳定性》,向大家介绍了什么是 SQL Profiles 及其作用,如何使用 SQL Tu...

    数据和云
  • 直播回顾 | 亿级并发丝毫不虚,TDSQL-SQL引擎架构演进与查询实战

    为帮助开发者更好地了解和学习分布式数据库技术,2020年3月,腾讯云数据库、云加社区联合腾讯TEG数据库工作组特推出为期3个月的国产数据库专题线上技术沙龙《你想...

    分布式数据库TDSQL
  • 直播回顾 | 亿级并发丝毫不虚,TDSQL-SQL引擎是如何炼成的

    腾讯云数据库国产数据库专题线上技术沙龙正在火热进行中,3月19日唐颢的分享已经结束,没来得及参与的小伙伴不用担心,以下就是直播的视频和文字回顾。

    腾讯云数据库 TencentDB
  • 一个"TOP SQL"类产品的构想

    作为一名DBA,SQL优化是工作中必不可少的部分。如何快速、准确的发现待优化的语句,是DBA经常需要考虑的问题。很多数据库都内置有慢查询、SQL报告等能力,这也...

    用户5548425
  • 为什么忘记 commit 也会造成 select 查询的性能问题

    作者:潇湘隐者 www.cnblogs.com/kerrycode/p/5836015.html 今天遇到一个很有意思的问题,一个开发人员反馈在测试服务器OR...

    顶级程序员
  • 【SQL】历史SQL监控(Historical SQL Monitoring ) 功能(12c)

    Oracle 11g版本 推出了实时SQL监控功能(Real-Time SQL Monitoring),用于实时地监视执行中SQL的性能;Oracle 12c ...

    TeacherWhat
  • 【SQL Performance】实时SQL监控功能(Real-Time SQL Monitoring)

    实时SQL监控功能(Real-Time SQL Monitoring)是Oracle11g推出的功能,通过这个功能可以实时地监视执行中的SQL性能。

    TeacherWhat
  • 【DB笔试面试464】动态SQL是什么?

    在PL/SQL开发过程中,使用SQL或PL/SQL可以实现大部分的需求,但是,在某些特殊的情况下,在PL/SQL中使用标准的SQL语句或DML语句不能实现自己的...

    小麦苗DBA宝典

扫码关注云+社区

领取腾讯云代金券