Quick look inside linux ‘top’ for APEX

Imagine… you’re a SysAdmin…
Imagine… you’re “terminal schooled”…
And you are running Oracle Application Express

You want to be able to support your APEX-team by helping them diagnose performance-related issues, but all you have is good, old ‘top‘ to get you some insight…


Not the most insightful, especially not because there is no real way to map the Oracle process to something useful inside the APEX environment, using the “good old DBA-queries” you might have laying around.
We all look at sql_text from v$sqlarea with the accompanying username from v$session
Every time you see a process on ‘top‘, you’ll be too late to capture what it was doing anyway, as the current statement, the interesting fields will be empty and you get nowhere.

With APEX, some useful new fields are added to v$session, like:

  • module
  • client_info
  • client_identifier

So we thought up a little doodle that might come in handy…

A small script, that monitors ‘top‘ to see if the process-number, you are interested in, is active, and then fires the query… repeatedly…
Again, it’s a doodle, but it did give us some quick run-time insight into the issue we were tracking.

It consists of a unix-shell script (devised by Turkel Mammadli):
clear; echo "process id :"; read var1; while [ true ] ; do
read -t 1 -n 1
if [ $? = 0 ] ; then
status=$(echo $var1|sqlplus -S "/as sysdba" @/home/oracle/scripts/trkl0.sql)
if [ $(echo $status |awk {'print $13'}) == ACTIVE ]; then
echo $var1|sqlplus -S "/as sysdba" @/home/oracle/scripts/sql_from_top.sql

Which inquires which process you want to monitor and then calls a check script:
set pagesize 0
select s.status
from v$session s
, v$process p
where s.paddr = p.addr
and p.spid=&var1

If the monitored process is active, it get’s you your insight:
set lines 200 pages 1000
column pu format a8 heading 'O/S|Login|ID'
column su format a20 heading 'Oracle|User ID'
column mw format a20 heading 'Ecaris|medewerker'
column stat format a8 heading 'Session|Status'
column ssid format 999999 heading 'Oracle|Session|ID'
column sser format 999999 heading 'Oracle|Serial|No'
column modu format a15 heading 'APEX module'
column clin format a30 heading 'APEX client'
column clid format a30 heading 'APEX ident'
column spid format a10 heading 'UNIX|Process|ID'
column txt format a40 heading 'Current Statment'
column event format a30
select p.username pu
, s.username su
, s.status stat
, s.sid ssid
, s.serial# sser
, s.module modu
, s.client_info clin
, s.client_identifier clid
, lpad(p.spid,7) spid
, substr(sa.sql_text,1,540) txt
, s.last_call_et seconds
from v$process p
, v$session s
, v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and p.spid=&var1
order by 1,2,7

And if you let it run, this is what you get:

It still “misfires” every now and again because of the speed of processing inside the database, but still gives a nice insight.

Note. If you have a better idea improvements, don’t be shy!

Leave a Reply

Your email address will not be published. Required fields are marked *