Find what is consuming resource

I came across this scenario today wherein i could see that oracle process is using 50 % of CPU and i was asked to dig out what is it doing.

I logged in to database checked the long operations which were running using following query.

SQL> select sid,message from v$session_longops;

       SID MESSAGE
———- ————————————————–
    12 Hash Join:  : 644 out of 644 Blocks done
    12 Hash Join:  : 931 out of 931 Blocks done
    12 Hash Join:  : 1057 out of 1057 Blocks done
    12 Hash Join:  : 1127 out of 1127 Blocks done
    12 Hash Join:  : 1232 out of 1232 Blocks done
    12 Hash Join:  : 1267 out of 1267 Blocks done
    12 Hash Join:  : 1246 out of 1246 Blocks done
    12 Table Scan:    RESOURCE_OWNER.DW_ASSIGNMENTS: 57658
       out of 57658 Blocks done

    12 Table Scan:    RESOURCE_OWNER.DW_ASSIGNMENTS: 57663

       SID MESSAGE
———- ————————————————–
       out of 57663 Blocks done

9 rows selected.

I found that session with id 12 is doing some long running query.

From the prstat command i found that process callled oracle/15 is consuming 49 % CPU.

bash-2.05$ prstat
  PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP      
27541 oracle    411M  370M cpu0     0    0   1:26:18  49% oracle/15

From ps command i found that following process is the culprit for bad performance.

bash-2.05$ ps -ef | grep  27541
  oracle 27541     1 49 14:53:58 ?       86:48 ora_j000_PROD

I again logged into database and ran following query to find which job it is running.

SQL> SELECT a.sid, c.serial#, a.job, a.failures, to_char(a.this_date, ‘mm/dd/yyyy hh:mi pm’) startdatetime, b.what
FROM dba_jobs_running a, dba_jobs b, v$session c
WHERE a.job = b.job AND a.sid = c.sid order by a.this_date
  2    3    4 
SQL> /

       SID    SERIAL#         JOB   FAILURES STARTDATETIME
———- ———- ———- ———- ——————-
WHAT
——————————————————————————–
    12        6         944      0 10/06/2008 02:53 pm
BEGIN
   pkg_reports.request_management (SYSDATE – 500, SYSDATE + 250);
END;

So job 944 was the culprit.