Oracle 19c’s In-Memory Features bring groundbreaking advancements to database management, allowing for real-time analytics and significantly enhancing query performance. By leveraging In-Memory Optimization, Oracle provides a robust solution for data-intensive applications. The technology’s dual-format architecture, which stores data in both row and column formats, enables efficient processing for a variety of workloads, from OLTP to OLAP. This article delves into the intricate details of these features, exploring their configuration, benefits, best practices, and advanced functionalities.
Understanding In-Memory Features
Oracle’s In-Memory Features enable data to be stored in a highly compressed columnar format, significantly accelerating analytical queries. This format is ideal for operations like aggregation, filtering, and reporting, which require quick access to large datasets. The In-Memory Optimization feature ensures that only the most critical data resides in memory, optimizing resource usage and improving overall system performance.
In a traditional database system, the system stores data in row format, which efficiently handles transactional workloads but is less effective for analytical queries. Oracle’s Database In-Memory feature tackles this issue by offering a columnar format, drastically reducing the amount of data that must be read and processed, thereby speeding up queries. This dual-format architecture allows Oracle databases to support both real-time analytics and high-performance transactional processing simultaneously.
Configuring In-Memory Features
Configuring In-Memory Features in Oracle 19c involves several key steps. The primary configuration parameter is INMEMORY_SIZE, which determines the amount of memory allocated to the In-Memory Column Store (IMCS). For example:
ALTER SYSTEM SET INMEMORY_SIZE = 110G SCOPE=BOTH SID='*';
This command allocates 110GB to the IMCS for storing data in a columnar format. After setting this parameter, restart the database. You can then enable tables, partitions, or even specific columns for In-Memory storage:
ALTER TABLE sales INMEMORY;
ALTER TABLE large_tab (c1 NUMBER, c2 VARCHAR2(20)) INMEMORY MEMCOMPRESS FOR QUERY LOW;
ALTER TABLE large_tab NO INMEMORY;
The first command stores the sales table in the IMCS, while the second specifies a low level of compression for the large_tab table. The third command reverts the table to row format, removing it from the IMCS.
Advanced In-Memory Features
Oracle 19c includes several advanced features that enhance the functionality of In-Memory storage. One of these is In-Memory Virtual Columns, which allows for the storage of computed values, reducing the need for real-time calculations. For instance:
ALTER TABLE sales ADD (revenue AS (price * quantity)) INMEMORY;
This command creates a virtual column revenue, calculated as price * quantity, stored in the IMCS. This setup speeds up queries that involve revenue calculations by avoiding on-the-fly computations.
Another advanced feature is In-Memory Compression. Oracle offers various levels of compression, such as MEMCOMPRESS FOR QUERY and MEMCOMPRESS FOR CAPACITY, which balance the trade-off between performance and storage efficiency. For example:
ALTER TABLE t1 INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;
This command applies a high level of compression to the t1 table, optimizing memory usage while maintaining query performance.
Database In-Memory Considerations
Oracle Database In-Memory is an option available in the Enterprise Edition, providing several unique features:
- Real-Time Performance: Enables running queries at the rate of billions of rows per second for each CPU core.
- Mixed Workload OLTP: Eliminates the need for analytic indexes, reducing transactional overhead.
- Unlimited Scale: Users can populate only performance-sensitive tables or partitions into memory.
- Trivial Adoption: Compatible with any application or tool that runs against the Oracle Database.
- 100% Compatibility: Supports key Oracle DB features like Maximum Availability and Multitenant architectures.
To store data in the new columnar format, the system creates a new memory pool called the IM Column Store. It converts segments in this pool to a columnar format, significantly speeding up queries. For example, data retrieval from the IM Column Store is at least 100 times faster than fetching from the Buffer Cache due to the compressed columnar format. Data in the IM Column Store can be compressed with a ratio ranging from 2x to 10x, offering substantial storage savings.
Benefits of In-Memory Features
In-Memory Features provide numerous benefits, making them indispensable for modern data-driven enterprises:
- Very Hot Data in In-Memory Column Store: Ensures that the most frequently accessed data is always available in memory, optimizing performance.
- Full RAC and Multitenant Support: Fully compatible with Oracle’s Real Application Clusters (RAC) and Multitenant architectures, ensuring high availability and scalability.
- No SQL Tuning Required: The columnar format and advanced compression techniques reduce the need for manual SQL tuning.
- No Restrictions on SQL: Supports all SQL operations, ensuring full functionality.
- Unchanged Backup and Recovery: The introduction of In-Memory Features does not affect existing backup and recovery processes.
- No Data Migration: Data can be seamlessly moved into the IM Column Store without requiring migration.
Deploying In-Memory Column Store
Deploying the IM Column Store involves a series of steps, starting with verifying the database compatibility, setting the IMCS size, and enabling specific objects for In-Memory storage Optimization. The configuration can be as simple as:
CREATE TABLE large_tab (c1 NUMBER, c2 VARCHAR2(20)) INMEMORY;
ALTER TABLE sales NO INMEMORY;
For more granular control, individual partitions or columns can be set for In-Memory storage, optimizing memory usage. For example:
CREATE TABLE countries_part (country_id NUMBER, country_name VARCHAR2(50))
PARTITION BY LIST (country_name)
(PARTITION p1 VALUES ('USA') INMEMORY, PARTITION p2 VALUES ('CANADA') NO INMEMORY);
This command specifies that only the USA partition should be stored in the IMCS, while the CANADA partition remains in row format.
In-Memory Advisor and Compression Estimation
The In-Memory Advisor tool assists in optimizing the IMCS by analyzing workload and recommending appropriate sizing, object selection, and compression levels. For example, after installing the In-Memory Advisor using the instimadv.sql script, administrators can run:
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO(
'TS_DATA', 'SSB', 'LINEORDER', NULL,
DBMS_COMPRESSION.COMP_INMEMORY_QUERY_LOW,
blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str,10000,1);
DBMS_OUTPUT.PUTLINE('Compression ratio = ' || cmp_ratio);
END;
/
This script provides an estimation of the compression ratio, allowing for informed decisions regarding data storage.
Automatic In-Memory Management for better Optimization
Oracle 18c introduced Automatic In-Memory (AIM), a feature that automates the management of the IMCS. AIM uses heat map statistics to determine the “working data set,” ensuring that frequently accessed data is kept in memory. To enable AIM, set the heat_map and INMEMORY_AUTOMATIC_LEVEL parameters:
ALTER SYSTEM SET heat_map=ON;
ALTER SYSTEM SET INMEMORY_AUTOMATIC_LEVEL = MEDIUM SCOPE=BOTH SID='*';
AIM automates the eviction of inactive segments, making room for more critical data. It operates based on the amount of time segments have been inactive, enhancing the efficiency of the IMCS.
Query Performance and Optimization
The In-Memory Column Store significantly improves query performance, particularly for large data scans, selective filtering, and complex joins. For example, to measure the performance improvement, one can execute a query with and without In-Memory Optimization:
SET timing ON;
SELECT max(lo_ordtotalprice) most_expensive_order FROM LINEORDER;
ALTER SESSION SET INMEMORY_QUERY="DISABLE";
SELECT max(lo_ordtotalprice) most_expensive_order FROM LINEORDER;
The execution plan for In-Memory queries typically includes the “TABLE ACCESS IN MEMORY FULL” operation, indicating the use of the IMCS.
Best Practices and Considerations
When implementing In-Memory Features, it is crucial to follow best practices. Selective population of data, regular monitoring using views like V$IM_SEGMENTS and DBA_SEGMENTS, and understanding the limitations and capabilities of the features are essential. For example, data compression can save significant storage space but may impact performance if overused. Utilizing tools like the In-Memory Advisor and AIM can help manage and optimize the IMCS effectively.
Conclusion
Oracle 19c’s In-Memory Features offer unprecedented capabilities for real-time analytics and efficient data processing. These features enable organizations to achieve faster query performance, gain immediate insights, and make data-driven decisions in real-time. By leveraging advanced technologies such as In-Memory Compression, Virtual Columns, and AIM, businesses can optimize their database environments, ensuring high performance and resource efficiency. As data volumes continue to grow, Oracle’s In-Memory Features will be crucial in maintaining a competitive edge and maximizing the value of data assets.
See more on Oracle’s website!
Be Oracle Performance Management and Tuning Certified Professional, this world is full of opportunities for qualified DBAs!
