Data … as usual

If like me you are using Virtualbox for your Oracle labs, maybe you have seen than SSE extensions are activated, but neither SSE4 (1 and 2) nor AVX extensions are activated in your VMs. But you have a modern CPU in your laptop and you cannot use these extensions in your VM (specially Oracle 12c with in Memory option) :

[oracle@oel64-112 ~]$ grep flags /proc/cpuinfo | uniq
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 ssse lahf_lm

Only SSE, SSE2, and SSSE3 are active and this can be seen in details in your Oracle utilization:

SQL> select display_name,value from v$mystat ms, v$statname n where ms.statistic#=n.statistic#
 2 and display_name in ('IM scan CUs columns accessed',
 3 'IM scan segments minmax eligible',
 4 'IM scan CUs pruned');

DISPLAY_NAME                                                          VALUE
---------------------------------------------------------------- ----------
IM scan CUs columns accessed                                              0
IM scan CUs pruned                                                        0
IM scan segments minmax eligible                                          0

SQL> select count(*) from s where amount_sold>1700;

COUNT(*)
----------
 2095

SQL> select display_name,value from v$mystat ms, v$statname n where ms.statistic#=n.statistic#
 2 and display_name in ('IM scan CUs columns accessed',
 3 'IM scan segments minmax eligible',
 4 'IM scan CUs pruned');

DISPLAY_NAME                                                          VALUE
---------------------------------------------------------------- ----------
IM scan CUs columns accessed                                              4
IM scan CUs pruned                                                        5
IM scan segments minmax eligible                                          9

SQL> select spid from v$process where addr=(select paddr from v$session where sid=sys_context('USERENV','SID'));

SPID
------------------------
23693

[oracle@oel64-112 ~]$ pmap -x 23693 | awk {'print $6;'} | grep lib | uniq
libpthread-2.12.so
libaio.so.1.0.1
libc-2.12.so
libm-2.12.so
libnuma.so.1
libnsl-2.12.so
librt-2.12.so
libnque12.so
libnss_files-2.12.so
libdl-2.12.so
libons.so
libocrutl12.so
libocrb12.so
libocr12.so
libskgxn2.so
libhasgen12.so
libdbcfg12.so
libclsra12.so
libipc1.so
libmql1.so
libskjcx12.so
libskgxp12.so
libcell12.so
libodmd12.so

Our server process doesn’t use libshpksse4212.so nor  libshpkavx12.so librairies. (nevertheless, SIMD extensions are used because we can see IM CU pruning). More details about this here: http://blog.tanelpoder.com/2014/10/05/oracle-in-memory-column-store-internals-part-1-which-simd-extensions-are-getting-used/ 

This is because Oracle VirtualBox doesn’t support officially  SSE4_1, SSE4_2, and AVX extension.

But if you read VirtualBox User Manual, we can see that, Starting with VirtualBox 4.3.8, SSE4 extensions can be activated on you VM guests. (This is experimental).

To do this, you have to execute those commands with VirtualBox CLI :

$ VBoxManage setextradata "OEL6.4 Oracle DB (192.168.99.8)" VBoxInternal/CPUM/SSE4.1 1

$ VBoxManage setextradata "OEL6.4 Oracle DB (192.168.99.8)" VBoxInternal/CPUM/SSE4.2 1

$ VBoxManage getextradata "OEL6.4 Oracle DB (192.168.99.8)" enumerate
Key: GUI/LastCloseAction, Value: PowerOffRestoringSnapshot
Key: GUI/LastGuestSizeHint, Value: 720,400
Key: GUI/LastNormalWindowPosition, Value: 10,31,720,442
Key: GUI/MiniToolBarAlignment, Value: bottom
Key: GUI/SaveMountedAtRuntime, Value: yes
Key: GUI/ShowMiniToolBar, Value: yes
Key: VBoxInternal/CPUM/SSE4.1, Value: 1
Key: VBoxInternal/CPUM/SSE4.2, Value: 1

Note: If you want to get the list of your VM, you can use this command: VBoxManage list vms

Now start your VM, and let’s check:

[oracle@oel64-112 ~]$ grep flags /proc/cpuinfo | uniq
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_1 sse4_2 lahf_lm

and now on the oracle server process side:

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 14 11:13:40 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select spid from v$process where addr=(select paddr from v$session where sid=sys_context('USERENV','SID'));

SPID
------------------------
3363

$ pmap -x 3363 | awk {'print $6;'} | grep lib | uniq
libpthread-2.12.so
libaio.so.1.0.1
libc-2.12.so
libm-2.12.so
libnuma.so.1
libnsl-2.12.so
librt-2.12.so
>>>>>  libshpksse4212.so <<<<<
libnque12.so
libnss_files-2.12.so
libdl-2.12.so
libons.so
libocrutl12.so
libocrb12.so
libocr12.so
libskgxn2.so
libhasgen12.so
libdbcfg12.so
libclsra12.so
libipc1.so
libmql1.so
libskjcx12.so
libskgxp12.so
libcell12.so
libodmd12.so

Ok, our database server is now using SSE4 SIMD extensions, but what about AVX ?

AVX extensions are not yet supported on VirtualBox at the moment, and it’s not announced to be (even in experimental mode) with VirtualBox 5.

So for the moment, if you want to use AVX extensions in your guests VM, you need to use VMWare Fusion or parallels (for Mac OS Users) but they are not free tools. (I didn’t search any hypervisor  on Windows or Linux that supports AVX extensions), if you know one … let me know.