生产环境sql语句调优实战第七篇(r2笔记99天)

在数据迁移完成之后,开始了例行的后期数据库维护,早上一来就发现了一个sql执行时间很长了。达到了37279秒。最后在改进调优之后执行速度在1分钟以内。 这个速度是毫无疑问的性能问题,但是是否是因为数据迁移直接导致的呢,通过简单的脚本分析,得出了如下的图表。 显示了同样的sql语句在7月份至今的执行情况,还真是奇怪,昨天以前一直正常,期间也进行过两次数据迁移,昨天的数据迁移完成以后,性能就出奇的差。

通过sql_monitor得到了执行计划和对应的sql语句。 可以看到执行时间是昨天的下午,一直执行到今天的凌晨。

Instance ID

:

xxxxxx (6839:20499)

SQL ID

:

16777216

Execution Started

:

09/17/2014 13:44:24

Last Refresh Time

:

37279s

Module/Action

:

xxxxxxx

Program

:

<span "="" style="word-wrap: break-word;">JDBC Thin Client

我从之前的监控记录中抓到了之前执行时间较短的执行计划来对比分析。看看性能瓶颈到底出在哪儿。

发现瓶颈还是主要在于IO request和buffer gets. 发送的io请求翻了180多倍。buffer gets翻了240多倍。 查看执行计划,发现主要的IO消耗都在SERVICE_DETAILS表中,这个表在数据迁移后有将近1亿条记录。发送了140多万次的io请求。按照这个情况read request有大概100G的样子。确实是个很高的比值。

发现瓶颈还是主要在于IO request和buffer gets. 发送的io请求翻了180多倍。buffer gets翻了240多倍。

查看执行计划,发现主要的IO消耗都在SERVICE_DETAILS表中,这个表在数据迁移后有将近1亿条记录。发送了140多万次的io请求。按照这个情况read request有大概100G的样子。确实是个很高的比值。

而之前的执行计划中,io请求要少的多。只有100多M的样子。 对应的sql语句如下:

select /*+ index(s SERVICE_DETAILS_2FK) index(d CHARGE_DISTRIBUTE_1IX) */
DISTINCT t.L9_IDENTIFICATION,
         rtrim(n.name_Elem2),
         rtrim(n.name_Elem4),
         c.BAN,
         rtrim(s.PRIM_RESOURCE_VAL),
         s.SUB_STATUS,
         p.PAYMENT_METHOD,
         rtrim(o1.SOC_NAME),
         to_char(g1.EFFECTIVE_DATE, 'dd/mm/yyyy'),
         to_char(g1.EXPIRATION_DATE, 'dd/mm/yyyy'),
         to_char(g.EFFECTIVE_DATE, 'dd/mm/yyyy'),
         to_char(g.EXPIRATION_DATE, 'dd/mm/yyyy')
  from subscriber        s,
       PAY_CHANNEL   p,
       charge_distribute d,
       ACCOUNT       c,
       address_name_link a,
       name_data         n,
       customer          t,
       SERVICE_DETAILS g,
       OFFER         o,
       SERVICE_DETAILS g1,
       OFFER         o1
 where g.soc = o.soc_cd
   and o.soc_name = 'DGT024'
   and o.soc_type = 'D'
   and (o.SALE_EXP_DATE is null or o.SALE_EXP_DATE > trunc(sysdate - 1))
   and trunc(g.effective_date) = trunc(sysdate - 1)
   and (g.expiration_date is null or g.expiration_date > trunc(sysdate - 1))
   and d.agreement_no = g.agreement_no
   and (d.expiration_date is null or d.expiration_date > trunc(sysdate - 1))
   and p.pym_channel_no = d.target_pcn
   and p.pcn_status = 'O'
   and c.ban = p.ban
   and s.prim_resource_tp = 'C'
   and s.subscriber_no = g.agreement_no
   and s.sub_status in ('A', 'S') and a.entity_id = s.customer_id
   and a.entity_type = 'CUSTOMER'
   and a.link_type = 'C'
   and a.EXPIRATION_DATE is null
   and n.name_id = a.name_id
   and t.customer_id = s.customer_id
   and g1.agreement_no = g.agreement_no
   and (g1.expiration_date is null or
       g1.expiration_date > trunc(sysdate - 1))
   and o1.soc_cd = g1.soc
   and o1.soc_type = 'P'

首先来分析表的关联情况,里面还需要有一定的业务知识,我有开发基础,这些业务的分析还能独立来做。最后的分析结果就是发现关联的表有11个。这么多表关联,大表小表在一起,数据库来分析可能分析不到业务的程度,我发现执行的顺序有问题,其实可以从service_details里面得到一个过滤后的数据集,过滤之后的数据就从亿条降低为几万条。在这个基础上在和其他的大表关联速度就好的多了。 所以我采用了leading的方式,提示oracle按照我指定的顺序来过滤数据。 在这个基础上毕竟service_details的过滤条件没有对应的索引列,做全表扫描也是无奈之举,但是我们还能够做点什么,来个并行。 这样速度就会提高很多。 改进后的hint如下。 /*+ leading(g,o,s,t,d,p,c,a,n,g1,o1) parallel(g,8) */ 改进后的执行计划如下:

Plan hash value: 2129239343

------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                            |     1 |   232 | 55927   (1)| 00:11:12 |        |      |            |
|   1 |  PX COORDINATOR                            |                            |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)                      | :TQ10002                   |     1 |   232 | 55927   (1)| 00:11:12 |  Q1,02 | P->S | QC (RAND)  |
|   3 |    HASH UNIQUE                             |                            |     1 |   232 | 55927   (1)| 00:11:12 |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                             |                            |       |       |            |          |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH                          | :TQ10001                   |       |       |            |          |  Q1,01 | P->P | HASH       |
|   6 |       NESTED LOOPS                         |                            |       |       |            |          |  Q1,01 | PCWP |            |
|   7 |        NESTED LOOPS                        |                            |     1 |   232 | 55925   (1)| 00:11:12 |  Q1,01 | PCWP |            |
|   8 |         NESTED LOOPS                       |                            |     1 |   212 | 55925   (1)| 00:11:12 |  Q1,01 | PCWP |            |
|   9 |          NESTED LOOPS                      |                            |     1 |   188 | 55925   (1)| 00:11:12 |  Q1,01 | PCWP |            |
|  10 |           NESTED LOOPS                     |                            |     1 |   166 | 55925   (1)| 00:11:12 |  Q1,01 | PCWP |            |
|  11 |            NESTED LOOPS                    |                            |     2 |   282 | 55925   (1)| 00:11:12 |  Q1,01 | PCWP |            |
|  12 |             NESTED LOOPS                   |                            |     2 |   270 | 55925   (1)| 00:11:12 |  Q1,01 | PCWP |            |
|  13 |              NESTED LOOPS                  |                            |     2 |   236 | 55925   (1)| 00:11:12 |  Q1,01 | PCWP |            |
|  14 |               NESTED LOOPS                 |                            |     1 |   100 | 55924   (1)| 00:11:12 |  Q1,01 | PCWP |            |
|  15 |                NESTED LOOPS                |                            |     1 |    80 | 55924   (1)| 00:11:12 |  Q1,01 | PCWP |            |
|* 16 |                 HASH JOIN                  |                            |     1 |    52 | 55924   (1)| 00:11:12 |  Q1,01 | PCWP |            |
|  17 |                  PX BLOCK ITERATOR         |                            | 22157 |   519K| 55870   (1)| 00:11:11 |  Q1,01 | PCWC |            |
|* 18 |                   TABLE ACCESS FULL        | SERVICE_DETAILS            | 22157 |   519K| 55870   (1)| 00:11:11 |  Q1,01 | PCWP |            |
|  19 |                  BUFFER SORT               |                            |       |       |            |          |  Q1,01 | PCWC |            |
|  20 |                   PX RECEIVE               |                            |     1 |    28 |    54   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|  21 |                    PX SEND BROADCAST       | :TQ10000                   |     1 |    28 |    54   (0)| 00:00:01 |        | S->P | BROADCAST  |
|* 22 |                     TABLE ACCESS FULL      | OFFER                      |     1 |    28 |    54   (0)| 00:00:01 |        |      |            |
|* 23 |                 TABLE ACCESS BY INDEX ROWID| SUBSCRIBER                 |     1 |    28 |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 24 |                  INDEX UNIQUE SCAN         | SUBSCRIBER_PK              |     1 |       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|  25 |                TABLE ACCESS BY INDEX ROWID | CUSTOMER                   |     1 |    20 |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 26 |                 INDEX UNIQUE SCAN          | CUSTOMER_PK                |     1 |       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 27 |               TABLE ACCESS BY INDEX ROWID  | CHARGE_DISTRIBUTE          |     6 |   108 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 28 |                INDEX RANGE SCAN            | CHARGE_DISTRIBUTE_1IX      |    15 |       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 29 |              TABLE ACCESS BY INDEX ROWID   | PAY_CHANNEL                |     1 |    17 |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 30 |               INDEX UNIQUE SCAN            | PAY_CHANNEL_PK             |     1 |       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 31 |             INDEX UNIQUE SCAN              | ACCOUNT_PK                 |     1 |     6 |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 32 |            TABLE ACCESS BY INDEX ROWID     | ADDRESS_NAME_LINK          |     1 |    25 |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 33 |             INDEX RANGE SCAN               | ADDRESS_NAME_LINK_11IX     |     2 |       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|  34 |           TABLE ACCESS BY INDEX ROWID      | NAME_DATA                  |     1 |    22 |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 35 |            INDEX UNIQUE SCAN               | NAME_DATA_PK               |     1 |       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 36 |          TABLE ACCESS BY INDEX ROWID       | SERVICE_DETAILS            |     1 |    24 |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 37 |           INDEX RANGE SCAN                 | SERVICE_DETAILS_PK         |    11 |       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 38 |         INDEX RANGE SCAN                   | OFFER_1IX                  |     1 |       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 39 |        TABLE ACCESS BY INDEX ROWID         | OFFER                      |     1 |    20 |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


  16 - access("G"."SOC"="O"."SOC_CD")
  18 - filter(("G"."EXPIRATION_DATE" IS NULL OR "G"."EXPIRATION_DATE">TRUNC(SYSDATE@!-1)) AND
              TRUNC(INTERNAL_FUNCTION("G"."EFFECTIVE_DATE"))=TRUNC(SYSDATE@!-1))
  22 - filter("O"."SOC_NAME"='DGT024' AND "O"."SOC_TYPE"='D' AND ("O"."SALE_EXP_DATE">TRUNC(SYSDATE@!-1) OR "O"."SALE_EXP_DATE" IS NULL))
  23 - filter(("S"."SUB_STATUS"='A' OR "S"."SUB_STATUS"='S') AND "S"."PRIM_RESOURCE_TP"='C')
  24 - access("S"."SUBSCRIBER_NO"="G"."AGREEMENT_NO")
  26 - access("T"."CUSTOMER_ID"="S"."CUSTOMER_ID")
  27 - filter("D"."EXPIRATION_DATE" IS NULL OR "D"."EXPIRATION_DATE">TRUNC(SYSDATE@!-1))
  28 - access("D"."AGREEMENT_NO"="G"."AGREEMENT_NO")
  29 - filter("P"."PCN_STATUS"='O')
  30 - access("P"."PYM_CHANNEL_NO"="D"."TARGET_PCN")
  31 - access("C"."BAN"="P"."BAN")
  32 - filter("A"."EXPIRATION_DATE" IS NULL)
  33 - access("A"."ENTITY_ID"="S"."CUSTOMER_ID" AND "A"."ENTITY_TYPE"='CUSTOMER' AND "A"."LINK_TYPE"='C')
  35 - access("N"."NAME_ID"="A"."NAME_ID")
  36 - filter("G1"."EXPIRATION_DATE" IS NULL OR "G1"."EXPIRATION_DATE">TRUNC(SYSDATE@!-1))
  37 - access("G1"."AGREEMENT_NO"="G"."AGREEMENT_NO")
  38 - access("O1"."SOC_CD"="G1"."SOC")
  39 - filter("O1"."SOC_TYPE"='P')


69 rows selected.

无论是资源消耗还是执行时间来说,都是很大的提高。
我在测试环境做了一定的测试之后,在生产开始一个简单的测试。
执行时间要快很多,竟然不到30秒。执行2次的结果如下:
Elapsed: 00:00:21.17 
Elapsed: 00:00:28.01

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

原文发表时间:2014-09-18

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据库新发现

字符集问题的初步探讨(二)

原文发表于itpub技术丛书《Oracle数据库DBA专题技术精粹》,未经许可,严禁转载本文.

1272
来自专栏deed博客

Oracle在Linux下安装

2042
来自专栏菩提树下的杨过

mybatis 使用经验小结

一、多数据源问题 主要思路是把dataSource、sqlSesstionFactory、MapperScannerConfigurer在配置中区分开,各Map...

2886
来自专栏乐沙弥的世界

PL/SQL 包编译时hang住的处理

       最近PL/SQL包在编译时被hang住,起初以为是所依赖的对象被锁住。结果出乎意料之外。下面直接看代码演示。

906
来自专栏数据和云

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

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

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

不经意发现的dba_objects和dba_tables中的细节(r7笔记第56天)

今天有一个同学问我一个问题,因为白天比较忙也没有在意,在下班后坐地铁的时候抽空看了这个问题,感觉还是蛮有意思的。但是当时也没有任何答案,就准备自己回去好好实验一...

3643
来自专栏乐沙弥的世界

使用 EXPLAIN PLAN 获取SQL语句执行计划

     SQL查询语句的性能从一定程度上影响整个数据库的性能。很多情况下,数据库性能的低下差不多都是不良SQL语句所引起。而SQL语句的执行 计划则决定了S...

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

一条运行了3天的"简单"的sql(r2笔记82天)

早上刚到公司,查看系统的负载,就马上看到一个进程的执行时间已经有3天了。 而且cpu的消耗极高。 Tasks: 2374 total, 19 running,...

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

巧用外部表避免大量的insert (r4笔记第71天)

昨天开发咨询我一个问题,希望我对下面的语句进行调优。 语句类似下面的形式 SELECT subscriber_no FROM SUBSCRIBER S W...

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

impdp ORA-39002,ORA-39166,ORA-39164的问题及解决(r2第6天)

今天在做imp和impdp的性能测试时,发现如果表中存在lob字段,加载真是慢的厉害,每秒钟大概1000条的样子,按照这种速度,基本上不用干活了。 比如5千万条...

3177

扫码关注云+社区