Oracle inMemory – First experiences

With Oracle 12.1.0.2 the brand new database option named inMemory has been introduced. There are many blogs and articles out in the field so I don’t want to go in to much detail of the main concept. To me “inmemory” sounds a little obvious because whenever we are working with the Oracle database most of our data should be in Memory – right? But there is another naming for it: Column store. So it stores the data in columns instead of rows? Due to the Oracle documentation this isn’t true because “The columnar format exists only in memory (Concepts Guide)”. So the concept of inMemory is that an area exists in memory as part of the sga and the data in this area is no longer populated in row format like the buffer cache but in a column format. Especially Datawarehouses and Online Analytic Systems with benefit but because there is no difference in the storage of data the option can be simply enabled and used in OLTP systems as well. For the main concept I would recommend reading the Oracle Concepts Guide and the whitepaper “Oracle Database In-Memory”.

Real Life

Some of my customers tried inMemory more or less successful. Most of the time the reason for an unsuccessful test was that the data was not suitable for inMemory (e.g. no star schema, too complex queries, insufficient amount of data). But at least I had an appointment with a customer who is mainly specialized on Business Intelligence solutions. So a perfect fit for inMemory. We did some basic tests in their environment and without any changes to the application the queries run 10 to 20 times faster (and even more). I’m not allowed to use the customer queries over here but I’ve created a test environment in my own lab and here are some results.

Examples

The queries are based on a star flake schema with one fact table named “UMSAETZE” (German for “REVENUE”) with about 145 million rows and four dimension tables named “ARTIKEL”; “REGION”, “ZEITEN” and “KUNDEN” (German for “ITEM”, “REGION”, “DATES” and “CUSTOMERS”).

The first query ist simply this:

SELECT count(*) FROM umsatz;

The second query is more complex:

SELECT sum(u.umsatz) as gesamtumsatz,
       a.artikelgruppe,
       z.monat_des_jahres,
       z.jahreszahl,
       r.region,
       k.plz,
       k.ort
  FROM region r
       INNER JOIN umsaetze u ON (r.region_id = u.region_id)
       INNER JOIN kunden k ON (k.kunden_id = u.kunden_id)
       INNER JOIN artikel a ON (a.artikel_id = u.artikel_id)
       INNER JOIN zeiten z ON (zeiten_id = zeit_id)
 WHERE r.region = 'Nord'
   AND z.jahreszahl in (2014,2015)
   AND z.monat_des_jahres = 12
 GROUP BY a.artikelgruppe, z.monat_des_jahres, z.jahreszahl, k.plz, k.ort, r.region
 FETCH FIRST 10 PERCENT ROWS ONLY;

To allow tables to be populated into the column store the parameter inmemory_size must be set. As the inmemory area is part of the sga this parameter must be set in conjunction with the sga_target or memory_target parameter. In my sample database the sga_target was about 10 GB while the inmemory_size was 5 GB. The inMemory area is not a cache like the buffer cache so if a table is too huge or the area is too small tables might be populated partly and an error message will be written to the alert log.

Populate the tables

Tables can be populated with the “ALTER TABLE … INMEMORY” command. Even though this is the default I would not recommend using it. Because with this command a table will only be populated when it is used the first time (e.g. with an DML or a SELECT command). Instead I would recommend to populate the most important fact table first and next all tables associated with it:

SQL> ALTER TABLE umsaetze INMEMORY PRIORITY CRITICAL;
SQL> ALTER TABLE artikel INMEMORY PRIORITY HIGH;
SQL> ALTER TABLE kunden INMEMORY PRIORITY HIGH;
SQL> ALTER TABLE region INMEMORY PRIORITY HIGH;
SQL> ALTER TABLE zeiten INMEMORY PRIORITY HIGH;

With this commands the table UMSAEZE will be populated immediately and whenever the instance is being started. After the table has been loaded completely into memory the rest of the tables will be populated as long as there is succifient space in the inmemory area.

The views v$im_segments and v$im_user_segments can be used to validate that the tables have been populated. I granted SELECT on the v$im_user_segments to my test user so there is no need to use a privileged user for the verification.

SELECT inst_id, segment_name, inmemory_size, bytes, bytes_not_populated, populate_status
FROM   v$im_user_segments;

SEGMENT_NAME         INMEMORY_SIZE      BYTES BYTES_NOT_POPULATED POPULATE_
-------------------- ------------- ---------- ------------------- ---------
REGION                     1179648     393216                   0 COMPLETED
KUNDEN                    24313856   83886080                   0 COMPLETED
UMSAETZE                2710831104 6978273280                   0 COMPLETED
ZEITEN                     1179648     131072                   0 COMPLETED

One strange behavior which I cannot explain yet: the table “ARTIKEL” is not populated at all. I tried with several options and even with “PRIORITY CRITICAL” the table is not listed. As this is a very small table (only 300 rows) it doesn’t affect the overall query result.

Now we are ready to start our first test:

1. Query 1 with the NO_INMEMORY hint

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     11.32      39.68     308384     308455          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     11.32      39.68     308384     308455          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 105
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=308455 pr=308384 pw=0 time=39683926 us)
 144500000  144500000  144500000   INDEX FAST FULL SCAN PK_UMSAETZE (cr=308455 pr=308384 pw=0 time=60586018 us cost=81707 size=0 card=144500000)(object id 92071)

2. Query 2 without the hint

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.95       1.14          0         12          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.95       1.17          0         12          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 105
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=12 pr=0 pw=0 time=1144222 us)
 144500000  144500000  144500000   TABLE ACCESS INMEMORY FULL UMSAETZE (cr=12 pr=0 pw=0 time=1130484 us cost=8708 size=0 card=144500000)

Result 1

The result is really impressive. Instead of approximately 40 seconds the query took only one second.

But what about the more complex query?

3. Query 2 with the NO_INMEMORY hint

all     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.04          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       88     29.36      69.63     854299     849731          0        1304
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       90     29.41      69.68     854299     849731          0        1304

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 105
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
      1304       1304       1304  VIEW  (cr=849731 pr=854299 pw=4619 time=69638236 us cost=268671 size=217524901 card=1587773)
     13031      13031      13031   WINDOW BUFFER (cr=849731 pr=854299 pw=4619 time=69645196 us cost=268671 size=130197386 card=1587773)
     13031      13031      13031    HASH GROUP BY (cr=849731 pr=854299 pw=4619 time=69609961 us cost=268671 size=130197386 card=1587773)
    511356     511356     511356     HASH JOIN  (cr=849731 pr=854299 pw=4619 time=56529021 us cost=238350 size=130197386 card=1587773)
       374        374        374      TABLE ACCESS FULL ARTIKEL (cr=5 pr=0 pw=0 time=115 us cost=3 size=5984 card=374)
    511356     511356     511356      HASH JOIN  (cr=849726 pr=854299 pw=4619 time=56394671 us cost=238343 size=104793018 card=1587773)
   1000000    1000000    1000000       TABLE ACCESS FULL KUNDEN (cr=9683 pr=9678 pw=0 time=54447 us cost=2745 size=21996920 card=999860)
    511356     511356     511356       HASH JOIN  (cr=840043 pr=839971 pw=0 time=43368955 us cost=229777 size=69862012 card=1587773)
      1351       1351       1351        TABLE ACCESS FULL REGION (cr=41 pr=0 pw=0 time=556 us cost=13 size=13734 card=1526)
   3128202    3128202    3128202        HASH JOIN  (cr=840002 pr=839971 pw=0 time=85763697 us cost=229748 size=222288150 card=6351090)
        32         32         32         TABLE ACCESS FULL ZEITEN (cr=14 pr=0 pw=0 time=154 us cost=6 size=616 card=56)
 144500000  144500000  144500000         TABLE ACCESS FULL UMSAETZE (cr=839988 pr=839971 pw=0 time=41869421 us cost=229378 size=3468000000 card=144500000)

4. Query 2 without a hint

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.31       0.37          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       88      1.83       2.74       4650         26          0        1304
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       90      2.14       3.11       4650         26          0        1304

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 105
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
      1304       1304       1304  VIEW  (cr=26 pr=4650 pw=4619 time=2735009 us cost=47289 size=217524901 card=1587773)
     13031      13031      13031   WINDOW BUFFER (cr=26 pr=4650 pw=4619 time=2736637 us cost=47289 size=130197386 card=1587773)
     13031      13031      13031    HASH GROUP BY (cr=26 pr=4650 pw=4619 time=2728219 us cost=47289 size=130197386 card=1587773)
    511356     511356     511356     HASH JOIN  (cr=26 pr=4650 pw=4619 time=2316748 us cost=16968 size=130197386 card=1587773)
       374        374        374      TABLE ACCESS INMEMORY FULL ARTIKEL (cr=5 pr=0 pw=0 time=28765 us cost=1 size=5984 card=374)
    511356     511356     511356      HASH JOIN  (cr=21 pr=4650 pw=4619 time=2211160 us cost=16964 size=104793018 card=1587773)
   1000000    1000000    1000000       TABLE ACCESS INMEMORY FULL KUNDEN (cr=3 pr=0 pw=0 time=72471 us cost=225 size=21996920 card=999860)
    511356     511356     511356       HASH JOIN  (cr=18 pr=0 pw=0 time=951774 us cost=10917 size=69862012 card=1587773)
      1351       1351       1351        TABLE ACCESS INMEMORY FULL REGION (cr=3 pr=0 pw=0 time=169990 us cost=4 size=13734 card=1526)
   3128202    3128202    3128202        HASH JOIN  (cr=15 pr=0 pw=0 time=645427 us cost=10897 size=222288150 card=6351090)
        32         32         32         JOIN FILTER CREATE :BF0000 (cr=3 pr=0 pw=0 time=14294 us cost=4 size=616 card=56)
        32         32         32          TABLE ACCESS INMEMORY FULL ZEITEN (cr=3 pr=0 pw=0 time=14084 us cost=4 size=616 card=56)
   3128202    3128202    3128202         JOIN FILTER USE :BF0000 (cr=12 pr=0 pw=0 time=227502 us cost=10529 size=3468000000 card=144500000)
   3128202    3128202    3128202          TABLE ACCESS INMEMORY FULL UMSAETZE (cr=12 pr=0 pw=0 time=188217 us cost=10529 size=3468000000 card=144500000)

Result 2

Again a fantastic performance gain: 70 seconds without inmemory and only 3 (!) seconds with inMemory.
As a first result inMemory looks very promising. The customer was impressed and we went to the next stage: the preproduction database. Unfortunately that was a RAC database – and not an Exadata.
In my next blog I will tell you about the challenges when using inMemory with RAC.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top