Thursday, November 13, 2014

"CPU :In-memory query" in Oracle 12c

 In 12c EM, we have a  " CPU :in-memory query" displayed in the  graphical report of performance tab. Since Oracle Database In-Memory sessions access only memory, it makes perfect sense to track the CPU utilized for such queries.  I did not see any separate event or  info of how it is derived. 
However, there are new columns called "in_memory_query" and "in_memory_populate" which have values 'Y' and 'N'
I think  "CPU :In-memory query" is derived from the following logic.

 From the v$active_session_history columns session_state, session_type and in_memory_query or in_memory_populate values and its meaning.

      session_state + session_type       + in_inmemory_xxx                                = "Wait-class"
     "ON CPU"        + "FOREGROUND" + in_inmemory_query ='Y'                      =  CPU in-memory query
      "ON CPU"       + "FOREGROUND" + in_inmemory_query ='N'                      =  CPU
      "ON CPU"       + "BACKGROUND" + in_inmemory_populate ='Y'                  =  CPU: in-memory populate
     "ON CPU"        + "BACKGROUND" + in_memory_populate='N'                     =  BCPU /* background CPU */

else
      "WAITING"                                                                                                 = "wait_class value"


the query for the above logic will be something like
 
SELECT sample_time,
DECODE (session_state,'ON CPU',
   DECODE(session_type,'FOREGROUND',
        DECODE( in_inmemory_query,'Y','CPU in-memory query','CPU'),'BACKGROUND',DECODE(in_inmemory_populate,'Y','CPU: in-memory populate','BCPU')),wait_class)
        FROM v$active_session_history order by sample_time desc;

No comments:

Post a Comment

Feedback welcome