Friday, 25 July 2014

Oracle Database 12.1.0.2 New Features

I found no issues whatsoever with downloading, installing the software and creating a container database plus enabling the in-memory option.

Oracle Database 12.1.0.2 New Features
When you setup the IM option, note the inmemory area specified in the output below:

SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size          2929936 bytes
Variable Size         511707888 bytes
Database Buffers       50331648 bytes
Redo Buffers            5455872 bytes
In-Memory Area        268435456 bytes
Database mounted.
Database opened.
SQL> show parameter inmemory

NAME                     TYPE                  VALUE
------------------------------------ --------------------------------- ------------------------------
inmemory_clause_default          string
inmemory_force               string                DEFAULT
inmemory_max_populate_servers        integer                   1
inmemory_query               string                ENABLE
inmemory_size                big integer               256M
inmemory_trickle_repopulate_servers_ integer                   1
percent
optimizer_inmemory_aware         boolean                   TRUE


Let me show you how one can see the incredible speed of the inmemory option:

SQL> alter table SALES inmemory;

Table altered.

SQL> select max(price) most_expensive_order from sales;

MOST_EXPENSIVE_ORDER
--------------------
           91978

Elapsed: 00:00:02.50

SQL> alter session set inmemory_query="DISABLE";

Session altered.

Elapsed: 00:00:00.03

SQL> select max(price) most_expensive_order from sales;

MOST_EXPENSIVE_ORDER
--------------------
           91978

Elapsed: 00:01:25.51
Check the following new commands and views related to the in-memory option:

SQL> alter table SALES inmemory memcompress for capacity high;

Table altered.

SQL> alter table SALES no inmemory (client);

Table altered.

SQL> select segment_name, inmemory_size, inmemory_compression, bytes/inmemory_size comp_ratio from v$im_segments;

SEGMENT_NAME         INMEMORY_SIZE INMEMORY_COMPRESSION       COMP_RATIO
-------------------- ------------- ------------------------------ ----------
SALES             24969216 FOR CAPACITY HIGH          11.6325459

SQL> select table_name, cache, inmemory_compression comp, inmemory_priority priority, inmemory_distribute RAC from dba_tables where table_name = 'SALES';

TABLE_NAME   CACHE  COMP             PRIORITY   RAC
------------ ---------- -------------------- ---------- ----------
SALES        N  FOR CAPACITY HIGH    NONE   AUTO

SQL> select view_name from dba_views where view_name like 'V_$IM%';

VIEW_NAME
----------------------------------------
V_$IM_SEGMENTS_DETAIL
V_$IM_SEGMENTS
V_$IM_USER_SEGMENTS
V_$IM_TBS_EXT_MAP
V_$IM_SEG_EXT_MAP
V_$IM_HEADER
V_$IM_COL_CU
V_$IM_SMU_HEAD
V_$IM_SMU_CHUNK
V_$IM_COLUMN_LEVEL

10 rows selected.

Some of the most interesting new features are:
  • Oracle Database In-Memory
  • In-Memory Aggregation and In-Memory Column Store
  • Oracle Big Data SQL
  • Advanced Index Compression
  • Automatic Big Table Caching
  • Zone Maps for full table access
  • New and optimized SQL function, APPROX_COUNT_DISTINCT()
  • Full Database Caching
  • Rapid Home Provisioning based on gold images stored in a catalog of pre-created homes
  • New database parameter: DBFIPS_140

Oracle Database 12.1.0.2 New Features