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:
99999999seconds, which is 115.74 days.
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 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.