通過shell腳本抓取awr報告中的問題sql

awr報告中的sql明細部分基本必看的部分，尤其是SQL Order by Elapsed time這個部分，能夠很清晰的看到哪些sql語句占用了較多的DB time，所占的比例。這個可以作為調優時的一個重要參考，可以有針對性的來看哪些sql需要格外關注。

Elapsed Time (s)

Executions

per Exec (s)

%Total

%CPU

%IO

SQL Id

SQL Module

SQL Text

3,601.90

48.52

99.77

0.16

<a>dfb15m5s2uwmc</a>

JDBC Thin Client

SELECT :1, machinecode, cn, co...

1,612.04

21

76.76

21.71

99.97

0.00

<a>8tmf11fvxy09j</a>

SELECT ROUND(AVG(SUM(END_TIME...

1,593.80

20

79.69

21.47

<a>cy55p6nrd31db</a>

SELECT MAX(USER_CLASS) FROM S...

298.34

14.92

4.02

99.96

<a>29tdwfv5d9s4f</a>

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

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

輸出的内容結果如下，和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，直接來判定是否需要更多的資訊。

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

2016年12月30日更新

可以使用更新後的分析函數，效果要好一些。輸入起始和結束的快照号 比如sh  showsnapsql.sh 100 101

col elapsed_time format 99999999.99

col cpu_time format 99999999.99

select d.dbid            dbid

     , d.name            db_name

     , i.instance_number inst_num

     , i.instance_name   inst_name

       v\$instance i;

select * from (

select distinct snap_id,sql_id,EXECUTIONS_DELTA,round(ELAPSED_TIME_DELTA/1000000,2) Elapsed_time,round(ratio_to_report(ELAPSED_TIME_DELTA) over() *100,2)||'%' per_total

 from dba_hist_sqlstat where snap_id=$2

 order by 4 desc)

where rownum

select distinct snap_id,sql_id,EXECUTIONS_DELTA,round(CPU_TIME_DELTA/1000000,2) CPU_time,round(ratio_to_report(CPU_TIME_DELTA) over() *100,2)||'%' per_total

EOF