How to Monitor Real-Time Top SQL on CPU Consuming

  • by
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.
  2. $ 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 "=========================================================================="

  3. Run the script to show the result.
  4. $ ./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.

Leave a Reply

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