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 */

      "WAITING"                                                                                                 = "wait_class value"

the query for the above logic will be something like
SELECT sample_time,
DECODE (session_state,'ON CPU',
        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;

Tweaks for Linux VirtualBox (VBOX) to use SIMD for 12c InMemory.

  Oracle InMemory uses  SSE4.2 extensions to run SIMD.
  However, by default, Virtualbox(VBOX) may not configure this. You can verify if the extensions are enabled
by checking  the flag in cpuinfo as follows in your Linux guest OS

  cat /proc/cpuinfo | grep sse4

If it is not set,  then no rows will be returned.

 you have to do the following tweak to enable it.
1)  Shutdown the guest VM, and the GUI VM manager.
2) cd to VM home directory.
    eg.  cd  D:\software\virtualbox
3) run the following command  Allowing VirtualBox to pass the SSE 4.2 extensions
( supported in VirtualBox starting in Versions 4.3.8)

D:\software\virtualbox>VBoxManage setextradata "VM name" VBoxInternal/CPUM/SSE4.2 1

Where "VM Name" is the name of your guest Linux VM.

4) start the GUI VManager  and start the guest OS.

5) verify if the  flag is set.
cat /proc/cpuinfo | grep sse4

[oracle@bigdatalite ~]$ cat /proc/cpuinfo | grep sse4
flags        : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx rdtscp lm constant_tsc rep_good nopl pni ssse3 sse4_2 lahf_lm

To see if the library is linked dynamically , you can run the following 
ps -ef | grep ora_p | awk '{system ( "pmap "$2 " |grep libshpk " ) ; print "-->"$8 }'

[oracle@bigdatalite ~]$  ps -ef | grep ora_p | awk '{system ( "pmap "$2 " |grep libshpk " ) ; print "-->"$8 }'
00007f732897d000   1500K r-x--  /u01/app/oracle/product/12.1.0/dbhome_1/lib/
00007f7328af4000   2044K -----  /u01/app/oracle/product/12.1.0/dbhome_1/lib/
00007f7328cf3000     72K rw---  /u01/app/oracle/product/12.1.0/dbhome_1/lib/
00007f8e01a71000   1500K r-x--  /u01/app/oracle/product/12.1.0/dbhome_1/lib/
00007f8e01be8000   2044K -----  /u01/app/oracle/product/12.1.0/dbhome_1/lib/
00007f8e01de7000     72K rw---  /u01/app/oracle/product/12.1.0/dbhome_1/lib/