eMarcel.com

Oracle Database, Fusion Middleware, Linux

Oracle Database 12c In-Memory How To Get Started

Oracle Database In-Memory feature has been released by Oracle in recent patch set 12.1.0.2. Here is how to upgrade Oracle database to version 12.1.0.2: Upgrade Oracle Database 12c with ASM 12.1.0.1 to 12.1.0.2

With the introduction of Oracle Database In-Memory, a single database can now efficiently support mixed workloads, delivering optimal performance for transactions while simultaneously supporting real-time analytics and reporting. This is possible due to a unique “dual-format” architecture that enables data to be maintained in both the existing Oracle row format, for OLTP operations, and a new purely in-memory column format, optimized for analytical processing. It is not a free feature though, see at the bottom for prices.

2015-05-25_000455

Database In-Memory uses an In-Memory column store (IM column store), which is a new component of the Oracle Database System Global Area (SGA), called the In-Memory Area.  The In-Memory area is a static pool within the SGA, whose size is controlled by the initialization parameter INMEMORY_SIZE (default 0) and must have a minimum size of 100MB.

The following objects can be stored in Oracle In-Memory database:

  • Tablespaces
  • Tables
  • Partitions and Sub-partitions
  • Materialized Views

If it is enabled at the tablespace level, then all new tables and materialized views in the tablespace will be enabled for the In-Memory column store by default.

Enabling In-Memory option in Oracle database:

Oracle Database In-Memory Area specified at the Container Database level (CDB) and by default each Pluggable Database (PDB) inherits the INMEMORY_SIZE from Container Database level (CDB).

Login as “sys” user to CDB (container database), change the parameter, restart database and start pluggable database PDB01;

SQL> ALTER SYSTEM SET inmemory_size = 512M scope=spfile;
SQL> shutdown immediate;
SQL> startup;
SQL> alter pluggable database "PDB01" open read write;

Enabling in-memory option for an entire table “customers”

Login as “sh” user to pluggable database PDB01 and alter table;

SQL> ALTER TABLE customers INMEMORY;
table CUSTOMERS altered.

SQL> select count(*) from customers;
  COUNT(*)
----------
     55500 

Checking in-memory option:

SELECT table_name,
  inmemory,
  inmemory_priority,
  inmemory_compression
FROM user_tables
WHERE table_name='CUSTOMERS';

TABLE_NAME INMEMORY INMEMORY_PRIORITY INMEMORY_COMPRESSION
---------- -------- ----------------- --------------------
CUSTOMERS  ENABLED  NONE              FOR QUERY LOW

Enabling various In-Memory options in Oracle database:

Enabling the In-Memory attribute on the EXAMPLE tablespace by specifying the INMEMORY attribute

SQL> ALTER TABLESPACE example INMEMORY;

Enabling the In-Memory attribute on the sales table but excluding the “prod_id” column

SQL> ALTER TABLE sales INMEMORY NO INMEMORY(prod_id);

Disabling the In-Memory attribute on one partition of the sales table by specifying the NO INMEMORY clause

SQL> ALTER TABLE sales MODIFY PARTITION SALES_Q1_1998 NO INMEMORY;

Enabling the In-Memory attribute on the customers table with a priority level of critical

SQL> ALTER TABLE customers INMEMORY PRIORITY CRITICAL;

Option CRITICAL: object is populated immediately after the database is opened.

I encourage you to read the following white paper “Oracle Database In-Memory” where you will find detailed information about In-Memory features such as In-Memory compression, scans, joins, aggregation, column store on RAC and much more.

Oracle claims In-Memory Database:

  • as much as 300 times faster in terms of performance improvement
  • fetching 1 mln records without in-memory option took ~20 minutes, while fetching 1 mln records with in-memory option enabled took roughly ~5 seconds

Oracle Database In-Memory prices

In-memory Price tag

 

 

(Visited 534 times, 1 visits today)

, , ,

4 Shares
Tweet
Share2
+1
Share2

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close