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

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

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

时间： 2024-09-20 13:47:43

关于索引扫描的极速调优实战（第二篇)的相关文章

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

一般在生产环境中,如果某个查询中涉及一个大表,走索引扫描是显然是最值得推荐的方式,但是索引扫描有unique index scan, range scan,skip scan, full scan, fast full scan,这些索引扫描看起来好像很繁杂,但是如果掌握得当,却能够在索引扫描的基础上极速提升性能.关于索引扫描的方式,可以参考.http://blog.itpub.net/23718752/viewspace-1335358/ 关于索引的使用模式  首先来看看这个问题. 开发反应这

生产环境sql语句调优实战第二篇

在生产环境通过sql monitor监控到有一条sql执行效率很差.执行了大约5个小时,得到的sql monitor报告如下: Global Information: EXECUTING Instance ID : 1 Buffer Gets IO Requests Database Time Wait Activity . 40M . 17M . . 8450s . . . 100% Session : PRODUSER(14:22343) SQL ID : fkzafpjs28d6d SQL

生产环境大型sql语句调优实战第一篇（一）

在生产环境中有一条sql语句的性能极差,在早晨非高峰时段运行抽取数据,平均要花费40分钟,有时候竟然要跑10个多小时.sql语句比较长,需要点耐心往下看.我对表的数据量都做了简单的说明. 首先拿到sql语句,一看到关联的表有一长串,而且都是很大的表.如果性能很差,可能和走全表扫描有关,还有可能和多表关联时,表的查取顺序也有一定的关系. SELECT   DISTINCT CA.L9_CONVERGENCE_CODE AS ATB2,                 CU.CUST_SUB_TYP

生产环境sql语句调优实战第九篇

生产环境中有一些sql语句是不定时炸弹,不声不响的运行着,可能相关的表很大,运行时间达数小时甚至数天. 上周在生产环境中发现一条sql语句,运行时间几乎是按照天来计算的.看到这种sql语句,就希望能够马上能够找到立竿见影的效果,不过欲速则不达,需要做不少工作.一定要反复验证可行. sql语句的运行情况如下: SQL Execution ID : 16777217  Execution Started : 10/18/2014 11:46:30  First Refresh Time : 10/1

生产环境大型sql语句调优实战第一篇（二）

继续昨天的部分,上一篇的链接为: http://blog.itpub.net/23718752/viewspace-1217012/ 对这条大sql的性能瓶颈进行了分析.主要瓶颈在于一个很大的业务表,数据量在亿级.如果通过时间条件来过滤,会有5%以内的数据被过滤出来. 但是没有时间相关的索引字段,所以会走全表扫描,在目前的产品线中,这个大分区表的索引时严格控制的,所以最后经过测试和比对,还是考虑加并行来提高数据的查取速度. --查找性能瓶颈,根据反馈,查取的数据其实并不错,可能在几千条以内的样子

SQL Server调优系列进阶篇（如何索引调优）

原文:SQL Server调优系列进阶篇(如何索引调优) 前言 上一篇我们分析了数据库中的统计信息的作用,我们已经了解了数据库如何通过统计信息来掌控数据库中各个表的内容分布.不清楚的童鞋可以点击参考. 作为调优系列的文章,数据库的索引肯定是不能少的了,所以本篇我们就开始分析这块内容,关于索引的基础知识就不打算深入分析了,网上一搜一片片的,本篇更侧重的是一些实战项内容展示,希望通过本篇文章各位看官能在真正的场景中找到合适的解决方法足以. 对于索引的使用,我希望的是遇到问题找到合适的解决方法就可以,

SQL Server调优系列进阶篇（如何维护数据库索引）

原文:SQL Server调优系列进阶篇(如何维护数据库索引) 前言 上一篇我们研究了如何利用索引在数据库里面调优,简要的介绍了索引的原理,更重要的分析了如何选择索引以及索引的利弊项,有兴趣的可以点击查看. 本篇延续上一篇的内容,继续分析索引这块,侧重索引项的日常维护以及一些注意事项等. 闲言少叙,进入本篇的主题. 技术准备 数据库版本为SQL Server2012,前几篇文章用的是SQL Server2008RT,内容区别不大,利用微软的以前的案例库(Northwind)进行分析,部分内容也会

SQL Server调优系列基础篇

原文:SQL Server调优系列基础篇 前言 关于SQL Server调优系列是一个庞大的内容体系,非一言两语能够分析清楚,本篇先就在SQL 调优中所最常用的查询计划进行解析,力图做好基础的掌握,夯实基本功!而后再谈谈整体的语句调优. 通过本篇了解如何阅读和理解查询计划.并且列举一系列最常用的查询执行运算符. 技术准备 基于SQL Server2008R2版本,利用微软的一个更简洁的案例库(Northwind)进行解析.  一.区别不同的运算符 在所有T-SQL语句在执行的时候,都会将语句分解

SQL Server调优系列基础篇（常用运算符总结）

原文:SQL Server调优系列基础篇(常用运算符总结) 前言 上一篇我们介绍了如何查看查询计划,本篇将介绍在我们查看的查询计划时的分析技巧,以及几种我们常用的运算符优化技巧,同样侧重基础知识的掌握. 通过本篇可以了解我们平常所写的T-SQL语句,在SQL Server数据库系统中是如何分解执行的,数据结果如何通过各个运算符组织形成的. 技术准备 基于SQL Server2008R2版本,利用微软的一个更简洁的案例库(Northwind)进行解析. 一.数据连接 数据连接是我们在写T-SQL语