Using the POPULATE_WAIT function to monitor IM column store population

March 21, 2023 | 3 minute read
Andy Rivenes
Product Manager
Text Size 100%:

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:


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;
  RAISE_APPLICATION_ERROR(-20000, 'Error populating IM column store');

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.


Andy Rivenes

Product Manager

Andy Rivenes is a Senior Principal Product Manager at Oracle Corporation and is a Product Manager for Database In-Memory. Andy has been working with Oracle products since 1992 and spent a large part of his career managing Oracle databases for both custom and E-Business Suite applications. Andy now spends his time helping customers implement Database In-Memory and providing information about Database In-Memory through blogs, conferences, and customer visits.

Previous Post

Enterprise Manager In-Memory Central

Andy Rivenes | 3 min read

Next Post

Oracle Database 23c Free - Wide Tables

Andy Rivenes | 5 min read