怎样保持Oracle数据库SQL性能的稳定性

有客户遇到SQL性能不稳定 突然变差导致系统性能出现严重问题的情况 对于大型的系统来说 SQL性能不稳定 有时突然变差 这是常常遇到的问题 这也是一些DBA的挑战

对于使用Oracle数据库的应用系统 有时会出现运行得好好的SQL 性能突然变差 特别是对于OLTP类型系统执行频繁的核心SQL 如果出现性能问题 通常会影响整个数据库的性能 进而影响整个系统的正常运行 对于个别的SQL 比如较少使用的查询报表之类的SQL 如果出现问题 通常只影响少部分功能模块 而不会影响整个系统

那么应该怎么样保持SQL性能的稳定性？

SQL的性能变差 通常是在SQL语句重新进行了解析 解析时使用了错误的执行计划出现的 下列情况是SQL会重新解析的原因

SQL语句没有使用绑定变量 这样SQL每次执行都要解析

SQL长时间没有执行 被刷出SHARED POOL 再次执行时需要重新解析

在SQL引用的对象（表 视图等）上执行了DDL *** 作 甚至是结构发生了变化 比如建了一个索引

对SQL引用的对象进行了权限更改

重新分析（收集统计信息）了SQL引用的表和索引 或者表和索引统计信息被删除

修改了与性能相关的部分参数

刷新了共享池

当然重启数据库也会使所有SQL全部重新解析

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

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

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

） 绑定变量的列上有直方图

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

select from orders where state=:b

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

） 绑定变量的值变化范围过大

同样假如orders表有一列created_date表示一笔订单的下单时间 orders表里面存储了最近 年的数据 有如下的SQL

Select from orders where created_date >=:b ;

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

） 分区数据量不均匀

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

select from orders where area=:b and product_id =:b

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

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

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

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

规划好优化统计信息的收集策略 对于Oracle g来说 默认的策略能够满足大部分需求 但是默认的收集策略会过多地收集列上的直方图 由于绑定变量与直方图固有的矛盾 为保持性能稳定 对使用绑定变量的列 不收集列上的直方图 对的确需要收集直方图的列 在SQL中该列上的条件就不要用绑定变量 统计信息收集策略 可以考虑对大部分表 使用系统默认的收集策略 而对于有问题的 可以用DBMS_STATS LOCK_STATS锁定表的统计信息 避免系统自动收集该表的统计信息 然后编写脚本来定制地收集表的统计信息 脚本中类似如下

exec dbms_stats unlock_table_stats…

exec dbms_stats gather_table_stats…

exec dbms_stats lock_table_stats…

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

修改隐含参数 _optim_peek_user_binds 为FALSE 修改这个参数可能会引起性能问题（这里讨论的是稳定性问题）

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

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

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

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

lishixinzhi/Article/program/Oracle/201311/18054

在公路建设中，通过建立多条车道可以提高道路的流量。其实这个道理在Oracle数据库中也行得通。即可以将关键数据文件存储在多块硬盘上，以提高Oracle数据库的性能。可惜的是，不少数据库管理员没有意识到这一点。在这篇文章中笔者就以Oracle11G为例，说明如何通过在硬盘之间分布关键数据文件来提高性能。 一、在硬盘之间分布关键数据文件的基本原则。

在传统的文件系统上(即不是在裸机上)部署Oracle数据库，可以通过将关键的数据文件分布到多个可用的文件系统上或者不同的硬盘上来提高数据库的性能。具体的来说，需要遵循如下几个原则。

一是对于表来说，往往包含两个部分，即基本表与索引表。只要为基本表中的字段创建了索引，其对应的就有一张索引表。当用户访问表中的数据时，应用系统需要同时访问到索引表与数据表。此时我们可以将这两张表比喻成两辆车。如果现在只有一个车道(即将他们同时存放在一个硬盘或者文件系统中)，那么两辆车必须前后行使。而如果现在有两个车道(即将基本表与其相对应的索引表存放在不同的硬盘或者文件系统中)，那么这两辆车就可以并排行使。显然，后者的效率更高。为此笔者建议，可将经常需要访问的表和与之对应的索引表分开来存放。

二是可以将日志文件也分开来存放。不光光是数据表与索引表存在着这种状况。其实在日志文件管理中也是如此。只要条件允许，那么最好能够将联机重做日志和归档日志与其它数据文件存放在不同的硬盘或者文件系统上。因为当用户往数据库中写入数据时，需要同时往数据文件与重做日志文件中写入数据。此时如果将它们分开来存放，那么就相当于有了多条车道，分别往不同的文件中写入数据。这无疑就可以提高数据写入的效率，从而提高数据库的性能。

二、哪些文件最好能够分开存放

在讲到硬盘之间分布关键数据文件的基本原则的时候，笔者举了几个需要分开存放的几个案例。但是在实际工作中，并不仅仅局限于上面提到的这些文件。笔者认为，如果条件允许的话，那么可以考虑将如下文件放置在不同的硬盘上。

一是表空间，如临时表空间、系统表空间、UNDO表空间等等。这三个表空间可能系统会同时进行访问。为此需要将其分开来存放。二是数据文件和索引文件。上面提到过，需要将经常访问的数据文件与其对应的索引文件存放在不同的硬盘上。因为这两类文件在访问数据时也可能会同时访问到。三是 *** 作系统盘与数据库文件单独存放。显然Oracle系统肯定是与 *** 作系统同时运行的。为了避免他们之间的I/Q冲突，就需要将Oracle部署在 *** 作系统盘以外的磁盘上。四是联机重做日志文件。这个文件比较复杂，不但要将其与其他文件分开来存放。而且还需要注意的是，最好能够将其存放在性能最佳的硬盘上。

最后需要说明的一点是，增加磁盘也会增加成本。这不光光是购买磁盘所需要的花费，还包括管理的成本。所以这之间也会涉及到成本与性能之间的一个均衡问题。如果企业的数据不是很多，或者主要是涉及到查询 *** 作，那么这么设计的话，就可能不怎么合理。因为投入要大于回报。

三、如何确定是否需要将文件分开来存放

在实际工作中，企业的数据是一个从少到多的过程。也就是说，刚开始使用数据库的时候，可能数据量比较少，此时出于成本的考虑，没有将相关文件存放在不同的磁盘上。但是随着工作的深入，用户会发现数据库的性能在逐渐的降低。此时管理员就需要考虑，能够采取这种多建车道的措施，来提高数据库性能。当然在采取这个措施之前，管理员需要先进性评估。此时评估所需要用到的一个指标就是磁盘的I/O争用。

磁盘争用通常发生在有多个进程试图同时访问一个物理磁盘的情况下。如现在用户需要访问某个数据表中的数据，此时系统需要访问索引文件与数据表文件。如果将它们放置在同一磁盘上，那么在访问时就会发生I/O冲突。所以评估I/O冲突的严重程度，可以帮我们来确定是否需要将关键文件存放在不同的磁盘上。

将I/O平均的分布到多个可用的磁盘上，这可以有效的减少磁盘之间的争用情况，提高数据存储与读取的性能。从而提高Oracle等应用程序的效率。在实际工作中，数据库控制文件中有两个参数可以用来帮助我们评估这个指标。这两个参数是文件平均读取时间和文件平均写入时间。不过在使用这两个参数的时候，其只评估所有与数据库相关联的文件。管理员如果有需要的话，也可以通过下面的查询语句来查询数据文件是否存在I/O问题。查询的语法与结果如下图所示：

从如上的查询结果中可以看出某个数据文件是否繁忙，数据文件之间是否存在着/I/O冲突文件。这里需要注意的是，这个结果是一个动态的结果。在不同的时刻、用户进行不同的 *** 作时往往会得出不同的结论。为此笔者建议，在使用这个数据的时候，最好能够多跟踪几次。然后分析多次运行的结果。只有如此，才能够得到比较合乎情理的判断。 通常情况下，管理员根据上面的结果可以得出三种结论。

第一种结论是上面这些数据文件都不是很忙。即文件的平均读取时间与写入时间都比较短，表示这两个文件都是比较空闲的。此时正常情况下，数据库的性能应该是不错的。也就是说，如果此时数据库的性能不理想的话，那么就不是磁盘的I/O所造成的。管理员应该从其他角度来改善数据库的性能。

第二种结论是每个数据库文件都非常的繁忙。此时有可能是读取时间或者写入时间比较长，或者说两个时间都比较长。当多个数据文件同时比较繁忙并且他们处于同一磁盘的话，那么管理员就需要考虑购买新的磁盘，然后将上面提到的这些关键文件重新整理，让他们部署在不同的磁盘上。

第三种结论是某几个特定的数据文件比较繁忙，而其他数据文件还可以。此时管理员如果成本受到限制，那么也不需要重新购买硬盘。在磁盘上的物理写入和读取次数上如果出现比较大的差异，就表明某个磁盘负载过大，即有很严重的I/O冲突。此时最好能够将这个磁盘中的文件进行调整，如将某些文件移动到另外的一块I/O相对不怎么严重的磁盘上。不过在采取这个 *** 作的时候，需要注意一点。对于联机重做日志文件来说，即使其所在的磁盘I/O冲突比较低，或者访问这个文件的时间比较短，但是也不建议将其他数据文件转移到其所在的磁盘上来。因为通常情况下，为了保障数据库的性能，我们都建议将联机重做日志文件单独存放，并且还需要讲起放置在性能比较高的硬盘上。

总之，将关键的Oracle数据库文件分开放置。如此的话可以有效避免磁盘争用成为Oracle数据库系统的性能瓶颈。

1硬件的环境cpu,内存,网络传输条件等均会影响到oracle的性能。硬件方面的例子,不用举例大家也会有所了解,这里不再详细说明。2数据库运行时的配置参数也会影响到Oracle的性能下面以一个示例项目中涉及问题为例,说明一下配置参数的影响当前我们的项目供某公司的一个部门使用,正常运行一段时间后就会出现运行特别慢的现象(当前的服务器有两块cpu),通过对linux中cup和内存的运行情况,我们发现是由于一块cpu在运行一段时间时,会出现100%被占用的情况,而另一块cpu却利用率很低,通过对两块cpu的负载均衡,程序运行速度恢复正常。3对于不合理的表结构设计也会影响到Oracle的性能对于不合理的表结构对性能的影响,我们在另一个项目中也有发生,当前有一张表,存储了用户手机号码,在统计时需要对用户的手机号码进行分类统计,而另一张表中存储了与手机号码前四或前七位匹配的地区名称,这时间程序员写了一条sql语句为: select from userPhone,Userarea where (left(userPhonephone,4) == userareaid or left(userPhone,7)==userareaid);该语句在前期userPhone表数据量不多时,没有感觉到性能方面的影响,但当userPhone达到1万条时,性能迅速降低,经过分析后, 确定是由于表结构不太合理,于是在userPhone中增加一个userarea的id列,sql语句修改为 select from userPhone,Userarea where userPhoneareaid ==userareaid),程序运行性能恢复正常4对于程序员而言,写出不合理的sql语句也会影响Oracle的性能(1)如果程序员创建一个数据库更新事务之类,而没有进行commit及进的提交,将会造成系统锁死的状态,这样会严重影响系统的性能。(2)在第3条中,程序员写出的类似于select from userPhone,Userarea where (left(userPhonephone,4) == userareaid or left(userPhone,7)==userareaid);的语句主要还是由于对sql运行的机制不了解,没有分析该条语句将要处理的数据记录的数量,从而没有发现表结构的不合理,由此造成性能的严重下降。

数据库性能优化是无止境的 无论哪种优化技术只是一种手段 但最重要的不是技术 而是思想 掌握了索引优化技术仅仅刚入门 只有融会贯通 举一反三才能成为高手

本文引用一套实验室信息管理系统（LIS）使用的数据库 假设我们要查询 年 月做检验的患者记录 条件是大于 岁 姓周的患者 最终结果按检查日期进行倒序排列 要使用的表有三个

◆lis_report 报告主表 我们要用到的字段包括i_checkno（检查号） d_checkdate（检查日期） i_patientid（患者ID）

◆m_patient 患者信息表 我们要用到的字段包括i_patientid（患者ID） s_name（患者姓名） s_code（患者住院号） i_age（患者年龄） i_dept（患者所在病区）

◆lis_code_dept 病区信息表 我们要用到的字段包括i_id（病区ID 主键 与m_patient中的i_dept关联） s_name（病区名）

最终我们构造的SQL如下

select a i_checkno a d_checkdate b s_name b s_code b i_age c s_name from lis_report a inner join m_patient b on a i_patientid = b i_patientid inner join lis_code_dept c on b i_dept = c i_id where a d_checkdate > and a d_checkdate < and b i_age>= and b s_name like 周% order by a d_checkdate desc

我们的SQL使用的这三张表除了创建主键时自动创建的索引外 均未创建其它索引 下图是无索引时的执行计划

表m_patient和lis_report都使用了全表扫描 m_patient全表扫描的成本是 lis_report全表扫描的成本是 只有表lis_code_dept因关联时使用的是其主键 因此这里使用了主键索引 从而避免了全表扫描 它的成本是 我们知道提高查询性能的目标之一就是消灭掉全表扫描 因此我们应该给表m_patient和lis_report加上适当的索引 在SQL代码的where子句中 对m_patient表 我们引用了i_age和s_name字段 对lis_report表 我们引用了d_checkdate字段 通常给这些条件中引用的字段加上索引会提高查询速度 我们先给m_patient的i_gae字段加上索引 下面是对应的执行计划

表m_patient的全表扫描消失了 取而代之的是索引唯一性扫描 成本从 一下子降低到 了 注意这里并未使用我们给i_age增加的索引 但却靠它触发了使用表主键对应的索引 但表lis_report仍然是全表扫描 由于where子句中引用了该表的d_checkdate字段 因此我们给该字段加上索引看看效果

表lis_report的全表扫描消失了 取而代之的是索引范围降序扫描（INDEX RANGE SCAN DESCENDING） 成本也从 下降到 注意这里的索引范围降序扫描的来历 因为我的where子句中引用d_checkdate是介于 至 的一个范围 这时引用的这种字段上建立的索引通常都是执行范围扫描 因为这种条件返回的值往往不止一行 使用降序扫描的原因是order by子句使用了降序排序 如果我们将SQL代码中的 order by a d_checkdate desc 改为 order by a d_checkdate 则变为索引范围扫描（INDEX RANGE SCAN）

至此我们全部消除了全表扫描 我们看到加上索引后 查询执行的成本开销也有所降低 因为数据库表中的记录数不大 因此效果不太明显 如果有上百万条记录则会更直观

lishixinzhi/Article/program/Oracle/201311/18362

优化update要根据使用方式决定：

1如果是在线事务系统，那么建立合适的索引非常重要，其次通过分区技术设立合理的分区键也可以大大提升更新前的查找定位效率。

2如果是后台批量数据分析系统，一次需要更新表中数据的大部分，那么可以考虑使用临时表过度，采用insert到临时表再替换原表的方式代替update *** 作，这样效率的提升非常可观，对系统的压力也小很多。也可以使用分区技术，对多个分区并行update *** 作以提升效率，但是系统的压力也会随之增大。

以上就是关于怎样保持Oracle数据库SQL性能的稳定性全部的内容，包括:怎样保持Oracle数据库SQL性能的稳定性、如何提高oracle数据库的性能 / 查查362、哪些因素可以影响Oracle数据库的性能等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！