循序渐进调优union相关的sql(r2笔记23天)

今天在生产中发现一条sql语句消耗了大量的cpu资源。使用top -c来查看。

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                        
17895 oratestdb  25   0 12.4g 217m  38m R 99.9  0.1   1232:43 oracleTESTDB (LOCAL=NO)                                                                        
12318 oratestdb  18   0 12.2g  35m  28m R 54.0  0.0  10:47.89 oracleTESTDB (LOCAL=NO)                                                                        
26316 oratestdb  18   0 12.3g 118m  28m D 21.2  0.1   0:40.33 oracleTESTDB (LOCAL=NO) 

查看进程对应的session正在运行的sql语句。 看这条语句倒也不复杂,account_id对应的是主键,查询应该是毫秒级的,但是查看生产中执行的效率,平均在3-5秒左右。

SELECT TEST_TEST_SUBSCRIBER_FA_V.MOBILE_NO, TEST_ACCOUNT.ACCOUNT_ID FROM TEST_ACCOUNT ,TEST_TEST_SUBSCRIBER_FA_V  
WHERE TEST_ACCOUNT.ACCOUNT_ID = TEST_TEST_SUBSCRIBER_FA_V.BAN  AND TEST_ACCOUNT.ACCOUNT_ID = xxxxx

查看这条语句对应的执行计划信息,如下:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6b1yv0ucyjsk3, child number 0
-------------------------------------
/* TEST_TESTGetSubscriberNumber_selectGetMobileNo_0 */ SELECT
TEST_TEST_SUBSCRIBER_FA_V.MOBILE_NO, TEST_ACCOUNT.ACCOUNT_ID FROM
TEST_ACCOUNT ,TEST_TEST_SUBSCRIBER_FA_V  WHERE TEST_ACCOUNT.ACCOUNT_ID =
TEST_TEST_SUBSCRIBER_FA_V.BAN  AND TEST_ACCOUNT.ACCOUNT_ID = :1

Plan hash value: 1685959897

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                        |       |       |       | 56898 (100)|          |
|   1 |  NESTED LOOPS                      |                        |   536K|    61M|       | 56898   (1)| 00:11:23 |
|*  2 |   INDEX UNIQUE SCAN                | TEST_ACCOUNT_PK        |     1 |     6 |       |     1   (0)| 00:00:01 |
|   3 |   VIEW                             | TEST_TEST_SUBSCRIBER_FA_V |536K|    58M|       | 56897   (1)| 00:11:23 |
|   4 |    SORT UNIQUE                     |                        |   536K|    98M|   113M| 56897 (100)| 00:11:23 |
|   5 |     UNION-ALL                      |                        |       |       |       |            |          |
|   6 |      NESTED LOOPS                  |                        |       |       |       |            |          |
|   7 |       NESTED LOOPS                 |                        |     1 |    89 |       |     3   (0)| 00:00:01 |
|   8 |        NESTED LOOPS                |                        |     1 |    31 |       |     2   (0)| 00:00:01 |
|*  9 |         INDEX RANGE SCAN           | TEST_PAY_CHANNEL_1IX   |     1 |    12 |       |     1   (0)| 00:00:01 |
|* 10 |         TABLE ACCESS BY INDEX ROWID| TEST_DISTRIBUTE        |     1 |    19 |       |     1   (0)| 00:00:01 |
|* 11 |          INDEX RANGE SCAN          | TEST_DISTRIBUTE_3IX    |     1 |       |       |     1   (0)| 00:00:01 |
|* 12 |        INDEX UNIQUE SCAN           | SUBSCRIBER_PK          |     1 |       |       |     1   (0)| 00:00:01 |
|* 13 |       TABLE ACCESS BY INDEX ROWID  | SUBSCRIBER             |     1 |    58 |       |     1   (0)| 00:00:01 |
|* 14 |      VIEW                          |                        |   536K|    98M|       | 34296   (1)| 00:06:52 |
|* 15 |       WINDOW SORT PUSHED RANK      |                        |   536K|    45M|    51M| 34296   (1)| 00:06:52 |
|* 16 |        HASH JOIN                   |                        |   536K|    45M|    28M| 23341   (1)| 00:04:41 |
|  17 |         INDEX FULL SCAN            | TEST_PAY_CHANNEL_1IX   |  1259K|    14M|       |  1254   (1)| 00:00:16 |
|* 18 |         HASH JOIN                  |                        |   536K|    39M|    26M| 18391   (1)| 00:03:41 |
|* 19 |          TABLE ACCESS FULL         | SUBSCRIBER             |   397K|    21M|       |  8527   (1)| 00:01:43 |
|* 20 |          TABLE ACCESS FULL         | TEST_DISTRIBUTE        |  1696K|    30M|       |  6046   (1)| 00:01:13 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("TEST_ACCOUNT"."ACCOUNT_ID"=TO_NUMBER(:1))
   9 - access("CPC"."BAN"=TO_NUMBER(:1))
  10 - filter("EG_DIST_TYPE"='D')
  11 - access("CPC"."PYM_CHANNEL_NO"="ED"."TARGET_PCN" AND "ED"."EXPIRATION_DATE" IS NULL)
       filter("ED"."EXPIRATION_DATE" IS NULL)
  12 - access("ED"."AGREEMENT_NO"="SUBSCRIBER"."SUBSCRIBER_NO")
  13 - filter(("SUBSCRIBER"."SUB_STATUS"<>'C' AND "SUBSCRIBER"."SUB_STATUS"<>'L' AND
              "SUBSCRIBER"."SUB_STATUS"<>'T'))
  14 - filter(("RANK"=1 AND "BAN"=TO_NUMBER(:1)))
  15 - filter(ROW_NUMBER() OVER ( PARTITION BY "SUBSCRIBER"."SUBSCRIBER_NO" ORDER BY
              "ED"."EXPIRATION_DATE")<=1)
  16 - access("CPC"."PYM_CHANNEL_NO"="ED"."TARGET_PCN")
  18 - access("ED"."AGREEMENT_NO"="SUBSCRIBER"."SUBSCRIBER_NO")
  19 - filter(("SUBSCRIBER"."SUB_STATUS"='C' OR "SUBSCRIBER"."SUB_STATUS"='L' OR
              "SUBSCRIBER"."SUB_STATUS"='T'))
  20 - filter("EG_DIST_TYPE"='D')

---从执行计划可以看出,瓶颈就在于有两个表走了全表扫描。那两个表数据也不少。
---统计信息如下:
Elapsed: 00:00:03.53

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
 96346  consistent gets
          0  physical reads
          0  redo size
        613  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

其中TEST_TEST_SUBSCRIBER_FA_V 是一个视图,里面使用了Union,(另外关于这个union的地方和开发确认过,暂时还不能改为union all)。
把视图的内容填进去,sql语句就成了如下的样子,在数据库里执行的时候也基本是这个样子的。对于在查询中没有用到的字段都给注释掉了。标注为灰色。
SELECT  MOBILE_NO, TEST_ACCOUNT.ACCOUNT_ID
FROM
(
SELECT cpc.ban,
          subscriber.prim_resource_val MOBILE_NO,
  --   subscriber.init_act_date,
        --  SUBSCRIBER.CUSTOMER_ID,
        --  subscriber.subscriber_no,
        --  SUBSCRIBER.SUBSCRIBER_TYPE,
         -- SUBSCRIBER.SUB_STATUS,
        --  SUBSCRIBER.SUB_STS_RSN_CD,
       --   SUBSCRIBER.SUB_STATUS_DATE,
       --   SUBSCRIBER.EFFECTIVE_DATE,
          1 RANK
     FROM subscriber, TEST_distribute ed, TEST_pay_channel cpc
    WHERE     cpc.pym_channel_no = ed.target_pcn
          AND ed.agreement_no = subscriber.subscriber_no
          AND eg_dist_type = 'D'
          AND ed.expiration_date IS NULL
          AND SUBSCRIBER.SUB_STATUS NOT IN ('C', 'L', 'T')
          )temp, TEST_ACCOUNT
          where TEST_ACCOUNT.account_id=temp.ban and TEST_ACCOUNT.account_id=10001245
   UNION
  SELECT   MOBILE_NO,  TEST_ACCOUNT.ACCOUNT_ID
FROM
(
   SELECT "BAN","PRIM_RESOURCE_VAL" MOBILE_NO--,"INIT_ACT_DATE","CUSTOMER_ID","SUBSCRIBER_NO","SUBSCRIBER_TYPE","SUB_STATUS","SUB_STS_RSN_CD","SUB_STATUS_DATE","EFFECTIVE_DATE","RANK"
     FROM (SELECT cpc.ban,
                  subscriber.prim_resource_val ,
  --   subscriber.init_act_date,
               --   SUBSCRIBER.CUSTOMER_ID,
               --   subscriber.subscriber_no,
               --   SUBSCRIBER.SUBSCRIBER_TYPE,
               --   SUBSCRIBER.SUB_STATUS,
               --   SUBSCRIBER.SUB_STS_RSN_CD,
                 -- SUBSCRIBER.SUB_STATUS_DATE,
            --      SUBSCRIBER.EFFECTIVE_DATE,
                  ROW_NUMBER ()
                  OVER (
                     PARTITION BY subscriber.subscriber_no
                     ORDER BY
                        ed.expiration_date, subscriber.subscriber_no DESC)
                     AS RANK
             FROM subscriber, TEST_distribute ed, TEST_pay_channel cpc
            WHERE     cpc.pym_channel_no = ed.target_pcn
                  AND ed.agreement_no = subscriber.subscriber_no
                  AND eg_dist_type = 'D'
                  AND SUBSCRIBER.SUB_STATUS IN ('C', 'L', 'T'))
    WHERE RANK = 1
    )temp, TEST_ACCOUNT
    where TEST_ACCOUNT.account_id=temp.ban and TEST_ACCOUNT.account_id=10001245


查看走全表扫描的两个表,TEST_pay_channel上有一个索引列中含有ban,从如上的执行计划中看出,没有尝试走索引,而是走了全表扫描,受此影响,TEST_distribute 也做了全表扫描。
根据查询条件TEST_ACCOUNT.account_id和TEST_pay_channel的ban是关联的,如果可以走索引的话,效率会大大提高。
TEST_PAY_CHANNEL和 TEST_DISTRIBUTE 中索引的信息如下:
INDEX_NAME                     TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST                    TABLE_TYPE STATUS   NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
TEST_PAY_CHANNEL_1IX            INDXS01    NORMAL     NONUNIQUE NO  BAN,BEN, CHANNEL_NO            TABLE      VALID     1229972 01-JUL-14 N
TEST_DISTRIBUTE_3IX             INDXS01    NORMAL     NONUNIQUE NO  TARGET_PCN,AGR_NO,EFFECT_DATE  TABLE      VALID     1597330 01-JUL-14 N

如果根据ban来关联的话,至少可以走一个range  index scan或者skip/scan了.
如果可以走索引的话。条件 “cpc.pym_channel_no = ed.target_pcn”对应的索引就可以激活了,test_distribute表也就不走全表扫描了,可以走range scan/或者skip scan了。

现在的工作就是来看看能不能不改动逻辑来看看能做些什么。
改动后的sql语句如下。首先是弃用了原有的视图。然后在union的两个子查询中使用TEST_ACCOUNT来和TEST_PAY_CHANNEL来做关联。虽然关联的表多了一个,但是因为都可以走索引,比全表扫描要效率高很多了。

SELECT  MOBILE_NO,  ACCOUNT_ID
FROM
(
  select prim_resource_val MOBILE_NO,ACCOUNT_ID,rank
  from  (
    SELECT  cpc.ban,TEST_ACCOUNT.ACCOUNT_ID,
            subscriber.prim_resource_val,
            1 RANK
       FROM subscriber, TEST_distribute ed, TEST_pay_channel cpc,TEST_ACCOUNT
      WHERE     cpc.pym_channel_no = ed.target_pcn
            AND ed.agreement_no = subscriber.subscriber_no
            AND eg_dist_type = 'D'
            AND ed.expiration_date IS NULL
            AND SUBSCRIBER.SUB_STATUS NOT IN ('C', 'L', 'T')
           AND        TEST_ACCOUNT.ACCOUNT_ID = cpc.BAN  AND TEST_ACCOUNT.ACCOUNT_ID = 10001245
       )TEST_TEST_SUBSCRIBER
  UNION
   select TEST_TEST_SUBSCRIBER.prim_resource_val MOBILE_NO,TEST_TEST_SUBSCRIBER.ACCOUNT_ID,rank
   from
  (SELECT cpc.ban, subscriber.prim_resource_val,  TEST_ACCOUNT.ACCOUNT_ID ,
                   ROW_NUMBER ()
                   OVER (
                      PARTITION BY subscriber.subscriber_no
                      ORDER BY
                         ed.expiration_date, subscriber.subscriber_no DESC)
                      AS RANK
              FROM subscriber, TEST_distribute ed, TEST_pay_channel cpc,TEST_ACCOUNT
             WHERE     cpc.pym_channel_no = ed.target_pcn
                   AND ed.agreement_no = subscriber.subscriber_no
                   AND eg_dist_type = 'D'
                   AND SUBSCRIBER.SUB_STATUS IN ('C', 'L', 'T')
                              AND TEST_ACCOUNT.ACCOUNT_ID = cpc.BAN  AND TEST_ACCOUNT.ACCOUNT_ID =10001245
  ) TEST_TEST_SUBSCRIBER
  ) where rank=1

来看看执行计划和统计信息和执行速度。
    首先是执行速度,用了0.01秒,相比原来的3秒左右提高了不少,毫秒级的速度。
    cpu的消耗一下子到了11,提高了很多倍
    两个全本走全表扫描的表,现在都走了index range scan.
    统计信息的提升,也提高了很多。从原来的96346降低到了目前的23

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1502756759
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     2 |   256 |    11  (28)| 00:00:01 |
|   1 |  VIEW                               |                      |     2 |   256 |    11  (28)| 00:00:01 |
|   2 |   SORT UNIQUE                       |                      |     2 |   184 |    11  (64)| 00:00:01 |
|   3 |    UNION-ALL                        |                      |       |       |            |          |
|   4 |     NESTED LOOPS                    |                      |       |       |            |          |
|   5 |      NESTED LOOPS                   |                      |     1 |    56 |     4   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                  |                      |     1 |    37 |     3   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                 |                      |     1 |    18 |     2   (0)| 00:00:01 |
|*  8 |         INDEX UNIQUE SCAN           | TEST_ACCOUNT_PK       |     1 |     6 |     1   (0)| 00:00:01 |
|*  9 |         INDEX RANGE SCAN            | TEST_PAY_CHANNEL_1IX  |     1 |    12 |     1   (0)| 00:00:01 |
|* 10 |        TABLE ACCESS BY INDEX ROWID  | TEST_DISTRIBUTE     |     1 |    19 |     1   (0)| 00:00:01 |
|* 11 |         INDEX RANGE SCAN            | TEST_DISTRIBUTE_3IX |     1 |       |     1   (0)| 00:00:01 |
|* 12 |       INDEX UNIQUE SCAN             | SUBSCRIBER_PK        |     1 |       |     1   (0)| 00:00:01 |
|* 13 |      TABLE ACCESS BY INDEX ROWID    | SUBSCRIBER           |     1 |    19 |     1   (0)| 00:00:01 |
|* 14 |     VIEW                            |                      |     1 |   128 |     5  (20)| 00:00:01 |
|* 15 |      WINDOW SORT PUSHED RANK        |                      |     1 |    56 |     5  (20)| 00:00:01 |
|  16 |       NESTED LOOPS                  |                      |       |       |            |          |
|  17 |        NESTED LOOPS                 |                      |     1 |    56 |     4   (0)| 00:00:01 |
|  18 |         NESTED LOOPS                |                      |     1 |    37 |     3   (0)| 00:00:01 |
|  19 |          NESTED LOOPS               |                      |     1 |    18 |     2   (0)| 00:00:01 |
|* 20 |           INDEX UNIQUE SCAN         | TEST_ACCOUNT_PK       |     1 |     6 |     1   (0)| 00:00:01 |
|* 21 |           INDEX RANGE SCAN          | TEST_PAY_CHANNEL_1IX  |     1 |    12 |     1   (0)| 00:00:01 |
|* 22 |          TABLE ACCESS BY INDEX ROWID| TEST_DISTRIBUTE     |     1 |    19 |     1   (0)| 00:00:01 |
|* 23 |           INDEX RANGE SCAN          | TEST_DISTRIBUTE_3IX |     1 |       |     1   (0)| 00:00:01 |
|* 24 |         INDEX UNIQUE SCAN           | SUBSCRIBER_PK        |     1 |       |     1   (0)| 00:00:01 |
|* 25 |        TABLE ACCESS BY INDEX ROWID  | SUBSCRIBER           |     1 |    19 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   8 - access("TEST_ACCOUNT"."ACCOUNT_ID"=10001245)
   9 - access("CPC"."BAN"=10001245)
  10 - filter("EG_DIST_TYPE"='D')
  11 - access("CPC"."PYM_CHANNEL_NO"="ED"."TARGET_PCN" AND "ED"."EXPIRATION_DATE" IS NULL)
       filter("ED"."EXPIRATION_DATE" IS NULL)
  12 - access("ED"."AGREEMENT_NO"="SUBSCRIBER"."SUBSCRIBER_NO")
  13 - filter("SUBSCRIBER"."SUB_STATUS"<>'C' AND "SUBSCRIBER"."SUB_STATUS"<>'L' AND
              "SUBSCRIBER"."SUB_STATUS"<>'T')
  14 - filter("RANK"=1)
  15 - filter(ROW_NUMBER() OVER ( PARTITION BY "SUBSCRIBER"."SUBSCRIBER_NO" ORDER BY
              "ED"."EXPIRATION_DATE")<=1)
  20 - access("TEST_ACCOUNT"."ACCOUNT_ID"=10001245)
  21 - access("CPC"."BAN"=10001245)
  22 - filter("EG_DIST_TYPE"='D')
  23 - access("CPC"."PYM_CHANNEL_NO"="ED"."TARGET_PCN")
  24 - access("ED"."AGREEMENT_NO"="SUBSCRIBER"."SUBSCRIBER_NO")
  25 - filter("SUBSCRIBER"."SUB_STATUS"='C' OR "SUBSCRIBER"."SUB_STATUS"='L' OR
              "SUBSCRIBER"."SUB_STATUS"='T')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
 23  consistent gets
          3  physical reads
          0  redo size
        613  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
有了一定的数据保证,就需要在测试环境中进行了模拟测试,可以比对是否和预期的一样有极大的提升。

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

原文发表时间:2014-07-04

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Spark学习技巧

MySQL的索引是什么?怎么优化?

索引对大数据的查询速度的提升是非常大的,Explain可以帮你分析SQL语句是否用到相关索引。 索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库...

3576
来自专栏java达人

oracle建表、建主键、外键基本语法

主键:唯一标识,不能为空,加快查询速度,自动创建索引 外键:约束内表的数据的更新,从定义外键时可以发现 外键是和主键表联系,数据类型要统一,长度(存储大小)要...

1945
来自专栏Java帮帮-微信公众号-技术文章全总结

Java企业面试——数据库

数据库部分 数据表连接问题,左外连接、右外连接、内连接等 一、交叉连接(CROSS JOIN) 交叉连接(CROSS JOIN):有两种,显式的和隐式的,不...

2564
来自专栏我叫刘半仙

原 荐 MySQL的索引是什么?怎么优化?

      索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降,虽然官方文档说500~8...

3926
来自专栏james大数据架构

你真的会玩SQL吗?简单的数据修改

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

1877
来自专栏乐沙弥的世界

SQL 基础--> 视图(CREATE VIEW)

视图来源于表,所有对视图数据的修改最终都会被反映到视图的基表中,这些修改必须服从基表的完整性约束,并同样会触发定义

523
来自专栏吴生的专栏

MySQL的索引是什么?怎么优化?

索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降,虽然官方文档说500~800w记录,...

35013
来自专栏三木的博客

顺序存储线性表的实现

最近复习数据结构,写了一个顺序存储的线性表,代码粘在这里:) 代码下载:git@github.com:Wang-Sen/algorithm.git /* * ...

1826
来自专栏乐沙弥的世界

SQL基础--> 序列(SEQUENCE)、同义词(SYNONYM)

--=============================================

512
来自专栏文渊之博

T-SQL基础--TOP

理解TOP子句 众所周知,TOP子句可以通过控制返回行的数量来影响查询。 我们知道TOP子句能很容易的满足返回指定行数的子集,接下来有一些例子来展示什么情况下使...

18010

扫描关注云+社区