oracle sql优化实战,某核心BOSS系统SQL优化实战-性能提升200倍以上
1相遇是缘
star童鞋最喜欢的西安美食之一“三秦套餐”刚上来，突然电话响了，某核心BOSS系统数据库异常缓慢，做为专业小’DBA’er,第一时间赶赴现场解决问题。。。。。。
赶到现场时SQLPLUS已无法登陆，数据库hang住，客户反应部分业务已受影响。
通过top命令查看资源使用情况，有2个oracle 客户端进程CPU使用率100%,经与客户确认，结束这2个进程后数据库恢复正常。
通过查看数据库故障时间历史会话视图，进行问题定位与分析，
select * from dba_hist_active_sess_historyy where y.sample_time >= trunc(sysdate)+9/24 and y.sample_time <=trunc(sysdate)+13/24；
统计分析在线活动进程：
通过分析历史会话表中出现异常的数据库进程信息，
步骤1： sid: 118,event:enq: US – contention锁定批量数据库进程
步骤2：查看进程118，发现118进程被sid:170，event: DFSlock handle锁定
步骤3：Sid: 170进各没有被阻塞
步骤4：在170运行前，sid:97号进程，运行语句：1234567896u1长时间未结束，造成数据库批量row cache lock，数据库运行环境恶化
步骤5：最终170成为触发条件，造成批量进程互锁，数据库HANG。。。。
锁定异常语句1234567896u1，相遇是缘。
2初识SQL君
查看数据库AWR报告
sql语句：1234567896u1每执时一次7413秒，2个小时！！！
查看执行计划
Coat: 1445，从执行计划看该语句,未发现严重性能问题，语句运行不应该如此慢。
3山穷水尽
查看语句：
--类似以下语句，由于安全问题，源语句较复杂无法提供，以下语句仅供参考
SELECTCOUNT(*) d,0q
FROMaaa A, bbb B, ccc C
WHEREA.aa = B.b
ANDSUBSTR(B.bb,6) = C.cc
ANDA.sjBETWEENto_date('20170301','yyyymmdd')ANDto_date('20170331','yyyymmdd')ANDC.cIN(1,2,3,4,5,6,7)
ANDA.a ='ddd'
该语句逻辑较为简单，由于未使用绑定变量，在sqlplus中再次运行，运行结果0.01秒就出来。
怀疑1、是否由于缓存软解析的原因，修改日期再次运行
SELECTCOUNT(*) d,0q
FROMaaa A, bbb B, ccc C
WHEREA.aa = B.b
ANDSUBSTR(B.bb,6) = C.cc
ANDA.sjBETWEENto_date('20170301','yyyymmdd')ANDto_date('20170331','yyyymmdd')ANDC.cIN(1,2,3,4,5,6,7)
ANDA.a ='ddd'
运行结果还是0.01秒出来。
怀疑2、是否该语句在运行时有大批量数据变化
通过查看AWRSQL、查看存储过程与业务沟通均未发现异常。
怀疑3、是否为OracleBug
非常简单的语句，客户端执行很快，程序执行异常缓慢，是否为Oracle Bug！！！
有点山穷水尽的感觉。
4 峰回路转
决定细致化深入分析AWRSQL,发现存在 TABLE ACCESS FULL等待事件，说明 b表存在全表扫描，查看A 、B、C表数分布情况
SQL> set line999
SQL> selectt.OWNER, t.TABLE_NAME, t.NUM_ROWS, t.LAST_ANALYZED, t.PARTITIONED
from dba_tables t where t.TABLE_NAME in ('a','b', 'c')
查看A表数据分布情况，
SELECTCOUNT(*) d,0q
FROMaaa A, bbb B, ccc C
WHEREA.aa = B.b
ANDSUBSTR(B.bb,6) = C.cc
ANDA.sjBETWEENto_date('20170301','yyyymmdd')ANDto_date('20170331','yyyymmdd')
ANDC.cIN(1,2,3,4,5,6,7)
ANDA.a ='ddd'
结合数据量分析sql语句，A表数据量40W,A表有两个条件sj日期类型、a字段字符类型，查看SQL语句对字段sj、a数据的抽取率，
表A数据总量42W,
SQL> selectcount(1) from a.a;
COUNT(1)
----------
420474
字段sj日期类型，抽取数据1827
SQL> select count(1) from a.a a where A.aBETWEENto_date('20170301','yyyymmdd')ANDto_date('20170331','yyyymmdd');
COUNT(1)
----------
1827
统计数据抽取比例0.43%,数据抽取比例较好，但需要抽取1827
SQL> selectround(1827/420474,4) from dual;
ROUND(1827/420474,4)
--------------------
.0043
查看2个字段同时使用，数据抽取比例，只抽取4条记录
SQL> selectcount(1) from aaa.a a where A.a BETWEEN to_date('20170301','yyyymmdd')ANDto_date('20170331','yyyymmdd')and A.b = '111';
COUNT(1)
----------
4
峰回路转是否可以通过添加索引优化。
5柳岸花明
为表A创建联合索引优化后执行计划
索引优化前cost:1445
索引优化后cost: 59
,
查看执行计划全表扫描变为索引扫描，数据库评估性能提升20倍以上，实际优化效果还有待进一步测试。
预估实际运行效果性能提升200倍以上，欲知实际运行效果，请见后续更新。
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！