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: