{"id":673,"date":"2014-07-24T10:26:01","date_gmt":"2014-07-24T08:26:01","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=673"},"modified":"2020-08-18T16:40:17","modified_gmt":"2020-08-18T14:40:17","slug":"in-memory-hands-on","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/in-memory-hands-on\/","title":{"rendered":"In-memory Columnar Store hands-on"},"content":{"rendered":"

As I’ve written in my previous post, the inmemory_size<\/strong>\u00a0parameter is static, so you need to restart\u00a0your instance to activate it or change its size. Let’s try to set it at 600M.<\/p>\n

SQL> show parameter inmem\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\ninmemory_clause_default              string\r\ninmemory_force                       string      DEFAULT\r\ninmemory_query                       string      ENABLE\r\ninmemory_size                        big integer 0\r\n\r\nSQL> alter system set inmemory_size=600M scope=spfile;\r\n\r\nSQL> shutdown\r\n\r\n...\r\n\r\nSQL> startup\r\n\r\n...\r\n\r\nSQL> show parameter inmem\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\ninmemory_clause_default              string\r\ninmemory_force                       string      DEFAULT\r\ninmemory_query                       string      ENABLE\r\ninmemory_size                        big integer 608M<\/pre>\n
 <\/p>\n
First interesting thing: it has been rounded to 608M so it works in chunks of 16M. (to be verified)<\/p>\n
Which views can you select for further information?<\/p>\n
SQL> select view_name from dba_views where view_name like 'V_$IM%';\r\n\r\nVIEW_NAME\r\n--------------------------------------------------------------------------------\r\nV_$IM_SEGMENTS_DETAIL\r\nV_$IM_SEGMENTS\r\nV_$IM_USER_SEGMENTS\r\nV_$IM_TBS_EXT_MAP\r\nV_$IM_SEG_EXT_MAP\r\nV_$IM_HEADER\r\nV_$IM_COL_CU\r\nV_$IM_SMU_HEAD\r\nV_$IM_SMU_CHUNK\r\nV_$IM_COLUMN_LEVEL\r\n\r\n10 rows selected<\/pre>\n
V$IM_SEGMENTS gives a few information about the segments that have a columnar version, including the segment size, the actual memory allocated, the population status and other compression indicators.<\/p>\n
The other views help understand the various memory chunks and the status for each column in the segment.<\/p>\n
Let’s create a table with a few records:<\/p>\n
SQL> create table ludovico.tinmem\r\nas\r\nselect\r\na.OWNER ,\r\na.TABLE_NAME ,\r\nb.owner owner2,\r\nb.table_name table_name2,\r\na.TABLESPACE_NAME ,\r\na.STATUS ,\r\na.PCT_FREE ,\r\na.PCT_USED ,\r\na.INI_TRANS ,\r\na.MAX_TRANS ,\r\na.INITIAL_EXTENT ,\r\na.NEXT_EXTENT ,\r\na.MIN_EXTENTS ,\r\na.MAX_EXTENTS ,\r\na.PCT_INCREASE ,\r\na.FREELISTS ,\r\na.FREELIST_GROUPS ,\r\na.LOGGING\r\n22 from all_tables a, all_tables b;\r\n\r\nTable created.\r\n\r\nSQL> select count(*) from ludovico.tinmem;\r\n\r\nCOUNT(*)\r\n----------\r\n5470921\r\n\r\nSQL><\/pre>\n
The table is very simple, it’s a cartesian of two “all_tables” views.<\/p>\n
Let’s also create an index on it:<\/p>\n
SQL> create index ludovico.tinmem_ix1 on ludovico.tinmem (table_name, pct_increase);\r\n\r\n\u00a0SQL> select segment_name, bytes\/1024\/1024 from dba_segments where owner='LUDOVICO';\r\n\r\nSEGMENT_NAME      BYTES\/1024\/1024\r\n----------------- ---------------\r\nTINMEM                        621\r\nTINMEM_IX1 \u00a0                  192<\/pre>\n
The table uses 621M and the index 192M.<\/p>\n
How long does it take to do a full table scan almost from disk?<\/p>\n
SQL> select distinct tablespace_name from ludovico.tinmem order by 1;\r\n\r\nTABLESPACE_NAME\r\n------------------------------\r\nSYSAUX\r\nSYSTEM\r\nUSERS\r\n\r\nElapsed: 00:00:15.05<\/pre>\n
15 seconds! Ok, I’m this virtual machine is on an external drive 5400 \u00a0RPM… \ud83d\ude41<\/p>\n
Once the table is fully cached in the buffer cache, the query performance progressively improves to ~1 sec.<\/p>\n
SQL> r\r\n1* select distinct tablespace_name from ludovico.tinmem order by 1\r\n\r\nTABLESPACE_NAME\r\n------------------------------\r\nSYSAUX\r\nSYSTEM\r\nUSERS\r\n\r\n\r\nElapsed: 00:00:01.42\r\nSQL> r\r\n1* select distinct tablespace_name from ludovico.tinmem order by 1\r\n\r\nTABLESPACE_NAME\r\n------------------------------\r\nSYSAUX\r\nSYSTEM\r\nUSERS\r\n\r\n\r\nElapsed: 00:00:00.99<\/pre>\n
There is no inmemory segment yet:<\/p>\n
SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION\r\n\r\n2 FROM V$IM_SEGMENTS; \r\n\r\nno rows selected<\/pre>\n
You have to specify it at table level:<\/p>\n
SQL> alter table ludovico.tinmem inmemory;\r\n\r\nTable altered.\r\n\r\nSQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION\r\nFROM V$IM_SEGMENTS; 2\r\n\r\nOWNER\r\n--------------------------------------------------------------------------------\r\nSEGMENT_NAME\r\n--------------------------------------------------------------------------------\r\nINMEMORY INMEMORY_COMPRESS\r\n-------- -----------------\r\nLUDOVICO\r\nTINMEM\r\nHIGH     FOR QUERY<\/pre>\n
The actual creation of the columnar store\u00a0takes a while, especially if you don’t specify to create it with high priority. You may have to query the table before seeing the columnar store and its population will also take some time and increase the overall load of the database (on my VBox VM, the performance overhead of columnar store population is NOT negligible).<\/p>\n
Once the in-memory store created, the optimizer is ready to use it:<\/p>\n
SQL> explain plan for select distinct tablespace_name from ludovico.tinmem order by 1;\r\n\r\nExplained.\r\n\r\n\r\nSQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());\r\n\r\nPLAN_TABLE_OUTPUT\r\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nPlan hash value: 1243998285\r\n\r\n--------------------------------------------------------------------------------------\r\n| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |\r\n--------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT            |        |     1 |    13 | 26132   (2)| 00:00:02 |\r\n|   1 |  SORT UNIQUE                |        |     1 |    13 | 25993   (1)| 00:00:02 |\r\n|   2 |   TABLE ACCESS INMEMORY FULL| TINMEM |  5470K|    67M|    26 (100)| 00:00:01 |\r\n--------------------------------------------------------------------------------------\r\n\r\n9 rows selected.\r\n<\/pre>\n
The previous query now takes half the time on the first attempt!<\/p>\n
SQL> select distinct tablespace_name from ludovico.tinmem order by 1;\r\n\r\nTABLESPACE_NAME\r\n------------------------------\r\nSYSAUX\r\nSYSTEM\r\nUSERS\r\n\r\nElapsed: 00:00:00.50<\/pre>\n
The columnar store for the whole table uses 23M out of 621M, so the compression ratio is very good compared to the non-compressed index previously created!<\/p>\n
SQL> select OWNER, SEGMENT_NAME, SEGMENT_TYPE, INMEMORY_SIZE, BYTES, BYTES_NOT_POPULATED, INMEMORY_PRIORITY, INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION\r\n\r\n2 from V$IM_SEGMENTS;\r\n\r\nOWNER    SEGMENT_NAME SEGMENT_TYPE INMEMORY_SIZE BYTES     BYTES_NOT_POPULATED INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS\r\n-------- ------------ ------------ ------------- --------- ------------------- -------- --------------- -----------------\r\nLUDOVICO TINMEM       TABLE        23527424      651165696 0                   HIGH     AUTO DISTRIBUTE FOR QUERY\r\n\r\nElapsed: 00:00:00.07<\/pre>\n
 <\/p>\n
This is a very short example. The result here (2x improvement) is influenced by several factors. It is safe to think that with “normal” production conditions the gain will be much higher in almost all the cases.
\nI just wanted to demonstrate that in-memory columnar store is space efficient and really provides\u00a0higher speed out of the box.<\/p>\n
Now that you know \u00a0about it, can you live\u00a0without? \ud83d\ude1b<\/p>\n","protected":false},"excerpt":{"rendered":"
As I’ve written in my previous post, the inmemory_size\u00a0parameter is static, so you need to restart\u00a0your instance to activate it or change its size. Let’s try to set it at 600M. SQL> show parameter inmem NAME TYPE VALUE ———————————— ———– … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[326,3,52,6,132,1],"tags":[172,290,118],"class_list":["post-673","post","type-post","status-publish","format-standard","hentry","category-oracle","category-oracledb","category-12c","category-perf","category-triblog","category-uncategorized","tag-columnar","tag-db12c","tag-in-memory"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/673","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=673"}],"version-history":[{"count":7,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/673\/revisions"}],"predecessor-version":[{"id":708,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/673\/revisions\/708"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=673"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=673"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=673"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}