转载

通过shell脚本抓取awr报告中的问题sql

awr报告中的sql明细部分基本必看的部分,尤其是SQL Order by Elapsed time这个部分,能够很清晰的看到哪些sql语句占用了较多的DB time,所占的比例。这个可以作为调优时的一个重要参考,可以有针对性的来看哪些sql需要格外关注。

比如说我们得到了一个awr报告,Elapsed time这个部分的内容如下。可以很明显看出sql_id为dfb15m5s2uwmc的sql需要格外关注,因为它占用了近一半的DB time.
Elapsed Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text
3,601.90 0   48.52 99.77 0.16 dfb15m5s2uwmc JDBC Thin Client SELECT :1, machinecode, cn, co...
1,612.04 21 76.76 21.71 99.97 0.00 8tmf11fvxy09j JDBC Thin Client SELECT ROUND(AVG(SUM(END_TIME...
1,593.80 20 79.69 21.47 99.97 0.00 cy55p6nrd31db JDBC Thin Client SELECT MAX(USER_CLASS) FROM S...
298.34 20 14.92 4.02 99.96 0.00 29tdwfv5d9s4f JDBC Thin Client SELECT NVL(SUM(OTAL), ...
awr提供的功能集很完整,如果我们能够更快的定位出来哪些sql占用了较多的DB time而不用每次都去生成一个awr报告,其实也是可以实现的,我们可以定制。
在之前的博客中提到awr的导出 http://blog.itpub.net/23718752/viewspace-1123134/
部分日志如下:
. . exported "SYS"."WRH$_SQL_PLAN"                       432.1 KB    1089 rows
. . exported "SYS"."WRH$_LATCH":"WRH$_LATCH_3645037571_0"  198.6 KB    3871 rows
. . exported "SYS"."WRH$_SYSMETRIC_HISTORY"              180.1 KB    3600 rows
可以看到awr的基表是wrh$开头的,这个和我们常用的数据字典表息息相关。
比如sqlstat,数据字典里的历史数据就把wrh$换位dba_hist即可。
我们查看dba_hist_sqlstat的基表,其实发现就是wrh$这样的基表。
$ ksh showdict.sh DBA_HIST_SQLSTAT
object_details
OWNER                           OBJECT_ID DATA_OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
------------------------------ ---------- -------------- ------------------------------ -------------------
SYS                                  9409                DBA_HIST_SQLSTAT               VIEW
PUBLIC                               9410                DBA_HIST_SQLSTAT               SYNONYM
synonym_details
OWNER                          SYNONYM_NAME
------------------------------ ------------------------------
PUBLIC                         DBA_HIST_SQLSTAT

view_details
VIEW_NAME                      TEXT
------------------------------ --------------------------------------------------------------------------------
DBA_HIST_SQLSTAT               select sql.snap_id, sql.dbid, sql.instance_number,
                                xxxx   from WRM$_SNAPSHOT sn, WRH$_SQLSTAT sql
                                 where     sn.snap_id         = sql.snap_id
                                       and sn.dbid            = sql.dbid
                                       and sn.instance_number = sql.instance_number
                                       and sn.status          = 0
那么我们就可以直接从这些数据字典历史表里去查看所需要的信息而不用每次都重新生成一个awr报告。
当然实现的过程也略微费了一些周折,把脚本稍一加工,就成了shell版本。
sqlplus -s $DB_CONN_STR@$SH_DB_SID <<EOF
break on db_name
set pages 50
set linesize 100
col elapsed_time format a10
col per_total format a10
prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~
select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
  from v/$database d,
       v/$instance i;
select snap_id,sql_id,EXECUTIONS_DELTA,max_elapsed elapsed_time,per_total||'%' per_total from 
(select distinct snap_id,sql_id,EXECUTIONS_DELTA,trunc(max(ELAPSED_TIME_DELTA) OVER (PARTITION BY snap_id,sql_id )/1000000,0)||'s' max_elapsed,
 trunc((max(ELAPSED_TIME_DELTA) OVER (PARTITION BY snap_id,sql_id))/(SUM(ELAPSED_TIME_DELTA) OVER (PARTITION BY snap_id )),2)*100 per_total
 from dba_hist_sqlstat where snap_id=$1 
 order by 5 desc
) where rownum<=5;

输出的内容结果如下,和html的格式比起来,也还是很清晰的,只需要输入结束的快照号即可。
$ ksh showsnapsql.sh 57584
Current Instance
~~~~~~~~~~~~~~~~
      DBID DB_NAME     INST_NUM INST_NAME
---------- --------- ---------- ----------------
2534640677 TESTDB2            1 xxxxx

   SNAP_ID SQL_ID        EXECUTIONS_DELTA ELAPSED_TI PER_TOTAL
---------- ------------- ---------------- ---------- ----------
     57494 dfb15m5s2uwmc                0 3601s      48%
     57494 8tmf11fvxy09j               21 1612s      21%
     57494 cy55p6nrd31db               20 1593s      21%
     57494 29tdwfv5d9s4f               20 298s       4%
     57494 c7k4g2urpu1sc                0 175s       2%
这个时候就可以轻松抓取到问题sql,直接来判定是否需要更多的信息。

正文到此结束
Loading...