方法一:
通过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
方法二:
通过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
方法二:
作者:
江枫 | 【转载时请务必以超链接形式标明文章原始出处和作者信息及本声明】
地址: http://rdc.taobao.com/blog/dba/html/126_get_top_n_cpu_sql_in_realtime.html
地址: http://rdc.taobao.com/blog/dba/html/126_get_top_n_cpu_sql_in_realtime.html
在 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
# 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
# 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, sqlRelated posts
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/90618/viewspace-625232/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/90618/viewspace-625232/