怎样保持Oracle数据库SQL性能的稳定性
社区服务
火星文
银行
结婚
道具中心
勋章中心
管理操作原因
基本信息
管理团队
管理操作
在线会员
会员排行
版块排行
帖子排行
手机版
小说论坛
用户中心
搜索
银行
猴岛论坛
帖子
用户
版块
帖子
高级搜索
跑跑卡丁车
聊天交友&心情咖啡
实物交易发布
校园青春
动漫剧场
综合游戏交流
纪念馆
原神
娱乐时尚星座
和平精英
关闭
选中
1
篇
全选
猴岛论坛
电脑百科
怎样保持Oracle数据库SQL性能的稳定性
发帖
回复
倒序阅读
最近浏览的帖子
最近浏览的版块
« 返回列表
新帖
悬赏
任务
交易贴
自动发卡
拍卖
红包
际遇红包
3
个回复
[资源分享]
怎样保持Oracle数据库SQL性能的稳定性
楼层直达
简.
ZxID:10955214
关注Ta
注册时间
2010-02-25
最后登录
2024-11-08
发帖
53447
在线
22791小时
精华
6
DB
5
威望
5
保证金
0
桃子
35
鲜花
0
鸡蛋
0
访问TA的空间
加好友
用道具
发消息
加好友
他的帖子
对该用户使用道具
qq
等级:
元老
配偶:
繁.
To Dearest WY
举报
只看楼主
使用道具
楼主
发表于: 2010-11-14
0
有客户遇到SQL性能不稳定,突然变差导致系统性能出
现严重问题的情况。对于大型的系统来说,SQL性能不
稳定,有时突然变差,这是常常遇到的问题。这也是一
些DBA的挑战。
对于使用Oracle数据库的应用系统,有时会出现运
行得好好的SQL,性能突然变差。特别是对于OLTP类型
系统执行频繁的核心SQL,如果出现性能问题,通常会
影响整个数据库的性能,进而影响整个系统的正常运
行。对于个别的SQL,比如较少使用的查询报表之类的
SQL,如果出现问题,通常只影响少部分功能模块,而
不会影响整个系统。
那么应该怎么样保持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:
1 select * from orders where state=:b1 这里:b1是变
量,在大多数情况下这个值为0,则应该使用索引,但是
如果SQL被重新解析,而第一次执行时应用传给变量b1
值为1,则不会使用索引,采用全表扫描的方式来访问
表。对于绑定变量的SQL,只在第一次执行时才会进行
绑定变量窥探,并以此确定执行计划,该SQL后续执行
时全部按这个执行计划。这样在后续执行时,b1变量传
入的值为0的时候,仍然是第一次执行时产生的执行计
划,即使用的是全表扫描,这样会导致性能很差。
2) 绑定变量的值变化范围过大:
同样假如orders表有一列created_date表示一笔订单
的下单时间,orders表里面存储了最近1年的数据,有如
下的SQL:
1 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:
1 select * from orders where area=:b1 and product_id
=:b2 这条SQL由于有area条件,因此会使用分区排
除。如果第1 次执行时应用传给b1变量的值正好落在P1
分区上,很可能导致SQL采用全表扫描访问,如前面所
描述的,导致SQL后续执行时全部使用了全表扫描。
3. 其他原因,比如表做了类似于MOVE操作之后,索
引不可用,对索引进行了更改。当然这种情况是属于维
护不当引起的问题,不在本文讨论的范围。
综上所述,SQL语句性能突然变差,主要是因为绑
定变量和统计信息的原因。注意这里只讨论了突然变差
的情况,而对于由于数据量和业务量的增加性能逐步变
差的情况不讨论。
为保持SQL性能或者说是执行计划的稳定性,需要
从以下几个方面着手:
1. 规划好优化统计信息的收集策略。对于Oracle 10g
来说,默认的策略能够满足大部分需求,但是默认的收
集策略会过多地收集列上的直方图。由于绑定变量与直
方图固有的矛盾,为保持性能稳定,对使用绑定变量的
列,不收集列上的直方图;对的确需要收集直方图的
列,在SQL中该列上的条件就不要用绑定变量。统计信
息收集策略,可以考虑对大部分表,使用系统默认的收
集策略,而对于有问题的,可以用
DBMS_STATS.LOCK_STATS锁定表的统计信息,避免
系统自动收集该表的统计信息,然后编写脚本来定制地
收集表的统计信息。脚本中类似如下:
1
exec dbms_stats.unlock_table_stats…
2
exec dbms_stats.gather_table_stats…
3 exec dbms_stats.lock_table_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;维护人员意外删除了表
个索引的统计信息。
本帖de评分:
共
1
条评分
DB +10
冬寒、雪未飘
DB
+10
优秀文章
隐藏
本帖de打赏:
共
条打赏
隐藏
打赏
收藏
新鲜事
相关主题
SQL Server数据库崩溃后的恢复之法
数据库优化与应用程序性能的五个平衡点
请大家进来看看,怎样解决我家部电脑CPU的问题?
跟我学,图解说,20分钟建立WIN2008SQL2005的ASP.NET网站
如何通过调整Windows 参数提高数据库服务器性能
怎样快速打开控制面板中的选项
不想成为什么样的人,只想好好活着。
回复
引用
鲜花[
0
]
鸡蛋[
0
]
景老大
ZxID:10922867
关注Ta
注册时间
2010-02-24
最后登录
2015-04-09
发帖
1998
在线
761小时
精华
0
DB
857
威望
752
保证金
0
桃子
0
鲜花
0
鸡蛋
0
访问TA的空间
加好友
用道具
发消息
加好友
他的帖子
对该用户使用道具
等级:
少将
风的脚步911kxw
举报
只看该作者
沙发
发表于: 2010-11-14
0
无语
本帖de评分:
共
0
条评分
隐藏
本帖de打赏:
共
条打赏
隐藏
击鼓其镗,踊跃用兵。土国城漕,我独南行。
从孙子仲,平陈与宋。不我以归,忧心有忡。
爰居爰处?爰丧其马?于以求之?于林之下。
死生契阔,与子成悦。执子之手,与子偕老。
于嗟阔兮,不我活兮。于嗟洵兮,不我信兮。
回复
引用
新鲜事
鲜花[
0
]
鸡蛋[
0
]
ZXid:13512531
ZxID:13512531
关注Ta
注册时间
2010-10-29
最后登录
2020-05-15
发帖
12717
在线
3787小时
精华
0
DB
329
威望
288
保证金
0
桃子
72
鲜花
0
鸡蛋
0
访问TA的空间
加好友
用道具
发消息
加好友
他的帖子
对该用户使用道具
qq
等级:
贵宾
举报
只看该作者
板凳
发表于: 2010-11-14
0
谢谢分享·
本帖de评分:
共
0
条评分
隐藏
本帖de打赏:
共
条打赏
隐藏
回复
引用
新鲜事
鲜花[
0
]
鸡蛋[
0
]
法国面具男丶
ZxID:13377058
关注Ta
注册时间
2010-10-05
最后登录
2024-10-18
发帖
5783
在线
4894小时
精华
0
DB
1044
威望
9749
保证金
0
桃子
2
鲜花
0
鸡蛋
0
访问TA的空间
加好友
用道具
发消息
加好友
他的帖子
对该用户使用道具
qq
等级:
大将
举报
只看该作者
地板
发表于: 2010-11-21
0
谢谢分享!
本帖de评分:
共
0
条评分
隐藏
本帖de打赏:
共
条打赏
隐藏
岛国老师全集.zip
[点击下载]
(958.4 K) 下载次数814572
累计下载获得 DB
985452刀
回复
引用
新鲜事
鲜花[
0
]
鸡蛋[
0
]
« 返回列表
发帖
回复
关闭