I often see and hear claims how many times faster Oracle 12c IM is compared to whatever. Although, the numbers are rather realistic than wishful thinking, I must say that all that strongly depends on how the IM option has been set up.
Besides the way you set up the tables, the MVs and the tablespaces, there are 7 init.ora parameters that directly affect the behavior, performance and speed of the inmemory option.
1. INMEMORY_SIZE
Default: 0
This initialization parameter sets the size of the IM column store in a database instance.
The default value is 0, which means that the IM column store is not used. This initialization parameter must be set to a non-zero value to enable the IM column store. If the parameter is set to a non-zero value, then the minimum setting is 100M.
In a multitenant environment, the setting for this parameter in the root is the setting for the entire multitenant container database (CDB). This parameter can also be set in each pluggable database (PDB) to limit the maximum size of the IM column store for each PDB. The sum of the PDB values can be less than, equal to, or greater than the CDB value. However, the CDB value is the maximum amount of memory available in the IM column store for the entire CDB, including the root and all of the PDBs. Unless this parameter is specifically set for a PDB, the PDB inherits the CDB value, which means that the PDB can use all of the available IM column store for the CDB.
Julian’s tip: the bigger the better: more stuff will fit in.
2. INMEMORY_MAX_POPULATE_SERVERS
Default: Half the effective CPU thread count or the PGA_AGGREGATE_TARGET value divided by 512M, whichever is less.
This initialization parameter specifies the maximum number of background populate servers to use for IM column store population, so that these servers do not overload the rest of the system. Set this parameter to an appropriate value based on the number of cores in the system.
Julian’s tip: use something between the default and its double
3. INMEMORY_FORCE
Default: DEFAULT
This initialization parameter can enable tables and materialized views for the IM column store or disable all tables and materialized views for the IM column store.
Set this parameter to DEFAULT, the default value, to allow the INMEMORY or NO INMEMORY attributes on the individual database objects determine if they will be populated in the IM column store.
Set this parameter to OFF to specify that all tables and materialized views are disabled for the IM column store.
Julian’s tip: no brainer
4. INMEMORY_CLAUSE_DEFAULT
Default: an empty string
This initialization parameter enables you to specify a default IM column store clause for new tables and materialized views.
Leave this parameter unset or set it to an empty string to specify that there is no default IM column store clause for new tables and materialized views. Setting the value of this parameter to NO INMEMORY has the same effect as setting it to the default value (the empty string).
Set this parameter to a valid INMEMORY clause to specify that the clause is the default for all new tables and materialized views. The clause can include valid clauses for IM column store compression methods and data population options.
If the clause starts with INMEMORY, then all new tables and materialized views, including those without an INMEMORY clause, are populated in the IM column store. If the clause omits INMEMORY, then it only applies to new tables and materialized views that are enabled for the IM column store with an INMEMORY clause during creation.
Julian’s tip: start with an emty string and depending on your IM strategy, modify it later on
5. INMEMORY_QUERY
Default: ENABLE
This initialization parameter specifies whether in-memory queries are allowed. Set this parameter to ENABLE, the default value, to allow queries to access database objects populated in the IM column store, or set this parameter to DISABLE to disable access to the database objects populated in the IM column store.
Julian’s tip: very useful parameter on session level for testing how fast the IM option is
6. INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT
Default: 1
This initialization parameter limits the maximum number of background populate servers used for IM column store repopulation, as trickle repopulation is designed to use only a small percentage of the populate servers. The value for this parameter is a percentage of the INMEMORY_MAX_POPULATE_SERVERS initialization parameter value. For example, if this parameter is set to 10 and INMEMORY_MAX_POPULATE_SERVERS is set to 10, then on average one core is used for trickle repopulation.
Julian’s tip: increase to to a level based on your CPU cores and need for fast repopulation.
7. OPTIMIZER_INMEMORY_AWARE
Default: TRUE
This initialization parameter enables or disables all of the optimizer cost model enhancements for in-memory. Setting the parameter to FALSE causes the optimizer to ignore the in-memory property of tables during the optimization of SQL statements.
Julian’s tip: good for testing purposes to see how IM changes the behaviour of the CBO
Interesting test case for the same SQL:
I would always recommend PARALLEL_DEGREE_POLICY = AUTO when using the 12c in-memory option.
And finally some interesting articles on 12c IM:
– Off May Not Be Totally Off: Is Oracle In-Memory Database 12c (12.1.0.2.0) Faster? by Craig Shallahamer
– 12c In-Memory in PDB by Kerry Osborne
– Getting started with Oracle Database In-Memory Part I – Installing & Enabling by Maria Colgan
– In-Memory Column Store in Oracle Database 12c Release 1 (12.1.0.2) by Tim Hall
– Our take on the Oracle Database 12c In-Memory Option by Tanel Poder
Julian Dontcheff's Database Blog 