New In-Memory Eligibility Test

A new In-Memory Eligibility Test has been made available in Oracle Database RU 19.20 which is now available. This new feature is also now part of the database in a package called DBMS_INMEMORY_ADVISE, and you can find more details in the Database In-Memory Guide. The purpose of the eligibility test is to create a simple way of identifying if your database workload would benefit from Database In-Memory. If the database is identified as "eligible" then it is a candidate for running the Oracle Database In-Memory Advisor to get more information about your analytic workload.

The In-Memory Eligibility Test analyzes workload based on an Automated Workload Repository (AWR) snapshot range. Since the eligibility test is now part of the database you don't have to install anything to run it. If you find eligible workload then you can download the Oracle Database In-Memory Advisor to find out more information.

Here is an example of running the In-Memory Eligibility Test on my Oracle Database after applying the 19.20 RU.

The DBMS_INMEMORY_ADVISE package has one procedure called IS_INMEMORY_ELIGIBLE that has been overloaded with different parameters:

SQL> desc dbms_inmemory_advise
PROCEDURE IS_INMEMORY_ELIGIBLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 START_SNAP_ID                  NUMBER                  IN
 END_SNAP_ID                    NUMBER                  IN
 INMEM_ELIGIBLE                 BOOLEAN                 OUT
 ANALYSIS_SUMMARY               VARCHAR2                OUT
 SNAP_DBID                      NUMBER                  IN     DEFAULT
PROCEDURE IS_INMEMORY_ELIGIBLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 START_TIME                     TIMESTAMP               IN
 END_TIME                       TIMESTAMP               IN
 INMEM_ELIGIBLE                 BOOLEAN                 OUT
 ANALYSIS_SUMMARY               VARCHAR2                OUT
 SNAP_DBID                      NUMBER                  IN     DEFAULT
PROCEDURE IS_INMEMORY_ELIGIBLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TIME_WINDOW_DAYS               NUMBER                  IN
 INMEM_ELIGIBLE                 BOOLEAN                 OUT
 ANALYSIS_SUMMARY               VARCHAR2                OUT
 SNAP_DBID                      NUMBER                  IN     DEFAULT

SQL>

The next example shows running the IS_INMEMORY_ELIGIBLE procedure for an AWR snapshot range and finding eligible workload:

SQL> set serveroutput on;
SQL> 
SQL> variable analysis_summary VARCHAR2(1000)
SQL> 
SQL> declare
  2    v_inmem_eligible boolean;
  3  begin
  4    dbms_inmemory_advise.is_inmemory_eligible(74, 76, v_inmem_eligible, :analysis_summary);
  5    --
  6    if v_inmem_eligible then
  7  	 dbms_output.put_line('Database is eligible');
  8    else
  9  	 dbms_output.put_line('Database is NOT eligible');
 10    end if;
 11  end;
 12  /
Database is eligible

PL/SQL procedure successfully completed.

SQL> 
SQL> print analysis_summary

ANALYSIS_SUMMARY
--------------------------------------------------------------------------------
Observed Analytic Workload Percentage is 31.67% is greater than target Analytic
Workload Percentage 20%


SQL>

In this example it would be warranted to run the Oracle Database In-Memory Advisor to get more information about the analytic workload.

>> Note: The following section has been revised to try and make the Not Eligible information clearer.

This final example will show a snapshot range that does not have analytic workload:

SQL> set serveroutput on;
SQL> 
SQL> variable analysis_summary VARCHAR2(1000)
SQL> 
SQL> declare
  2    v_inmem_eligible boolean;
  3  begin
  4    dbms_inmemory_advise.is_inmemory_eligible(70, 71, v_inmem_eligible, :analysis_summary);
  5    --
  6    if v_inmem_eligible then
  7  	 dbms_output.put_line('Database is eligible');
  8    else
  9  	 dbms_output.put_line('Database is NOT eligible');
 10    end if;
 11  end;
 12  /
Database is NOT eligible

PL/SQL procedure successfully completed.

SQL> 
SQL> print analysis_summary

ANALYSIS_SUMMARY
-------------------------------------------------------------------------------------------------
Observed Analytic Workload Percentage is 10% is less than target Analytic Workload Percentage 20%

SQL>

In this case we see that the database is not eligible because the analytic workload for the specified AWR snapshot period is only 10%. The IS_INMEMORY_ELIGIBLE procedure considers analytic workload less that 20% as not eligible for Database In-Memory.

In rare cases you may see the following message:

Snapshot range cannot be analyzed as it has insufficient activity.

In this case we see a message about insufficient activity. That means the database was idle or had very little activity.

As you can see, the new In-Memory Eligibility test makes it very simple to determine whether there is a significant amount of analytic workload. Imagine having a large number of databases. With the In-Memory Eligibility Test you do not have to download and install additional software to determine which databases have analytic workload. For the ones that do, you can then consider running the Oracle Database In-Memory Advisor.