Oracle In-Memory Advisor

Oracle In-Memory Advisor

 

Oracle Database In-Memory can be used to improve queries on a variety of OLTP and/or data warehouse operations. The In-Memory Advisor helps to optimize performance with recommendations to run analytics processing faster. It gives customers insight into the sizing of the workload and offers actionable recommendations for running workloads at peak performance. The Advisor analyzes workloads and related objects making specific recommendations to which objects would give you the greatest benefit and performance if they were placed In-Memory.

Key Capabilities:

• Assists with In-Memory size selection
• Recommendations for tables, partitions and sub-partitions for a given In-Memory size
• Uses workload and performance data to prioritize objects
• Takes into account differences in disk and memory footprint, as well as compression ratios

Actionable Recommendations

• Workload based cost/benefit analysis
  • Cost: Offers estimated memory size with various compression options
  • Benefit: Offers estimated database time reduction metrics for workload processing
• In-memory area population plan

Reporting

• Ability to vary In-Memory size to receive specific loading plan
• Generates DDL scripts with all the tables/partitions/sub-partitions recommended
• Top SQL benefits from any given configuration

 MOS Note: https://support.oracle.com/epmos/faces/DocumentDisplay?id=1965343.1

It can be run on Oracle Database 11.2.0.3 and above. And of course the recommendations can be implemented on Oracle Database 12.1.0.2. (and newer). Important to know: The In-Memory Advisor is licensed as part of the Database Tuning Pack

Configuration Of In Memory Advisor

1. Download the advisor zip file from  MOS (1965343.1)

2. Install the Advisory Pack

$ unzip imadvisor.zip
$ sqlplus sys/<password> as sysdba

SQL> @instimadv

3. Running the advisor

$ sqlplus sys/<password> as sysdba
SQL> @imadvisor_analyze_and_report

4. Generate the Output

exec DBMS_INMEMORY_ADVISOR.GENERATE_RECOMMENDATIONS();