Sessions "IN" the cpu

A session ON_CPU is doing something, which is generally a good thing. But where is it spending its time ON the cpu.
Looking a v$active_session_history we can look at sampled activity of what the session was doing ON_CPU.

alter session set nls_date_format = "DD-MON-YYYY HH24:MI:SS"; set line 200 col SAMPLE_TIME for a30 col SES_STATE for a9 col CON for a3 col PRS for a3 col HD_PRS for a6 col SQL_EXC for a7 col PLSQL_EXC for a9 col PLSQL_RPC for a9 col PLSQL_CMP for a9 col JAV for a3 col BND for a3 col CLS for a3 col SEQ for a3 select SAMPLE_TIME, SESSION_STATE SES_STATE, IN_CONNECTION_MGMT CON, IN_PARSE PRS, IN_HARD_PARSE HD_PRS, IN_SQL_EXECUTION SQL_EXC, IN_PLSQL_EXECUTION PLSQL_EXC, IN_PLSQL_RPC PLSQL_RPC, IN_PLSQL_COMPILATION PLSQL_CMP, IN_JAVA_EXECUTION JAV, IN_BIND BND, IN_CURSOR_CLOSE CLS, IN_SEQUENCE_LOAD SEQ from v$active_session_history where session_id = 528 and session_serial# = 1234 order by SAMPLE_TIME; SAMPLE_TIME SESSION CON PRS HD_PRS SQL_EXC PLSQL_EXC PLSQL_RPC PLSQL_CMP JAV BND CLS SEQ ------------------------------ ------- --- --- ------ ------- --------- --------- --------- --- --- --- --- ... 30/07/2015 08:05:49 ON CPU N N N N Y Y N N N N N 30/07/2015 08:05:50 ON CPU N N N N Y Y N N N N N 30/07/2015 08:05:51 ON CPU N N N N Y Y N N N N N 30/07/2015 08:05:52 ON CPU N N N N Y Y N N N N N 30/07/2015 08:05:53 ON CPU N N N N Y Y N N N N N 30/07/2015 08:05:54 ON CPU N N N N Y Y N N N N N 30/07/2015 08:05:55 ON CPU N N N N Y Y N N N N N 30/07/2015 08:05:56 ON CPU N N N N Y Y N N N N N

From the output above I can see that the session was spending time working on the cpu IN_PLSQL_EXECUTION and IN_PLSQL_RPC.
Now i can investigate what PLSQL the session is running

col program for a30 select sample_time, SESSION_STATE SES_STATE, PROGRAM, SQL_ID, PLSQL_OBJECT_ID, PLSQL_SUBPROGRAM_ID from v$active_session_history where session_id = 528 and session_serial# = 1234 order by SAMPLE_TIME; SAMPLE_TIME SES_STATE PROGRAM SQL_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID ------------------------------ --------- ------------------------------ ------------- --------------- ------------------- ... 30/07/2015 08:05:51 ON CPU frmweb@server1 (TNS V1-V3) 55377 2 30/07/2015 08:05:52 ON CPU frmweb@server1 (TNS V1-V3) 55377 2 30/07/2015 08:05:53 ON CPU frmweb@server1 (TNS V1-V3) 55377 2 30/07/2015 08:05:54 ON CPU frmweb@server1 (TNS V1-V3) 55377 2 30/07/2015 08:05:55 ON CPU frmweb@server1 (TNS V1-V3) 55377 2 30/07/2015 08:05:56 ON CPU frmweb@server1 (TNS V1-V3) 55377 2 SQL> select OWNER, OBJECT_NAME, PROCEDURE_NAME from dba_procedures where object_id = 55377 and SUBPROGRAM_ID = 2; OWNER OBJECT_NAME PROCEDURE_NAME ------------------------------ ------------------------------ ------------------------------ ABCSE_OWNER ABCSETT_R VALIDATE_DET

******************************************
keywords: v$active_session_history on cpu
******************************************
rdbms version: 11g
******************************************