(To) real-time access to the most CPU resources consumption SQL statement

2010-12-18  来源:本站原创  分类:Database  人气:76 

Oracle performance in the diagnosis and daily monitoring, the most consuming CPU's statement is what we need most often concern statements. Therefore, the awr in Oracle10g, the cpu time and elapsed time up to the statements of the statement, and put in the first two parts of SQL statements. Then in the usual monitoring, shell scripts can also capture system CPU time consumed in the process of running up to the SQL, a more effective and timely diagnosis and find the problem.

First, under the spid to write a script or the 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;

Then in another shell script to get the system CPU topsql.sh most oracle server process consumes the spid, calls the first cycle to obtain SQL script

#! / 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

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

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

Then the call is very simple, if we look at the system top 3 in the sql statement, you can only perform topsql.sh 3. Of course, if our own through the top / topas and other tools has received spid, then just run get_by_spid.sh spid can get the process running the sql statement.