所以这个问题的处理就比较纠结，想得到立竿见影的效果吧，使用index_ss不起作用，做全局变更吧，这样可能会影响其它的sql运行。使用alter session处理，在程序中实现又不现实。
SELECT /*+opt_param(‘_optimizer_skip_scan_enabled‘,true)*/ ....
SELECT /*+opt_param(‘_optimizer_skip_scan_enabled‘,
‘true‘)*/ ....
这样就能够达到预期的目标了。从0.5秒到0.01秒，绝对是性能的极大提升。
Plan hash value: 387232563
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 |
6 (
67)|
00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 37 | 3 (67)| 00:00:01 | 13 | 25 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION | 1 | 37 | 3 (67)| 00:00:01 | 13 | 25 |
|* 3 |
INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | | 3 (67)| 00:00:01 | 13 | 25 |
| 4 | SORT AGGREGATE | | 1 | 34 | | | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 34 | 3 (67)| 00:00:01 | 13 | 25 |
|* 6 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | 34 | 3 (67)| 00:00:01 | 13 | 25 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
"ITEM_ID"=15131)
filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131 AND
TO_CHAR("CYCLE_YEAR",‘9999‘)||TO_CHAR("CYCLE_MONTH",‘09‘)= (SELECT
MAX(TO_CHAR("CYCLE_YEAR",‘9999‘)||TO_CHAR("CYCLE_MONTH",‘09‘)) FROM "PRDUSG3O"." CRDT_LMT_NOTIFICATION"
" CRDT_LMT_NOTIFICATION" WHERE "CYCLE_CODE"=25 AND "OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND
"CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131))
6 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
"ITEM_ID"=15131)
filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131)
可能这个问题到此就告一段落了，我在得到了一个初步的结论之后和开发部门进行协调，他们也试图从业务上进行简化。
最后他们把纠结的cycle_month和cycle_year的拼接去除了。改为在程序中处理。
与其说是改进不是直接说是简化。
SELECT
LAST_THRESHOLD, CYCLE_MONTH, CYCLE_YEAR
FROM PM9_CRDT_LMT_NOTIFICATION
WHERE ITEM_ID = :a
AND AGREEMENT_ID = :a
AND CYCLE_CODE = :a
AND OFFER_INSTANCE = :a
AND CUSTOMER_ID = :a
AND CYCLE_YEAR=:a
AND CYCLE_MONTH=:a
这条sql语句直观来看肯定是走唯一性扫描，但是效果有多好呢。可以看看几个指标，都已经达到了最低。
Plan hash value: 404442430
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 |
1 (0)|
00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 37 | 1 (0)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION | 1 | 37 | 1 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX UNIQUE SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=TO_NUMBER(:A) AND "CYCLE_MONTH"=TO_NUMBER(:A) AND "CYCLE_YEAR"=TO_NUMBER(:A) AND
"CUSTOMER_ID"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND "OFFER_INSTANCE"=TO_NUMBER(:A) AND
"ITEM_ID"=TO_NUMBER(:A))
从开发得到的反馈是这个逻辑的修改也不复杂，最后他们决定使用简化后的sql。
在协调部署之后。速度有了极大的提升。
处理的事务数有了近10倍的提升。从十万事务到近百万事务 ，处理的速度还是提升了很多。
以下是事务处理的一些反馈数据。可以看到效果还是很明显的。
|
TIME
|
COUNT
|
|
20141212 00
|
119844
|
|
20141212 01
|
57357
|
|
20141212 02
|
23153
|
|
20141212 03
|
20610
|
|
20141212 04
|
111148
|
|
20141212 05
|
102540
|
|
20141212 06
|
59834
|
|
20141212 07
|
213985
|
|
20141212 08
|
69733
|
|
20141212 09
|
137163
|
|
20141212 10
|
163106
|
|
20141212 11
|
87091
|
|
20141212 12
|
89880
|
|
20141212 13
|
841172
|
|
20141212 14
|
960209
|
|
20141212 15
|
948309
|
|
20141212 16
|
899030
|
|
20141212 17
|
870231
|
|
20141212 18
|
953362
|
通过这个实例，我们可以看到业务优化还是最好的优化，从数据库的角度来做优化，也需要考虑到影响范围，尽量是影响和变更最低，效率最高。
