Skip to content

Oracle Database In-Memory Advisor

    Oracle Database In-Memory Advisor is designed to optimize analytical processing in the database. The In-Memory Advisor analyzes the analytical processing workload present in your database to determine an estimated benefit for the database as a whole if that analytical workload is optimized.

    The Oracle Database In-Memory Advisor differentiates analytics processing from other database activity based upon SQL plan cardinality, Active Session History (ASH), use of parallel query, and other statistics.

    The In-Memory Advisor estimates the In-Memory size of objects based upon statistics and heuristic compression factors.

    The In-Memory Advisor estimates analytic processing performance improvement factors based upon the following:

    • Elimination of user I/O waits, cluster transfer waits, buffer cache latch waits, etc.
    • Certain query processing advantages related to specific compression types.
    • Decompression cost heuristics per specific compression types.
    • SQL plan selectivity, number of columns in the result set, etc.

    The Advisor produces a recommendation report. The report lists a number of In-Memory sizes with estimated performance benefits, lists the objects which should be placed in the In-Memory column store for a given In-Memory size and the recommended compression factor for those objects.  It also produces a SQLPLUS script to modify the objects to place them In-Memory. 

    1) Start SQL*Plus.

    2) Create a temporary directory:

    CREATE DIRECTORY IM_ADVISORDIR AS '/tmp';

    3) Run the AWR extraction script:

    @?/rdbms/admin/awrextr

    4) Enter the DBID in the “Enter value for dbid:” prompt.

    5) Enter the number of days in the “Enter value for num_days:” prompt.

    6) Enter the AWR snapshots IDs in the “Enter value for begin_snap:” and “Enter value for “end_snap:” prompts.

    7) Enter “IM_ADVISORDIR” in the “Enter value for directory_name:” prompt.

    8) Press the Enter key in the “Enter value for the file_name:” prompt.

    9) Run the AWR augment export script:

    @imadvisor_awr_augment_export

    10) Press the Enter key in the “Enter value for sqlset_owner:” and “Enter value for sqlset_name:” prompts.

    11) Enter “IM_ADVISORDIR” in the “Please enter the Oracle directory object to use for export (default=DATA_PUMPDIR)?” prompt.

    12) Press the Enter key in the “Please press <return> to use this name prefix; otherwise, enter an alternative name prefix?” prompt.

    13) Copy the generated files to the /tmp folder of the target host.

    14) Optionally, remove the temporary directory:

    DROP DIRECTORY IM_ADVISORDIR;

    Target Database

    1) Start SQL*Plus.

    2) Run the advisor installation script:

    @instimadv

    3) Press the Enter key in the “Enter value for permanent_tablespace:” and “Enter value for temporary_tablespace:” prompts.

    4) Restart SQL*Plus.

    5) Create a temporary directory:

    CREATE DIRECTORY IM_ADVISORDIR AS '/tmp';

    6) Run the AWR load script:

    @?/rdbms/admin/awrload

    7) Enter “IM_ADVISORDIR” in the “Enter value for directory_name:” prompt.

    8) Enter the dump file name in the “Enter value for file_name:”.

    9) Press the Enter key in the “Enter value for schema_name:”, “Enter value for default_tablespace:” and “Enter value for temporary_tablespace:” prompts.

    10) Run the AWR augment import script:

    @imadvisor_awr_augment_import

    11) Enter “IM_ADVISORDIR” in the “Please enter the Oracle directory object to use for import (default=DATA_PUMPDIR)?” prompt.

    12) Press the Enter key in the “The default IM Advisor AWR augment dump file name prefix is imadvisor_awr_augment. Please press to use this name prefix; otherwise, please enter an alternative name prefix?” prompt.

    13) Run the advisor:

    @imadvisor_recommendations

    14) Press the Enter key in the “Enter value for task_name:” prompt.

    15) Enter “YES” in the “Enter value for run_against_augmented_awr:” prompt.

    16) Enter the DBID of the source database in the “Enter value for dbid:” prompt.

    17) Press the Enter key in the “Enter value for inmemory_size:”, “Enter value for begin_time:” and “Enter value for duration:” prompts.

    18) Optionally, remove the temporary directory:

    DROP DIRECTORY IM_ADVISORDIR;

    19) Optionally, uninstall the advisor:

    @catnoimadv

    Also See:

    Oracle Database In-Memory

    Oracle ORAchk