关于索引扫描的极速调优实战(第二篇)(r3笔记第82天)

在上一篇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

通过这个实例,我们可以看到业务优化还是最好的优化,从数据库的角度来做优化,也需要考虑到影响范围,尽量是影响和变更最低,效率最高。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-12-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏芋道源码1024

电商系统设计之订单

1. 前言2. 付款2.1 成功2.2 人祸2.4 天灾2.4 注释2.5 表结构2.5.1 交易表2.5.2 支付记录表2.5.3 订单表3. 运输4. 收货...

1743
来自专栏Grace development

电商系统设计之商品 (下)

完成上述流程则是完成了一笔交易,经常网上购物的童鞋都懂这个。今天我们讲下从商品系统到交易系统和订单系统的存储过程及其设计上的应该注意的“坑”。

9952
来自专栏杨建荣的学习笔记

生产环境大型sql语句调优实战第一篇(二) (r2笔记32天)

继续昨天的部分,上一篇的链接为: http://blog.itpub.net/23718752/viewspace-1217012/ 对这条大sql的性能瓶颈进...

2756
来自专栏跟着阿笨一起玩NET

(1)显示每个类别最新更新的数据

在项目中经常遇到求每个类别最新显示的数据,比如显示某某某类别最新更新的5条数据。特写下这个sql记录于此:

821
来自专栏Java帮帮-微信公众号-技术文章全总结

Oracle应用实战七——多表查询+PL/SQL

1 多表查询 内连接 使用一张以上的表做查询就是多表查询 语法: SELECT {DISTINCT} *|列名.. FROM 表名 别名,表名1 别名 {WH...

7614
来自专栏杨建荣的学习笔记

联系生活来简化sql(r3笔记第43天)

目前生产环境中有一条sql语句的CPU消耗很高。执行时间比较长。从awr中抓到的sql语句如下: SELECT run_request.run_mode, ...

2863
来自专栏杨建荣的学习笔记

关于索引扫描的极速调优实战(第一篇) (r3笔记第81天)

一般在生产环境中,如果某个查询中涉及一个大表,走索引扫描是显然是最值得推荐的方式,但是索引扫描有unique index scan, range scan,sk...

2474
来自专栏杨建荣的学习笔记

通过使用hint unnest调优sql语句(r4笔记第38天)

生产环境中有一条sql语句通过sql_monitor看到执行的时间实在是太惊人了,竟然达到了13个小时,而且还没有执行完。 SessionAPPC (20015...

3198
来自专栏Java学习123

ORACLE数据库日常维护

5768
来自专栏杨建荣的学习笔记

MySQL表连接优化的初步分析

每每一些很深刻的优化案例时,就会无比想念Oracle里的优化技巧,因为无论是从工具还是信息,都会丰富许多。

862

扫码关注云+社区

领取腾讯云代金券