Introduction
There are a few new features in 19c for In-memory. The key ones are
- Oracle Data Guard Multi-Instance Redo Apply Works with the In-Memory Column Store
- Database In-Memory Wait on Populate
- In-Memory External Tables
- Resource Manager Automatically Enabled for Database In-Memory
- Enhancements for In-Memory External Tables
Oracle Data Guard Multi-Instance Redo Apply Works with the In-Memory Column Store
We have had In-Memory Column Store and Data Guard Multi-Instance Redo Apply for sometime now. (12.2.0.1) However, in past releases you could not have these enabled at the same time in your standby environment,
With 19c, In-Memory Column Store and Data Guard Multi-Instance Redo Apply can both be enabled on an Active Standby.
It is also worth noting, that Multi-Instance Redo Apply can also make use of the In-Memory Column Store, if possible, to improve the apply process.
A new initialization parameter is required to enable both at the same time. This is listed below. And of course, you also need to have enabled multi instance redo apply. See notes below.
alter system set enable_imc_with_mira = true scope=both sid=*
Note
This is only valid if the redo apply method has been set to all instances. for example,
SQL> alter database recover managed standby database instances all;
DGMGRL> edit database MyDb1 set property ApplyInstances=All;
Database In-Memory Wait on Populate
There is a new function called POPULATE_WAIT which is part of the DBMS_INMEMORY_ADMIN package.
The purpose of the function is to force a wait until all objects set at of a given priority are populated to a given percentage. An example use case is, to ensure that applications relying on fast performance have the in-memory column store populated before use. For example,
SELECT DBMS_INMEMORY_ADMIN.POPULATE_WAIT(PRIORITY=>'NONE', PERCENTAGE => 80, TIMEOUT => 300) FROM dual;
Notes
The statement above submits a population task for all objects with a priority level of none or greater. e.g All im-memory objects. It will populate to the specified percentage of 80% and will wait 300 seconds before returning one of 5 values to the client. See chart below.
Also, it is worth remembering that by default when an object has an inmemory priority of none, they would only get populated during a full scan.
Other objects that have had a priority set, are populated automatically when the database instance starts and based on the priority level set on the object; LOW, MEDIUM, HIGH or CRITICAL. This population is done over time. e.g You can specify the order of population but not the speed.
Text | Value | Description |
---|---|---|
POPULATE_TIMEOUT | -1 | The timed out value specified was reached before completion. |
POPULATE_SUCCESS | 0 | Population was successful |
POPULATE_OUT_OF_MEMORY | 1 | Not enough memory for the in-memory pool to complete the operation completely |
POPULATE_NO_INMEMORY_OBJECTS | 2 | No objects met the population request |
POPULATE_INMEMORY_SIZE_ZERO | 3 | In-Memory Column Store not configured |
In-Memory External Tables
External tables can now be loaded into the In-Memory pool. The inmemory clause can be specified when creating the external table.
However, there are some restrictions.
- In-Memory sub-clauses :- column clause, distribute clause, and priority clause are not valid.
- Optimized Arithmetic number support controlled by the initialization parameter INMEMORY_OPTIMIZED_ARITHMETIC is not supported for external tables.
- Partitioning is not supported
- Join groups are not supported
- In-Memory expressions are not supported.
- DISTRIBUTE FOR SERVICE on Oracle Active Data Guard is not supported.
- Sessions that query In-Memory external tables must have the session parameter QUERY_REWRITE_INTEGRITY set to stale_tolerated
Published 15th March 2023