生产sql调优之统计信息分析(89天)

今天凌晨,又被电话叫醒了,说是有1个sql,现在跑的很慢。问题已经挺严重了,想让我看看,能不能做点什么。 首先就是和他们确认最近有什么改动,他们说这个是用了很久的sql语句了,没有任何的改动,再听他们说,之前也没有任何的问题。 然后就和他们确认之前这个jobl处理大概多长时间,那个哥们说他也是最近才进的这个项目,可能数据量不同,他也不清楚。但是他肯定的说这个job会跑的很快,几个小时肯定能处理完。 大概了解了下,他们也确定具体的sql语句是什么,没有得到太多的信息,首先通过top命令来抓一下目前消耗资源比较多的进程。 PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 9543 oradb1 25 0 12.2g 25m 21m R 100.0 0.0 426:03.72 oracleDB01 (LOCAL=NO) 32334 oradb1 25 0 12.2g 26m 22m R 100.0 0.0 36:46.28 oracleDB01 (LOCAL=NO) 通过如下的脚本可以抓到当前的process正在进行的sql语句。

if [ -z "$1" ]; then
 echo "no process has provided!"
 exit 0
fi
sh_tmp_process=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID <<END
set pagesize 0 feedback off verify off heading on echo off
select addr from v\\$process where spid=$1;
exit;
END`
if [ -z "$sh_tmp_process" ]; then
 echo "no process exists or session is not from a DB account"
 echo
 echo "####### Process Information from OS level as below ########"
 ps -ef|grep $1|grep -v "grep"|grep ora
 echo "##############################################"
 exit 0
else
 echo '*******************************************'
 echo "Process has found, pid: $1  ,  addr: $sh_tmp_process    "
 echo
 echo "####### Process Information from OS level as below ########"
 ps -ef|grep $1|grep -v grep|grep ora
 echo "##############################################"
 sqlplus -s $DB_CONN_STR@$SH_DB_SID <<EOF
col machine format a20
col terminal format a15
col osuser format a15
col process format a15
col username format a15
set linesize 150
select sid,serial#,username,osuser ,machine,process,terminal,type,to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')login_time from v\$session
where paddr='$sh_tmp_process';
prompt .
col sql_id format a30
col prev_sql_id format a30
col sql_text format a60
set linesize 150
set pages 50
select sql_id,sql_text from v\$sql where sql_id in (select sql_id from v\$session where paddr='$sh_tmp_process' and sql_id is not null  ) and rownum<2;
select sql_id prev_sql_id ,sql_text from v\$sql where sql_id in (select prev_sql_id sql_id from v\$session where paddr='$sh_tmp_process'  ) and rownum<2;
EOF
fi

结果看了一下,和他们确认,就是他们的job要用的sql语句。可以看到里面已经有一些hint,想必是之前就碰到过问题,优化过的。 这个语句也算是挺长的一个sql了,里面用到了union all来做两个结果的合并。

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,
       MEDIUM_SUB SB,
       (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 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 OR
       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 OR
       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')

和他们确认了基本的时间后,先抓一个awr看看里面怎么说。然后在这个基础上抓一个awr的sql report。看看执行计划的情况。 从执行计划来看,也是一个执行时间比较的sql语句,查看指标,消耗并不高,大概执行时间为5秒,但是根据他们的反馈,需要跑几十万个这样的查询。这样下来。这个语句得跑 8秒*80万次/60/60=111个小时,这样确实太长了。得跑将近5天的样子。 首先既然他们肯定了sql语句没有做过改动,而且也已经经过调优了,那就先看看为什么显示执行计划消耗没那么高,实际上却差别这么大,首先想到的就是statistics的问题。 在查看了关联的几个达标之后,发现有一个大表的统计信息误差有10%左右,其他的都在1%以内的样子。 这也是一个可能原因,在查看索引的使用情况,都走索引了,没有任何索引失效的情况。 sql语句不能随便动,只能下手的地方就是统计信息了,其他的性能参数一直都没有做过改动了。 和他们确认之后,先做了一个统计信息收集。

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> NULL, TABNAME => 'HUGE_RESOURCE',    CASCADE => TRUE,METHOD_OPT =>'FOR ALL INDEXED COLUMNS SIZE 1', ESTIMATE_PERCENT => 40 , DEGREE =>  8,GRANULARITY =>'ALL');

执行之后就创建了一个快照,然后过了几分钟之后又生成一个快照,发现明显快了不少,从5秒降低到了2秒。也算是不少的收获了。2秒*80000次/60/60=44.4小时,还是有一些问题,然后剩下的问题就是看看之前的执行情况了。 下面是我在最近这一个月左右的时间内现有的awr中找到的。时间浮动还是比较小。

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....

21st May

413.88

614

0.67

1.50

99.74

0.17

8686n41y0jsnd

gext1GenericEx@ccbdbpr1 (TNS V1-V3)

/* */ SELECT DISTINCT 'K', AR....

12th May

100.17

63

1.59

0.80

99.68

0.25

da18v3wt1u09y

gext1GenericEx@ccbdbpr1 (TNS V1-V3)

SELECT DISTINCT 'K', AR.RESOUR...

9th April

818.45

793

1.03

3.73

99.86

0.09

da18v3wt1u09y

gext1GenericEx@ccbdbpr1 (TNS V1-V3)

SELECT DISTINCT 'K', AR.RESOUR.

然后我仔细比较了一下对应的sql语句。发现今天碰到sql语句和之前的有一些不同之处。看来也不能全信 偶有了他们说的。

有了基本的参照,就可以在这个基础上分析了。

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

原文发表时间:2014-05-31

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏「3306 Pai」社区

《那些年,我在乙方的日子 -- 神谕篇NO1》

某个夏日的午后,窗外知了在大声鸣叫。而我却在睡梦中跟基友一起吃鸡,正准备抢空投时 。手机突然铃声响起,惊醒后一看是领导电话,一下子回到了现实中。心想又得去公司吃...

572
来自专栏HT

基于HTML5实现3D热图Heatmap应用

Heatmap热图通过众多数据点信息,汇聚成直观可视化颜色效果,热图已广泛被应用于气象预报、医疗成像、机房温度监控等行业,甚至应用于竞技体育领域的数据分析。 h...

3335
来自专栏hightopo

基于HTML5实现3D热图Heatmap应用

932
来自专栏数据和云

性能为王:SQL标量子查询的优化案例分析

黄廷忠(网名:认真就输) 云和恩墨技术专家 个人博客:http://www.htz.pw/ 本篇整理内容是黄廷忠在“云和恩墨大讲堂”微信分享中的讲解案例,S...

3365
来自专栏HT

基于HTML5实现的Heatmap热图3D应用

Heatmap热图通过众多数据点信息,汇聚成直观可视化颜色效果,热图已广泛被应用于气象预报、医疗成像、机房温度监控等行业,甚至应用于竞技体育领域的数据分析。 ?...

2588
来自专栏乐沙弥的世界

使用 DBMS_REPAIR 修复坏块

       对于Oracle数据块物理损坏的情形,在我们有备份的情况下可以直接使用备份来恢复。对于通过备份恢复,Oracel为我们提供了很多种方式,冷备,基于...

582
来自专栏用户画像

网上书店管理系统数据库 sql sever

1.数据库各数据对象的设计与实现:表、约束、完整性体现、查询、视图,要求用合理的数据体现。

863
来自专栏文渊之博

利用PowerShell复制SQLServer账户的所有权限

问题   对于DBA或者其他运维人员来说授权一个账户的相同权限给另一个账户是一个很普通的任务。但是随着服务器、数据库、应用、使用人员地增加就变得很枯燥乏味又耗时...

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

通过shell脚本得到数据字典的信息 (r2笔记72天)

在平时的工作中,可能需要查询一些数据字典的信息,比如数据字典对应的基表信息,可以得到更多数据库内部的一些详细信息。 比如user_objects这个数据字典视图...

2687
来自专栏乐沙弥的世界

参数job_queue_processes与Oracle jobs

Oracle jobs为Oracle开发人员和数据库管理员提供了数据库层面维护的极大便利性。对于Oracle jobs在Oracle 9i之前,是由dbms_...

623

扫描关注云+社区