Introduction
About the In-Memory feature
The Oracle Database has a feature known as Database In-Memory, which accelarates analytics workload with an entire table, index, or partition loaded in memory. Starting with 19.8, this feature can be used with Enterprise Edition limited up to 16 GB of memory, which is defined as In-Memory Base Level. To use more than 16 GB or any value in older version, an additional license to use the In-Memory Option is required.
About the In-Memory Advisor
Traditionally Oracle has developed an In-Memory Advisor that can be downloaded from the My Oracle Support (MOS), and installed in the target Database. This old Advisor is the same used by the OEM In-Memory Advisor, and can be installed in any Oracle Database with 11.2.0.3 version or higher, including non-CDB and CDB. Note that the advisor require a Tuning Management pack license.
You can find this old Advisor here.
New with 23ai
Starting with 23ai, a new Advisor was included in the database as a Built-in standard package, so we not need to download and install a separeted package anymore. The new Advisor is implemented in the DBMS_INMEMORY_ADVISE package, which currently have the following procedures and functions:
/* Package SYS.DBMS_INMEMORY_ADVISE */
/* PROCEDURE SYS.DBMS_INMEMORY_ADVISE.IS_INMEMORY_ELIGIBLE */
SYS.DBMS_INMEMORY_ADVISE.IS_INMEMORY_ELIGIBLE( START_SNAP_ID => p_IN_param0 /* NUMBER */,
END_SNAP_ID => p_IN_param1 /* NUMBER */,
INMEM_ELIGIBLE => p_OUT_param2 /* BOOLEAN */,
ANALYSIS_SUMMARY => p_OUT_param3 /* VARCHAR2 */,
SNAP_DBID => p_IN_param4 /* NUMBER */);
/* PROCEDURE SYS.DBMS_INMEMORY_ADVISE.IS_INMEMORY_ELIGIBLE */
SYS.DBMS_INMEMORY_ADVISE.IS_INMEMORY_ELIGIBLE( START_TIME => p_IN_param0 /* TIMESTAMP */,
END_TIME => p_IN_param1 /* TIMESTAMP */,
INMEM_ELIGIBLE => p_OUT_param2 /* BOOLEAN */,
ANALYSIS_SUMMARY => p_OUT_param3 /* VARCHAR2 */,
SNAP_DBID => p_IN_param4 /* NUMBER */);
/* PROCEDURE SYS.DBMS_INMEMORY_ADVISE.IS_INMEMORY_ELIGIBLE */
SYS.DBMS_INMEMORY_ADVISE.IS_INMEMORY_ELIGIBLE( TIME_WINDOW_DAYS => p_IN_param0 /* NUMBER */,
INMEM_ELIGIBLE => p_OUT_param1 /* BOOLEAN */,
ANALYSIS_SUMMARY => p_OUT_param2 /* VARCHAR2 */,
SNAP_DBID => p_IN_param3 /* NUMBER */);
/* PROCEDURE SYS.DBMS_INMEMORY_ADVISE.START_TRACKING */
SYS.DBMS_INMEMORY_ADVISE.START_TRACKING( TASK_ID => p_OUT_param0 /* NUMBER */);
/* PROCEDURE SYS.DBMS_INMEMORY_ADVISE.STOP_TRACKING */
SYS.DBMS_INMEMORY_ADVISE.STOP_TRACKING();
/* PROCEDURE SYS.DBMS_INMEMORY_ADVISE.GENERATE_ADVISE */
SYS.DBMS_INMEMORY_ADVISE.GENERATE_ADVISE();
/* FUNCTION SYS.DBMS_INMEMORY_ADVISE.LIST_ADVISE */
/* RETURN PL/SQL RECORD */
v_ret := SYS.DBMS_INMEMORY_ADVISE.LIST_ADVISE( TASK_ID => p_IN_param0 /* NUMBER */,
INMEMORY_SIZE => p_IN_param1 /* NUMBER */);
/* PROCEDURE SYS.DBMS_INMEMORY_ADVISE.DROP_TASK */
SYS.DBMS_INMEMORY_ADVISE.DROP_TASK( TASK_ID => p_IN_param0 /* NUMBER */);
/* PROCEDURE SYS.DBMS_INMEMORY_ADVISE.DROP_OLD_TASKS */
SYS.DBMS_INMEMORY_ADVISE.DROP_OLD_TASKS( DAYS => p_IN_param0 /* NUMBER */);
Also, a new dictionary view DBA_INMEMORY_ADVISOR_RECOMMENDATION was introduced, where we can query the tasks results directly using SQL query:
NAME DATA TYPE NULL DEFAULT COMMENTS TASK_ID NUMBER Yes IM advisor task id INMEMORY_SIZE NUMBER Yes Simulated IM size ESTIMATED_DB_TIME_LOW NUMBER Yes Low end of the predicted DB Time range with IM ESTIMATED_DB_TIME_HIGH NUMBER Yes High end of the predicted DB Time range with IM ESTIMATED_DB_TIME_ANALYTICS_LOW NUMBER Yes Low end of the predicted DB analytical time range with IM ESTIMATED_DB_TIME_ANALYTICS_HIGH NUMBER Yes High end of the predicted DB analytical Time range with IM RECOMMENDED_OBJ_LIST CLOB Yes List of recommended objects for the simulated IM size
Get Started
In-Memory Eligibility Test
Before run the Advisor itself, it is a good idea execute an In-Memory Eligibility Test, which is implemented by the IS_INMEMORY_ELIGIBLE procedure. This step executes in just few seconds, evaluates a past database workload using AWR and ASH, and determines if the database is a candidate to use the In-Memory feature.
The following example executes the test considering the last 4 hours of AWR and ASH history:
set serveroutput on;
declare
inmem_eligible BOOLEAN;
analysis_summary VARCHAR2(4000);
begin
dbms_inmemory_advise.is_inmemory_eligible(sysdate-4/24, sysdate, inmem_eligible, analysis_summary);
DBMS_OUTPUT.PUT_LINE('====================================================================');
dbms_output.put_line('Is In-Memory Elegible..: ' || to_char(inmem_eligible));
dbms_output.put_line('Analysis Summary.......: ' || analysis_summary);
DBMS_OUTPUT.PUT_LINE('====================================================================');
end;
/
Example:
====================================================================
Is In-Memory Elegible..: TRUE
Analysis Summary.......: Observed Analytic Workload Percentage is 97.56% is greater than target Analytic Workload Percentage 20%
====================================================================
PL/SQL procedure successfully completed.The Eligibility Test resulted in TRUE, indicating that this database can be a good candidate to use In-Memory feature.
TIP: This procedure was included in 19c starting with 19.20 RU.
In-Memory Advisor
Different then In-Memory Eligibility Test that only validates if could be benefical to use In-Memory in this database, the In-Memory Advisor itself executes a detailed analysis of the workload, and generates a list of recommended tables and or partitions that should use In-Memory.
1) First, we need to enable the heap_map feature:
alter system set heat_map=on;
2) Now we can create a Advisor task:
set serveroutput on;
declare
taskId number;
begin
DBMS_INMEMORY_ADVISE.START_TRACKING(TASK_ID => taskId);
dbms_output.put_line('Task ID: ' || taskId);
end;
/
The result should be similar to this:
Task ID: 4
PL/SQL procedure successfully completed.Here you should wait some time while the applications use the database, as the Advisor will observe the live workload.
3) After some time, we can stop the workload tracking:
EXEC DBMS_INMEMORY_ADVISE.STOP_TRACKING();
4) So we can generate the recommendations for the last task:
EXEC DBMS_INMEMORY_ADVISE.GENERATE_ADVISE();
5) Finally, we can get the task results using the LIST_ADVISE function.
The usage of this function can be complex for beginners and the example in the official docs look like incomplete at this time.
I invested some time adapting and testing the below script, which now works fine:
- In task_id, provide the number returned in step 1.
- In inmemory_size_mb, provide the size of In-Memory that you intend to configure, or any value that you want to simulate.
In the real world you can need to adjust the script to use GB instead of MB).
/*
Script to print the In-Memory Advisor report using PL/SQL API (23ai+)
Maicon Carneiro (dibiei.blog)
02/04/2025
*/
undef task_id
undef inmemory_size_mb
set serveroutput on;
DECLARE
v_task_id NUMBER := &task_id;
v_inmemory_size NUMBER := (&inmemory_size_mb *1024*1024);
v_ret SYS.DBMS_INMEMORY_ADVISE.INMEMORY_ADVISOR_RECOMMENDATION;
v_ObjectList varchar2(4000);
BEGIN
v_ret := SYS.DBMS_INMEMORY_ADVISE.LIST_ADVISE(TASK_ID => v_task_id, INMEMORY_SIZE => v_inmemory_size );
DBMS_OUTPUT.PUT_LINE('====================================================================');
DBMS_OUTPUT.PUT_LINE('In-Memory Size (MB)...........: ' || round(v_ret.inmemory_size/1024/1024));
DBMS_OUTPUT.PUT_LINE('DB Time Baseline..............: ' || v_ret.db_time_baseline);
DBMS_OUTPUT.PUT_LINE('DB Time Baseline (Analytics)..: ' || v_ret.db_time_baseline_analytics);
DBMS_OUTPUT.PUT_LINE('DB Time High..................: ' || v_ret.db_time_high);
DBMS_OUTPUT.PUT_LINE('DB Time Low...................: ' || v_ret.db_time_low);
DBMS_OUTPUT.PUT_LINE('Analytics Time High...........: ' || v_ret.db_time_analytics_high);
DBMS_OUTPUT.PUT_LINE('Analytics Time Low............: ' || v_ret.db_time_analytics_low);
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('Recommended tables list.......: ');
FOR i IN 1 .. v_ret.recommended_obj_list.COUNT LOOP
v_ObjectList := v_ret.recommended_obj_list(i).Owner || '.' || v_ret.recommended_obj_list(i).Object_Name;
if (v_ret.recommended_obj_list(i).Subobject_Name is not null) THEN
v_ObjectList := v_ObjectList || ' (' || v_ret.recommended_obj_list(i).Subobject_Name || ')';
end if;
DBMS_OUTPUT.PUT_LINE(v_ObjectList);
END LOOP;
DBMS_OUTPUT.PUT_LINE('====================================================================');
END;
/
The result should be similar to this:
Enter value for task_id: 4
Enter value for inmemory_size_mb: 100
====================================================================
In-Memory Size (MB)...........: 100
DB Time Baseline..............: 57
DB Time Baseline (Analytics)..: 57
DB Time High..................: 19
DB Time Low...................: 9
Analytics Time High...........: 19
Analytics Time Low............: 9
Recommended tables list.......:
SH.SALES
SH.TIMES
SH.CUSTOMERS
SH.COUNTRIES
SH.CHANNELS
SH.PRODUCTS
====================================================================
PL/SQL procedure successfully completed.Another option is to query the DBA_INMEMORY_ADVISOR_RECOMMENDATION view:
set sqlformat
set long 99999
SET LINES 400
COL TASK_ID HEADING 'Task|ID' FORMAT 999999
COL INMEMORY_SIZE HEADING 'In-Memory|Size (MB)' FORMAT 999,999,999
COL ESTIMATED_DB_TIME_LOW HEADING 'Low|Estimated|DB Time' FORMAT 999999
COL ESTIMATED_DB_TIME_HIGH HEADING 'High|Estimated|DB Time' FORMAT 999999
COL ESTIMATED_DB_TIME_ANALYTICS_LOW HEADING 'Low|Estimated|DB Time Analytics' FORMAT 999999
COL ESTIMATED_DB_TIME_ANALYTICS_HIGH HEADING 'High|Estimated|DB Time Analytics' FORMAT 999999
COL RECOMMENDED_OBJ_LIST HEADING 'Recommended | Object| List' FORMAT A100
SELECT TASK_ID,
INMEMORY_SIZE/1024/1024 INMEMORY_SIZE,
ESTIMATED_DB_TIME_LOW,
ESTIMATED_DB_TIME_HIGH,
ESTIMATED_DB_TIME_ANALYTICS_LOW,
ESTIMATED_DB_TIME_ANALYTICS_HIGH,
RECOMMENDED_OBJ_LIST
FROM DBA_INMEMORY_ADVISOR_RECOMMENDATION
WHERE TASK_ID = 4;
Which produces the following result:
Low High Low High Recommended
Task In-Memory Estimated Estimated Estimated Estimated Object
ID Size (MB) DB Time DB Time DB Time Analytics DB Time Analytics List
------- ------------ --------- --------- ----------------- ----------------- ----------------------------------------------------------------------------------------------------
4 0 57 57 57 57
4 21 17 25 17 25 Owner: SH Table: SALES ; Owner: SH Table: TIMES ;
4 35 9 19 9 19 Owner: SH Table: SALES ; Owner: SH Table: TIMES ; Owner: SH Table: CUSTOMERS ; Owner: SH Table: COUN
TRIES ; Owner: SH Table: CHANNELS ; Owner: SH Table: PRODUCTS ;
3 rows selected.Alternativelly, I created this version of the query that allow us to filter a specific In-Memory size, and the format of Object List was customized as well:
set sqlformat
set verify off;
set long 99999
SET LINES 400
SET PAGES 100
COL TASK_ID HEADING 'Task|ID' FORMAT 999999
COL INMEMORY_SIZE HEADING 'In-Memory|Size (MB)' FORMAT 999,999,999
COL ESTIMATED_DB_TIME_LOW HEADING 'Low|Estimated|DB Time' FORMAT 999999
COL ESTIMATED_DB_TIME_HIGH HEADING 'High|Estimated|DB Time' FORMAT 999999
COL ESTIMATED_DB_TIME_ANALYTICS_LOW HEADING 'Low|Estimated|DB Time Analytics' FORMAT 999999
COL ESTIMATED_DB_TIME_ANALYTICS_HIGH HEADING 'High|Estimated|DB Time Analytics' FORMAT 999999
COL RECOMMENDED_OBJ_LIST HEADING 'Recommended | Object| List' FORMAT A50
undef inmemory_size_mb;
SELECT TASK_ID,
INMEMORY_SIZE/1024/1024 INMEMORY_SIZE,
ESTIMATED_DB_TIME_LOW,
ESTIMATED_DB_TIME_HIGH,
ESTIMATED_DB_TIME_ANALYTICS_LOW,
ESTIMATED_DB_TIME_ANALYTICS_HIGH,
REPLACE(REPLACE(REPLACE(RECOMMENDED_OBJ_LIST,'Owner: ',''),' Table: ','.'),' ; ',chr(10)) AS RECOMMENDED_OBJ_LIST
FROM DBA_INMEMORY_ADVISOR_RECOMMENDATION a
WHERE TASK_ID = &task_id
AND ( nvl('&&inmemory_size_mb',0) = 0
OR INMEMORY_SIZE =
(SELECT MAX(INMEMORY_SIZE)
FROM DBA_INMEMORY_ADVISOR_RECOMMENDATION b
WHERE a.TASK_ID = b.TASK_ID
AND (INMEMORY_SIZE/1024/1024) <= nvl('&&inmemory_size_mb',0)
)
);
Example for the result for this second query:
Enter value for task_id: 4
Enter value for inmemory_size_mb: 100
Low High Low High Recommended
Task In-Memory Estimated Estimated Estimated Estimated Object
ID Size (MB) DB Time DB Time DB Time Analytics DB Time Analytics List
------- ------------ --------- --------- ----------------- ----------------- --------------------------------------------------
4 35 9 19 9 19 SH.SALES
SH.TIMES
SH.CUSTOMERS
SH.COUNTRIES
SH.CHANNELS
SH.PRODUCTS
1 row selected.Another example where the SALES table was partitioned:
Enter value for task_id: 3
Enter value for inmemory_size_mb: 40
Estimated Estimated Recommended
Task InMemory Estimated Estimated DB Time Analytics DB Time Analytics Object
ID Size DB Time (Low) DB Time (High) (Low) (High) List
------- ------------ ------------- -------------- ------------------ ------------------ ----------------------------------------------------------------------------------------------------
3 37 6 7 6 7 SH.SALES Partition: SYS_P1241
SH.SALES Partition: SYS_P1182
SH.SALES Partition: SYS_P1208
SH.SALES Partition: SYS_P1186
SH.SALES Partition: SYS_P1242
SH.SALES Partition: SYS_P1229
SH.SALES Partition: SYS_P1190Conclusion
In this blog post we demonstrated how to starting to use the In-Memory Advisor available as a new feature in Oracle Database 23ai, which is a great improvement as we don’t need to download and install the Advisor manually. This reduce the complexibility and effort to use this feature, and ensure that the Advisor will be updated automatically as part of the quartely release update.
At this time, I don’t see any option to generate HTML report as we can do in the legacy In-Memory Advisor, but I noticed that the package has been improved in the latest update of Database 23ai FREE instance (23.7.0.25.01), so I believe that we can expect new things in the next updates.
References
https://www.oracle.com/database/technologies/inmemory-advisor.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_INMEMORY_AIMTASKDETAILS.html