怎样保持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

如果是oracle9i版本，可以考虑如下两步实现：

（10g直接执行第二步就可以了）

统计一个用户下所有表的行数：

1.exec

dbms_stats.gather_schema_stats(owner=>'用户名')

2.select

sum(num_rows)

from

user_tables

统计全库所有表的行数：

1.exec

dbms_stats.gather_database_stats(estimate_percent=>'30')

2.select

sum(num_rows)

from

dba_tables

生产环境中需谨慎考虑收集新的统计信息后对应用产生的影响。

alter table tablename move [tablespace tablespacename]\x0d\x0a\x0d\x0adelete数据不会回收已经分配出去的block（也就是delete前后你查看user_segments中的信息不会有改动）。\x0d\x0a但这时你对表执行analyze后查看dba_tables表的话会发现empty_block数目变大或者avg_space数据变小。\x0d\x0a \x0d\x0a如果你希望减少该table占用的实际block数目，\x0d\x0a你需要使用move *** 作将table重建，oracle才会重新分配block，这时table上的索引会失效，需要rebuild。\x0d\x0a\x0d\x0a一，创建测试环境\x0d\x0a1.1 创建测试表，为其插入16万条记录\x0d\x0acreate table jax_t11 \x0d\x0aas\x0d\x0aselect * from dba_objects \x0d\x0awhere rownum user, -- 表的拥有者\x0d\x0a tabname=>upper('jax_t11'),-- 表名称\x0d\x0a method_opt =>'for all indexed columns size 1', -- 获得所有索引列的柱状图\x0d\x0a cascade=>TRUE )-- 级联获取 indexes的统计信息\x0d\x0aend\x0d\x0a\x0d\x0a1.4 查看表占用空间大小\x0d\x0aselect segment_name,segment_type,bytes/1024/1024 from dba_segments ds\x0d\x0awhere ds.segment_name in ( 'JAX_T11', upper('idx_jax_t11_01'))\x0d\x0a\x0d\x0aSEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024\x0d\x0aJAX_T11 TABLE 17\x0d\x0aIDX_JAX_T11_01 INDEX 9\x0d\x0a这里我们可以看到，表占空间17M，索引占空间9M；\x0d\x0a\x0d\x0a表空间占用明细\x0d\x0aSELECT table_name,tablespace_name,\x0d\x0anum_rows, -- 记录行数\x0d\x0aavg_row_len, --平均行长度 \x0d\x0ablocks,\x0d\x0aavg_space, \x0d\x0aempty_blocks\x0d\x0afrom user_tables ut\x0d\x0awhere ut.table_name = 'JAX_T11'\x0d\x0a\x0d\x0aTABLE_NAME TABLESPACE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS AVG_SPACE EMPTY_BLOCKS\x0d\x0aJAX_T11 DRP_DATA 160000 100 2146 0 0\x0d\x0a\x0d\x0a索引空间占用明细\x0d\x0aSELECT index_name,table_name,leaf_blocks,distinct_keys,num_rows\x0d\x0afrom user_indexes ut\x0d\x0awhere ut.index_name = upper('idx_jax_t11_01')\x0d\x0aINDEX_NAME TABLE_NAME LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS\x0d\x0aIDX_JAX_T11_01 JAX_T11 1036 9832 160000\x0d\x0a\x0d\x0a二，删除90%的记录后的空间占用\x0d\x0a2.1 删除90%的记录\x0d\x0adelete from jax_t11\x0d\x0a where rowid in (select r1\x0d\x0a from (select rowid r1, mod(rownum, 100) r2 from jax_t11) t\x0d\x0a where r2 user, -- 表的拥有者\x0d\x0a tabname=>upper('jax_t11'),-- 表名称\x0d\x0a method_opt =>'for all indexed columns size 1', -- 获得所有索引列的柱状图\x0d\x0a cascade=>TRUE )-- 级联获取 indexes的统计信息\x0d\x0aend\x0d\x0a\x0d\x0a2.3 查看表占用空间大小\x0d\x0aselect segment_name,segment_type,bytes/1024/1024 from dba_segments ds\x0d\x0awhere ds.segment_name in ( 'JAX_T11', upper('idx_jax_t11_01'))\x0d\x0a\x0d\x0aSEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024\x0d\x0aJAX_T11 TABLE 17\x0d\x0aIDX_JAX_T11_01 INDEX 9\x0d\x0a这里我们可以看到，表占空间17M，索引占空间9M；与删除数据前相比，没有任何改变\x0d\x0a\x0d\x0a表空间占用明细\x0d\x0aSELECT table_name,tablespace_name,\x0d\x0anum_rows, -- 记录行数\x0d\x0aavg_row_len, --平均行长度 \x0d\x0ablocks,\x0d\x0aavg_space, \x0d\x0aempty_blocks\x0d\x0afrom user_tables ut\x0d\x0awhere ut.table_name = 'JAX_T11'\x0d\x0a\x0d\x0aTABLE_NAME TABLESPACE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS AVG_SPACE EMPTY_BLOCKS\x0d\x0aJAX_T11 DRP_DATA 14400 100 2146 0 0\x0d\x0a\x0d\x0a索引空间占用明细\x0d\x0aSELECT index_name,table_name,leaf_blocks,distinct_keys,num_rows\x0d\x0afrom user_indexes ut\x0d\x0awhere ut.index_name = upper('idx_jax_t11_01')\x0d\x0aINDEX_NAME TABLE_NAME LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS\x0d\x0aIDX_JAX_T11_01 JAX_T11 998 7654 14400\x0d\x0a\x0d\x0a三，move table &rebuild index\x0d\x0a3.1 删除90%的记录\x0d\x0aalter table jax_t11 move\x0d\x0aalter index idx_jax_t11_01 rebuild\x0d\x0a\x0d\x0a3.2 分析表及索引\x0d\x0abegin\x0d\x0a dbms_stats.gather_table_stats\x0d\x0a ( ownname=>user, -- 表的拥有者\x0d\x0a tabname=>upper('jax_t11'),-- 表名称\x0d\x0a method_opt =>'for all indexed columns size 1', -- 获得所有索引列的柱状图\x0d\x0a cascade=>TRUE )-- 级联获取 indexes的统计信息\x0d\x0aend\x0d\x0a\x0d\x0a3.3 查看表占用空间大小\x0d\x0aselect segment_name,segment_type,bytes/1024/1024 from dba_segments ds\x0d\x0awhere ds.segment_name in ( 'JAX_T11', upper('idx_jax_t11_01'))\x0d\x0a\x0d\x0aSEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024\x0d\x0aJAX_T11 TABLE 2\x0d\x0aIDX_JAX_T11_01 INDEX 0.8125\x0d\x0a这里我们可以看到，表占空间2M，索引占空间0.8125M；与删除数据前相比，该回收的空间已经回收完毕

---