Mapping a resource-intensive process to a database process

posted Apr 24, 2012, 6:55 AM by Sachchida Ojha   [ updated May 22, 2013, 9:13 AM ]
This is most common in DBA's troubleshooting task to identify an OS intensive process in the BOX and then MAP OS process back to a database process. If the database process is a SQL process then display user of the SQL statement and also the SQL.

1. Find the top consuming queries with the ps command.

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

2. you can now determine what type of Oracle process.

'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 ||chr(10)
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(+);

3. If you want to investigate further about the SQL statement consuming OS resources then

SQL> SELECT * from table(DBMS_XPLAN.DISPLAY_CURSOR(('&&sql_id')));

You can also use ORADEBUG utility to display top consuming SQL Statement if you know the OS ID.