如何通过AWR中的信息分析SQL的性能历史

如果一条SQL正在执行，发生性能问题，我们可以通过从内存抓取的方式获得这条SQL的所有执行情况，比如执行计划，执行次数，逻辑读，物理读，CPU使用等，从而采取合理措施优化SQL。

而往往我们需要做事后分析，因为SQL性能问题时我们无法做现场分析，此时就需要从哪个Oracle历史记录中获取，这里我们通过记录在AWR中的SQL执行性能历史，来进一步分析SQL问题所在。

我们使用如下脚本实现

set verify on lines 200 pages 9999

undefine sqlid

accept level char DEFAULT 'ADVANCED' PROMPT "Explain Plan level (BASIC, TYPICAL, ALL, ADVANCED)  (Default : ADVANCED) : "

col cpu_exe format 9999.9999

col ela_exe format 9999.9999

col prds_exe format 9999.9999

col bg_exe format 99999999

col rows_exe format 999999

col snap_time format a14

col inst format 999

col sql_id new_value sqlid noprint

break on plan_hash_value skip 1

select

  sql_id,

  plan_hash_value,

  s.instance_number inst,

  s.snap_id,

  to_char(s.begin_interval_time,'YYYYMMDD:HH24:MI') snap_time,

  executions_delta delta_exe,

  cpu_time_delta/1000000 delta_cpu_sec,

  (case when executions_delta>0 then ((cpu_time_delta/1000000)/executions_delta) else 0 end) cpu_exe,

  (case when executions_delta>0 then ((elapsed_time_delta/1000000)/executions_delta) else 0 end) ela_exe,

  (case when executions_delta>0 then (buffer_gets_delta/executions_delta) else 0 end) bg_exe,

  (case when executions_delta>0 then (disk_reads_delta/executions_delta) else 0 end) prd_exe,

  (case when executions_delta>0 then (rows_processed_delta/executions_delta) else 0 end) rows_exe

from

  dba_hist_sqlstat b,

  dba_hist_snapshot s

where

  b.sql_id = '&sqlid'

  and b.snap_id = s.snap_id

  and s.begin_interval_time >= sysdate-&days_back

  and s.instance_number = b.instance_number

order by

  2,3,4

;

select * from table(dbms_xplan.display_awr('&sqlid','','','&level'))

;

undefine sqlid

clear columns

clear breaks

下面是示例使用该脚本

SQL> @hissqlawr.sql

Explain Plan level (BASIC, TYPICAL, ALL, ADVANCED)  (Default : ADVANCED) : 

Enter value for sqlid: 6n3gsy19qxh8a

old  18:   b.sql_id = '&sqlid'

new  18:   b.sql_id = '6n3gsy19qxh8a'

Enter value for days_back: 1

old  20:   and s.begin_interval_time >= sysdate-&days_back

new  20:   and s.begin_interval_time >= sysdate-1

这里查询近一天的该SQL的执行历史。输出类似如下所示，我们需要重点理解字段含义，再分析什么是好的执行计划和什么不是优的执行计划

PLAN_HASH_VALUE INST    SNAP_ID   SNAP_TIME  DELTA_EXE DELTA_CPU_SEC       CPU_EXE       ELA_EXE       BG_EXE    PRD_EXE   ROWS_EXE

--------------- ---- ---------- ----------- ---------- ------------- ------------- ------------- ------------ ---------- ----------

2349384962         1     112053 20130625:06          2     26.875913       13.4380       54.3881       657671    34007.5      37760

下面是不好的执行计划

PLAN_HASH_VALUE INST    SNAP_ID   SNAP_TIME  DELTA_EXE DELTA_CPU_SEC       CPU_EXE       ELA_EXE       BG_EXE    PRD_EXE   ROWS_EXE

--------------- ---- ---------- ----------- ---------- ------------- ------------- ------------- ------------ ---------- ----------

1713891141         3     118090 20130827:04          1     71.738094       71.7381      269.9821      4207596    3389259        230

我们看到不好的计划中获得一行数据需要访问的数据块数量为33029.80个， (4207596 + 3389259 / 230)，而好的执行计划得一行数据需要访问的数据块数量为18.31 (657671 + 34007.5 / 37760)，前者其实走了全表扫描，而后者走了索引。

这里分析SQL的调优重点在分析同一条SQL的获得每一条记录需要读的块数，这个块数是物理读 +逻辑读，这里可以看出一个执行计划的优劣，当然分析执行计划是不是优，还需要分析数据库对象统计信息，数据库参数设置，操作系统参数设置以及系统资源变化等。这里我们强调的是通过AWR中一条SQL的执行历史，分析这个历史中哪个SQL是比较好的，SQL调优是一个相对工作量和技术含量的工作，这里我们不做讨论，后续会不断分享学习心得。