Trace session causing high cpu process

The server is heavily loaded, there are oracle processes causing high cpu usage. You want to know what the session causing the high cpu usage is doing.

Step 1: Find sid and serial# by spid

You have the process pid from top/prstat, now find the sid and serial# from oracle. In the example the pid is 22639

SQL> select s.sid,s.serial# from v$process p,v$session s where s.paddr = p.addr and p.spid = 22639;

       SID    SERIAL#
---------- ----------
      1841      15028

Step 2: Enable tracing

SQL> ALTER SYSTEM SET timed_statistics = true;
SQL> execute dbms_system.set_sql_trace_in_session(1841, 15028, true);

Step 3: Find the trace file

Head to USER_DUMP_DEST and look at the file. Deeper insight about what happens can be gained by using the TRCANLYZR utility.