生产环境sql语句调优实战第十篇(r3笔记第39天)

陆陆续续写了九篇关于生产环境sql语句的调优案例,发现了不少问题,可能有些问题回头来看是比较低级的错误,稍加改动就能够运行在秒级,有些可能是在秒级到毫秒级的小步提升等等,不管调优的改进多大,从dba的角度来看,好多问题都是基于资源来调优的,比如添加索引,降低IO,降低CPU消耗,提高CPU利用率等等。如果有时候从业务角度来下下功夫,可能某种程度上效果要更好于基于资源/代价的调优。 最近客户反馈有几条sql语句IO消耗很高,希望我们能够给提点建议。 sql语句很短,但是运行时间在9秒左右。运行频率也是蛮高的。平均下来一个小时100次。 语句如下: select product_name from service_details where subscriber_no in (select subscriber_no from subscriber where prim_resource_val = :1 and sub_status = 'A') and product_status = 'A' ;

其中service_details是一个亿级的大表,subscriber是百万级的表,但是prim_resource_val字段不是索引列。所以导致subscriber表走了全表扫描。

Stat Name

Statement Total

Per Execution

% Snap Total

Elapsed Time (ms)

5,304,719

9,558.05

0.88

CPU Time (ms)

1,806,243

3,254.49

0.87

Executions

555

Buffer Gets

158,171,280

284,993.30

0.95

Disk Reads

158,091,403

284,849.37

4.60

Parse Calls

555

1.00

0.00

Rows

5,612

10.11

User I/O Wait Time (ms)

3,351,159

Cluster Wait Time (ms)

0

Application Wait Time (ms)

4,142

Concurrency Wait Time (ms)

0

Invalidations

0

Version Count

35

Sharable Mem(KB)

818

执行计划如下: Plan hash value: 2174296646 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 126 | 4284 | 50533 (1)| 00:10:07 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 126 | 4284 | 50533 (1)| 00:10:07 | |* 3 | TABLE ACCESS FULL | SUBSCRIBER | 18 | 342 | 50517 (1)| 00:10:07 | |* 4 | INDEX RANGE SCAN | SERVICE_DETAILS_PK | 9 | | 1 (0)| 00:00:01 | |* 5 | TABLE ACCESS BY INDEX ROWID| SERVICE_DETAILS | 7 | 105 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("PRIM_RESOURCE_VAL"=:1 AND "SUB_STATUS"='A') 4 - access("SUBSCRIBER_NO"="SUBSCRIBER_NO") 5 - filter("PRODUCT_STATUS"='A') 如果从资源代价的角度来看,一种思路是添加对应的索引。因为这个表是产品线中统一规划的。所以要加入索引还是很不容易的。 如果没有其它的调优思路,可能并行就是一把双刃剑了,相对来说速度会高一些,但是IO和CPU的消耗会比较高,对于执行如此频繁的语句来说使用多个并行可能对于系统负载时很高的。 看着sql语句比较简单,但是还没有立竿见影的效果也有些让人着急。数据库的角度的一些调整可能奏效不大,自己就想看看从业务角度能做点什么。 静下心来看看sql语句。 select product_name from service_details where subscriber_no in (select subscriber_no from subscriber where prim_resource_val = :1 and sub_status = 'A') and product_status = 'A' ; sql语句中prim_resource_val就跟我们使用的手机号有些类似,这样一个号码为什么没有加入索引,从业务的角度来琢磨,可能是有做号码变更之类的操作的时候这个号码就会变化比较频繁。而保持不变的就是subscriber_no。就类似我们去银行办理业务的时候显示的客户号。这个字段就是主键列。 可能有的人有多个资源号的时候,打个比方,比如有机顶盒号,手机号等,在这个时候手机号就是主要的资源号。 这个时候再来分析为什么产品线中没有规划给resource_value作为索引列,也是考虑了后期的一些变更。这个列还是变化性比较大。这样考虑也就有一定的道理了。 因为对这部分的业务还比较熟悉,发现所需要的资源号,完全可以从一个独立的表中得到更完整的信息。subscriber_resource。 这个表尽管也是亿级的表,但是根据资源号来查找subscriber可以走index range scan。得到数据也要快很多。 subscriber_resource中存放着一个用户所使用的各类资源信息。从这个表里直接映射resource_value得到的用户信息就很有限了。因为根据条件是只需要激活状态的用户,那么我们完全可以在得到一个很简单的用户列表后直接来过滤用户状态就能得到所需要的信息了。最后做了如下的改动。把资源表关联进来。 select product_name from service_details ser where subscriber_no in (select subscriber_no from subscriber where sub_status = 'A' and (subscriber_no, prim_resource_tp) in (select subscriber_no, resource_type from subcriber_resource where resource_value=:1) ) and soc_status = 'A' 先来看看执行计划,表面来看所走的索引还是比较高效的。

-----------------------------------------------------------------------------------------------------------------------------------
|  Id  | Operation                                | Name                   | Rows   | Bytes | Cost (%CPU)| Time     | Pstart| Pstop  |
-----------------------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                         |                         |     7  |   196 |     7  (15)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                             |                         |       |       |            |           |       |       |
|   2 |   NESTED LOOPS                           |                          |     7 |   196 |     7  (15)| 00:00:01 |       |        |
|   3 |    VIEW                                  | VW_NSO_1                 |     1 |    13 |     5   (0)| 00:00:01 |       |       |
|   4 |     HASH  UNIQUE                          |                         |     1 |    36 |             |          |       |       |
|   5 |      NESTED LOOPS                         |                         |       |       |            |          |        |       |
|   6 |       NESTED LOOPS                       |                          |     1 |    36 |     5   (0)| 00:00:01 |       |       |
|   7 |         PARTITION RANGE ALL               |                         |     1 |    25 |      4   (0)| 00:00:01 |     1 |    11 |
|   8 |         TABLE ACCESS BY LOCAL  INDEX ROWID| SUBSCRIBER_RESOURCE     |     1 |    25 |     4   (0)| 00:00:01 |      1 |    11 |
|*  9 |          INDEX RANGE SCAN                |  SUBSCRIBER_RESOURCE_1IX |     1 |       |     3   (0)| 00:00:01 |     1 |    11  |
|* 10 |        INDEX UNIQUE SCAN                 | SUBSCRIBER_PK            |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 11 |       TABLE  ACCESS BY INDEX ROWID        | SUBSCRIBER              |     1 |    11 |     1    (0)| 00:00:01 |       |       |
|* 12 |    INDEX RANGE SCAN                       | SERVICE_DETAILS_PK      |     9 |       |     1   (0)| 00:00:01 |       |        |
|* 13 |   TABLE ACCESS BY INDEX ROWID            | SERVICE_DEAILS           |     7 |   105 |     1   (0)| 00:00:01 |       |        |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate  Information (identified by operation  id):
---------------------------------------------------
   9 -  access("RESOURCE_VALUE"=:1)
  10 -  access("SUBSCRIBER_NO"="SUBSCRIBER_NO")
  11 - filter("SUB_STATUS"='A' AND  "PRIM_RESOURCE_TP"="RESOURCE_TYPE")
  12 -  access("SUBSCRIBER_NO"="SUBSCRIBER_NO")
  13 - filter("PRODUCT_STATUS"='A')   

最后在备份库做相关的测试,执行时间都在毫秒级。 所以有些时候业务的角度来调优可能会有意向不到的收获。还有几个类似的语句,执行时间在分钟级,调整成类似的形式之后,都在毫秒级就完成了数据查询。

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

原文发表时间:2014-10-28

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

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

job处理缓慢的性能问题排查与分析(r4笔记第18天)

昨天开发的同事找到我说,生产有个job处理数据的速度很慢,想让我帮忙看看是怎么回事,最近碰到这种问题相对比较多了,但是问题的原因也是五花八门。我还是大体找他们了...

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

复杂SQL性能优化的剖析(一)(r11笔记第36天)

今天本来是处理一个简单的故障,但是发现是一环套一环,花了我快一天的时间。 开始是早上收到一条报警: 报警内容: CPUutilization is too hi...

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

关于sequence问题的紧急处理(r2第26天)

今天早上收到邮件,说有一个很紧急的问题,是关于sequence的。 错误日志里面还有ORA的错误 ----- ... 7 more Cause...

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

MySQL中的Online DDL(第一篇)(r11笔记第3天)

记得有一天快下班的时候,一位开发同事找到我说,需要对一个表做变更,数据量据说有上千万,而当时是使用的MySQL版本是5.5,这可如何是好,对于在线业务要求高的情...

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

alert日志中的一条ora警告信息的分析(59天)

今天照例检查数据库alert日志,发现一个错误。但是也没在意,想可能有大的操作导致的,马上会释放空间的,但是转眼一想,这是生产库,而且现在时早上,泰国的运营商还...

2755
来自专栏数据和云

极限优化:从75到2000,由技能到性能提升岂止20倍

崔华,网名 dbsnake Oracle ACE Director,ACOUG 核心专家 编辑手记:感谢崔华授权我们独家转载其精品文章,也欢迎大家向“Oracl...

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

sql monitor的使用(一) (r2第30天)

在sql调优中,对于sql语句的实时监控显得尤为重要,如果某条sql语句的性能比较差。可能从前端的直观感觉就是执行时间比较长。 对于dba来说,可能关注的相关因...

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

DBMS_STATS收集统计信息的问题及解决

收集数据库的统计信息是dba工作的一部分,如果在数据快速增长的库上,统计信息如果收集的频率太慢,会对执行计划有一定的影响。 而对于逐渐客户饱和的系统来说,统计信...

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

Oracle中的段(r10笔记第81天)

Oracle的体系结构中,关于存储结构大家应该都很熟悉了。 估计下面这张图大家都看得熟悉的不能再熟悉了。 ? 简单来说,里面的一个重要概念就是段,如果是开发...

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

一次数据库响应慢的问题诊断(r6笔记第39天)

今天接到开发一个同事的电话,说前端系统那边反馈有一个查询很慢,初步怀疑是有一些并发或者锁之类的问题导致的。 接到问题之后,自己还是带着一些的紧迫感来处理的。 首...

2755

扫码关注云+社区