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

陆陆续续写了九篇关于生产环境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‘ ;

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‘)  

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