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

一般在生产环境中，如果某个查询中涉及一个大表，走索引扫描是显然是最值得推荐的方式，但是索引扫描有unique index scan, range scan,skip scan, full scan, fast full scan,这些索引扫描看起来好像很繁杂，但是如果掌握得当，却能够在索引扫描的基础上极速提升性能。关于索引扫描的方式，可以参考。http://blog.itpub.net/23718752/viewspace-1335358/ 关于索引的使用模式 
首先来看看这个问题。
开发反应这周有一个process处理数据特别慢，有很多的业务处理都受到了影响，想让我看看在数据库级别能够发现什么。
从这个反馈来说，可能数据库中是有锁了，或者是存在着一些异常的进程消耗了较多的资源，要不就是sql语句的问题。因为这个库已经运行很长时间了。涉及到的开发变更还是比较少的。所以就先查看了数据库的负载。
BEGIN_TIME------------------------- END_TIME--------------------------- ELAPSED_TIME- BTIME----- WORKLOAD_PER--------
----------------------------------- ----------------------------------- ------------- ---------- --------------------
12360 ** 11-DEC-14 01.00.06.432 AM  12361 ** 11-DEC-14 02.00.08.531 AM         60.035     103.07 171%
12361 ** 11-DEC-14 02.00.08.531 AM  12362 ** 11-DEC-14 03.00.11.099 AM         60.043     105.13 175%
12362 ** 11-DEC-14 03.00.11.099 AM  12363 ** 11-DEC-14 04.00.13.507 AM         60.040     148.71 247%
12363 ** 11-DEC-14 04.00.13.507 AM  12364 ** 11-DEC-14 05.00.17.328 AM         60.064     169.35 281%
12364 ** 11-DEC-14 05.00.17.328 AM  12365 ** 11-DEC-14 06.00.20.742 AM         60.057      89.84 149%
12365 ** 11-DEC-14 06.00.20.742 AM  12366 ** 11-DEC-14 07.00.23.766 AM         60.050      89.49 149%
12366 ** 11-DEC-14 07.00.23.766 AM  12367 ** 11-DEC-14 08.00.25.956 AM         60.037     113.92 189%
12367 ** 11-DEC-14 08.00.25.956 AM  12368 ** 11-DEC-14 09.00.28.480 AM         60.042      92.33 153%
12368 ** 11-DEC-14 09.00.28.480 AM  12369 ** 11-DEC-14 10.00.31.163 AM         60.045     180.66 300%
12369 ** 11-DEC-14 10.00.31.163 AM  12370 ** 11-DEC-14 11.00.34.040 AM         60.048     204.65 340%
12370 ** 11-DEC-14 11.00.34.040 AM  12371 ** 11-DEC-14 12.00.37.255 PM         60.054     361.93 602%
12371 ** 11-DEC-14 12.00.37.255 PM  12372 ** 11-DEC-14 01.00.40.741 PM         60.058     400.98 667%
12372 ** 11-DEC-14 01.00.40.741 PM  12373 ** 11-DEC-14 02.00.43.680 PM         60.049     225.84 376%
12373 ** 11-DEC-14 02.00.43.680 PM  12374 ** 11-DEC-14 03.00.46.353 PM         60.045     220.51 367%
12374 ** 11-DEC-14 03.00.46.353 PM  12375 ** 11-DEC-14 04.00.48.809 PM         60.041     276.56 460%
12375 ** 11-DEC-14 04.00.48.809 PM  12376 ** 11-DEC-14 05.00.51.411 PM         60.043     204.22 340%
从整体来看，负载还是可以接受的。
然后查看锁的情况，也没有发现什么延迟的锁等待。这个时候锁等待导致的延迟可能也排除了。
这个时候抓一个awr报告看看细节。

Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 12314 09-Dec-14 03:00:07 253 4.4
End Snap: 12315 09-Dec-14 04:00:09 248 4.5
Elapsed:   60.04 (mins)    
DB Time:   86.64 (mins)    

从load profile来看，cpu使用率不高。相对来说,logical reads较高。

Per Second Per Transaction Per Exec Per Call
DB Time(s): 1.4 1.4 0.00 0.00
DB CPU(s): 1.4 1.4 0.00 0.00
Redo size: 81,566.1 77,546.7    
Logical reads: 121,122.2 115,153.6    
Block changes: 393.3 373.9    
Physical reads: 9.7 9.2    
Physical writes: 16.6 15.8    
User calls: 534.7 508.4    
Parses: 3.8 3.6    
Hard parses: 0.1 0.1    
W/A MB processed: 0.1 0.1    
Logons: 0.1 0.1    
Executes: 291.1 276.8    
Rollbacks: 0.0 0.0    
Transactions: 1.1      

等待事件的情况如下。

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU   5,124   98.56  
db file sequential read 34,433 65 2 1.24 User I/O
log file sync 3,515 16 5 0.30 Commit
control file sequential read 34,785 10 0 0.20 System I/O
SQL*Net message to client 1,751,834 1 0 0.03 Network

直接进入sql语句的部分。

Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text
4,352.98 8,375 0.52 83.73 99.95 0.01 05g6ywz7311f6 m1EnvelopeMT@ccbdbpr5 (TNS V1-V3) /* */ SELECT LAST_THRESHOLD, C...
250.14 4,942 0.05 4.81 99.94 0.00 06pyusmmaz7bn m1EnvelopeMT@ccbdbpr5 (TNS V1-V3) /* */ select count (1) from RP...
158.68 4,810 0.03 3.05 99.89 0.00 d59u1a0r9xa9c m1EnvelopeMT@ccbdbpr5 (TNS V1-V3) /* */ SELECT IDENTIFIER FROM (...

这个时候可以很明显的看到sql语句05g6ywz7311f6占用了83%的比例。可以看到每条语句的执行时间在0.52秒左右。看起来还是可以的，但是从报表中来看，这条语句的执行频率很高。
对应的sql语句如下：
SELECT LAST_THRESHOLD, CYCLE_MONTH, CYCLE_YEAR
  FROM CRDT_LMT_NOTIFICATION
 WHERE ITEM_ID = :a
   AND AGREEMENT_ID = :a
   AND CYCLE_CODE = :a
   AND OFFER_INSTANCE = :a
   AND CUSTOMER_ID = :a
   AND (TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09')) =
       (SELECT MAX(TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09'))    --这个语句的关键就在于标黄的部分,这条语句是想得到cycle_year,cycle_month最高的值，把year,month拼接成20141209这样的形式
          FROM CRDT_LMT_NOTIFICATION
         WHERE ITEM_ID = :a
           AND AGREEMENT_ID = :a
           AND CYCLE_CODE = :a
           AND OFFER_INSTANCE = :a
           AND CUSTOMER_ID = :a)
对应的索引如下：
INDEX_NAME                     TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST                                                                                                          TABLE_TYPE STATUS   NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- --------------------- ---------- ------ ---------- --------- --------------------- ---------- ------ ---------- --------- --------------------- --------
CRDT_LMT_NOTIFICATION_PK              NORMAL     UNIQUE    YES CYCLE_CODE,CYCLE_MONTH,CYCLE_YEAR,CUSTOMER_ID,AGREEMENT_ID,OFFER_INSTANCE,ITEM_ID TABLE      N/A       5457339 03-DEC-14 N

查看对应的执行计划。
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |     2 |    74 |  4281   (1)| 00:00:52 |       |       |
|   1 |  PARTITION RANGE ITERATOR          |                              |     2 |    74 |  2141   (1)| 00:00:26 |   KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID|     CRDT_LMT_NOTIFICATION    |     2 |    74 |  2141   (1)| 00:00:26 |   KEY |   KEY |
|*  3 |    INDEX RANGE SCAN                |     CRDT_LMT_NOTIFICATION_PK |     1 |       |  2141   (1)| 00:00:26 |   KEY |   KEY |
|   4 |     SORT AGGREGATE                 |                              |     1 |    34 |            |          |       |       |
|   5 |      PARTITION RANGE ITERATOR      |                              |     7 |   238 |  2141   (1)| 00:00:26 |   KEY |   KEY |
|*  6 |       INDEX RANGE SCAN             |     CRDT_LMT_NOTIFICATION_PK |     7 |   238 |  2141   (1)| 00:00:26 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("CYCLE_CODE"=:A AND "CUSTOMER_ID"=:A AND "AGREEMENT_ID"=:A AND "OFFER_INSTANCE"=:A AND "ITEM_ID"=:A)
       filter("OFFER_INSTANCE"=:A AND "AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND "ITEM_ID"=:A AND
              TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT /*+ PUSH_SUBQ OPT_ESTIMATE (TABLE
              "CRDT_LMT_NOTIFICATION" SCALE_ROWS=1016.803110 ) OPT_ESTIMATE (INDEX_FILTER "CRDT_LMT_NOTIFICATION"
              "CRDT_LMT_NOTIFICATION_PK" SCALE_ROWS=440.696164 ) INDEX ("CRDT_LMT_NOTIFICATION" "CRDT_LMT_NOTIFICATION_PK")
              */ MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM "CRDT_LMT_NOTIFICATION"
              "CRDT_LMT_NOTIFICATION" WHERE "CYCLE_CODE"=:A AND "OFFER_INSTANCE"=:A AND "AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND
              "ITEM_ID"=:A))
   6 - access("CYCLE_CODE"=:A AND "CUSTOMER_ID"=:A AND "AGREEMENT_ID"=:A AND "OFFER_INSTANCE"=:A AND "ITEM_ID"=:A)
       filter("OFFER_INSTANCE"=:A AND "AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND "ITEM_ID"=:A)
                                                                                                                                     
我们使用sql_profile来看看调优的建议。这里的不同之处是原本的range scan变成了skip scan. 资源消耗一下子小了几十倍。
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |     2 |    74 |     6  (67)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR          |                              |     2 |    74 |     3  (67)| 00:00:01 |   KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION_PK    |     2 |    74 |     3  (67)| 00:00:01 |   KEY |   KEY |
|*  3 |    INDEX SKIP SCAN                 | CRDT_LMT_NOTIFICATION_PK_PK |     1 |       |     3  (67)| 00:00:01 |   KEY |   KEY |
|   4 |     SORT AGGREGATE                 |                              |     1 |    34 |            |          |       |       |
|   5 |      PARTITION RANGE ITERATOR      |                              |     7 |   238 |     3  (67)| 00:00:01 |   KEY |   KEY |
|*  6 |       INDEX SKIP SCAN              | CRDT_LMT_NOTIFICATION_PK_PK |     7 |   238 |     3  (67)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("CYCLE_CODE"=:A AND "CUSTOMER_ID"=:A AND "AGREEMENT_ID"=:A AND "OFFER_INSTANCE"=:A AND "ITEM_ID"=:A)
       filter("OFFER_INSTANCE"=:A AND "AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND "ITEM_ID"=:A AND
              TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT /*+ OPT_ESTIMATE (TABLE "CRDT_LMT_NOTIFICATION_PK"
              SCALE_ROWS=1016.803110 ) OPT_ESTIMATE (INDEX_FILTER "CRDT_LMT_NOTIFICATION_PK" "CRDT_LMT_NOTIFICATION_PK_PK"
              SCALE_ROWS=440.696164 ) */ MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM
              "PRDUSG3O"."CRDT_LMT_NOTIFICATION_PK" "CRDT_LMT_NOTIFICATION_PK" WHERE "CYCLE_CODE"=:A AND "OFFER_INSTANCE"=:A AND
              "AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND "ITEM_ID"=:A))
   6 - access("CYCLE_CODE"=:A AND "CUSTOMER_ID"=:A AND "AGREEMENT_ID"=:A AND "OFFER_INSTANCE"=:A AND "ITEM_ID"=:A)
       filter("OFFER_INSTANCE"=:A AND "AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND "ITEM_ID"=:A)
在和开发确认之后，这条语句是关键的语句，是在一个新开发的需求中新加的。因为情况紧急，压力一下子堆在了我身上，大家希望我来对这条语句进行调优，能从0.5秒进行更高效的调优。
今天章节开篇先来介绍一下问题的情况，明天来详细的分析一下处理的思路，各种方案的对比和最终的建议。

时间： 2024-09-20 11:42:23

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

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

在上一篇http://blog.itpub.net/23718752/viewspace-1364914/ 中我们大体介绍了下问题的情况,已经初步根据awr能够抓取到存在问题的sql语句. 这条sql语句执行很频繁,目前平均执行时间在0.5秒.开发部门希望我们能不能做点优化,他们也在同时想办法从业务上来优化这个问题.从0.5秒的情况下,能够再提高很多,是得费很大力气的. 况且这个问题比较紧急,从拿到sql语句开始,就感觉到一种压力. 最开始的注意力都集中在cycle_month和cycle_ye

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

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

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

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

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

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

生产环境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 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语