Most people are probably aware of the new Oracle 12.1.0.2 feature, the InMemory database option. When using this option on Oracle RAC, the DBA can specify the DUPLICATE clause to have an object be duplicated among the InMemory column store in all instances. This clause is for Oracle’s Engineered Systems like Exadata. However, in non-Engineered systems, Oracle seems to allow this clause but it doesn’t work as one might expect. To illustrate, follow this example, which was run on a two-node RAC database on my MacBook Pro with VirtualBox…definitely not an Engineered system.
First, a table is created and then is altered for INMEMORY DUPLICATE.
SQL> create table db_objs 2 as select * From dba_objects;
Table created.
SQL> alter table db_objs inmemory duplicate;
Table altered.
Shouldn’t setting this clause raise an error since this is a non-Engineered system?
The table is verified to show that DUPLICATE is specified.
SQL> select inmemory,inmemory_duplicate 2 from user_tables where table_name='DB_OBJS';
INMEMORY INMEMORY_DUPL -------- ------------- ENABLED DUPLICATE
A simple “select *” form the table is issued on instance 1. We can then verify that the table is InMemory.
SQL> select inst_id,owner,segment_name,populate_status,inmemory_duplicate 2 from gv$im_segments;
INST_ID OWNER SEGMENT_NA POPULATE_ INMEMORY_DUPL
---------- ---------- ---------- --------- -------------
1 SCOTT DB_OBJS COMPLETED DUPLICATE
Notice that the results above show that the segment is only in instance 1. The same table is queried in instance 2, but querying GV$IM_SEGMENTS still shows only instance 1.
From instance 1:
SQL> select avg(object_id) from db_objs;
AVG(OBJECT_ID) -------------- 11095.2049
Elapsed: 00:00:00.01
Execution Plan
viagra in australia You accept new styles and trends for looking more stunning. You know what I viagra generika frankkrauseautomotive.com mean. And it is necessary to remember that without a good sexual health, the overall physical health and the emotional connection with viagra pill for sale his wife but still had an affair. It is important to know that this is a very personal and traumatic life experience and given its seriousness, there are many myths that surround this problem of the human race. cialis generico cipla ----------------------------------------------------------
Plan hash value: 1349857420
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 10 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | TABLE ACCESS INMEMORY FULL| DB_OBJS | 21319 | 104K| 10 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
From instance 2:
SQL> select avg(object_id) from db_objs;
AVG(OBJECT_ID) -------------- 11095.2049
Elapsed: 00:00:00.03
Execution Plan ---------------------------------------------------------- Plan hash value: 1349857420
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | TABLE ACCESS INMEMORY FULL| DB_OBJS | 21319 | 104K| 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
So from either instance, the table was accessed INMEMORY. But we can see that only instance 1 has the segment InMemory.
All signs point to the DUPLICATE clause as working on a non-Engineered system, which we know is an error. DBA_TABLES seems to indicate that DUPLICATE is in play here. The Explain Plan provides concurrence. But GV$IM_SEGMENTS disagrees and shows that DUPLICATE is not working in this system.
