Many workloads benefit from Database In-Memory, however some may not. IS_INMEMEMORY_ELIGIBLE determines whether or not a workload would benefit from Database In-Memory and should be considered eligible or ineligible. Eligibility is gauged by the percentage of analytical activity in the workload. If you are planning to implement Database In-Memory, you can use this tool to quickly identify and filter out databases that are ineligible; those where analytic activity is low and where you would see no substantive gain from the use of Database In-Memory. You can then focus your Database In-Memory deployment on databases whose workload includes more analytic activity and could therefore benefit substantially.

Criterion Used by IS_INMEMEMORY_ELIGIBLE

The determination that a workflow is eligible or ineligible for Database In-Memory is based on a system-defined threshold. If the percentage of the workload involved analytic activity is below that threshold, then Database In-Memory will not noticeably improve performance. The greater the level of activity above that target analytic workload percentage threshold, the greater is the potential benefit that may be seen from use of Database In-Memory.

IS_INMEMORY_ELIGIBLE Procedure Syntax

IS_INMEMORY_ELIGIBLE has three overloads that let you choose how to define the scope of the eligibility test. The output parameters are the same in all three. The optional input parameter SNAP_DBID is also common.

The input parameters are mutually exclusive to each overload as shown in the syntax below. You cannot combine them in a single procedure call.

Syntax 1: Input as Most Recent n Days, Including Today

DBMS_INMEMORY_ADVISE.IS_INMEMORY_ELIGIBLE (
    TIME_WINDOW_DAYS   IN NUMBER,
    INMEM_ELIGIBLE     OUT BOOLEAN,
    ANALYSIS_SUMMARY   OUT VARCHAR2,  
    SNAP_DBID          IN NUMBER DEFAULT NULL
);

Syntax 2: Input as a Snapshot Range

DBMS_INMEMORY_ADVISE.IS_INMEMORY_ELIGIBLE (
    START_SNAP_ID     IN NUMBER,
    END_SNAP_ID       IN NUMBER,
    INMEM_ELIGIBLE    OUT BOOLEAN,
    ANALYSIS_SUMMARY  OUT VARCHAR2,
    SNAP_DBID         IN NUMBER DEFAULT NULL
);

Syntax 3: Input as Time Interval (Start Time and End Time)

DBMS_INMEMORY_ADVISE.IS_INMEMORY_ELIGIBLE (   
   START_TIME        IN TIMESTAMP,
   END_TIME          IN TIMESTAMP,
   INMEM_ELIGIBLE    OUT BOOLEAN,
   ANALYSIS_SUMMARY  OUT VARCHAR2,
   SNAP_DBID         IN NUMBER DEFAULT NULL
);

Parameters

Usage Notes

Within a given workload, the level of analytical activity may vary over a series of snapshots. Depending on which subset of all available snapshots are selected for testing, the analytic workload percentage calculated by IS_INMEMORY_ELIGIBLE may correspondingly vary. So you may want to do several evaluations with different input parameters. Using the TIME_WINDOW_DAYS parameter gives you a quick summary of the workload based on that time window. Then you can drill down into specific time periods, using the parameters based on snapshot IDs to get a perspective on patterns in the workload.

Examples

Example 1: Testing for eligibility within the time window of the last n days as input.

 set serverout on;
 set lines 200;
 
 DECLARE
     inmem_eligible BOOLEAN;
     analysis_summary VARCHAR2(200);
 BEGIN
     
     dbms_inmemory_advise.is_inmemory_eligible(1, inmem_eligible, analysis_summary);
     
     DBMS_OUTPUT.PUT_LINE(
             CASE WHEN inmem_eligible = TRUE
                 THEN 'Eligibile for In Memory'
                 ELSE 'Not Eligible for In Memory'
             END 
     );
     
     DBMS_OUTPUT.PUT_LINE(analysis_summary);
 
 END;
 /
 

Example 2: Testing for eligibility with a range of snapshot IDs as input.

 set serverout on;
 set lines 200;
 DECLARE
     inmem_eligible BOOLEAN;
     analysis_summary VARCHAR2(200);
 BEGIN
     
     dbms_inmemory_advise.is_inmemory_eligible(355, 356, inmem_eligible, analysis_summary);
     
     DBMS_OUTPUT.PUT_LINE(
             CASE WHEN inmem_eligible = TRUE
                 THEN 'Eligibile for In Memory'
                 ELSE 'Not Eligible for In Memory'
             END 
     );
     
     DBMS_OUTPUT.PUT_LINE(analysis_summary);
 
 END;
 /

Example 3: Testing for eligibility with a time interval as input. All snapshots beginning and ending within the time interval are included.

 set serverout on;
 set lines 200;
 DECLARE
     inmem_eligible BOOLEAN;
     analysis_summary VARCHAR2(120);
 BEGIN
     
     dbms_inmemory_advise.is_inmemory_eligible(TO_TIMESTAMP('21-JUN-23 05.22.27.262 PM', 'DD-Mon-RR HH:MI:SS.FF AM'), 
                                                TO_TIMESTAMP('21-JUN-23 05.26.04.446 PM', 'DD-Mon-RR HH:MI:SS.FF AM'), 
                                                inmem_eligible, analysis_summary
     );  
 
     DBMS_OUTPUT.PUT_LINE(
             CASE WHEN inmem_eligible = TRUE
                 THEN 'Eligibile for In Memory'
                 ELSE 'Not Eligible for In Memory'
             END 
     );
 
     DBMS_OUTPUT.PUT_LINE(analysis_summary);
 
 END;
 /