Mapping a resource intensive OS Process to DB Process

Run OS Command

ps -e -o pcpu,pid,user,tty,args|grep -i oracle|sort -n -k 1 -r|head

11.0  2847 oracle   ?        ora_dia0_mydb1
 9.1 27211 oracle   pts/7    /u02/app/oracle/product/11.2.0.4/dbhome_1/jdk/bin/java -server -Xmx384M -XX:MaxPermSize=400M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=40 -DORACLE_HOME=/u02/app/oracle/product/11.2.0.4/dbhome_1 -Doracle.home=/u02/app/oracle/product/11.2.0.4/dbhome_1/oc4j -Doracle.oc4j.localhome=/u02/app/oracle/product/11.2.0.4/dbhome_1/emisdb05_noorstby/sysman -DEMSTATE=/u02/app/oracle/product/11.2.0.4/dbhome_1/emisdb05_noorstby -Doracle.j2ee.dont.use.memory.archive=true -Djava.protocol.handler.pkgs=HTTPClient -Doracle.security.jazn.config=/u02/app/oracle/product/11.2.0.4/dbhome_1/oc4j/j2ee/OC4J_DBConsole_emisdb05_noorstby/config/jazn.xml -Djava.security.policy=/u02/app/oracle/product/11.2.0.4/dbhome_1/oc4j/j2ee/OC4J_DBConsole_emisdb05_noorstby/config/java2.policy -Djavax.net.ssl.KeyStore=/u02/app/oracle/product/11.2.0.4/dbhome_1/sysman/config/OCMTrustedCerts.txt-Djava.security.properties=/u02/app/oracle/product/11.2.0.4/dbhome_1/oc4j/j2ee/home/config/jazn.security.props -DEMDROOT=/u02/app/oracle/product/11.2.0.4/dbhome_1/emisdb05_noorstby -Dsysman.md5password=true -Drepapi.oracle.home=/u02/app/oracle/product/11.2.0.4/dbhome_1 -Ddisable.checkForUpdate=true -Doracle.sysman.ccr.ocmSDK.websvc.keystore=/u02/app/oracle/product/11.2.0.4/dbhome_1/jlib/emocmclnt.ks -Dice.pilots.html4.ignoreNonGenericFonts=true -Djava.awt.headless=true -jar /u02/app/oracle/product/11.2.0.4/dbhome_1/oc4j/j2ee/home/oc4j.jar -config /u02/app/oracle/product/11.2.0.4/dbhome_1/oc4j/j2ee/OC4J_DBConsole_emisdb05_noorstby/config/server.xml
 6.2  1635 oracle   ?        asm_lms0_+ASM1
 1.9  2897 oracle   ?        ora_mmnl_mydb1
 1.9  2138 oracle   ?        oraclemydb1 (LOCAL=NO)
 1.6  2094 oracle   ?        oraclemydb1 (LOCAL=NO)
 1.5  2861 oracle   ?        ora_lms2_mydb1
 1.5  2857 oracle   ?        ora_lms1_mydb1
 1.5  2853 oracle   ?        ora_lms0_mydb1
 1.5  2833 oracle   ?        ora_vktm_mydb1

---------------------------------------------------------------------------
Run below sql command to identify db session


SELECT
'USERNAME : ' || s.username || CHR(10) ||
'SCHEMA : ' || s.schemaname || CHR(10) ||
'OSUSER : ' || s.osuser || CHR(10) ||
'PROGRAM : ' || s.program || CHR(10) ||
'SPID : ' || p.spid || CHR(10) ||
'SID : ' || s.sid || CHR(10) ||
'SERIAL# : ' || s.serial# || CHR(10) ||
'KILL STRING: ' || '''' || s.sid || ',' || s.serial# || '''' || CHR(10) ||
'MACHINE : ' || s.machine || CHR(10) ||
'TYPE : ' || s.type || CHR(10) ||
'TERMINAL : ' || s.terminal || CHR(10) ||
'SQL ID : ' || q.sql_id || CHR(10) ||
'SQL TEXT : ' || q.sql_text
FROM v$session s
,v$process p
,v$sql q
WHERE s.paddr = p.addr
AND p.spid = '&&PID_FROM_OS'
AND s.sql_id = q.sql_id(+);

The prior script prompts you for the operating system process ID. Here is the output for this

USERNAME : 
SCHEMA : SYS
OSUSER : oracle
PROGRAM : oracle@mypc.oracle.com (DIA0)
SPID : 2847
SID : 11731
SERIAL# : 1
KILL STRING: '11731,1'
MACHINE : node1.oracle.com
TYPE : BACKGROUND
TERMINAL : UNKNOWN
SQL ID : 
SQL TEXT : 

-------------------------------------------------

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('&&sql_id')));

--------------------------------------------------

You can use Oracle’s oradebug utility to display top consuming SQL statements if you know the operating
system ID. For example, suppose that you have used a utility such as top or ps to identify a high CPUconsuming
operating system process, and from the name of the process you determine it’s a database
process. Now log in to SQL*Plus and use oradebug to display any SQL associated with the process. In this
example, the OS process ID is 7853:
SQL> oradebug setospid 7853;
Oracle pid: 18, Unix process pid: 7853, image: oracle@xengdb (TNS V1-V3)

---------------------------------------------

alter system kill session 'integer1, integer2 [,integer3]' [immediate];

$ kill -9 6254

---------------------------------------------