JL Computer Consultancy

Guilty Party - Who's the worst sinner right now (Unix only)

Prior to Aug 1999


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.

  1. Convert the Unix PID into an Oracle SID, username, osuser, sql_hash_value and sql_address.
  2. Use the sql_address to find the current SQL, and its total system cost and usage.
  3. Use the XXXX to find the full text of the SQL statement.

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)

Back to Main Index of Topics


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
;

Back to Main Index of Topics