Dontcheff

Archive for July, 2014|Monthly archive page

Oracle Database 12.1.0.2 New Features

In Database options, DBA, Oracle database on July 23, 2014 at 14:25

Oracle Database 12.1.0.2 was released yesterday, July 22nd 2014.

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

OEMDBEXpress12102b

Here are few useful links:

1. Oracle Database 12.1.0.2 Software Download
2. Oracle 12cR1 Documentation
3. MOS 1905806.1 about 12.1.0.2
4. Oracle Database Blog: 12.1.0.2 is available!!!

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

OEMDBEXpress12102c