前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >关于索引扫描的极速调优实战（第二篇)(r3笔记第82天)

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

作者头像
jeanron100
发布于 2018-03-15 12:05:02
7830
发布于 2018-03-15 12:05:02
举报
文章被收录于专栏：杨建荣的学习笔记杨建荣的学习笔记

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

代码语言：javascript
复制
    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

自己采用了如下的方式来改进，但是查看收效甚微，基本没有变化。

代码语言：javascript
复制
 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.

代码语言：javascript
复制

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; 然后查看执行计划。效率极大的提高了。

代码语言：javascript
复制
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秒，绝对是性能的极大提升。

代码语言：javascript
复制
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的拼接去除了。改为在程序中处理。 与其说是改进不是直接说是简化。

代码语言：javascript
复制
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语句直观来看肯定是走唯一性扫描，但是效果有多好呢。可以看看几个指标，都已经达到了最低。

代码语言：javascript
复制
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

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

本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2014-12-12，如有侵权请联系 cloudcommunity@tencent.com 删除
数据库
sql

本文分享自 杨建荣的学习笔记 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ，欢迎热爱写作的你一起参与！

数据库
sql
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！
产品介绍
精选特惠 用云无忧
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2024 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论