This post was originally published on this site

One of the extensions in Oracle 20c is the possiblity to use the In-Memory Database option for Partitioned External Tables and Hybrid Partitioned Tables. In my opinion, this opens up many possibilities to perform efficient ad-hoc queries on Data Lakes. That’s why I prepared a demo script for my DOAG presentation about SQL features in Oracle 20c. Unfortunately, it turned out differently than planned. A drama in four acts.

Whenever possible, I try to include some live demos in conference presentations, because this adds variety to the presentation and shows the participants in detail how a feature actually works. For my presentation Interesting SQL Enhancements in Oracle 20c at DOAG conference 2020, I prepared three demo scripts that I wanted to show during the session. The third one was about the In-Memory support for Hybrid Partitioned Tables. If you ever prepared a live demo for your own presentations, you know that this always takes more time than expected. The demo should contain a clear message, show the correct results, the result should fit on a screen, the script must be restartable, and many more details. But it’s worth to take this effort. I have many demo scripts that I can reuse in training sessions, in meetings with customers or colleagues, and of course in conference presentations. But not every development of a demo scripts comes to a successful end, as you can read in the following “drama”:

Overture

The beginning was easy: To show several features on Oracle 20c, I created a 20c database in the Oracle Cloud. With the step-by-step description of Beda Hammerschmidt in his blog post How to get an Oracle 20c preview release on the Oracle Cloud and how to connect to it with SQL Developer, this was an easy task – even for a developer like me. After a few minutes, I had a running VM with an Oracle Database 20c Enterprise Edition. I used it to test several SQL features for the planned presentation. As described in the Oracle documentation, 20c is only a preview release, so you should not use it for production databases.

DBSystem Oracle 20c

Act One: On the Wrong Track

To use the INMEMORY clause that is available in Oracle 20c for Partitioned External Tables and Hybrid Partitioned Tables, I first had to enable the In-Memory column store. For this, the Oracle Database In-Memory Base Level Feature is available in 20c. As you can read in the blog post of Andy Rivenes, it allows to use the Database In-Memory feature with a column store of up to 16GB without additional license cost. That’s perfect for my tests. So, I enabled this feature and configured a column store of 200MB. This should be enough for my demo script.

Connected to:

Oracle Database 20c EE High Perf Release 20.0.0.0.0 – Production

Version 20.3.0.0.0

 

SQL> alter system set inmemory_force = base_level scope=spfile;

 

System altered.

 

SQL> alter system set inmemory_size = 200M scope=spfile;

 

System altered.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-12754: Feature ‘In-Memory Base Level’ is disabled due to missing capability ‘Database Editions’.

 

This was not what I expected, and in the first moment, I was lost with my limited DBA knowledge. Fortunately, my Trivadis fellow Martin Berger helped me to fix the database in a very short time (as a firefighter he is used to react quickly). And even better: He also explained me what he did, so that I can fix such an issue myself, if it happens again. And it happened again…

Act Two: Let’s Go Extreme

In the Database Features and Licensing App, I found the information that the feature requires the “Enterprise Edition – Extreme Performance” release. The “High Performance” release, which I installed (it is the default) is not enough.

In-Memory Base Level License Details

No problem! After the good experience with the previous installation, I created another database in the Oracle Cloud and did the same steps again. The result was exactly the same:

Connected to:

Oracle Database 20c EE Extreme Perf Release 20.0.0.0.0 – Production

Version 20.3.0.0.0

 

SQL> alter system set inmemory_force = base_level scope=spfile;

 

System altered.

 

SQL> alter system set inmemory_size = 200M scope=spfile;

 

System altered.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-12754: Feature ‘In-Memory Base Level’ is disabled due to missing capability ‘Database Editions’.

 

With the good explanation in the e-mail from Martin, I was able to fix the issue easily and could restart the database again. If you (like me) don’t know the steps, here a short summary:

# change to parameter file directory

[oracle@db20c ~]$ cd /u01/app/oracle/dbs

 

# login to idle database

[oracle@db20c dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 20.0.0.0.0 – Production on Sat Nov 7 11:46:28 2020

Version 20.3.0.0.0

 

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

# create and edit pfile

SQL> create pfile=’/u01/app/oracle/dbs/initDB20CEE.ora’ from spfile=’/u01/app/oracle/dbs/spfileDB20CEE.ora’;

 

$ vi /u01/app/oracle/dbs/initDB20CEE.ora

-> Delete line

*.inmemory_force=’BASE_LEVEL’

 

# create new spfile from changed pfile

[oracle@db20c dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 20.0.0.0.0 – Production on Sat Nov 7 11:46:28 2020

Version 20.3.0.0.0

 

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> create spfile=’/u01/app/oracle/dbs/spfileDB20CEE.ora’ from pfile=’/u01/app/oracle/dbs/initDB20CEE.ora’;

 

# start database

SQL> startup

ORACLE instance started.

 

Total System Global Area   3221221392 bytes

Fixed Size                    9572368 bytes

Variable Size              1275068416 bytes

Database Buffers           1694498816 bytes

Redo Buffers                 23977984 bytes

In-Memory Area              218103808 bytes

 

Although I was not able to use the In-Memory Base Level feature, I could at least start the database with a configured In-Memory column store of 200MB.

Act Three: Poor Performance

Finally, I was able to prepare my demo script for a Hybrid Partitioned Table with an INMEMORY clause. I created a table, based on (fake) data of Monsterbräu, our craft beer brewery. The table contains four yearly partitions for the years 2017 to 2020. The current partition is stored in the database, the partitions of the previous years are external partitions and read the data from CSV files. Each partition contains between 2 and 3 million rows. To show the difference in query performance, the partition for 2017 does not contain an INMEMORY clause.

CREATE TABLE hybrid_beer_delivery

(  order_no         NUMBER(10)

 , beer_name        VARCHAR2(40)

 , delivery_date    DATE

 , quantity         NUMBER(3)

 , price_per_unit   NUMBER(5,2)

 , price_total      NUMBER(7,2)  

)

EXTERNAL PARTITION ATTRIBUTES

(TYPE oracle_loader

 DEFAULT DIRECTORY data_pump_dir

 ACCESS PARAMETERS

  ( RECORDS DELIMITED BY NEWLINE

    SKIP 1

    FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘ LDRTRIM

    MISSING FIELD VALUES ARE NULL

    (order_no, beer_name, delivery_date DATE ‘dd.mm.yyyy’, quantity, price_per_unit, price_total)

  )

)

PARTITION BY RANGE (delivery_date)

  (PARTITION p_2017 VALUES LESS THAN (TO_DATE(‘01.01.2018’, ‘dd.mm.yyyy’))

       EXTERNAL LOCATION (‘beer_delivery_2017.csv’)

  ,PARTITION p_2018 VALUES LESS THAN (TO_DATE(‘01.01.2019’, ‘dd.mm.yyyy’))

          INMEMORY

       EXTERNAL LOCATION (‘beer_delivery_2018.csv’)

  ,PARTITION p_2019 VALUES LESS THAN (TO_DATE(‘01.01.2020’, ‘dd.mm.yyyy’))

          INMEMORY

       EXTERNAL LOCATION (‘beer_delivery_2019.csv’)             

  ,PARTITION p_2020 VALUES LESS THAN (TO_DATE(‘01.01.2021’, ‘dd.mm.yyyy’))

          INMEMORY

  );

 

INSERT INTO hybrid_beer_delivery SELECT * FROM external_beer_delivery PARTITION (p_2020);

COMMIT;

 

To be sure that all data is populated in the In-Memory column store, I first executed some COUNT(*) queries on the table:

SQL> SELECT COUNT(*) FROM hybrid_beer_delivery;

 

  COUNT(*)

———-

   9837711

 

SQL> SELECT COUNT(*) FROM hybrid_beer_delivery PARTITION (p_2017);

 

  COUNT(*)

———-

   1923319

 

SQL> SELECT COUNT(*) FROM hybrid_beer_delivery PARTITION (p_2018);

 

  COUNT(*)

———-

   2849715

 

SQL> SELECT COUNT(*) FROM hybrid_beer_delivery PARTITION (p_2019);

 

  COUNT(*)

———-

   2263125

 

SQL> SELECT COUNT(*) FROM hybrid_beer_delivery PARTITION (p_2020);

 

  COUNT(*)

———-

   2801552

 

Finally, the following query should show the performance benefit of the In-Memory feature:

SQL> SELECT /*+ gather_plan_statistics */
  2         EXTRACT(YEAR FROM delivery_date) year
  3       , beer_name
  4       , SUM(quantity)
  5  FROM hybrid_beer_delivery
  6  GROUP BY EXTRACT(YEAR FROM delivery_date), beer_name
  7  ORDER BY 1, 3 DESC;
 
      YEAR BEER_NAME                                SUM(QUANTITY)
———- —————————————- ————-
      2017 Blauer Pirat                                  46063790
      2017 Weisses Einhorn                               31458800
      2017 Glatthopfen                                   29919000
      2017 Schwarze Kobra                                17454500
      2017 Lazariter                                     10455000
… 
24 rows selected.

 

The execution plan looks perfect: For the two external partitions P_2018 and P2019 as well as for the internal partition P_2020, INMEMORY is used. But the response time is very slow on the external partitions (see column A-Time in execution plan). I did several tests, but in all cases, it did not make any difference whether the external partitions are defined with an INMEMORY clause or not. Really frustrating!

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'iostats -rows +partition last'));
 
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                 | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                      |      1 |       |       |     24 |00:00:11.22 |    9473 |
|   1 |  SORT ORDER BY                              |                      |      1 |       |       |     24 |00:00:11.22 |    9473 |
|   2 |   HASH GROUP BY                             |                      |      1 |       |       |     24 |00:00:11.22 |    9473 |
|   3 |    VIEW                                     | VW_TE_2              |      1 |       |       |   9837K|00:00:09.27 |    9473 |
|   4 |     UNION-ALL                               |                      |      1 |       |       |   9837K|00:00:09.26 |    9473 |
|   5 |      PARTITION RANGE ITERATOR               |                      |      1 |     2 |     4 |   7914K|00:00:07.28 |    9450 |
|   6 |       TABLE ACCESS HYBRID PART INMEMORY FULL| HYBRID_BEER_DELIVERY |      3 |     2 |     4 |   7914K|00:00:07.27 |    9450 |
|   7 |        TABLE ACCESS INMEMORY FULL           | HYBRID_BEER_DELIVERY |      1 |     2 |     4 |   2801K|00:00:00.08 |    9427 |
|   8 |      PARTITION RANGE SINGLE                 |                      |      1 |     1 |     1 |   1923K|00:00:01.95 |      23 |
|   9 |       EXTERNAL TABLE ACCESS FULL            | HYBRID_BEER_DELIVERY |      1 |     1 |     1 |   1923K|00:00:01.95 |      23 |
-------------------------------------------------------------------------------------------------------------------------------------

 

Act Four: Bitter End of the Story

To find out the reason for this poor performance, I looked into V$IM_SEGMENTS to see what data was populated to the In-Memory column store:

SQL> SELECT segment_name, partition_name, inmemory_size, bytes

  2    FROM v$im_segments

  3  ORDER BY segment_name, partition_name;

 

SEGMENT_NAME                   PARTITION_ INMEMORY_SIZE      BYTES

—————————— ———- ————- ———-

HYBRID_BEER_DELIVERY           P_2018          26345472          0

HYBRID_BEER_DELIVERY           P_2019          20054016          0

HYBRID_BEER_DELIVERY           P_2020          29556736  132988928

 

Although, all three partitions are visible in the view, only the internal partition P_2020 was populated. For the two external partitions, BYTES (size of the on-disk segment that was populated in the column store) is 0, so nothing was populated.

Because I had no explanation for this behaviour, I finally decided to skip the demo in my DOAG presentation. Officially, because I had not enough time, but the real reason was, that I was not able to explain the bad performance. Very sad!

Encore: After the Presentation

Because I’m still curious what I missed in my demo script and why the expected performance boost did not work, I sent an e-mail to Andy Rivenes, product manager for Database In-Memory at Oracle. His (also very quick) reply explained why the demo did not work:

  1. In-Memory Base Level should work in Enterprise Edition Extreme Edition, so the strange behaviour I had in my tests, should not happen. He filed a bug, so hopefully, the feature will be available in Oracle 21c.
  2. In-Memory on Partitioned External Tables and Hybrid Partitioned Tables is only available on Exadata, but not on Database Cloud Services. So, it is “only” a bug in the documentation of the License Guide. Not really helpful, because Oracle 20c is only available on Database Cloud Services.

I know that Oracle 20c is only a preview release. Therefore I don’t complain that some features do not work as expected. But still I was quite disappointed that I couldn’t show this feature, which sounds very promising, live. At least I learned a lot during the preparation of the live demo, and I am confident that In-Memory for Partitioned External Tables and Hybrid Partitioned Tables will work in Oracle 21c, at least on Exadata.