When visiting a site for the first time, one of the standard things I do is start up a copy of top a Unix utility for reporting which process are currently the most CPU intensive ones on the box. The output of top varies with flavour of Unix, but typically look something like:
last pid: 9655; load averages: 1.17, 1.27, 1.28 09:19:50 69 processes: 67 sleeping, 1 running, 1 on cpu Memory: 365M real, 266M active, 85M free, 80M swap, 413M free swap PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND 8303 oracle 35 -10 69M 33M run 6:38 32.0% 12.50% oracle 8565 oracle 59 -5 68M 13M sleep 3:10 30.0% 11.72% oracle 9655 lewisj 49 0 1220K 0K cpu 0:00 14.0% 5.47% top 9604 oracle 49 -10 68M 11M sleep 0:00 1.0% 0.39% oracle 231 root 59 0 1308K 820K sleep 1:03 0.0% 0.00% lpsched
Whenever an Oracle process starts to show up as an extreme process I run the following script to find out what it is doing, passing in the Unix PID as a parameter.
The script is structured in three stages to avoid using too much CPU in tracking down the SQL being run by the process.
See my note V$ Views - a health warning for an explanation of this approach.
Warning - at present this code fails to handle the problem that v$session and v$sqltext return hash-values in the range 0 to 2^32 (i.e. unsigned 32 bit), whereas v$sqlarea returns values in the range -(2^32) to +(2^32) (i.e. signed 32-bit). This should be handled in the first SQL statement by selecting the hash_values twice, with a decode of their sign to add an optional power(2,32)
rem
rem Script: sinner.sql
rem Author: J.P.Lewis
rem Last Update: 01-June-1998
rem Purpose: Get recent SQL Text and Cost for a Unix PID
rem
rem Input variables:
rem Unix process id (of a PQ slave or oracle{SID} process)
rem
rem Usage:
rem start sinner {UNIX pid}
rem start sinnger 28120
rem
rem Notes:
rem For performance reasons the code runs in steps rather then
rem using a simple join. (Apart from the v$session/process bit
rem where there are no useful pseudo-indexed columns).
rem
rem The use of UNION ALLs instead of a simple OR is for the same reason
rem
define m_pid=&1
clear breaks clear columns set verify off set pagesize 22
column sql_address new_value m_sql_addr noprint column sql_hash_value new_value m_sql_hash noprint format 9999999999999999 column prev_sql_addr new_value m_prev_addr noprint column prev_hash_value new_value m_prev_hash noprint format 9999999999999999
column logon_time format a14
select ses.sid, ses.username, ses.osuser, to_char(ses.logon_time,'dd-mon hh24:mi') logon_time, ses.sql_address, ses.sql_hash_value, ses.prev_sql_addr, ses.prev_hash_value from v$session ses, v$process pro where ses.paddr = pro.addr and pro.spid = &m_pid ;
column which format a9 break on which skip 1
rem =================================================== rem rem This gets the cost, use, and first 2,000 characters rem rem ===================================================
select 'Current' which, executions, parse_calls, sorts, buffer_gets, disk_reads, sql_text from v$sqlarea where hash_value = &m_sql_hash and address = '&m_sql_addr' UNION ALL select 'Previous' which, executions, parse_calls, sorts, buffer_gets, disk_reads, sql_text from v$sqlarea where hash_value = &m_prev_hash and address = '&m_prev_addr' ;
rem ======================== rem rem This gets the whole text rem rem ========================
column piece noprint
select 'Current' which, piece, sql_text from v$sqltext where hash_value = &m_sql_hash and address = '&m_sql_addr' UNION ALL select 'Previous' which, piece, sql_text from v$sqltext where hash_value = &m_prev_hash and address = '&m_prev_addr' order by 1,2 ;