通過shell指令碼快速定位active session問題

如果你得到反饋，資料庫突然間效能下降了好多，希望你能夠儘快的定位出問題來，有一些思路和方法可以參考。分別從資料庫層面，系統層面來定位，但是個人感覺而言還是不夠快和準。
因為絕大多數的問題都是由於active session導致的，所以我們的注意力集中在ash是比較合理的。ASH在這個時候就是一個利器，通過它能夠得到幾乎實時的資料庫變化。
相比而言我們通過ashrpt得到ash的報告來診斷問題理論上可行，但是有個缺點就是不夠直觀。報告裡面的描述著實很詳細，有時候是有優點有時候可能就是缺點。
公司的同事寫了如下的指令碼，個人在使用中感覺非常的直觀，定位問題真有一目瞭然的感覺。
指令碼內容如下：
sqlplus -s $DB_CONN_STR@$SH_DB_SID < set lin 200
set pages 50
col SID for 99999 trunc
col running_sec for a11 head "ELAP_SEC"
col inst_id for 9 trunc head "I"
col serial# for 99999 trunc head SER#
col username for a12 trunc head "USERNAME"
col osuser for a10 trunc head "OSUSER"
col status for a3 trunc head "STAT"
col machine for a10 trunc
col process for a7 trunc head "RPID"
col spid for a6 trunc head "SPID"
col program for a20 trunc
col module for a13 trunc
col temp_mb for 999999 head "TEMP_MB"
col undo_mb for 999999 head "UNDO_MB"
col logon_time for a11
col rm_grp for a6 trunc
col sql_id for a13
col sql for a49 trunc
col tsps for a6 trunc
SELECT /* use_hash(sess,proc,undo,tmp) use_nl(s)*/ distinct
sess.inst_id,
sess.sid,
sess.serial#,
sess.username,
substr(osuser,1,10) osuser,
status,
sess.process,
proc.spid,
sess.machine,
sess.program,
regexp_substr(NUMTODSINTERVAL(nvl((SYSDATE-SQL_EXEC_START)*24*60*60,last_call_et), 'SECOND'),'+\d{2} \d{2}:\d{2}:\d{2}') running_sec,
TEMP_MB, UNDO_MB,
s.sql_id ,
TSPS.NAME TSPS,
decode(sess.action,null,'',sess.action||', ')||replace(s.sql_text,chr(13),' ') sql
FROM
gv\$session sess,
gv\$process proc,
gv\$sql s,
(select ses_addr as saddr,sum(used_ublk/128) UNDO_MB from v\$transaction group by ses_addr) undo,
(select session_addr as saddr, SESSION_NUM serial#, sum((blocks/128)) TEMP_MB from gv\$sort_usage group by session_addr, SESSION_NUM) tmp,
(select inst_id,sid,serial#,event,t.name from gv\$session ls, sys.file$ f, sys.ts$ t where status='ACTIVE' and ls.p1text in ('file number','file#') and ls.p1=f.file# and f.ts#=t.ts#) tsps
WHERE sess.inst_id=proc.inst_id (+)
and sess.saddr=tmp.saddr (+) and sess.serial#=tmp.serial# (+)
AND sess.status='ACTIVE' and sess.username is not null
and sess.sid=tsps.sid (+) and sess.inst_id=tsps.inst_id(+) and sess.serial#=tsps.serial#(+)
AND sess.paddr=proc.addr (+)
and sess.sql_id = s.sql_id (+)
and sess.saddr=undo.saddr (+)
ORDER BY running_sec desc,4,1,2,3
;
EOF

執行指令碼的結果如下：
I SID SER# USERNAME OSUSER STA RPID SPID MACHINE PROGRAM ELAP_SEC TEMP_MB UNDO_MB SQL_ID TSPS SQL
-- ------ ------ ------------ ---------- --- ------- ------ ---------- -------------------- ----------- ------- ------- ------------- ------ -------------------------------------------------
1 14889 55175 PRDAPPC cowrk01 ACT 1234 23366 ccbdbpr1 JDBC Thin Client 04 11:44:12 519 648600hq1s1s8 UNDOTB SELECT ban_details.COMPANY_CODE, ban_detai
1 19 16945 PRDAPPC blwrk01 ACT 9442 9442 ccbdbpr1 oracle@ccbdbpr3 (P02 02 05:35:02 b9xg175fbzuk5 INSERT INTO TRUE9_BILL_SUMMARY (CYCLE_SEQ_NO, PAY
1 421 20337 PRDAPPC blwrk01 ACT 9444 9444 ccbdbpr1 oracle@ccbdbpr3 (P02 02 05:35:02 b9xg175fbzuk5 INSERT INTO TRUE9_BILL_SUMMARY (CYCLE_SEQ_NO, PAY
1 684 14023 PRDAPPC blwrk01 ACT 9446 9446 ccbdbpr1 oracle@ccbdbpr3 (P02 02 05:35:02 b9xg175fbzuk5 INSERT INTO TRUE9_BILL_SUMMARY (CYCLE_SEQ_NO, PAY
1 6502 24857 PRDAPPC blwrk01 ACT 9458 9458 ccbdbpr1 oracle@ccbdbpr3 (P03 02 05:35:02 b9xg175fbzuk5 INSERT INTO TRUE9_BILL_SUMMARY (CYCLE_SEQ_NO, PAY
1 8880 35991 PRDAPPC blwrk01 ACT 24531 25882 ccbdbpr1 sqlplus@ccbdbpr1 (TN 02 05:35:02 b9xg175fbzuk5 INSERT INTO TRUE9_BILL_SUMMARY (CYCLE_SEQ_NO, PAY
1 9536 26661 PRDAPPC truwl21 ACT 1234 6158 ccbappr2 JDBC Thin Client 00 00:06:38 DATAS0
1 14566 64567 PRDAPPC truwl25 ACT 1234 23179 ccbappr2 JDBC Thin Client 00 00:06:32 fg5mc598u799u DATAS0 select /*+ leading (bpm_ai bpm_pi) use_nl (bpm
1 2799 36269 PRDAPPC truwld6 ACT 1234 1147 ccbappr13 JDBC Thin Client 00 00:05:40 fg5mc598u799u DATAS0 select /*+ leading (bpm_ai bpm_pi) use_nl (bpm
1 3490 41433 CCBSFMDEV pcowrk01 ACT 4860 8520 ccbdbpr1 sqlplus@ccbdbpr1 (TN 00 00:03:38 gt64t81rs1yp6 DATAL0 insert into ccbsfmdev.ar9_temp_e2e_restore (msisd
1 2807 45859 PRDAPPC truwl45 ACT 1234 26921 ccbappr4 JDBC Thin Client 00 00:01:50 fg5mc598u799u DATAS0 select /*+ leading (bpm_ai bpm_pi) use_nl (bpm
1 22051 15289 PRDAPPC truwl21 ACT 1234 17442 ccbappr2 JDBC Thin Client 00 00:01:40 fg5mc598u799u DATAS0 select /*+ leading (bpm_ai bpm_pi) use_nl (bpm

可以從上面的資訊中得出不少的內容。
首先是session對應的sql語句，哪些session在執行哪些語句，執行的時間都一目瞭然。
佔用的表空間情況，從第一條記錄可以看到，執行了近12個小時，佔用了大量的Undo空間。
第2~4行正在執行的是一個並行查詢，並行度為4，並行協調session是(8880,35991) 目前執行時間已經達5個小時，對於並行來說，還是存在問題，需要進一步分析。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1449811/，如需轉載，請註明出處，否則將追究法律責任。

通過shell指令碼快速定位active session問題

相關文章