Prior to the introduction of In-Memory Advisor, a DBA had to manually identify the tables to be placed in the In-Memory Column Store (IMCS). However, with IMA this manual task has been eliminated.
This Blog introduces In-Memory Advisor (IMA), a new feature of Database 12c (18.104.22.168) and its subsequent benefits. I will also discuss how it works. This feature is available in Oracle Database 22.214.171.124 and higher versions.
Major Benefits of - In-Memory Advisor (IMA):
- Faster analytical queries
- Quicker OLTP transactions
- No application changes
- Less manual work for DBA to optimize the database performance
Prior to the introduction of In-Memory Advisor, a DBA had to manually identify the tables to be placed in the In-Memory Column Store (IMCS). However, with IMA this manual task has been eliminated. IMA analyses the analytical workload of the database and produces a recommendation report (which includes SQL commands to place the tables in IMCS).
IMA also gives specific recommendations on how to configure Oracle Database In-Memory.
The Advisor produces a report identifying the objects that should be placed In-Memory for maximum benefit, along with a SQL*PLUS script which implements those recommendations. The installation zip and installation instructions can be found in My Oracle Support (MOS) Doc ID 1965343.1. Basically scripts are run to install the Advisor at the database level. It can be run on Oracle Database 126.96.36.199 and above. The recommendations can be implemented on Oracle Database 188.8.131.52 or higher.
The DBMS_INMEMORY_ADVISOR package is then used to get advice on analytic workloads that can best benefit from placing in the in-memory option. It does this by analysing Active Session History (ASH) and AWR data. A report is produced, as well as a script file – this contains the SQL that can then be executed on the target databases, in order to place the recommended objects in-memory with the suggested compression types.
How In-Memory Advisor (IMA) works:
IMA uses Oracle In-Memory Dual-Format Architecture for better performance. Let me explain what Dual-Format Architecture is.
Traditionally Oracle Database stores data in row-format, with each transaction stored in the database as a new row, which is ideal for online transaction systems as you can quickly access all columns in a record. A column format database stores each transaction attribute in a separate column structure and is ideal for analytics as you retrieve fewer columns. When it comes to DML Operations (insert, update or delete) a row format is efficient as it updates an entire record in one operation. If you run the same operations against a table with column format, all of the column structure in the table must be changed, which is inefficient in processing row-wise DML.
In Oracle Database 12c, Data can populate in both an In-Memory row format and In-Memory column format – this is called dual-format architecture. The existing buffer cache maintains the row format and a new In-Memory column store (IM column store) maintains column format. The In-Memory column store is a new component of the Oracle Database System Global Area (SGA).
Source url: https://www.doag.org/formes/servlet/DocNavi?action=getFile&did=6770401&key=
Broadly, we can say the idea behind it, especially if you have a table that's really wide and with several hundred million or even billions of rows, is that you can scan that table and filter it extremely quickly. You can do this scan even in a data warehousing environment. Essentially you can join the columns extremely quickly from a fact table to multiple dimension tables in unbelievable speed. The idea is that because it's in a columnar format instead of a row major format, you can access the data much more quickly, especially if you have a very wide table because you can eliminate a lot of the intervening columns.
Oracle Database In-Memory can be used to improve queries on a variety of OLTP and/or data warehouse operations.
- Assistance with In-Memory size selection
- Recommendations for tables, partitions and sub-partitions for a given In-Memory size
- Utilizes workload and performance data to prioritize objects
- Accommodates differences in disk and memory footprint, as well as compression ratios
- 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
- 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
By using the In-Memory option, you can speed up analytical queries, OLTP transactions and optimize the database performance easily with less DBA involvement. From a business point of view, businesses can benefit from better decisions made in real time, improved productivity, increased competitiveness and lowered costs. Click below for any questions on the topic: