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倍以上，欲知实际运行效果，请见后续更新。