Many Automatic In-Memory (AIM) improvements assess workload trends and then automatically activate or deactivate Database In-Memory functionalities to speed up column-store queries. Leveraging Join Groups, cached hashed dictionaries, In-Memory Optimized Arithmetic, automatic sizing, DATE component extraction, RAC-level global dictionaries, selective column controls, and vectorized multi-level joins and aggregations, which you can apply either selectively or globally, improves query performance and decreases memory usage without manual adjustments.
Key Takeaways:
- Automatic In‑Memory (AIM) uses workload analysis to enable or disable In‑Memory features (Join Groups, hashed dictionary caching for join keys, In‑Memory Optimized Arithmetic) selectively or globally, improving application performance while conserving column‑store space without manual intervention.
- Automatic In‑Memory sizing dynamically grows and shrinks the In‑Memory column store, eliminating manual resizing, enabling In‑Memory on Autonomous AI Database, and improving Exadata scan performance for partially populated objects.
- Engine‑level enhancements-vectorised multi‑level joins and aggregations, In‑Memory DATE extraction, RAC‑level global dictionaries, and Selective In‑Memory columns-boost join/aggregation and distributed hash‑join performance and simplify configuration with no SQL changes.
Automatic In-Memory Overview
AIM’s advanced workload analysis provides benefits by automatically managing Database In-Memory features like Join Groups, cached hashed dictionary values for join keys, and In-Memory Optimized Arithmetic. This allows for selective or global activation of these features to improve query performance and save column-store space.
Key features and capabilities
AIM will reliably choose the most suitable features for your particular workload. These features include global or selective enablement, automatic sizing for ADB, global dictionaries at the RAC level, the capability to control columns selectively using an ALL sub-clause, population of DATE components, and sophisticated vectorized multi-level joins and aggregations that bypass the requirement for SQL modifications.
- Join Groups: groups joined columns to share dictionaries, reducing redundant storage and accelerating hash joins.
- Cached hashed dictionary values for join keys: stores pre-hashed join values to cut hash-lookup overhead during joins.
- In-Memory Optimized Arithmetic: accelerates CPU-bound arithmetic operations inside the IM column store for faster aggregates and expressions.
- Automatic In-Memory sizing: the column store automatically grows and shrinks with workload, enabling use on Autonomous AI Database and removing manual resize tasks.
- In-Memory Optimized Dates: extracts DAY, MONTH, YEAR components into IM columns to speed date-based analytics.
- RAC-level global dictionary: synchronizes common dictionaries across RAC nodes to improve distributed hash join performance.
- Selective In-Memory columns with ALL sub-clause: lets you enable or exclude all columns without long include/exclude lists, reducing configuration errors.
- Vectorized multi-level joins and aggregations: full deep-vectorization using SIMD, multi-join-key support, and multi-level hash joins for faster analytic patterns with no application changes.
Once AIM has examined runtime patterns, it strategically selects the most efficient subset of features that optimizes performance relative to space for your specific workload.
Performance benefits and trade-offs
You’ll notice quicker joins and aggregations, a smaller IM footprint, and less administrative overhead because AIM automatically configures features and scales.
Practically speaking, AIM streamlines the tuning process by implementing join groups for recurring join patterns, activating hashed-dictionary caching for join keys with high cardinality, and extracting three DATE components to eliminate redundant date calculations. Even your most time-consuming analytic queries can often see performance improvements with no modifications to the SQL code. Monitor CPU and IM-population churn in workloads that experience quick changes in schema or access patterns, as frequent enable/disable cycles can temporarily raise the load while AIM adjusts to the stable configuration that optimizes performance per byte.
Automatic In-Memory Sizing
Automatic In-Memory Sizing dynamically adjusts the IM column store’s size to match current workload demands, eliminating the need for manual resizing during peak or slow periods. This allows for instant messaging on the Autonomous AI Database, boosts Exadata’s scan efficiency with partially filled objects, and saves storage by strategically filling Join Groups, hashed dictionary caches, and In-Memory Optimized Arithmetic only when these features offer a noticeable advantage.
Dynamic growth and shrink behavior
Continuous adjustments are made: AIM keeps hot segments and join-key dictionaries in memory by expanding the column store, and then frees up space by retracting when activity decreases. For example, if your nightly ETL touches 50 million rows, AIM will prioritize populating Join Groups and hashed dictionary caches for the affected columns.
Implications for Autonomous AI Databases
AIM’s sizing feature allows you to activate In-Memory on Autonomous AI Database without manual adjustments. The service will automatically allocate the space for IM-optimized DATE columns, vectorized joins, and IM Optimized Arithmetic. This results in faster analytics for your applications while simultaneously reducing memory usage.
Operationally, the Autonomous AI Database offers benefits by now being able to apply AIM policies that selectively activate Join Groups, in-memory hashed dictionary caches for join keys, and In-Memory Optimized Dates, only when query patterns show substantial join or date-component utilization. Partially filled objects on Exadata benefit from quicker scans because of the retention of frequently accessed columns. This results in reduced query response times and less administrative overhead, all while keeping memory usage down.
Adaptive Feature Management
AIM now guides feature selection with an improved workload-analysis algorithm, allowing it to activate or deactivate Join Groups, implement hashed-dictionary caching for join keys, and utilize In-Memory Optimized Arithmetic either selectively or globally. This approach aims to maximize benefits while preserving space in the in-memory column store.
Automatic enablement of Join Groups and dictionaries
When AIM identifies recurring join patterns, it establishes Join Groups. The groups share a unified dictionary, and the system caches the hashed values of dictionary entries for join key columns.
Selective In-Memory columns and In-Memory optimized dates
Previously, enabling or disabling in-memory columns required listing them individually. You can now manage all columns at once using an ALL sub-clause, eliminating the need for extensive include/exclude lists. Extracting DAY, MONTH, and YEAR from In-Memory Optimized Dates now uses the In-Memory Expressions framework. By transferring this data to the IM store, date-centric queries benefit from sped up aggregations, filtering, and range scans.
For instance, you could select just the 6-10 key analytical columns from a broad 200-column sales table by employing ALL/EXCLUDE rules, reducing the in-memory footprint and minimizing errors.
RAC and Global Dictionary Enhancements
Automatic In-Memory now synchronizes Join Groups and global dictionaries across RAC nodes for a unified dictionary of joined columns. By allowing AIM to enable features selectively or for entire clusters, you save space in your In-Memory column store, eliminate the need for manual tuning, and boost the performance of distributed hash joins on your RAC instances.
RAC-level global dictionary synchronization
When your cluster is part of a Join Group, the global dictionary is synchronized across all RAC instances, ensuring that each node utilizes identical encoding and hashed values for join keys. In a four-node Real Application Clusters (RAC) setup, for instance, you can remove approximately three identical dictionaries. This action diminishes memory duplication and decreases the metadata traffic between nodes when performing joins.
Impact on distributed joins and scalability
Dictionaries and hashed key caches remain the same across different nodes, which means your distributed hash joins can bypass expensive re-partitioning and additional network shuffling. This leads to reduced latency and improved CPU efficiency as your cluster expands. AIM allows for global application with heavily joined schemas or selective use for hot tables, enabling you to scale joins across more nodes without a proportional increase in memory usage or network traffic.
Moreover, synchronized dictionaries generate the same hash codes on different nodes. This means that multi-level and multi-key joins, when integrated with vectorized deep SIMD processing, result in reduced data movement and fewer remote lookups. Consequently, you gain from decreased memory usage per dictionary (approximately (N-1)/N savings in an N-node cluster) and quicker distributed aggregations and joins. This is particularly true for wide analytics workloads that AIM identifies and optimizes on its own.
Vectorized Query Processing Improvements
You’ll now automatically benefit from multi-level hash joins, SIMD-optimized code, and In-Memory aggregation, all without modifying your SQL queries.
Multi-level hash joins and SIMD utilization
Multi-level hash joins arrange hash tables into L1/L2-friendly buckets, which reduces cache misses during probes. Additionally, SIMD lanes can examine multiple keys with a single instruction—eight 32-bit lanes on AVX2 and sixteen on AVX-512—allowing for parallel processing of tuple batches.
In-Memory group-by aggregation support
Your group-by operations are executed entirely in memory with vectorized aggregation. This involves partial, per-thread aggregation within SIMD-friendly buffers, native handling of multi-column keys, and automatic AIM activation for optimal performance, resulting in reduced analytics latency without the need for manual adjustments.
Specifically, aggregation employs a two-stage approach: SIMD-enhanced local aggregation writes to hash buffers sized for L1/L2 caches, thereby minimizing random memory access. Subsequently, parallel merges combine these into a unified global IM hash table.
Implementation and Best Practices
Once AIM is activated, compare its workload analysis to AWR/ASH baselines. Prioritize implementing features that will have the greatest impact, such as enabling Join Groups for large joins, utilizing hashed-dictionary caching for join keys with low cardinality, and employing In-Memory Optimized Dates for analytics that heavily involve dates. Execute controlled tests and anticipate measurable improvements (e.g., 20-50% faster analytic scans in comparable benchmarks), after which AIM can selectively activate features to preserve IMCS space. Establish initial query latency and IM hit ratio benchmarks to measure ROI during the rollout.
Monitoring, space management, and tuning
Instrument IMCS with the following metrics: IM usage, evictions per second, dictionary cache hit ratio, and the percentage of objects populated. Monitor global-dictionary sync latency and per-node evictions in RAC. Adjust AIM thresholds, disable features with minimal benefits, or optimize by pinning specific columns.
Deployment considerations and rollback strategies
Implement AIM incrementally by beginning with 10-25% of schemas or non-critical tenants. Validate the performance over 24-72 hour periods before expanding. To roll back, follow these documented steps: first, disable AIM on the relevant objects. Second, evict the IMCS populations. Finally, restore any saved manual INMEMORY directives. Utilize AWR baselines to verify that performance metrics revert to their state before the deployment and maintain a recovery period (usually 48-72 hours) prior to implementing broader modifications.
Before activating AIM, it is operationally necessary to obtain a comprehensive baseline, including AWR reports, execution plans, and IM population maps. When reverting, switch off AIM at the specific instance or object, send targeted evictions to clear IMCS, and then reapply the previous INMEMORY configurations. Re-execute your standard queries and track AWR changes. If issues continue after your recovery period, consider restoring from a previous backup or snapshot.
Conclusion
Automatic in-memory enhancements now allow users to seamlessly activate, size, and prune Database In-Memory features. Workload analysis automates the management of these features, such as Join Groups, cached hashed dictionaries, and in-memory optimized arithmetic and DATE expressions.
Automatic In-Memory enhancements automatically activate, adjust, and manage Database In-Memory capabilities, including Join Groups, cached hashed dictionaries, and In-Memory Optimized Arithmetic and DATE expressions, using workload analysis. These functions allow for faster joins, aggregations, and date inquiries.
They also enable RAC-level global dictionaries and customizable column selections, all while dynamically modifying the In-Memory column store. This dynamic adjustment lessens manual tuning and enhances analytic performance without needing code changes.
