生产系统调优之_敢于质疑

接着昨天的那个问题来说。有个sql语句在做了统计信息收集之后，速度有了一定的提升，从5秒的响应降低到了2秒。但是和预期还是有一定
的差距，按照80条查询请求在短时间内响应。2秒*80000次/60/60=44.4小时，本来感觉可以接受的一下子就成了大问题。
Elapsed Time (s)
Executions


Elapsed Time per Exec (s)


%Total
%CPU
%IO
SQL Id
SQL Module
SQL Text

31st May
305.44
145
2.11
25.56
99.69
0.26
2fjzq67jbztwv
gext1GenericEx@ccbdbpr1 (TNS V1-V3)
/* */ SELECT DISTINCT 'K', AR....

和那个同事又确认了下，他说在其他项目也用的这个sql语句数据量还要大的多，就是没有问题。
有了昨天的一些数据，我自己也基本心里有数了，我表示怀疑，在此基础上能做的就是仔细看看这个sql语句了，看看到底实现细节有没有问题。
可以看到加入了一些hint，嵌入了子查询。
SELECT DISTINCT 'K',
                AR.RESOURCE_VALUE,
                AR.RESOURCE_TYPE,
                GREATEST(TO_CHAR(AR.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS'),
                         TO_CHAR(SB.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS')),
                LEAST(NVL(TO_CHAR(AR.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'),
                          '47001231000000'),
                      NVL(TO_CHAR(SB.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'),
                          '47001231000000')),
                AR.AGR_NO,
                SB.MEDIUM_CUS_ID,
                SB.SUB_STATUS,
                SB.BUSINESS_ENTITY_ID,
                SB.LANGUAGE,
                SB.ROUTING_POLICY_ID,
                SB.L9_PORT_IND,
                SB.L9_SPLIT_PERIOD
  FROM HUGE_RESOURCE AR,  --这个表有大约2000多万条数据，做了分区
       MEDIUM_SUB SB,   --这个表有50万左右的数据
       (select /*+ RESULT_CACHE */
        DISTINCT PARAM_NAME as PARAM_NAME 
          from SMALL_PARAM    --这个表比较小，有不到2万条记录，如果加过滤条件，能过滤掉一半多的数据，因为那个字段不在索引字段里，所以加了result_cache
         where GUIDING_IND = 'Y') OP,
       MEDIUM_CUS CS
WHERE AR.AGR_NO = 1056851
   AND AR.AGREEMENT_KEY = MOD(1056851, 100)
   AND (AR.RESOURCE_STATE != 'F' OR AR.RESOURCE_STATE IS NULL)
   AND AR.RANGE_IND = 'N'
   AND SB.MEDIUM_SUB_NO = AR.AGR_NO
   AND EXISTS
(select /*+ INDEX(OP SMALL_PARAM_1IX) */
         1
          from SMALL_PARAM OP
         where OP.PARAM_NAME in (AR.RESOURCE_PRM_CD, AR.BASE_PARAM_NAME) )
   AND SB.MEDIUM_CUS_ID = CS.MEDIUM_CUS_ID
   AND (SB.EXPIRATION_DATE IS NULL OR SB.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS'))
   AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS'))
   AND (AR.EFFECTIVE_DATE
       SB.EXPIRATION_DATE IS NULL)
   AND (AR.EXPIRATION_DATE IS NULL OR
       AR.EXPIRATION_DATE > SB.EFFECTIVE_DATE)
   AND SB.SUB_STATUS != 'T'
UNION ALL
SELECT DISTINCT 'K',
                AR.RESOURCE_VALUE,
                AR.RESOURCE_TYPE,
                GREATEST(TO_CHAR(AR.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS'),
                         TO_CHAR(SH.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS')),
                LEAST(NVL(TO_CHAR(AR.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'),
                          '47001231000000'),
                      NVL(TO_CHAR(SH.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'),
                          '47001231000000')),
                AR.AGR_NO,
                SH.MEDIUM_CUS_ID,
                SH.SUB_STATUS,
                SH.BUSINESS_ENTITY_ID,
                SH.LANGUAGE,
                SH.ROUTING_POLICY_ID,
                SH.L9_PORT_IND,
                SH.L9_SPLIT_PERIOD
  FROM HUGE_RESOURCE AR,
       MEDIUM_SUB_HISTORY SH,
       (select /*+ RESULT_CACHE */
        DISTINCT PARAM_NAME as PARAM_NAME
          from SMALL_PARAM
         where GUIDING_IND = 'Y') OP,
       MEDIUM_CUS CS
WHERE AR.AGR_NO = 1056851
   AND AR.AGREEMENT_KEY = MOD(1056851, 100)
   AND (AR.RESOURCE_STATE != 'F' OR AR.RESOURCE_STATE IS NULL)
   AND AR.RANGE_IND = 'N'
   AND SH.MEDIUM_SUB_NO = AR.AGR_NO
   AND EXISTS
(select /*+ INDEX(OP SMALL_PARAM_1IX) */
         1
          from SMALL_PARAM OP
         where OP.PARAM_NAME in (AR.RESOURCE_PRM_CD, AR.BASE_PARAM_NAME))
   AND SH.MEDIUM_CUS_ID = CS.MEDIUM_CUS_ID
   AND (SH.EXPIRATION_DATE IS NULL OR SH.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS'))
   AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS'))
   AND (AR.EFFECTIVE_DATE
       SH.EXPIRATION_DATE IS NULL)
   AND (AR.EXPIRATION_DATE IS NULL OR
       AR.EXPIRATION_DATE > SH.EFFECTIVE_DATE)
   AND SH.SUB_STATUS NOT IN ('C', 'T')

试着在生产上抓了一个执行计划，又看了一下统计信息。

Statistics
----------------------------------------------------------
        113  recursive calls
          8  db block gets
      31581  consistent gets
          0  physical reads
          0  redo size
       1997  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         20  sorts (memory)
          0  sorts (disk)
          6  rows processed
 Elapsed: 00:02:44.14

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |    13 |  2905 |   519  (59)| 00:00:07 |       |       |

在备份库上查看，结果同样的查询一下子没有了反应。执行了将近3分钟还是没有反应。备份库的资源要比生产差一些。好了感觉有问题了。开始看看这个sql语句的第一部分。
SELECT DISTINCT 'K',
                AR.RESOURCE_VALUE,
                AR.RESOURCE_TYPE,
                GREATEST(TO_CHAR(AR.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS'),
                         TO_CHAR(SB.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS')),
                LEAST(NVL(TO_CHAR(AR.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'),
                          '47001231000000'),
                      NVL(TO_CHAR(SB.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'),
                          '47001231000000')),
                AR.AGR_NO,
                SB.MEDIUM_CUS_ID,
                SB.SUB_STATUS,
                SB.BUSINESS_ENTITY_ID,
                SB.LANGUAGE,
                SB.ROUTING_POLICY_ID,
                SB.L9_PORT_IND,
                SB.L9_SPLIT_PERIOD
  FROM HUGE_RESOURCE AR,  --这个表有大约2000多万条数据，做了分区
       MEDIUM_SUB SB,   --这个表有50万左右的数据
       (select /*+ RESULT_CACHE */
        DISTINCT PARAM_NAME as PARAM_NAME 
          from SMALL_PARAM    --这个表比较小，有不到2万条记录，如果加过滤条件，能过滤掉一半多的数据，因为那个字段不在索引字段里，所以加了result_cache
         where GUIDING_IND = 'Y') OP,
       MEDIUM_CUS CS
WHERE AR.AGR_NO = 1056851
   AND AR.AGREEMENT_KEY = MOD(1056851, 100)
   AND (AR.RESOURCE_STATE != 'F' OR AR.RESOURCE_STATE IS NULL)
   AND AR.RANGE_IND = 'N'
   AND SB.MEDIUM_SUB_NO = AR.AGR_NO
   AND EXISTS
(select /*+ INDEX(OP SMALL_PARAM_1IX) */
         1
          from SMALL_PARAM OP
         where OP.PARAM_NAME in (AR.RESOURCE_PRM_CD, AR.BASE_PARAM_NAME) )
   AND SB.MEDIUM_CUS_ID = CS.MEDIUM_CUS_ID
   AND (SB.EXPIRATION_DATE IS NULL OR SB.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS'))
   AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS'))
   AND (AR.EFFECTIVE_DATE
       SB.EXPIRATION_DATE IS NULL)
   AND (AR.EXPIRATION_DATE IS NULL OR
       AR.EXPIRATION_DATE > SB.EFFECTIVE_DATE)
   AND SB.SUB_STATUS != 'T'
因为结果集的输出中没有op这个表的列，而且在where子句中存在exists语句，在exists里面也没有做关联，那个同事坚持说想在做关联的时候把op的数据先做了result cache,在子查询中就能做关联了，避免重复的表扫描。听起来好像有道理，我觉得语句有问题，尽管说是产品部分提供的方案。op在from 后，但是和后面的流程都没有关联，但也没有做笛卡尔积。在他的强烈反对中我把以下的部分从from中删除。
  (select /*+ RESULT_CACHE */
        DISTINCT PARAM_NAME as PARAM_NAME 
          from SMALL_PARAM    --这个表比较小，有不到2万条记录，如果加过滤条件，能过滤掉一半多的数据，因为那个字段不在索引字段里，所以加了result_cache
         where GUIDING_IND = 'Y') OP,
然后在备份库上重新跑一次，没想到一下子就有了反应。存在一定的物理读，第二次运行就没有了。逻辑度有了近6倍的提升，执行时间在0.02-0.07之间。
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5854  consistent gets
        605  physical reads
         72  redo size
       1900  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed
按照这个速度，0.07秒*80000次/60/60=1.55小时，剩下的事情就是和他们确认一些具体的细节了。下午晚些时候产品部分也确认这确实是个问题。

时间： 2024-10-28 20:08:12

生产系统调优之_敢于质疑的相关文章

生产系统调优之_毫秒级的改进

生产中有一个sql语句,做了union-all操作,对于时间的要求是极其严格的,目前已经从2秒的改进调整到了1秒以内,在此基础上还想做进一步的调整,因为极其频繁的查询,如果一丁点的改进都会在时间上的飞跃,以下的sql语句目前时间控制在不到半秒的样子. 因为表SMALL_OFFER_PARAM 是一个数据字典表,查询的字段上没有相关的索引.目前采用了exisits来做关联. SELECT DISTINCT 'K',                 AR.RESOURCE_VALUE,      

Linux系统调优参数知多少？

所有的TCP/IP调优参数都位于/proc/sys/net/目录.例如,下面是最重要的一些调优参数,后面是它们的含义: 1./proc/sys/net/core/rmem_max - 最大的TCP数据接收缓冲 2./proc/sys/net/core/wmem_max - 最大的TCP数据发送缓冲 3./proc/sys/net/ipv4/tcp_timestamps - 时间戳在(请参考RFC 1323)TCP的包头增加12个字节 4./proc/sys/net/ipv4/tcp_sack -

图表分析系统调优全面性的必要

继昨天对系统进行了初步的调优后,效果还是比较显著,DB time从500%降到了100%以内,当然过程还是充满艰辛,期间也是各种工具和分析语句斟酌了再三. 今天也没有收到任何报警,所以感觉问题似乎已经彻底解决了.在下午的时候查看了一下,发现在凌晨的时候有一个大的抖动,然后DB time基本持续在300%~400%左右.比预期的要高很多. 这个时候的第一感觉就是是不是调优出问题了,性能还是不够稳定啊,带着疑问查看了今天的数据归档情况,发现在这段时间内的数据归档频率还是很高的.在短短几个小时之内就会

ShopEx“我的网店”系统调优

全国最大电子商务软件及服务提供商ShopEx旗下"我的网店"作为独立网店在线开店平台,以其最新版面世,试图为电子商务创业者提供最为快速.易用.经济的开店服务.据悉,其完全整合了ECSHOP最新的稳定版系统,为商家提供免费空间.免费二级域名.免费网店维护等多种基础服务,更加囊括网店备案.网店装修.网店货源.网店代理.货源批发.网店推广.网店代销.通淘宝等在内的多项相关服务."我的网店"的全新版本,为众多有意向创业的电商朋友提供最优的方案平台. 自2007年上市以来,S

UNIX系统调优经典文章

导致系统运行迟缓的原因 有许多不同的潜在的原因会导致系统运行迟缓,但通常可以将它们分为以下几个方面: 进程太多.您的系统可能仅仅只是同时运行了太多的应用程序,或者正在运行少量CPU密集型的操作.要么是服务器超负荷运行,要么是失控进程耗尽了系统资源. 活动内存太多.如果进程使用了大量的内存,那么系统可能会从磁盘换入大量的页面并将大量的页面换出到磁盘,这意味着您的系统花费在内存交换上的时间比真正使用内存的时间更多. 硬件故障.有时候,您会碰到导致系统运行迟缓的硬件故障.不能正常工作的网卡.硬盘或内存

Winform 系统调优

  小白鼠条件: 以常见的树形结构树为例: 有两张结构相同的表table1(1w数据),table2 (2w数据),需要对比数据差异. 表结构如下:       id , parent_id, col1, col2, col3   常规做法是:            常规思想:        循环table1, 一.充分利用缓存效果: 操作系统的高速缓存.磁盘缓存等等,都是利用混存技术来提高系统响应速度.充分利用缓存可以大幅提高系统的响应时间.口说无凭,还是找一个小白鼠来看看:   现有系统中有

使用 tuned/tuned-adm 工具动态调优系统

使用 tuned/tuned-adm 工具动态调优系统 RHEL/CentOS 在 6.3 版本以后引入了一套新的系统调优工具 tuned/tuned-adm,其中 tuned 是服务端程序,用来监控和收集系统各个组件的数据,并依据数据提供的信息动态调整系统设置,达到动态优化系统的目的:tuned-adm 是客户端程序,用来和 tuned 打交道,用命令行的方式管理和配置 tuned,tuned-adm 提供了一些预先配置的优化方案可供直接使用,比如:笔记本.虚拟机.存储服务器等. 如果你正在使

使用tuned/tuned-adm工具动态调优你的CentOS系统教程

什么是tuned/tuned-adm tuned/tuned-adm是CentOS 在 6.3 版本以后引入了一套新的系统调优工具,其中 tuned 是服务端程序,用来监控和收集系统各个组件的数据,并依据数据提供的信息动态调整系统设置,达到动态优化系统的目的:tuned-adm 是客户端程序,用来和 tuned 打交道,用命令行的方式管理和配置 tuned,tuned-adm 提供了一些预先配置的优化方案可供直接使用,比如:笔记本.虚拟机.存储服务器等. 如果你正在使用笔记本(电池电源),想优化

结合第三方工具工具对Weblogic进行调优

web 随着客户对系统性能的要求越来越高,对于任何系统来讲,如何保证系统的性能并且能够在出现性能问题之前可以预测和定位到问题,成了关键.系统上线之前的系统测试和上线之后对整个系统各个环节的性能监控是确保系统以优异性能运行的方法. 去年的年末写了篇关于如何简单使用JPROBE发现和定位J2EE应用中的性能瓶颈,JPROBE是QUEST公司的一个针对开发过程中应用程序的性能优化工具,但这不能满足上面提出的对于系统全面的性能监控和管理要求.针对这种要求,结合目前市场上的性能分析,调优和管理工具,比如I