Press Coverage at The Register: Click here.
Executive Summary
This blog post offers proof that you can trigger In-Memory Column Store feature usage with the default INMEMORY_* parameter settings. These parameters are documented as the approach to ensure In-Memory functionality is not used inadvertently–or at least they are documented as the “enabling” parameters.
Update: Oracle Acknowledges Software Defect
During the development of this study, Oracle’s Product Manager in charge of the In-Memory feature has cited Bug #19308780 as it relates to my findings. I need to point out, however, that it wasn’t until this blog installment that the defective functionality was acknowledged as a bug. Further, the bug being cited is not visible to customers so there is no closure. How can one have closure without knowing what, specifically, is acknowledged as defective?
Index of Related Posts
This is part 4 in a series: Part I, Part II, Part III, Part IV, Part V.
Other Blog Updates
Please note, blog updates are listed at the end of the article.
What Really Matters?
This is a post about enabling versus using the Oracle Database 12c Release 12.1.0.2 In-Memory Column Store feature which is a part of the separately licensed Database In-Memory Option of 12c. While reading this please be mindful that in this situation all that really matters is what actions on your part effect the internal tables that track feature usage.
Make Software, Not Enemies–And Certainly Not War
There is a huge kerfuffle regarding the separately licensed In-Memory Column Store feature in Oracle Database 12c Release 12.1.0.2–specifically how the feature is enabled and what triggers usage of the feature.
I pointed out a) the fact that the feature is enabled by default and b) the feature is easily accidentally used. I did that in Part I and Part II in my series on the matter. In Part III I shared how the issue has lead to industry journalists quoting–and then removing–said quotes. I’ve endured an ungodly amount of shameful backlash even from some friends on the Oaktable Network list as they asserted I was making a mountain out of a mole hill (that was a euphemistic way of saying they all but accused me of misleading my readers). Emotion and high-technology are like watery oil.
About the only thing that hasn’t happened is for anyone to apologize for being totally wrong in their blind-faith rooted feelings about this issue. What did he say? Please read on.
From the start I pointed out that the INMEMORY_QUERY feature is enabled by default–and that it is conceivable that someone could use it accidentally. The back lash from that was along the lines of how many parameters and what user actions (e.g., database reboot) are needed for that to be a reality. Maria Colgan–who is Oracle’ s PM for the In-Memory Column Store feature–tweeted that I’m confusing people when announcing her blog post on the fact that In-Memory Column Store usage is controlled not by INMEMORY_QUERY but instead INMEMORY_SIZE. Allow me to add special emphasis to this point. In a blog post on oracle.com, Oracle’s PM for this Oracle database feature explicitly states that INMEMORY_SIZE must be changed from the default to use the feature.
If I were to show you everyone else was wrong and I was right, would you think less of me? Please, don’t let it make you feel less of them. We’re just people trying to wade through the confusion.
The Truth On The Matter
Here is the truth and I’ll prove it in a screen shot to follow:
- INMEMORY_QUERY is enabled by default. If it is set you can trigger feature usage–full stop.
- INMEMORY_SIZE is zero by default. Remember this is the supposedly ueber-powerful setting that precludes usage of the feature and not, in fact, the more top-level-sounding INMEMORY_QUERY parameter.
In the following screenshot I’ll show that INMEMORY_QUERY is at the default setting of ENABLE and INMEMORY_SIZE is at the default setting of zero. I prove first there is no prior feature usage. I then issue a CREATE TABLE statement specifying INMEMORY. Remember, the feature-blocking INMEMORY_SIZE parameter is zero. If “they” are right I shouldn’t be able to trigger In-Memory Column Store feature usage, right? Observe–or better yet, try this in your own lab:
So ENABLED Means ENABLED? Really? Imagine That.
So I proved my point which is any instance with the default initialization parameters can trigger feature usage. I also proved that the words in the following three screenshots are factually incorrect:
Screenshot of blog post on Oracle.com:
Screenshot of email to Oracle-L Email list:
Summary
I didn’t want to make a mountain out of this mole hill. It’s just a bug. I don’t expect apologies. That would be too human–almost as human as being completely wrong while wrongly clinging to one’s wrongness because others are equally, well, wrong on the matter.
BLOG UPDATE 2014.07.31: Click here to view an article on The Register regarding Oracle Database In-Memory feature usage.
BLOG UPDATE 2014.07.30: Oracle’s Maria Colgan has a comment thread on her blog on the In-Memory Column Store feature. In the thread a reader reports precisely the same bug behavior you will see in my proof below. Maria’s comment is that feature usage is tracked in spite of the supposed disabling feature INMEMORY_SIZE set to the default value. While this agrees with what I already knew about this feature it is in my opinion not sufficient to speak of a bug of such consequence without citing the bug number. Furthermore, such a bug must be visible to users with support contracts. Click here for a screenshot of the Oracle blog. In case Oracle changes their mind on such an apparently sensitive topic I uploaded the blog to the Wayback Machine here.
BLOG UPDATE 2014.07.29: Oracle’s Maria Colgan issued a tweet stating “What u found in you 3rd blog is a bug […] Bug 19308780.” Click here for a screenshot of the tweet. Also, click here for a Wayback Machine (web.archive.org) copy of the tweet.
Sundry References
Print out of Maria’s post on Oracle.com and link to same: Getting started with Oracle Database In-Memory Part I
Franck Pachot 2014.07.23 findings reported here: Tweet , screenshot of tweet.
Kevin Closson's Blog: Platforms, Databases and Storage 
It’s clear to me that having exhausted all other ways to increase revenue of the cash cow, now Oracle has to resort to pure extortion.
Oh, no, what am I saying? Of course not!
It’s the “bad dba’s” fault! For not guessing the convoluted parameter settings that of course the “community of experts by decree” are all fully aware of as is amply shown here!
Sorry, silly me! It was just a small lapse into truth, reality and reasonable marketing.
Quickly gone, back to the alternate reality yachting show!…
Kevin, you’ve made a valid point. The example I quoted, enabling RT Query on a DataGuard standby by accident because ORADIM only supports startup nomount or startup open is a similar fault.
All this points to one thing. Oracle may claim they so not want to put customers through the hassle of requiring things like enabling keys for features purchased, but current practice creates potentially huge liabilities for customers and their staff in an increasing number of chargeable options enabled by default.
Oracle will not want to do that as presumably they rake in millions by hitting customers after-the-fact with audits showing usage of features some did not even know they had. So the only way forward may be court cases in the US and Europe setting a precedent, showing that courts will not enforce licence agreements where the clear intention is entrapment.
I expect Oracle would not use the DBA Feature Usage Statistics table as it stands to determine usage, they are likely to look at other things to determine actual usage. It may be in later releases, the ‘usage’ detection logic for the dbfus view is corrected!
what about the inmemory_force setting at DEFAULT? if you create the table using the inmemory clause it will use it, right? Could that be why it still uses the in-memory option for the table without changing the inmemory_size?
@fojo: No. INMEMORY_FORCE=DEFAULT does nothing. Please see Part II where I show a scenario of “mistakenly” using an init.ora file with INMEMORY_FORCE=INMEMORY to prove that I triggered feature usages without issuing any CREATE TABLE or ALTER TABLE statements. Simply creating a database in that example triggered feature usage.
Ah interesting. I may be missing something in Part II, but the “mistaken” init.ora file sets INMEMORY_CLAUSE_DEFAULT=’inmemory’, right? Which will make all tables use the inmemory option I think. It doesn’t set INMEMORY_FORCE to anything – so it will be set to DEFAULT. What about setting INMEMORY_FORCE to OFF? My understanding of what it is supposed to do is “If OFF is specified, then even if the IM column store is configured on this instance, no tables or materialized are populated in memory.”
I don’t think this affects your scenario of having a “mistaken” init.ora file, but might be interesting to know what this other parameter really does.
@fojo: So the point I aimed to make in Part II was the people’s common understanding of how a separately licensed option usage would entail. I proved that one need not create a single user table nor issue the ALTER TABLE statement.
When I was strapped to the whipping post on Oaktable internal email on the matter it was pointed out to me that basically one would have to be an idiot to not presume the In-Memory feature would apply to Oracle’s internal tables if one “accidentally” added INMEMORY_CLAUSE_DEFAULT=”INMEMORY” to an init.ora file and then created a database. I don’t care about that assertion because, idiot or not, there are probably some folks that weren’t aware of these things. All I aimed to show in that part of the series was one can “use” the feature without issuing CREATE TABLE or ALTER TABLE statements.
Yes makes sense. I’m exploring this in-memory option and it has been good reading your posts about it. Thanks for the information.
It is very interesting to see that quite many of “oracle db celebrities” has became prostitues of oracle marketing. Setting imemory_query=’ENABLE’ by default is realiy dirty practice from oracle. for partitioning and advanced compression, this is even worse! all waht you need is one poweruser with create table option (or even to be owner of table for ACO and move it to compressed OLTP format). And I’m nit going to spend any word on Kerry…
@Pavol: I’m not going to call out anyone that doesn’t first go on record with a shot at me based on erroneous information. As an aside, yes, there are many of what you refer to as “oracle db celebrities” that hold elevated Oracle ACE program status at the “ACE Director” level. I don’t know if the program still does so but at one time free travel to conferences was a part of the package. It seems unlikely that anyone traveling on Oracle Corporation’s funds would dare say anything that an Oracle Program Manager (for example) would consider off-script.
I’m also not saying anyone I know would forego their own scientific skills and moral compass just to play the free-loading shill at conferences. In other words, none of my friends would do so.
Well said.
Well said, I have to repeat. On the other hand, I thought it was like british car journalist. Imagine Steve Sutcliffe from autocar. I can rember so clearly his test of very very expensive mercedes AMG black edition. Mercedes payed for business class tickets, beautiful and very luxury 5 star hotel, let him drive it on circuit and then Steve said it was not really good car… Moreover, went home and wrote it to his magazine 😉
Well, I know. One of the biggest crimes in U.S. is to make any complain of your own corporation (or at least company which invites you to OOW). And one more note, I don’t think calling you dude is worth of any Ace Director
@Pavol: Friends call me dude…even, at times, when they are saying something incorrect to me. I don’t have a problem with that. By the way, have you ever seen this: https://kevinclosson.files.wordpress.com/2014/07/exadata-book-cover-markup.png ?
well I now you have reviewed the book where Kerry was one of the authors (tohether with Tanel and Ranndy Johnson), I was one of the first buyers (at least I think so 😉 ). However calling anyone dude in such an expert forum as Freelist-L is… this is unacceptable to me, I can’t help myself
Cheers
“dude” is just dialect… we take it not as a pejorative…especially among friends. Not an issue.
Kevin, isn’t that default behavior for many other options? E.g. you can easily create a partitioned table with the default install. Also, remember the fight with Tunning/Diagnostic packs, which were so hard to disable? Advanced compression – easily done, no special parameters. Just put COMPRESS FOR OLTP when creating a table and you’re there…
@yavor: We’ve all seen how past separately licensed features have been different to deal with. You may recall user community outlash that resulted in the ability to disable the Diagnostic and Tuning Pack but it wasn’t until 11g that the CONTROL_MANAGEMENT_PACK_ACCESS parameter finally materialized. To read more on that history please see:
http://www.pythian.com/blog/an-open-letter-to-larry-ellison-on-awr-and-ash-licensing/
http://www.pythian.com/blog/oracle-releases-method-to-disable-awr-collection/
As for prior art on other paid features like Partitioning and Real Application Clusters, I’ve already pointed out in Part II (http://wp.me/p21zc-18u) that the chopt command can be used to totally remove Partitioning, Real Application Clusters and Real Application Testing. Further, the Real Application Clusters top-level initialization parameter CLUSTER_DATABASE is set to FALSE by default. That’s top-level, nice clear and simple to understand and most importantly it totally disables the feature.
Finally, may I ask if you think it is OK for Oracle to do these sorts of things with separately licensed features without any standard, easily-understood approach to globally enabling/disabling features–merely because there is prior art with the Diagnostic and Tuning pack and ACO. Adding wrongs to make a right? These things get very confusing and as I’ve shown–most particularly in Part III( http://wp.me/p21zc-199)–there are pitfalls. The scenario I presented in Part II (http://wp.me/p21zc-18u) should be compelling since I didn’t create any user tables yet I triggered feature usage. These things get very confusing.
A good feature request would be to have Oracle extend the chopt command to include In-Memory Column Store disablement to the existing list as shown here.
well maybe there should be another open letter at least for partitioning, advanced compression and in_memory database. Yes, all settings which have anything to do with in-memory should definitely be disabled by default.
@Pavol: One of the main things I point out in Part II (http://wp.me/p21zc-18u ) is the fact that unlike In-Memory Column Store one can simply chopt and unlink partitioning.
All I’m pushing for is solid, simply ways to use and avoid using features.
Well I know unlinking partitioning is possible, but I have never been brave enough to do it in producion. What about AWR, beggining with 11g it uses interval partitions for internal wrh$ tables (and i think few tables were partitioned even id 10g). there are several tables partitioned in SYSTEM schema. Will system feature continue to work? Maybe I should have a try instead of bothering you, but… 😉
You can add multitenant to the list as well. In DBCA on step two the checkbox for “Create As Container Database” is checked by default and you are asked for a PDB name. You have to explicitly opt-out of multitenant if you don’t want it when using the GUI.
Good post. Oracle is famous for making it easy for folks to accidentally use their features. I think the pendulum is too far to the left and countless clients through audit have confirmed this belief. At the same time I think IBM is too far to the right, license guys, expiration trials and I intend to change that for DB2 in the near future.
I read through this comments and was kind of shocked by the Oracle’s PM email. Because, sure the DBA has to do something to take advantage, but last I knew, most DBAs are not the folks that are buying the technology. In larger companies, there is a catalog of technology. NOW I could be wrong (my Oracle certification was in the 10gR2 release) but perhaps if you go to create such a in-memory cache that they offer, you could get a message warning you it isn’t licensed.
I think I ran into this situation with my wife Kelly today. I opened the fridge and saw some left over linguini from the restaurant the other night. I ate it – it was there. I had to take it out and warm it out and do some stuff with it, but it was damn good. Kelly was ticked off because it was hers – I say she should have put a note on it saying “You are not allowed to eat this without permission” and then at least I would have known. 🙂
As an aside, I was disappointing to see some of the comments you experienced. Here is what I can say about Kevin Closson. One day I’m sure our paths will cross as competitors, and if they do, I’m sure the respect will continue both ways. But no matter where he sits, I know him time and time again from his postings to be a man of integrity and fact. If you are going to tangle with Closson ensure you are hands on, because he is. If there is a mistake, it is an accident. When I post benchmark stuff, he cadences me and asks me for sources and I’m glad to see that we are cut form the same cloth. So to watch folks question integrity, well, this is the thing: folks are entitled to their own opinions…but not their own set of facts.
@PaulZ: I’m speechless!
Speechless — you seemed to have said a lot in this blog 🙂 Keep up the good work.
Like others, I “know” Kevin from his posts. Only. Like him and others we pact with technology, architectures and features.. especially from Oracle and other BIG’s..
But behind any BIG names are peoples, and peoples cannot always say what they believe or worst.. are covered with FUD.. And that is “kind” understandable.. Indeed, Kevin is very hands on, and for sure many Oracle employee learn from him “in secret”.. And that is good.
Time to time “new star is born” and, “naturally” wants to “grow too fast”. Some of them post here..
Guys, is there any one that really, please allow me to emphasis the word *really*, believe Oracle choose to be clear in that case? Any, Yes? If No, please don’t write words just because you can write..
Like I say, I don’t even personally know Kevin, but I choose to learn from his posts many times.. Folks, let’s say hypothetical Kevin is wrong.. Nothing to admire in the way can be “wrong”? 🙂
Hi Kevin,
Usually i am not a person who is in doubt for someone’s integrity, especially if i do not know this person, but rather do my own research and find out by myself about the topic involved and then respond.
In response to your blogpost about the in-memory option i played around with it and i also did what you write in your blogpost that even when database parameter inmemory_size have value of zero, there will be a registration of in-memory option usage when creating or altering a table with the in memory clause. So i performed the exact steps you did and what i saw was a surprise for me.
My environment:
I upgraded a 12.1.0.1 CDB with 2 PDB’s to 12.1.0.2 and executed the steps you write in your blogpost. Below is the out coming from my steps:
hans@HVZPDB1> show parameter inmemory
NAME TYPE VALUE
———————————— ———– ——————————
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE
hans@HVZPDB1> @features
Press return to continue
NAME DETECTED_USAGES
——————————————— —————
In-Memory Aggregation 0
In-Memory Column Store 0
hans@HVZPDB1> create table my_obj inmemory as select * from all_objects;
Table created.
hans$HVZPDB1> exit
[oracle@hvz01 sql]$ sql
SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 3 08:41:35 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
sys@HVZCDB1> @update_feature_usage_view.sql
PL/SQL procedure successfully completed.
sys@HVZCDB1> exit
hans@HVZPDB1> @features
Press return to continue
NAME DETECTED_USAGES
——————————————— —————
In-Memory Aggregation 0
In-Memory Column Store 0
hans@HVZPDB1>
Enable inmemory on existing table:
hans@HVZPDB1> alter table t1 inmemory;
Table altered.
sys@HVZPDB1> @update_feature_usage_view.sql
PL/SQL procedure successfully completed.
sys@HVZPDB1> exit
hans@HVZPDB1> @features
Press return to continue
NAME DETECTED_USAGES
——————————————— —————
In-Memory Aggregation 0
In-Memory Column Store 0
hans@HVZPDB1> select count(*) from t1;
Press return to continue
COUNT(*)
———-
3000000
hans@HVZPDB1> @features
Press return to continue
NAME DETECTED_USAGES
——————————————— —————
In-Memory Aggregation 0
In-Memory Column Store 0
A select from user_tables shows inmemory is enabled:
hans@HVZPDB1> select table_name,inmemory from user_tables;
Press return to continue
TABLE_NAME INMEMORY
——————– ——–
T1 ENABLED
EMP DISABLED
T9 DISABLED
EMP_DEPT DISABLED
DEPT_INFO DISABLED
PLAN_TABLE DISABLED
MY_OBJ ENABLED
T2 DISABLED
T DISABLED
12 rows selected.
hans@HVZPDB1>
This was a surprise, despite using the inmemory clause with create table and alter table, detected_usages in dba_feature_usage_statistics remain zero. Then i thought maybe Kevin did not use a CDB/PDB database but a normal non multi tenant database. So i upgraded another database in my lab which is not a multi tenant database (i use this database as repository for my OEM CC test environment) and performed the same tests as i did with CDB/PDB database.
sys@EMCC> show parameter inmemory
NAME TYPE VALUE
———————————— ———– ——————————
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE
sys@EMCC>
After the upgrade i started OMS and executed dbms_feature_usage_internal.exec_db_usage_sampling(SYSDATE); and after that i see in dba_feature_usage_statistics detected_usages is set on 1.
sys@EMCC> @update_feature_usage_view.sql
PL/SQL procedure successfully completed.
sys@EMCC> @features
Press return to continue
NAME DETECTED_USAGES
——————————————— —————
In-Memory Aggregation 0
In-Memory Column Store 1
sys@EMCC>
I suspect after OMS is started certain objects are using in memory option (i will look into that later). But this actually shows that detected_usages can be non-zero even when database parameter inmemory_size still have the default value of zero.
I also created a table with in memory clause and take a look at detected_usages again and see indeed that detedted_usages is lifted to 3.
sys@EMCC> create table my_obj inmemory as select * from all_objects;
Table created.
sys@EMCC> @update_feature_usage_view.sql
PL/SQL procedure successfully completed.
sys@EMCC> @features
Press return to continue
NAME DETECTED_USAGES
——————————————— —————
In-Memory Aggregation 0
In-Memory Column Store 3
sys@EMCC>
sys@EMCC> select table_name,inmemory from dba_tables where inmemory = ‘ENABLED’;
Press return to continue
TABLE_NAME INMEMORY
——————– ——–
MY_OBJ ENABLED
sys@EMCC>
What the above post show is a difference in behavior between CDB/PDB and non-CDB/PDB. That is remarkable, i would expect the same behavior, but it is not. In spite of Maria Colgan saying that in-memory option is and i quote „As you can see the INMEMORY_SIZEparameter is set to 0 and therefore Database In-Memory is not enabled, as there is no IM column store allocated.” end quote. So it must be a bug when the value in the detected_usages column related to in-memory column store is non-zero and the inmemory_size parameter is zero.
I just want to show it and i say to all whom is reading the above, test it yourself.
Best regards,
Hans
I was on vacation during the week this happened so I’ve come back and read the whole thing through. What struck me most was how emotional the whole issue became.
Ten years ago I used to look at some of the Oracle “celebrities” and be inspired by their work, the way they applied scientific method – as you have here, Kevin – to explain how something works. By simply demonstrating each step they would allow us humble regular folk to walk in their footsteps and see the same results for ourselves.
Fast forward to today and it appears that this practice is less common. Maybe I’m taking this a leap too far but it feels like some commentators in the Oracle world have too much to lose by being critical of Oracle. The ACED programme is one such example, while anyone working for an Oracle partner clearly has to consider their business relationship with the mothership. Also, while Oracle’s product managers are clearly (and acceptably) biased towards their own products, that doesn’t make their word gospel.
I’m not pointing any fingers here (and in case of confusion I want to make it specifically clear that I am *not* referring to Kerry) but it makes me sad that the old ways of scientific method and proof are being lost to rhetoric and accusations.
It might be an emotional subject for some, but for me it will always remain science: state your theory, offer your proof and then allow others to concur or refute it. So on that basis, keep up the good work Kevin – it’s good to have you back blogging.
-flashdba
The Oracle business model (with all these options you have to pay for in addition to the base cost) might be very lucrative, but is a big joke in the end. Come on, how can one seriously call “Enterprise Edition” a rdbms which comes with no partitioning option included in the base cost ? Same for the tuning features (Pay more so that we can help you to reduce sql query Cpu consumption … hum wait, our business model is cpu/core driven … Schizophrenia inside ?????). To me, “enterprise” has allways meant “You get everything”. It may be time for Oracle to introduce the “Super Enterprise” Edition ….
Don’t forget how you need to get an option to use another option as well – or it requires it implicitly. Like the new in-memory – now they have multiple compression options and then you need to buy partitioning because you won’t be able to get all the data of a large table in memory. IBM went the route of Advanced Editions to do the everything … I see the need to upwell capabilities in fairness, I think middle of the ground (as with most things) is fair to all. But yes, Oracle is odd that way. Like their Standard Edition, if you want DataGuard you HAVE to go to Enterprise. Not to be an IBM commercial, but I was pretty proud to see their Advanced Workgroup which is Enterprise capability for SMB. (Disclaimer: I do work for IBM).