oracle 高耗cpu sql語句的捕捉 。

season0891發表於2010-01-15
OracleSQL
方法一：
透過TOP D 1 –> P(SORT BY CPU USGAE) 找出耗CPU最高的SPID ，然後以這個SPID為引數，查最耗CPU的SQL
SELECT c.SPID,a.*
from v$sqlarea a, v$session b ,v$process c
where a.address = decode(b.SQL_ADDRESS,’00′,b.prev_sql_addr,b.SQL_ADDRESS ) and b.paddr = c.addr
and c.SPID = :SPID

方法二：

在 Oracle效能診斷和日常監控中，最耗CPU的語句通常也是我們最需要關心的語句。所以在Oracle10g的awr中，將cpu time和elapsed time最高的語句加入到了報表，並且放到了SQL語句部分的前兩位。那麼在平時的監控中，也可以透過shell指令碼實時捕獲系統中CPU耗用最多的程式 中正在執行的SQL，以更加有效和及時的診斷和發現問題。

首先寫一個根據spid來或者其SQL的指令碼get_by_spid.sql

#!/bin/ksh
# creator:NinGoo
# function: get sql statement by spid
# parameter: spid
# useage: get_by_spid.sh spid

sqlplus -S /nolog < connect / as sysdba;
 col SERIAL# format 999999
 col sid format 99999
 col username format a10
 col machine format a12
 col program format a32
 col sql_text format a81
 set lines 1000
 set pages 1000
 set verify off
 col sql_hash_value new_value hash_value  head hash_value
select sid,serial#,username,program,sql_hash_value,
         to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') as login_time
  from v\$session
 where paddr in ( select addr from v\$process where spid=$1);

select sql_text
  from v\$sqltext_with_newlines
  where hash_value = &hash_value
 order by piece;
exit;
EOF

然後再在另外一個shell指令碼topsql.sh中獲得系統中CPU耗用最多的oracle server process的spid，迴圈呼叫第一個指令碼獲得SQL

#!/bin/ksh
# creator:NinGoo
# function: get top cpu sql
# parameter: N
# useage: topsql.sh N

if [ $# -eq 0 ]; then
   echo "Usage: `basename $0` N"
   exit 1
fi

topcpu=`ps auxw|grep LOCAL|sort -rn +2 |head -$1|awk '{print $2}'`
i=0

for spid in $topcpu
do
i=`expr $i + 1`
echo "\033[32;1m===============top $i cpu sql=============\033[0m"
. /home/oracle/worksh/get_by_spid.sh $spid
done

那麼呼叫就很簡單了，假如我們要看系統top 3的sql語句，只需要執行topsql.sh 3即可。當然，如果我們自己透過top/topas等工具已經獲得spid了，那麼只要執行get_by_spid.sh spid就能獲得該程式正在執行的sql語句了。

Tags: oracle, sql

Related posts


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

相關文章