关于索引扫描的极速调优实战(第二篇)

在上一篇http://blog.itpub.net/23718752/viewspace-1364914/ 中我们大体介绍了下问题的情况,已经初步根据awr能够抓取到存在问题的sql语句。
这条sql语句执行很频繁,目前平均执行时间在0.5秒。开发部门希望我们能不能做点优化,他们也在同时想办法从业务上来优化这个问题。从0.5秒的情况下,能够再提高很多,是得费很大力气的。
况且这个问题比较紧急,从拿到sql语句开始,就感觉到一种压力。
最开始的注意力都集中在cycle_month和cycle_year的处理上。
对于下面的部分,是这条sql语句的关键,cycle_year,cycle_month是在索引列中,但是根据业务逻辑,需要把cycle_year,cycle_month拼成一个数字,然后计算cycle_year+cycle_month最大的值。
目前的实现是把cycle_year准换成为字符型,然后使用这个字符串在子查询中匹配。这样的话,cycle_year,cycle_month作为索引列就不能直接使用索引了,还得依靠第一个索引列cycle_code.
   AND (TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09')) =
       (SELECT MAX(TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09'))
          FROM CRDT_LMT_NOTIFICATION

自己采用了如下的方式来改进,但是查看收效甚微,基本没有变化。
 AND (CYCLE_YEAR,CYCLE_MONTH) =
       (SELECT substr(MAX(cycle_year*100+cycle_month),0,4),substr(MAX(cycle_year*100+cycle_month),5,6)

所以看来需要索引扫描上多下点功夫。
根据sqlprofile中的提示,使用index skip scan效率最高。
但是使用index_ss却始终都是走index range scan.
SELECT /*+index_ss(CRDT_LMT_NOTIFICATION CRDT_LMT_NOTIFICATION_PK)*/LAST_THRESHOLD, CYCLE_MONTH, CYCLE_YEAR
  FROM CRDT_LMT_NOTIFICATION
 WHERE CYCLE_CODE = 25
        AND ITEM_ID = 15131
       AND AGREEMENT_ID = 15997361
       AND OFFER_INSTANCE = 223499890
       AND CUSTOMER_ID = 10349451
   AND (TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09')) =
       (SELECT  MAX(TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09'))
          FROM PM9_CRDT_LMT_NOTIFICATION
         WHERE CYCLE_CODE = 25
        AND ITEM_ID = 15131
       AND AGREEMENT_ID = 15997361
       AND OFFER_INSTANCE = 223499890
       AND CUSTOMER_ID = 10349451)          
SQL> @plan
Plan hash value: 2310822947
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |     1 |    37 |  4281   (1)| 00:00:52 |       |       |
|   1 |  PARTITION RANGE ITERATOR          |                              |     1 |    37 |  2141   (1)| 00:00:26 |    13 |    25 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID|     CRDT_LMT_NOTIFICATION    |     1 |    37 |  2141   (1)| 00:00:26 |    13 |    25 |
|*  3 |    INDEX RANGE SCAN                |     CRDT_LMT_NOTIFICATION_PK |     1 |       |  2140   (1)| 00:00:26 |    13 |    25 |
|   4 |     SORT AGGREGATE                 |                              |     1 |    34 |            |          |       |       |
|   5 |      PARTITION RANGE ITERATOR      |                              |     1 |    34 |  2140   (1)| 00:00:26 |    13 |    25 |
|*  6 |       INDEX RANGE SCAN             |     CRDT_LMT_NOTIFICATION_PK |     1 |    34 |  2140   (1)| 00:00:26 |    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 "    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)

最后发现主要的原因是因为隐含参数_optimizer_skip_scan_enabled 值为"false"导致的。
SQL> show parameter skip_scan
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_optimizer_skip_scan_enabled         boolean     FALSE
 
对于这个参数,我使用alter sessison在session级做了改动。
alter session set "_optimizer_skip_scan_enabled"=true;
然后查看执行计划。效率极大的提高了。
SQL> @plan
Plan hash value: 387232563
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |     1 |    37 |     3  (34)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR          |                              |     1 |    37 |     2  (50)| 00:00:01 |   KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID|     CRDT_LMT_NOTIFICATION    |     1 |    37 |     2  (50)| 00:00:01 |   KEY |   KEY |
|*  3 |    INDEX SKIP SCAN                 |     CRDT_LMT_NOTIFICATION_PK |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|   4 |     SORT AGGREGATE                 |                              |     1 |    34 |            |          |       |       |
|   5 |      PARTITION RANGE ITERATOR      |                              |     1 |    34 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  6 |       INDEX SKIP SCAN              |     CRDT_LMT_NOTIFICATION_PK |     1 |    34 |     1   (0)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("CYCLE_CODE"=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))
       filter("OFFER_INSTANCE"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND
              "ITEM_ID"=TO_NUMBER(:A) 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"=TO_NUMBER(:A) AND "OFFER_INSTANCE"=TO_NUMBER(:A) AND
              "AGREEMENT_ID"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND "ITEM_ID"=TO_NUMBER(:A)))
   6 - access("CYCLE_CODE"=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))
       filter("OFFER_INSTANCE"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND
              "ITEM_ID"=TO_NUMBER(:A))
为什么skip scan效率这么高,但是使用隐含参数禁用了它呢。
产品部门的解释是对于skip scan在大多数的场景中,效率不是很理想,基本跟index full scan的效果一样,所以从优化器内部使用隐含参数禁用,就使用了range scan.
所以这个问题的处理就比较纠结,想得到立竿见影的效果吧,使用index_ss不起作用,做全局变更吧,这样可能会影响其它的sql运行。使用alter session处理,在程序中实现又不现实。

最后使用另外一个hint解决上面的顾虑。opt_param,这个hint是在10gR2之后引进的,要解决的问题就是可以避免系统级的db参数变更。
尝试的hint格式如下。
SELECT  /*+opt_param('_optimizer_skip_scan_enabled',true)*/  ....
但是执行计划中缺还是走了range scan。资源消耗跟没加hint一个样。
最后发现对于这个hint需要写为:
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

通过这个实例,我们可以看到业务优化还是最好的优化,从数据库的角度来做优化,也需要考虑到影响范围,尽量是影响和变更最低,效率最高。
上一篇:使用TensorFlow提供的slim模型来训练数据模型供iOS使用


下一篇:支付宝UI篇-扫码和AR