oracle的sql问题性能优化经验总结

ORACLE的SQL问题性能优化经验总结
1.SQL 执行计划查看
使用PLSQL 可以看到SQL的执行计划，执行计划有四个执行方案：
默认的是ALL ROWS
如果对ALL ROWS 的执行计划不满意的话，我们可以选择Rule 规则查看。

如果Rule 规则的执行计划比较满意，我们就可以考虑使用如下方案进行优化：
1)检查SQL中对应数据表的分析时间，语句如下：
Select * from all_tables where table_name in (‘xxx’,’xxx’)
重点检查LAST_ANAL YZED 和NUM_ROWS 字段
如果分析时间太早，或者行数相差太大（包括行数为0实际不为0的情况）
有必要重新进行表分析。

2)如果表分析仍然无法凑效，则需要考虑使用hint的方式
就是在SQL的开始的中的select 语句部分加上/*+rule */ ，然后再进行测试。

2.执行计划分析
如果使用RULE 仍然没有好的执行计划，那么就要对SQL语句进行分析。

1）检查Table Access Full 全表扫描部分
查看数据表的数量是否很大，对大表的全表扫描是非常致命的；
2）检查Index Full Scan 全索引扫描部分
查看对应数据表的数量是否很大，对大表的全索引扫描只是比全表扫描好一点；
3）仔细对照使用的到的索引，是否有过滤性很差的索引
有部分索引的过滤能力很差，比如tb_cm_serv 的prod_id , tb_ba_subscription 的
联合索引(state,stat_date) 之类的，要注意大部分联合索引只能用到前面的确定条
件部分，后面的部分如果没有确定条件是不能用到的。

4）检查能否去掉不必要的排序
对于distinct ,union ,order by 这些语句，对SQL的性能影响很大，如果怀疑结果集
很大再排序导致SQL慢的情况，可以把这些排序部分去掉再观察SQL性能是否有
所改善，如果有改善的话，可以和开发商量能否修改写法、去掉排序部分。

5）局部分析
MBOSS的SQL语句，有很多都是非常复杂的，嵌套和union很多重，一下子看执
行计划容易一般很晕。

此时可以通过执行计划进行
3.索引选用方法
1）对于单表查询的情况，如果没有选择最佳的索引，则可以考虑使用表分析方式看是否会改善ORACLE选择，如果不能的话，则需要使用强制索引或者屏蔽非最佳
索引字段方式来强迫选择最佳索引。

例如：
select * from tb_rp_bizs_app_fin_list where app_date>sysdate-1
and app_date <sysdate and exch_id ='91001'
ORACLE选择的是exch_id 索引为先，app_date索引在后，如果用RULE规则，
也只会选择exch_id 索引，表分析后仍不改变选择（ORACLE对确定条件的优先
级权值比非确定条件高）
解决方法：
1.exch_id = ‘91001’改为exch_id || ‘’ = ‘91001’
2.select 后面使用强制索引条件，强制不让使用exch_id索引
select/*+no_index(a idx_rp_bizsappfinlist_5)*/* from tb_rp_bizs_app_fin_list a where app_date>sysdate-1and app_date
<sysdate and exch_id ='91001'
2）对于多表关联查询，需要通过观察执行计划和SQL语句的关联条件，找出当前索引路径，分析最佳索引路径，通过屏蔽等手段让ORACLE走最佳路径。

如果不是大范围扫描的语句，那么必将有1到多个的切入条件，这个切入条件一般是常量型的查询。

（某表索引字段.运算符.常量或者绑定变量），可以通过执行计划
和SQL语句来寻找第一索引切入。

不过，如果被切入的索引不幸是state=’B0A’, org_id=’91001’ ,prod_id=1 ，XXXid=0 ，city_id=’200’之类的话，那么从切入点开始，语句就进入了大范围扫描状态，里面的n重循环就更加没戏了，赶紧想办法找更好的切入点把。

如果切入点没有问题，那么就要通过执行计划和SQL语句，把ORACLE对每个表的索引路径总结出来。

例如：
SELECT s.subs_code,
a.action_id,
b.action_name,
TO_CHAR(s.stat_date, 'YYYYMMDDHH24MISS'),
s.cust_id,
s.req_id
FROM tb_pm_action b,
tb_ba_action a,
tb_pm_action_relation r,
tb_ba_subscription s
WHERE s.serv_id = 0
and r.city_id = s.native_code
and a.city_id = r.city_id
and b.city_id = a.city_id
and s.native_code = :3
AND s.cust_id = :anCustId
AND (s.stat NOT IN ('S0Z', 'S0C', 'S0R', 'S0X') or
s.stat = 'S0Z'AND S.STAT_DATE > SYSDATE - 1 / 48)
AND a.subs_id = s.subs_id
AND a.action_id = b.action_id
AND r.state = 'S0A'
AND r.relate_type = 'ART001'
AND r.relate_action_id = :anActionId
AND r.action_id = a.action_id
执行计划：
我们可以通过执行计划看到
ORACLE 对这个SQL的索引路径是
Tb_ba_subscription s
s.serv_id = 0 / s.cust_id = :anCustId
=》
tb_pm_action_relation r
r.relate_action_id = :anActionId
=》
tb_ba_action a
a.subs_id = s.subs_id /r.action_id = a.action_id
=》
tb_pm_action b
a.action_id =
b.action_id
这个路径的问题在第一切入点：
Tb_ba_subscription s s.serv_id = 0
这个切入点的过滤能力太差，会影响SQL的执行
所以SQL优化建议中，需要改为 s.serv_id + 0 =0 ，把 s.serv_id = 0 这个切入点屏蔽掉
当然，除了屏蔽方式外，通过表分析等都可以让ORACLE选择其他执行计划，不过ORACLE的CBO索引算法不是万能的，如果表分析也无法搞定，那就只能修改SQL的写法了。

3)对于全表扫描或者使用弱索引的数据表，如果发现更强的查询条件，但是该条件对
应字段没有索引，则需要考虑建立索引
例如：
delete from TB_CM_DYNBILL where object_id = :serv_id and dynbill_type = 'SERV'
这里明显需要建立object_id 索引，否则只能全表扫描。