Oracle 12cR1: It’s here! Oracle Database In-Memory

06 Aug Oracle 12cR1: It’s here! Oracle Database In-Memory

In the last weeks, it was officially launched the latest version of the Database 12c (12.1.0.2.0), and the peculiarity of this version is the inclusion of a new database technology that implements in memory a new format of columnar data , to deliver extremely fast processing SQL.

Among the first questions that come with this feature of the RDBMS 12c, is if I need to have my entire database in memory, and the answer is no, it is not necessary, this option is designed to keep in memory the data that impact the most system performance due to I/O.

Another question that arises is, I need to double my memory to have this option, and the answer is the same, no, it is not necessary.

RENE@orcl > show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------------------------------------------------------------------
compatible string 12.1.0.2.0
noncdb_compatible boolean FALSE
RENE@orcl >show parameter inmemory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------------------------------------------------------------------
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_query string ENABLE
inmemory_size big integer 1G

To see which segments are in our “In-Memory”store , use the V $ IM_SEGMENTS, which for the moment we will not see anything because we have not used a table, or allocated a table so that it is in our “In-Memory” Store.

RENE@orcl > Select v.owner, v.segment_name name, v.populate_status status From v$im_segments v;

no rows selected

RENE@orcl >Select table_name, cache, inmemory_priority, inmemory_distribute, inmemory_compression
2 From user_tables;

TABLE_NAME CACHE INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION
-------------------- -------------------- -------------------- -------------------- --------------------
EMPLOYEES Y
LOCATIONS Y
CUSTOMER Y
JOBS Y
DEPARTMENTS Y

5 rows selected.

Now, what we will do is modify our tables to be added to our “In-Memory” store.

RENE@orcl > Alter table EMPLOYEES inmemory;

Table altered.

RENE@orcl > Alter table LOCATIONS inmemory;

Table altered.

RENE@orcl > Alter table CUSTOMER inmemory;

Table altered.

RENE@orcl > Alter table JOBS inmemory;

Table altered.

RENE@orcl > Alter table DEPARTMENTS inmemory;

Table altered.

What we see now are the attributes of the table in the “In-Memory” store.

RENE@orcl > Select table_name, cache, inmemory_priority, inmemory_distribute, inmemory_compression
From user_tables;

TABLE_NAME CACHE INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION
-------------------- -------------------- -------------------- -------------------- --------------------
EMPLOYEES Y NONE AUTO DISTRIBUTE FOR QUERY
LOCATIONS Y NONE AUTO DISTRIBUTE FOR QUERY
CUSTOMER Y NONE AUTO DISTRIBUTE FOR QUERY
JOBS Y NONE AUTO DISTRIBUTE FOR QUERY
DEPARTMENTS Y NONE AUTO DISTRIBUTE FOR QUERY

5 rows selected.

But as we have not used any of the tables, these are not loaded to the store.

RENE@orcl > Select v.owner, v.segment_name name, v.populate_status status From v$im_segments v;

no rows selected

Now what we will do is use them, and also see that part was left out or if all segments are inside the “In-Memory” store.

RENE@orcl > select /*+ full(DEPARTMENTS) noparallel(DEPARTMENTS) */ count(*) from DEPARTMENTS;

COUNT(*)
----------
1978

1 row selected.

RENE@orcl > select /*+ full(JOBS) noparallel(JOBS) */ count(*) from JOBS;

COUNT(*)
----------
5420

1 row selected.

RENE@orcl > select /*+ full(CUSTOMER) noparallel(CUSTOMER) */ count(*) from CUSTOMER;

COUNT(*)
----------
65231

1 row selected.

RENE@orcl > select /*+ full(LOCATIONS) noparallel(LOCATIONS) */ count(*) from LOCATIONS;

COUNT(*)
----------
723743

1 row selected.

RENE@orcl > select /*+ full(EMPLOYEES) noparallel(EMPLOYEES) */ count(*) from EMPLOYEES;

COUNT(*)
----------
15297516

1 row selected.

RENE@orcl > SELECT v.owner, v.segment_name name, v.populate_status status, v.bytes_not_populated
FROM v$im_segments v;

OWNER NAME STATUS BYTES_NOT_POPULATED
-------------------- ------------------------------ --------- -------------------
RENE JOBS COMPLETED 0
RENE DEPARTMENTS COMPLETED 0
RENE LOCATIONS COMPLETED 0
RENE CUSTOMER COMPLETED 0
RENE EMPLOYEES COMPLETED 0

5 rows selected.

In the same way we can see the compression ratio of our segments in memory.

RENE@orcl > Select v.segment_name name,
2 v.bytes orig_size,
3 v.inmemory_size in_mem_size ,
4 v.bytes/v.inmemory_size comp_ratio
5 From v$im_segments v
6 Order by 4;

NAME ORIG_SIZE IN_MEM_SIZE COMP_RATIO
-------------------- ---------- ----------- ----------
LOCATIONS 67108864 8519680 7.87692308
CUSTOMER 67108864 5373952 12.4878049
JOBS 67108864 1179648 56.8888889
DEPARTMENTS 67108864 1179648 56.8888889
EMPLOYEES 738197504 1572864 469.333333

5 rows selected.

Conclusion

DBs and In-Memory Data is not a new concept, but in the new version of Oracle RDBMS is to work in a row and columnar in shape, and response times are impacted in a positive way that will help them for all DBs that are a mix between DW and OLTP.

I will write another post later and with more time, so you can see the advantage of using this option in your RDBMS.