场景：在存储执行的过程中，进行调试发现一段代码执行很慢。
存储过程只是一个参数的传入没有用到绑定变量；两次调试都是第一次调试，共享池中没有缓存执行计划
整个优化效果从原来的62秒到最后的0.2秒
环境：oracle 10.2。
优化过程如下：
部分原始代码：
select count(1)
from (select quxiantodishi(ti.city_code) city_code, t.trade_money, rownum
from trade_history t, sp_info ti
where t.sp_id = ti.sp_id
and t.partition_id = partition_num
and t.start_time between
to_date(trade_date || ' 00:00:00', 'YYYYMMDD HH24:MI:SS') and
to_date(trade_date || ' 23:59:59', 'YYYYMMDD HH24:MI:SS')
and t.des_payorg_id = 'DX05'
and t.ap_id = '306'
and t.ability_type = '0101')
where '0' || city_code = '0591'
传入的参数是：trade_date
测试过程1：传入参数为20160202
解释计划如下：
select count(*)
from (select quxiantodishi(ti.city_code) city_code, t.trade_money
from trade_history t, sp_info ti
where t.sp_id = ti.sp_id
and t.partition_id = 2
and t.start_time between
to_date('20160202 00:00:00', 'YYYYMMDD HH24:MI:SS') and
to_date('20160202 23:59:59', 'YYYYMMDD HH24:MI:SS')
and t.des_payorg_id = 'DX05'
and t.ap_id = '306'
and t.ability_type = '0101'
and t.result = '0'
) aa
where '0' || city_code = '0591'
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | | 10268 (3)| | |
| 1 | SORT AGGREGATE | | 1 | 66 | | | | |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID | TRADE_HISTORY | 1 | 46 | | 10268 (3)| 2 | 2 |
| 3 | NESTED LOOPS | | 21 | 1386 | | 10268 (3)| | |
|* 4 | TABLE ACCESS FULL | SP_INFO | 78 | 1560 | | 59 (7)| | |
| 5 | PARTITION RANGE SINGLE | | | | | | 2 | 2 |
| 6 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 7 | BITMAP AND | | | | | | | |
| 8 | BITMAP CONVERSION FROM ROWIDS| | | | | | | |
| 9 | SORT ORDER BY | | | | | | | |
|* 10 | INDEX RANGE SCAN | IDX_TRADE_HISTORY_ID | 2878 | | | 6 (0)| 2 | 2 |
| 11 | BITMAP CONVERSION FROM ROWIDS| | | | | | | |
| 12 | SORT ORDER BY | | | | 19M| | | |
|* 13 | INDEX RANGE SCAN | IDX_START_TIME | 2878 | | | 38 (6)| 2 | 2 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."DES_PAYORG_ID"='DX05' AND "T"."AP_ID"='306' AND "T"."ABILITY_TYPE"='0101' AND
"T"."RESULT"='0' AND "T"."PARTITION_ID"=2)
4 - filter('0'||"QUXIANTODISHI"("TI"."CITY_CODE")='0591')
10 - access("T"."SP_ID"="TI"."SP_ID")
filter("T"."SP_ID"="TI"."SP_ID")
13 - access("T"."START_TIME">=TO_DATE(' 2016-02-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"T"."START_TIME"<=TO_DATE(' 2016-02-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- 'PLAN_TABLE' is old version
执行时长为：62秒左右
解释计划分析：
（oracle 优化器的优化单位是查询块，所以我们可以命名查询块，特别是在复杂的语句中产生了查询转换，如果命名了查询块，能更使执行计划更加清晰明了）
(1)：先通过id=10的access("T"."SP_ID"="TI"."SP_ID")，("T"."SP_ID"="TI"."SP_ID") 这一步操作，进行筛选数据的结果集，放到pga中并排序.记为结果集1
结果集1是把得到的rowid,转换成位图，产生排序的原因：由于通过INDEX RANGE SCAN，产生了一些其他列的数据，不能保证数据是有序的。由于这个产生的
其他列的数据都是在这个组合索引（IDX_TRADE_HISTORY_ID）中，所以本身很小，不会产生临时表空间
因为oracle优化器认为前导列T.SP_ID右侧的TI.SP_ID，不固定，无法从指定索引地方查找，所以后续还进行了一个filter操作
(2): 再通过id=13的access("T"."START_TIME">=TO_DATE(' 2016-02-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"T"."START_TIME"<=TO_DATE(' 2016-02-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss')),筛选出结果集，放到pga中排序，不过这个数据量比较
大产生了临时表空间TempSpc。记为结果集2
结果集2是把得到的rowid,转换成位图
(3): 结果集1的位图和结果集2的位图进行位图与运算即id=7(BITMAP AND)
(4): 在通过BITMAP CONVERSION TO ROWIDS,转换成rowid,与SP_INFO进行nl链接
(5): nl链接后产生的rowid,进行回表扫描，筛选出符合要求的数据。
--发现问题1：谓词推入过早
我们发现id=2 即：
filter("T"."DES_PAYORG_ID"='DX05' AND "T"."AP_ID"='306' AND "T"."ABILITY_TYPE"='0101' AND
"T"."RESULT"='0' AND "T"."PARTITION_ID"=2)在
id=4即
filter('0'||"QUXIANTODISHI"("TI"."CITY_CODE")='0591')之后才执行，可见优化器选择了谓词推入
谓词推入的语句是 where '0' || city_code = '0591'，
这个谓词推入直接造成的结果是sp_info表的TABLE ACCESS FULL，也就是说每查询一条就要进行一次filter.很容易产生大量的逻辑读
而且这个谓词推入的是没有过滤掉大部分数据之前推入进去的，很不符合我们的一般的优化原则
所以我们的解决方案，是阻止谓词推入。即加入rownum,在aa 这个子查询执行完以后在进行筛选
--优化方案如下：
select count(*)
from (select quxiantodishi(ti.city_code) city_code, t.trade_money, rownum
from trade_history t, sp_info ti
where t.sp_id = ti.sp_id
and t.partition_id = 2
and t.start_time between
to_date('20160202 00:00:00', 'YYYYMMDD HH24:MI:SS') and
to_date('20160202 23:59:59', 'YYYYMMDD HH24:MI:SS')
and t.des_payorg_id = 'DX05'
and t.ap_id = '306'
and t.ability_type = '0101'
and t.result = '0') aa
where '0' || city_code = '0591'
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2002 | 17206 (1)| | |
| 1 | SORT AGGREGATE | | 1 | 2002 | | | |
|* 2 | VIEW | | 2085 | 4076K| 17206 (1)| | |
| 3 | COUNT | | | | | | |
|* 4 | HASH JOIN | | 2085 | 134K| 17206 (1)| | |
| 5 | PARTITION RANGE SINGLE | | 2085 | 95910 | 17150 (1)| 2 | 2 |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID| TRADE_HISTORY | 2085 | 95910 | 17150 (1)| 2 | 2 |
|* 7 | INDEX RANGE SCAN | IDX_START_TIME | 11677 | | 364 (1)| 2 | 2 |
| 8 | TABLE ACCESS FULL | SP_INFO | 7797 | 152K| 56 (2)| | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter('0'||"CITY_CODE"='0591')
4 - access("T"."SP_ID"="TI"."SP_ID")
6 - filter("T"."DES_PAYORG_ID"='DX05' AND "T"."AP_ID"='306' AND "T"."ABILITY_TYPE"='0101' AND
"T"."RESULT"='0' AND "T"."PARTITION_ID"=2)
7 - access("T"."START_TIME">=TO_DATE(' 2016-02-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"T"."START_TIME"<=TO_DATE(' 2016-02-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- 'PLAN_TABLE' is old version
优化后执行时长：0.2秒
发现问题2：组合索引创建不合适
为什么会产生BITMAP CONVERSION FROM ROWIDS。通过查资料我们发现，是由于组合索引的选择性不高，造成的。 ok,我们验证一下这个问题
首先两个索引创建方式如下：
create index IDX_START_TIME on TRADE_HISTORY (START_TIME);
create index IDX_TRADE_HISTORY_ID on TRADE_HISTORY (SP_ID, SERVICE_ID, AP_ID);
select count(distinct sp_id) dist_sp_id, --5910
count(distinct service_id) dist_service_id,---11326
count(distinct ap_id) dist_ap_id, --33
count(*) cnt -- 82619157
from TRADE_HISTORY;
select count(*) -- 11352
from (select sp_id, service_id, ap_id
from TRADE_HISTORY
group by sp_id, service_id, ap_id);
通过上面初步判断 组合索引唯一性基本上是SERVICE_ID 决定的。而且全表8200万数据量，组合索引三列的唯一值只有11352个，选择性很差
组合索引的创建原则一般是：（1）选择性差的作为前导列，可以选择索引跳跃扫描，即（ap_id，service_id，sp_id）（2）选择性好的作为前导列，可以过滤更多数据，即（service_id，sp_id，ap_id）
--优化方案如下：
修改索引的顺序，不过希望不大，还是重复性太多。因此我们采用另外一个方法，既然是组合索引唯一性不高导致的位图转换，那我们禁止位图转换
select /*+ opt_param('_b_tree_bitmap_plans','false') */ count(*)
from (select quxiantodishi(ti.city_code) city_code, t.trade_money
from trade_history t, sp_info ti
where t.sp_id = ti.sp_id
and t.partition_id = 2
and t.start_time between
to_date('20160202 00:00:00', 'YYYYMMDD HH24:MI:SS') and
to_date('20160202 23:59:59', 'YYYYMMDD HH24:MI:SS')
and t.des_payorg_id = 'DX05'
and t.ap_id = '306'
and t.ability_type = '0101'
and t.result = '0'
) aa
where '0' || city_code = '0591'
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 17209 (1)| | |
| 1 | SORT AGGREGATE | | 1 | 66 | | | |
|* 2 | HASH JOIN | | 21 | 1386 | 17209 (1)| | |
|* 3 | TABLE ACCESS FULL | SP_INFO | 78 | 1560 | 59 (7)| | |
| 4 | PARTITION RANGE SINGLE | | 2085 | 95910 | 17150 (1)| 2 | 2 |
|* 5 | TABLE ACCESS BY LOCAL INDEX ROWID| TRADE_HISTORY | 2085 | 95910 | 17150 (1)| 2 | 2 |
|* 6 | INDEX RANGE SCAN | IDX_START_TIME | 11677 | | 364 (1)| 2 | 2 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SP_ID"="TI"."SP_ID")
3 - filter('0'||"QUXIANTODISHI"("TI"."CITY_CODE")='0591')
5 - filter("T"."DES_PAYORG_ID"='DX05' AND "T"."AP_ID"='306' AND "T"."ABILITY_TYPE"='0101' AND
"T"."RESULT"='0' AND "T"."PARTITION_ID"=2)
6 - access("T"."START_TIME">=TO_DATE(' 2016-02-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"T"."START_TIME"<=TO_DATE(' 2016-02-02 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- 'PLAN_TABLE' is old version
优化后执行时长：0.2秒。
优化完毕。
测试过程2：传入参数为20160307
解释计划如下：
select count(*)
from (select quxiantodishi(ti.city_code) city_code, t.trade_money
from trade_history t, sp_info ti
where t.sp_id = ti.sp_id
and t.partition_id = 3
and t.start_time between
to_date('20160307 00:00:00', 'YYYYMMDD HH24:MI:SS') and
to_date('20160307 23:59:59', 'YYYYMMDD HH24:MI:SS')
and t.des_payorg_id = 'DX05'
and t.ap_id = '306'
and t.ability_type = '0101'
and t.result = '0'
) aa
where '0' || city_code = '0591'
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 20217 (1)| | |
| 1 | SORT AGGREGATE | | 1 | 66 | | | |
|* 2 | HASH JOIN | | 14 | 924 | 20217 (1)| | |
|* 3 | TABLE ACCESS FULL | SP_INFO | 78 | 1560 | 59 (7)| | |
| 4 | PARTITION RANGE SINGLE | | 1440 | 66240 | 20158 (1)| 3 | 3 |
|* 5 | TABLE ACCESS BY LOCAL INDEX ROWID| TRADE_HISTORY | 1440 | 66240 | 20158 (1)| 3 | 3 |
|* 6 | INDEX RANGE SCAN | IDX_START_TIME | 9145 | | 381 (1)| 3 | 3 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SP_ID"="TI"."SP_ID")
3 - filter('0'||"QUXIANTODISHI"("TI"."CITY_CODE")='0591')
5 - filter("T"."DES_PAYORG_ID"='DX05' AND "T"."AP_ID"='306' AND "T"."ABILITY_TYPE"='0101' AND
"T"."RESULT"='0' AND "T"."PARTITION_ID"=3)
6 - access("T"."START_TIME">=TO_DATE(' 2016-03-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"T"."START_TIME"<=TO_DATE(' 2016-03-07 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- 'PLAN_TABLE' is old version
why 传入参数为20160307 产生的执行计划和测试过程一种的禁用位图转换的执行计划一样呢。难道是索引没有收集好。so,重新收集IDX_START_TIME，IDX_TRADE_HISTORY_ID
这两个索引，收集方式如下：
BEGIN
SYS.DBMS_STATS.GATHER_INDEX_STATS(OwnName => 'UPCFJ',
IndName => 'IDX_START_TIME',
Estimate_Percent => 100,
Degree => SYS.DBMS_STATS.DEFAULT_DEGREE,
No_Invalidate => FALSE);
END;
BEGIN
SYS.DBMS_STATS.GATHER_INDEX_STATS(OwnName => 'UPCFJ',
IndName => 'IDX_TRADE_HISTORY_ID',
Estimate_Percent => 100,
Degree => SYS.DBMS_STATS.DEFAULT_DEGREE,
No_Invalidate => FALSE);
END;
再次执行时候发现执行计划不变，另外经过多次测试，发现只有2月分区才会出现位图转换的情况。为什么导致只有2月份的分区选择了位图转换呢，位图索引转换的目的就是为了较少回表的代价。由于本人开发权限有限，只能从侧面分析，进行位图转换说明减少回表，说明扫描了无用的rowid。
由此想到了高水位和选择性不好的索引,联系了另外一个开发人员说：前一段时间对，2月的分区删除大量数据，没有回收高水位。
联系DBA回收2月分区的高水位
总结：1:运用查询块快速定位 2：分析解释计划步骤查找问题。3：根据数据组合找到不合适的组合索引。4：高水位线的侧面分析方法
本站文章版权归原作者及原出处所有 。内容为作者个人观点， 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台，并不用于任何商业目的，如果有任何问题，请及时联系我们，我们将根据著作权人的要求，立即更正或者删除有关内容。本站拥有对此声明的最终解释权。