生产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 条评论
登录 后参与评论

相关文章

来自专栏Jerry的SAP技术分享

使用BAPISDORDER_GETDETAILEDLIST创建S/4HANA的Outbound Delivery

要在S/4HANA里创建Outbound Delivery,首先要具有一个销售订单,ID为376,通过事务码VA03查看。

751
来自专栏xingoo, 一个梦想做发明家的程序员

oracle数据结构

 数据类型: 1 字符数据:CHAR VARCHAR NCHAR NVARCHAR2 LONG CLOB NCLOB  2 数字数据类型:NUMBER 唯一用来...

1986
来自专栏数据库新发现

Oracle9i新特性-索引监视及注意事项[修正版]

Last Updated: Saturday, 2004-12-04 10:28 Eygle

723
来自专栏数据库新发现

Oracle10gR2 New Feature:V$SESSION change tracking

作者:eygle 出处:http://www.eygle.com/blog 日期:September 15, 2005 本文链接:http://www.eyg...

432
来自专栏乐沙弥的世界

导入导出 Oracle 分区表数据

导入导入Oracle 分区表数据是Oracle DBA 经常完成的任务之一。分区表的导入导出同样普通表的导入导出方式,只不过导入导出需要考

1025
来自专栏乐沙弥的世界

Oracle 重建索引脚本

      索引是提高数据库查询性能的有力武器。没有索引,就好比图书馆没有图书标签一样,找一本书自己想要的书比登天还难。然而索引在使用的过程中,尤其是在批量的D...

811
来自专栏java达人

join和where区别以及各类join的示例

1 WHERE子句中使用的连接语句,在数据库语言中,被称为隐性连接。INNER JOIN……ON子句产生的连接称为显性连接。(其他JOIN参数也是显性连接...

18610
来自专栏C/C++基础

MySQL问题集锦

(1)SELECT子句是必选的,其它子句如WHERE子句、GROUP BY子句等是可选的。

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

通过shell解析dump生成parfile(r2笔记76天)

当我们得到一个dump文件的时候,总是有些不太确定dump文件中是否含有一些我们原本不希望出现的表,如果在未知的情况下对dump文件进行操作时很危险的,比如我们...

2083
来自专栏沃趣科技

应用示例荟萃 | performance_schema全方位介绍(下)

使用performance_schema中的语句当前事件记录表和语句事件历史记录表可以查询数据库中最近执行的一些SQL语句,以及语句相关的信息,这里我们以eve...

1693

扫码关注云+社区