Oracle 12c In-Memory Database
- Posted by Gavin Soorma
- On October 2, 2014
- 0 Comments
While data warehouses do have their own place, most OLTP databases today are still used to run analytical and DSS type queries and workloads to support real-time decision-enabling information requirement which the business has. Designing a database to cater for this hybrid mix of OLTP and DSS workloads presents its own challenges to the DBA. For example the DBA needs to create indexes to support those adhoc or analytical queries, but the presence of those indexes slows down the day to day OLTP Insert/Update/Delete statements.
OLTP databases are best served by having data in the row format for optimise DML activity – few rows many columns. A row format allows quick access to all of the columns in a record since all of the data for a given record are kept together either in memory in the database buffer cache or on disk storage.
Reporting and decision-making type queries require just the opposite data model – few columns but which span vast number of rows. A column format is ideal for analytics, as it allows for faster data retrieval when only a few columns are selected but the query accesses a large portion of the data set.
The Database In-Memory feature was introduced in Oracle 12c 12.1.0.2 (June 2014) and provides the ability to easily perform real-time data analysis together with real-time transaction processing without any application change or re-design. A subset of data Data can now be stored in an in-memory column format, optimized for analytical processing.
Oracle Database In-Memory feature provides the best of both worlds by allowing data to be simultaneously populated in both an in-memory row format (the database buffer cache) and a new in memory column format.
An area in the SGA is allocated for the In-Memory column store called In-Memory Area and here the data is stored in column format instead of the traditional row format.
Let us now look at a worked example of the In-Memory feature in action!
I created a SALES2 table based on the SALES table residing the example SH schema. The table was populated with about 11 million rows and there are no indexes on the table.
This is the test query I used:
SELECT CALENDAR_MONTH_DESC, sum(quantity_sold), lag(sum(quantity_sold),1) over (ORDER BY CALENDAR_MONTH_DESC) "Previous Month" FROM SH.sales2, SH.TIMES WHERE SH.times.TIME_ID = SH.sales2.TIME_ID AND SH.times.CALENDAR_YEAR <> '2000' GROUP BY CALENDAR_MONTH_DESC ORDER BY CALENDAR_MONTH_DESC ASC;
We need to first enable the In-Memory memory area and note that this portion of memory is taken from the SGA already allocated to the database.
When we start the database we can see a new In-Memory area of memory displayed.
SQL> alter system set inmemory_size=1024m scope=spfile; System altered. SQL> startup; ORACLE instance started. Total System Global Area 3221225472 bytes Fixed Size 2929552 bytes Variable Size 603982960 bytes Database Buffers 1526726656 bytes Redo Buffers 13844480 bytes In-Memory Area 1073741824 bytes Database mounted. Database opened.
We then use the INMEMORY clause to enable the SALES2 and TIMES tables to use the In-Memory column store.
The INMEMORY attribute can be
specified on a tablespace, table, (sub)partition, or materialized view.
SQL> alter table sh.times inmemory; Table altered. SQL> alter table sh.sales2 inmemory; Table altered.
Note – at this stage since the tables have not been accessed, they have not been populated in the IM column store memory.
SQL> select segment_name, INMEMORY_SIZE , populate_status from v$im_segments; no rows selected
ALTER TABLE customers INMEMORY PRIORITY CRITICAL;
Let us now execute the query – it takes .40 seconds to execute
CALENDAR SUM(QUANTITY_SOLD) Previous Month -------- ------------------ -------------- 2001-10 294444 246948 2001-11 268836 294444 2001-12 273708 268836 ... ... 36 rows selected. Elapsed: 00:00:00.40
Note the Explain Plan
SQL> select * from table(dbms_xplan.display_cursor()); ---------------------------------------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 770 (100)| | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | | | | | | | 3 | VECTOR GROUP BY | | 60 | 1200 | 5 (20)| 00:00:01 | | 4 | KEY VECTOR CREATE BUFFERED | :KV0000 | | | | | |* 5 | TABLE ACCESS INMEMORY FULL | TIMES | 1461 | 29220 | 4 (0)| 00:00:01 | | 6 | WINDOW BUFFER | | 60 | 2520 | 765 (26)| 00:00:01 | | 7 | SORT GROUP BY | | 60 | 2520 | 765 (26)| 00:00:01 | |* 8 | HASH JOIN | | 60 | 2520 | 764 (26)| 00:00:01 | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ | 9 | VIEW | VW_VT_0834CBC3 | 60 | 1320 | 762 (26)| 00:00:01 | | 10 | VECTOR GROUP BY | | 60 | 660 | 762 (26)| 00:00:01 | | 11 | HASH GROUP BY | | 60 | 660 | 762 (26)| 00:00:01 | | 12 | KEY VECTOR USE | :KV0000 | | | | | |* 13 | TABLE ACCESS INMEMORY FULL| SALES2 | 11M| 115M| 758 (25)| 00:00:01 | | 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6600_5B3351 | 60 | 1200 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ 5 - inmemory("TIMES"."CALENDAR_YEAR"<>2000) filter("TIMES"."CALENDAR_YEAR"<>2000) 8 - access("ITEM_5"=INTERNAL_FUNCTION("C0") AND "ITEM_6"="C2") 13 - inmemory(SYS_OP_KEY_VECTOR_FILTER("SALES2"."TIME_ID",:KV0000)) filter(SYS_OP_KEY_VECTOR_FILTER("SALES2"."TIME_ID",:KV0000)) Note ----- - vector transformation used for this statement
The In-Memory column store has now been populated with the SALES2 and TIMES tables.
SQL> select segment_name, INMEMORY_SIZE , populate_status from v$im_segments; SEGMENT_NAME -------------------------------------------------------------------------------- INMEMORY_SIZE POPULATE_ ------------- --------- TIMES 1179648 COMPLETED SALES2 91422720 COMPLETED
Disable In-Memory column store
We see that the same query now takes 2.85 seconds
SQL> alter session set inmemory_query=disable; Session altered. CALENDAR SUM(QUANTITY_SOLD) Previous Month -------- ------------------ -------------- 2001-10 294444 246948 2001-11 268836 294444 2001-12 273708 268836 ….. ……. 36 rows selected. Elapsed: 00:00:02.85
Note the difference in the Explain Plan with In-Memory option disabled
SQL> SQL> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ SQL_ID 2qvd8xzntr6fr, child number 1 ------------------------------------- SELECT CALENDAR_MONTH_DESC, sum(quantity_sold), lag(sum(quantity_sold),1) over (ORDER BY CALENDAR_MONTH_DESC) "Previous Month" FROM SH.sales2, SH.TIMES WHERE SH.times.TIME_ID = SH.sales2.TIME_ID AND SH.times.CALENDAR_YEAR <> '2000' GROUP BY CALENDAR_MONTH_DESC ORDER BY CALENDAR_MONTH_DESC ASC Plan hash value: 1281295501 ------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 14659 (100)| | | 1 | WINDOW BUFFER | | 60 | 1860 | 14659 (1)| 00:00:01 | | 2 | SORT GROUP BY | | 60 | 1860 | 14659 (1)| 00:00:01 | |* 3 | HASH JOIN | | 918K| 27M| 14638 (1)| 00:00:01 | |* 4 | TABLE ACCESS FULL| TIMES | 1461 | 29220 | 18 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL| SALES2 | 918K| 9870K| 14617 (1)| 00:00:01 | -------------------------------------------------------------------------------
0 Comments