Wednesday, September 28, 2011

Mapping OS (Linux/unix) process to database process

So, you want to map the resource-intensive process to the database process and if the that process is a SQL process, you like to find out the user and sql text for that process.

use the ps command as follow on linux/unix



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

Here is some sample output:

51.0 31848 oracle   ?        oracletestdb (LOCAL=NO)
0.4 27051 oracle     ?        oracletestdb (LOCAL=NO)
0.4 16741 oracle     ?        oracletestdb (LOCAL=NO)
0.3  6576 oracle      ?        oracletestdb (LOCAL=NO)
0.3 18875 oracle               pts/0    /bin/bash
0.1   473 oracle       ?        oracletestdb (LOCAL=NO)
0.1 30454 oracle     ?        oracletestdb (LOCAL=NO)
0.1 12225 oracle     ?        oracletestdb (LOCAL=NO)
0.0  9841 oracle      ?        oracletestdb (LOCAL=NO)
0.0  9729 oracle      ?        oracletestdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PRO                         TOCOL=beq)))

The  output identifies one operating system process consuming an excessive amount of CPU (51.0%). The process ID is 31848 and name is oracletestdb. From the process name, it’s an Oracle process associated with the 'testdb' database. 

In SQL PLUS, run the below query and at the promp, provide the process number from the above output eg 31848

SQL> 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(+); 


'USERNAME:'||S.USERNAME||CHR(10)||'SCHEMA:'||S.SCHEMANAME||CHR(10)||'OSUSER:'||S
--------------------------------------------------------------------------------
USERNAME   : BILAL
SCHEMA     : BILAL_SCHEMA
OSUSER     : bashraf
PROGRAM    : SQL Developer
SPID       : 31848
SID        : 17
SERIAL#    : 33995
KILL STRING: '17,33995'
MACHINE    : RDSRV07
TYPE       : USER
TERMINAL   : unknown

'USERNAME:'||S.USERNAME||CHR(10)||'SCHEMA:'||S.SCHEMANAME||CHR(10)||'OSUSER:'||S
--------------------------------------------------------------------------------
SQL ID     : by3c8848gyngu
SQL TEXT   : Select * from my_test_table;


The output indicates that this is a SQL*Plus process with a database SID of 17 and SERIAL# of 33995. 
You’ll need this information if you decide to terminate the process with the ALTER SYSTEM KILL SESSION statement. 
In this example, since the process is running a SQL statement, further details about the query can be 
extracted by generating an execution plan: 

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

You’ll be prompted for the sql_id when you run the prior statement (in this example, the sql_id is 
by3c8848gyngu). 

No comments:

Post a Comment