透過shell指令碼監控sql執行頻率

在生產環境中，可能會存在各種潛在的sql問題，比如由於硬體資源導致，如果影響到了IO,CPU，就會導致一些本來執行很快的sql語句變慢或者系統響應嚴重減緩。
比如由於資料庫的一些設定導致執行計劃的問題，sql_profile導致的執行計劃穩定帶來的效能不穩定,表中統計資訊的變更導致的執行計劃的變化等等。
有時候等到問題發生的時候，可能已經發生較長的一段時間了。如果想一下子定位問題還是很困難的，在sql_id沒有發生變化的情況下，如果要檢視語句的執行頻率，一種可能就是透過在awr中篩查，但是可行性太差。畢竟耗時費力，還不靈活，很有可能問題sql的執行時間再特定的幾個時間段內，那麼完整的抽取也就有些冗餘了，針對性不強。這個時候可以使用指令碼來抽取DBA_HIST_SQLSTAT的資訊，然後結合快照資訊，得到一個快照級的sql語句執行情況。
shell指令碼的內容如下：

sqlplus -s < set linesize 200
col BEGIN_INTERVAL_TIME format a30
set pages 50
select * from (
select
BEGIN_INTERVAL_TIME,
SQL_ID,
 sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
sum(Executions_total),
sum(EXECUTIONS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and to_char(s.begin_interval_time,'yyyymmdd')='$3'
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between $1 and $2
and sql_id='$4'
group by
SQL_ID,
executions_total,
EXECUTIONS_DELTA,
BEGIN_INTERVAL_TIME
order by
sum(CPU_TIME_DELTA) desc)
where rownum <20;

exit
EOF

執行指令碼的情況如下：
我們想檢視在2014年11月9號的1點到22點之間，sql_id 7sx5p1ug5ag12的執行頻率，得到的結果如下：

ksh showsqlhist.sh 1 22 20141109 7sx5p1ug5ag12

BEGIN_INTERVAL_TIME            SQL_ID        SUM(CPU_TIME_DELTA) SUM(DISK_READS_DELTA) SUM(EXECUTIONS_TOTAL) SUM(EXECUTIONS_DELTA)   COUNT(*)
------------------------------ ------------- ------------------- --------------------- --------------------- --------------------- ----------
09-NOV-14 05.00.39.436 AM      7sx5p1ug5ag12             1071833                     0                 59785                 11712          1
09-NOV-14 12.00.25.211 AM      7sx5p1ug5ag12             1040858                     0                 48075                 12052          1

可以簡單的驗證一下。比如我們想檢視12:00左右的時候sql_id 7sx5p1ug5ag12 的執行情況。來得到一個awr報告。

Executions	Rows Processed	Rows per Exec	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	SQL Text
12,052	12,052	1.00	1.03	101.5	0		DBMS_SCHEDULER	SELECT SPARE4 FROM SYS.OPTSTAT...

或者來反證，可能絕大多數時候發現問題的時候，我們會生成awr報告，如果看到某些sql語句可能存在問題，可以使用指令碼得到一個語句的執行頻率，結合快照來看更加具有針對性。
對於排查問題來說還是有一定的幫助的。