Query optimization in Oracle 12c database in-memory

Abstract

Traditional on-disk row major tables have been the dominant storage mechanism in relational databases for decades. Over the last decade, however, with explosive growth in data volume and demand for faster analytics, has come the recognition that a different data representation is needed. There is widespread agreement that in-memory column-oriented databases are best suited to meet the realities of this new world. Oracle 12c Database In-memory, the industry's first dual-format database, allows existing row major on-disk tables to have complementary in-memory columnar representations. The new storage format brings new data processing techniques and query execution algorithms and thus new challenges for the query optimizer. Execution plans that are optimal for one format may be sub-optimal for the other. In this paper, we describe the changes made in the query optimizer to generate execution plans optimized for the specific formatrow major or columnarthat will be scanned during query execution. With enhancements in several areasstatistics, cost model, query transformation, access path and join optimization, parallelism, and cluster-awarenessthe query optimizer plays a significant role in unlocking the full promise and performance of Oracle Database In-Memory. This section provides a brief introduction to Oracle DBIM; more details are in [13] and [18]. Oracle 12c Database In-Memory (DBIM) is a dual-format database where data from a table can reside in both columnar format in an in-memory column store and in row major format on disk. The in-memory columnar format speeds up analytic queries and the row major format is well-suited for answering OLTP queries. Note that scanning on-disk tables does not necessarily mean disk I/O; some or all of the blocks of the table may be cached in the row major buffer cache [4]. A dedicated in-memory column store called the In-Memory Area acts as the storage for columnar data. The in-memory area is a subset of the database shared global area (SGA).

Fault-tolerant real-time analytics with distributed Oracle Database In-memory

“…Once the query is coordinated, the SQL query optimizer [13] calculates the degree of parallelism based on the cost of accessing the in-memory object and ensures that at least one parallel execution scanner process gets allocated on the instances populated with the IMCUs for the in-memory object.…”

Accelerating analytics with dynamic in-memory expressions

“…Highly selective OLTP-style queries (e.g. lookup by primary key) are directed by the Optimizer [7] to use the row format, while analytic queries are directed by the Optimizer to use the column formatregardless of which format is used, the same results are returned by the query.…”

“…The logical expression tree is then processed by the optimizer to generate an execution plan. The optimizer will generate an inmemory execution plan (via the table scan row source) if the cost is less than a non-in-memory plan [7]. The optimizer takes filter and decompression costs into consideration, as well as the percentage of the table being processed in-memoryrecall that with Oracle DBIM, the entire table/segment need not be inmemory [4].…”

ReCache

“…Unlike CPU caches where data is loaded into the cache for free before being used, database caches incur additional overhead reading, parsing and storing intermediate results into an in-memory cache. For the sake of simplicity, however, commercial DBMS and analytics systems use relatively straightforward caching policies [14,33]. First, they cache data in memory using a fixed, pre-defined layout [8,14,33].…”

“…For the sake of simplicity, however, commercial DBMS and analytics systems use relatively straightforward caching policies [14,33]. First, they cache data in memory using a fixed, pre-defined layout [8,14,33]. Moreover, they use relatively naive cache admission and eviction policies.…”