Using the POPULATE_WAIT function to monitor IM column store population

The POPULATE_WAIT function was introduced in Oracle Database 19c to monitor IM column store population. This creates the ability to use a PL/SQL function to wait for IM column store population rather than having to query a system view repeatedly. Why is this useful? Once Database In-Memory has been enabled you may have applications that need to wait until objects are populated in the IM column store before allowing analytic queries to run. When objects are not populated in the IM column store queries will still execute, but they will probably take much longer to run, and if analytic reporting indexes have been removed, performance could suffer for the entire database due to increased I/O and CPU usage.

With the POPULATE_WAIT function it is now much easier to create a process that can wait until population has been completed and then allow analytic queries to begin. The documentation shows an example using the POPULATE_WAIT function with STARTUP RESTRICT to prevent anyone other than administrative users from accessing the database until population is complete. Database services can also be used to prevent connections until objects are populated.

The POPULATE_WAIT function was added to the DBMS_INMEMORY_ADMIN package and accepts multiple parameters to control how to monitor population. This is documented in the Database PL/SQL Packages and Types Reference, but perhaps an easier way to visualize how this works is with some examples.

The function returns a code to identify the population status. The codes are the following:

-1 = POPULATE_TIMEOUT
 0 = POPULATE_SUCCESS
 1 = POPULATE_OUT_OF_MEMORY
 2 = POPULATE_NO_INMEMORY_OBJECTS
 3 = POPULATE_INMEMORY_SIZE_ZERO

and can be captured with code like the following:

select dbms_inmemory_admin.populate_wait(
 priority => 'HIGH', percentage => 100, timeout => 60 )
INTO v_rc
from dual;

and then the return code can be handled with something like this:

IF v_rc = 0 THEN
  v_done := TRUE;
ELSIF v_rc = -1 THEN
  v_wait := v_wait + 1;
ELSE
  RAISE_APPLICATION_ERROR(-20000, 'Error populating IM column store');
END IF;

There are four input parameters available in the POPULATE_WAIT function:

• PRIORITY - Waits for all objects with the specified priority or higher to be populated.
• PERCENTAGE - Percentage of population required for function to consider population complete.
• TIMEOUT - Specifies number of seconds the function waits. Will return -1 if not complete. Note that the default is 99999999 seconds, which is 115.74 days.
• FORCE - Will first evict all segments from the IM column store that have a priority greater to or equal to the PRIORITY parameter.

In the example above I passed in priority, percentage and timeout values. In general I think it makes sense to set a time out period shorter than the normal population time for your objects and then loop until population is either complete or an error occurs.

Another interesting thing to note is that if SERVEROUTPUT is set to ON then the function will emit status messages when it completes.

For a working example of PL/SQL code and the output of running that example check out this Github link.