How to Monitor Real-Time Top SQL on CPU Consuming

You can identify the most cpu-consuming SQL statements from database memory by querying v$sql.cpu_time, but they are not real-time data, they are history data. If we want real-time data, there is an indirect way can identify the real-time SQL statements on cpu consuming. First, collect the most cpu-consuming process id on OS-level, then map these process id (PID) to sql statements on database-level.

The following script has been tested on Solaris and Linux platform.

1. Edit the script to retrieve the top 10 cpu-consuming processes on OS-level, then compose a retrieving sql script temporarily, and last, execute the sql script by sqlplus.
$ vi Check_ORCL.sh
...
#!/bin/bash
clear
# Start to retrieve Top Database Processes
echo "  DateTime: "`date '+%Y-%m-%d %T'`
echo ""
echo "  <Top Processes on OS>"
echo ""
echo "  PID USER      CPU PROCESS_NAME"
echo "-------------------------------------------------------------------"
ps -eo pid,user,pcpu,args | sort -k3rn | egrep '_ORCL|eORCL' | head -10
echo "-------------------------------------------------------------------"

# Start to retrieve Top SQL from Process ID
echo ""
echo "  <Top SQL on Database>"
USERNAME=system
PASSWORD=password

# Compse a SQL statement to retrieve the data
echo "set linesize 120;" > ORCL.sql
echo "set pagesize 20;" >> ORCL.sql
echo "column spid format a4;" >> ORCL.sql
echo "column username format a6;" >> ORCL.sql
echo "column sid format 99999;" >> ORCL.sql
echo "column type format a4;" >> ORCL.sql
echo "column sql_text format a50;" >> ORCL.sql
echo "SELECT distinct p.spid, s.username, s.sid, substr(s.type,1,4) "type", substr(q.sql_text,1,200) "sql_text" FROM v$session s, v$sql q, v$process p WHERE p.addr = s.paddr AND s.sql_hash_value = q.hash_value AND p.spid IN (" >>  ORCL.sql
ps -eo pid,user,pcpu,args | sort -k3rn | egrep '_ORCL|eORCL' | head -10 | awk '{print $1}' > ORCL.pro
LASTPID=`cat ORCL.pro | tail -1`
for var in `cat ORCL.pro`
        do
        if [ $var != $LASTPID ] ;
        then
                echo "'$var'," >>  ORCL.sql
        else
                echo "'$var'" >>  ORCL.sql
        fi
done
echo ");" >>  ORCL.sql
echo "exit;" >>  ORCL.sql
# Start to execute the SQL statement
sqlplus -S $USERNAME/$PASSWORD@ORCL @ORCL.sql
echo "=========================================================================="

2. Run the script to show the result.
$ ./Check_ORCL.sh
  DateTime: 2012-11-26 19:36:54

  <Top Processes on OS>

  PID USER      CPU PROCESS_NAME
-------------------------------------------------------------------
 6104 oracle   13.3 oracleORCL (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
 6063 oracle    0.0 ora_pmon_ORCL
 6065 oracle    0.0 ora_dbw0_ORCL
 6067 oracle    0.0 ora_lgwr_ORCL
 6069 oracle    0.0 ora_ckpt_ORCL
 6071 oracle    0.0 ora_smon_ORCL
 6073 oracle    0.0 ora_reco_ORCL
 6075 oracle    0.0 ora_cjq0_ORCL
 6079 oracle    0.0 ora_s000_ORCL
 6081 oracle    0.0 ora_d000_ORCL
-------------------------------------------------------------------

  <Top SQL on Database>

SPID USERNA    SID TYPE SQL_TEXT
---- ------ ------ ---- --------------------------------------------------
6071             5 BACK select u.name, o.name, t.spare1, t.pctfree$    fro
                        m sys.obj$ o, sys.user$ u, sys.tab$ t    where  (b
                        itand(t.trigflag, 1048576) = 1048576) and
                          o.obj#=t.obj# and o.owner# = u.user#

6073             6 BACK select o.owner#,o.name,o.namespace,o.remoteowner,o
                        .linkname,o.subname,o.dataobj#,o.flags from obj$ o
                         where o.obj#=:1

6104 SH          9 USER select * from sales

==========================================================================

Now, you can identify the most consuming process# 6104 and map it to the sql statement "select * from sales" that is issued by a user named SH.