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

一般在生产环境中,如果某个查询中涉及一个大表,走索引扫描是显然是最值得推荐的方式,但是索引扫描有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秒进行更高效的调优。 今天章节开篇先来介绍一下问题的情况,明天来详细的分析一下处理的思路,各种方案的对比和最终的建议。

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

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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

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

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

在上一篇http://blog.itpub.net/23718752/viewspace-1364914/ 中我们大体介绍了下问题的情况,已经初步根据awr能...

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

关于sql_profile中的绑定变量(r4笔记第57天)

使用sql_profile来调优一些紧急的性能sql可以起到立竿见影的效果,如果sql语句本身结构就很清晰,简单,略作修改就能得到调优后的sql语句。 但是如果...

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

执行计划中的COLLECTION ITERATOR PICKLER FETCH导致的性能问题 (r5笔记第49天)

今天开发的同事找到我,让我评估一个sql语句。因为这条语句被应用监控组给抓取出来了,需要尽快进行性能调优。 sql语句比较长,是由几个Union连接起来的子查询...

3735
来自专栏数据和云

元宵佳节:看Oracle技术粉们用SQL画团圆

话团圆,画团圆,元宵佳节倍思亲,可是大家知道吗,万能的SQL可以帮助大家绘制团圆。 在ITPUB论坛里,一群SQL爱好者们会用SQL来描摹一切可能。请看如下这段...

3005
来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(35)-文章发布系统②-构建项目

注:阅读本文,需要阅读本系列的之前文章 代码生成器下载地址(文章开头处)  接下来我们建立数据库的表和各层的代码 我们只需要两张表,文章列表(MIS_Artic...

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

通过shell脚本得到数据字典的信息 (r2笔记72天)

在平时的工作中,可能需要查询一些数据字典的信息,比如数据字典对应的基表信息,可以得到更多数据库内部的一些详细信息。 比如user_objects这个数据字典视图...

2907
来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(41)-组织架构

本节开始我们要实现工作流,此工作流可以和之前的所有章节脱离关系,也可以紧密合并。 我们当初设计的项目解决方案就是可伸缩可以拆离,可共享的项目解决方案。所以我们同...

2167
来自专栏james大数据架构

通用分页存储过程

/*通用分页存储过程*/ USE HotelManagementSystem GO IF EXISTS(SELECT * FROM sys.objects WH...

2538
来自专栏沃趣科技

SQL优化案例-从执行计划定位SQL问题(三)

当SQL出现问题,能从执行计划中快速的定位哪部分出现问题很重要,SQL文本如下(为保证客户隐私,已经将注释和文字部分去掉):

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

生产系统调优之_毫秒级的改进 (92天)

生产中有一个sql语句,做了union-all操作,对于时间的要求是极其严格的,目前已经从2秒的改进调整到了1秒以内,在此基础上还想做进一步的调整,因为极其频繁...

3548

扫码关注云+社区

领取腾讯云代金券