Finding details of an Oracle process using PID

In our environment, users will often report EBS application is running slow. In such situations I found load on the DB Tier is above normal. Load is more than 40. So I decided to check what the top memory consuming process is doing. Below are the steps I used to follow.

Find the top memory consuming processes using top command. once executed top command, press 'm' key to list the process in descending order of memory usage.

Top

Next find the SQL ID of the process id which consumes more memory

SET LINES 400
SET PAGES 999
COL OSUSER FOR A9
COL DBUSER FOR A9
COL SID FOR 9999
COL MACHINE FOR A25
COL PROGRAM for A30
SELECT OSUSER,USERNAME DBUSER,SID,SERIAL#,SQL_ID,STATUS,MACHINE,PORT,PROGRAM,LAST_CALL_ET ACTIVE_TIME_SEC FROM V$SESSION WHERE PADDR=(SELECT ADDR FROM V$PROCESS WHERE SPID=&PID);

Now find the sql text of that process by using it's sql id obtained from previous query

SET LONG 200000000

SET LINES 300

SET PAGES 999

COL PARSING_SCHEMA_NAME FOR A20 

COL SQL_FULLTEXT FOR A60

SELECT PARSING_SCHEMA_NAME,SQL_FULLTEXT FROM V$SQL WHERE SQL_ID='&SQL_ID';

You can also use the pid to directly get the sql text

COL PID FOR A5

SELECT S.OSUSER,P.SPID PID,S.USERNAME DBUSER,S.SID,S.SERIAL#,S.STATUS,S.LAST_CALL_ET ACTIVE_TIME_SEC, S.SQL_ID,SQL.SQL_FULLTEXT FROM V$SESSION S, V$PROCESS P, V$SQL SQL WHERE  S.PADDR=P.ADDR AND S.SQL_ID=SQL.SQL_ID AND S.SQL_CHILD_NUMBER=SQL.CHILD_NUMBER AND P.SPID='&PID';

 ****************************நன்றி****************************