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.
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.
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
[[email protected] ~]$ cd /u01/app/oracle/dbs
# login to idle database
[[email protected] 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
[[email protected] 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:
- 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.
- 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.
