生产SQL语句突然变慢问题定位

   在客户生产环境中，突然某条SQL语句非常慢，无法跑出结果，以下是诊断过程记录：

1、定位sql_id：
    select sql_id from v$sql where sql_text like '%xxx%'
   sql_id
-----------------------
564s6g59axuk4'

2、统计AWR视图中，该语句执行效率：
    set linesize 155
    col execs for 999,999,999
    col avg_etime for 999,999.999
    col avg_lio for 999,999,999.9
    col begin_interval_time for a30
    col node for 99999
    break on plan_hash_value on startup_time skip 1
    select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
     nvl(executions_delta,0) execs,
     (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
     (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
     from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
     where sql_id = nvl('&sql_id','564s6g59axuk4')
     and ss.snap_id = S.snap_id
     and ss.instance_number = S.instance_number
     and executions_delta > 0
     order by 1, 2, 3
      /
 SNAP_ID  NODE BEGIN_INTERVAL_TIME            SQL_ID        PLAN_HASH_VALUE      EXECS  AVG_ETIME    AVG_LIO
---------- ----- ------------------------------ ------------- --------------- ---------- ---------- ----------
     20001     1 26-10月-17 08.00.30.657 上午   564s6g59axuk4      2005782661         30 14.1517821 1865567.83
     20025     1 27-10月-17 08.00.20.698 上午   564s6g59axuk4      2005782661         27 13.3753969 1844812.18
     20049     1 28-10月-17 08.00.49.876 上午   564s6g59axuk4      3677205750         24 11.9541753 1659475.33
     20050     1 28-10月-17 09.00.03.143 上午   564s6g59axuk4      3677205750          3 13.4740316 2533988.33
     20073     1 29-10月-17 08.00.19.029 上午   564s6g59axuk4      3677205750         21 8.46142976 1441061.47
     20097     1 30-10月-17 08.00.09.581 上午   564s6g59axuk4      2810744384         21 9.88548957 1340974.47
     20121     1 31-10月-17 08.00.14.292 上午   564s6g59axuk4      2810744384         24 9.11253825 1414630.87
     20145     1 01-11月-17 08.00.43.216 上午   564s6g59axuk4      2005782661         21  10.182155 1393004.14
     20169     1 02-11月-17 08.00.09.892 上午   564s6g59axuk4       342558915          9 280.462698 16771588.3
     20173     1 02-11月-17 12.00.24.738 下午   564s6g59axuk4      2005782661          3 19.7334523    3270556
     20174     1 02-11月-17 01.00.28.307 下午   564s6g59axuk4      2005782661          9 12.2578504 1799912.11
     20193     1 03-11月-17 08.00.38.295 上午   564s6g59axuk4       342558915          9 244.750394 12790174.7
     20199     1 03-11月-17 02.00.09.612 下午   564s6g59axuk4       342558915          1 3515.82643  178237676
     20200     1 03-11月-17 03.00.03.620 下午   564s6g59axuk4      2797223102          1 1660.86502   89454616
从报告中可以看出，执行计划中出现一些错误，查看错误的执行计划：

3、统计一下数据库执行计划
    select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss')  TIMESTAMP
    from dba_hist_sql_plan
    where SQL_ID='564s6g59axuk4' order by TIMESTAMP;
SQL_ID        PLAN_HASH_VALUE TIMESTAMP
------------- --------------- -----------------
564s6g59axuk4      2005782661 20170714 11:30:09
564s6g59axuk4      3677205750 20170715 08:16:24
564s6g59axuk4      2810744384 20171030 08:18:15
564s6g59axuk4       342558915 20171102 08:18:12
564s6g59axuk4      2797223102 20171103 15:07:06

4、错误SQL的执行计划：
  col options for a30
  col operation for a40
  col object_name for a20
  select plan_hash_value,id,LPAD(' ', 2 * (depth - 1)) || OPERATION || ' ' ||DECODE(ID, 0, 'Cost = ' || POSITION) "OPERATION",
   options,object_name,CARDINALITY,cost,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss')  TIMESTAMP
    from DBA_HIST_SQL_PLAN  
    where sql_id ='564s6g59axuk4'
    and plan_hash_value='342558915'
    order by ID,TIMESTAMP;
分析执行计划中，选择错误的索引，以渠道日期进行NL，造成执行效率低下。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/354732/viewspace-2146800/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/354732/viewspace-2146800/