In-Memory Advisor in Oracle 23ai

The In-Memory Advisor in Oracle 23ai analyzes database workloads to estimate DB-Time for various In-Memory sizes. Additionally, it can recommend which objects should be placed in the In-Memory column store.

To use this feature, you must leverage the DBMS_INMEMORY_ADVISE package. The procedure for running the In-Memory Advisor is similar to the Privilege Analysis feature. To generate advice regarding In-Memory optimization, you first need to start a task and let it analyze the database workload. Once sufficient analysis has been performed, you can stop the task and review the generated report.

The following sections detail the steps to generate the report:

Step 1: Enable Heat Map
To use the DBMS_INMEMORY_ADVISE package, you must first enable the Heat Map feature:

SQL> exec dbms_inmemory_advise.start_tracking(:task_id);  
ORA-20000: Heat map is not enabled.  
ORA-06512: at "SYS.PRVT_DBMS_INMEMORY_ADVISOR", line 1578  
ORA-06512: at "SYS.PRVT_DBMS_INMEMORY_ADVISOR", line 1667  
ORA-06512: at "SYS.DBMS_INMEMORY_ADVISE", line 191  
ORA-06512: at line 1  
Help: https://docs.oracle.com/error-help/db/ora-20000/  

SQL> ALTER SYSTEM SET HEAT_MAP = ON;  
System altered.  

Step 2: Start Tracking
After enabling Heat Map, initiate a task using the DBMS_INMEMORY_ADVISE.START_TRACKING procedure:

PROCEDURE START_TRACKING  
 Argument Name                  Type                    In/Out Default?  
 ------------------------------ ----------------------- ------ --------  
 TASK_ID                        NUMBER                  OUT  

SQL> variable task_id NUMBER;  
SQL> exec dbms_inmemory_advise.start_tracking(:task_id);  
PL/SQL procedure successfully completed.  

SQL> print task_id  
   TASK_ID  
----------  
         1

Step 3: Simulate Workload
For testing purposes, simulate a workload in the database:

SQL> declare  
a number;  
b number;  
begin  
for i in 1..100000 loop  
select sum(LINE) into a from tbl1;  
end loop;  
end;  
/  

Step 4: Stop the Task
After the workload analysis, stop the tracking task:

SQL> exec dbms_inmemory_advise.stop_tracking;  
PL/SQL procedure successfully completed.  

Step 5: Generate Advice
Finally, execute the DBMS_INMEMORY_ADVISE.GENERATE_ADVISE procedure to generate recommendations:

SQL> exec dbms_inmemory_advise.generate_advise;  
PL/SQL procedure successfully completed.  

Step 6: View the Recommendations
You can query the DBA_INMEMORY_ADVISOR_RECOMMENDATION view to review the output:

SELECT  
  task_id,  
  INMEMORY_SIZE,  
  ESTIMATED_DB_TIME_LOW,  
  ESTIMATED_DB_TIME_HIGH,  
  ESTIMATED_DB_TIME_ANALYTICS_LOW,  
  ESTIMATED_DB_TIME_ANALYTICS_HIGH,  
  TO_CHAR(RECOMMENDED_OBJ_LIST) RECOMMENDED_OBJ_LIST  
FROM  
  DBA_INMEMORY_ADVISOR_RECOMMENDATION;  

The output reveals the estimated reduction in DB-Time if the TBL1 table is moved to the In-Memory column store. Additionally, it predicts the required In-Memory size. Here is an example of the output:

TASK_ID    1
INMEMORY_SIZE    25231360
ESTIMATED_DB_TIME_LOW    75
ESTIMATED_DB_TIME_HIGH    145
ESTIMATED_DB_TIME_ANALYTICS_LO    69
ESTIMATED_DB_TIME_ANALYTICS_HI    139
RECOMMENDED_OBJ_LIST    Owner: USEF Table: TBL1 ;