Impugn My Character Over Technical Points–But You Should Probably Be Correct When You Do So. Oracle 12c In-Memory Feature Snare? You Be The Judge ‘Cause Here’s The Proof.
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.
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.Index of Related Posts
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.What Really Matters?
This is a post about enabling versus using the Oracle Database 12c Release 126.96.36.199 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 188.8.131.52–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 from friends on the Oaktable Network list as they asserted I was making a mole hill out of something that was a total lark (that was a euphemistic way of saying they all but accused me of misleading my readers). I even had friends suggesting this is a friendship-ending issue. Emotion and high-technology are watery-oil like in nature.
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 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 top-level-sounding INMEMORY_QUERY parameter. As such this should be the parameter that would prevent you for paying for usage of the feature.
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 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:
I didn’t want to make a mole hill of this one. 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.
Filed under: oracle
Oracle Database 12c Release 184.108.40.206 – My First Observations. Licensed Features Usage Concerns – Part II.
In this post you’ll see that I provide an scenario of accidental paid-feature “use.” The key elements of the scenario are: 1) I enabled the feature (by “accident”) but 2) I didn’t actually use the feature because I neither created nor altered any tables.
In Part I of this series I aimed to bring to people’s attention what I see as a significant variation from the norm when it comes to Oracle licensed-option usage triggers and how to prevent them from being triggered. Oracle Database Enterprise Edition supports several separately licensed options such as Real Application Clusters, Partitioning, and so on. A feature like Real Application Clusters is very expensive but if “accidental usage” of this feature is a worry on an administrator’s mind there is a simple remedy: unlink it. If the bits aren’t in the executable you’re safe. Is that a convoluted procedure? No. An administrator simply executes make -d ins_rdbms.mk rac_off and then relinks the Oracle executable. Done.
What about other separately licensed options like Partitioning? As I learned from Paul Bullen, once can use the Oracle-supplied chopt command to remove any chance of using Partitioning if, in fact, one does not want to use Partitioning. I thought chopt might be the solution to the issue of possible, accidental usage of the In-Memory Column Store feature/option. However, I found that chopt, as of this point, does not offer the ability to neutralize the feature as per the following screenshot.
There is yet no way I know of to prevent accidental use of the In-Memory Column Store feature/option. Am I just making a mountain out of a mole hill? I’ll let you be the judge. And if you side with folks that do feel this is a mountainous-mole hill you’d be in really good company.
Lest folks think that we Oaktable Network Members are a blind, mutual admiration society, allow me to share the rather sizzling feedback I got for raising awareness to this aspect of Oracle Database 12c:
No, I didn’t just want to dismiss this feedback. Instead I pushed the belt-sander off of my face and read the words a couple of times. The author of this email asserted I’m conveying misinformation ( aka “BS”) and to fortify that position it was pointed out that one must:
- Set a database (instance initialization) parameter.
- Bounce the instance.
- Alter any object to use the feature. I’ll interpret that as a DDL action (e.g., ALTER TABLE, CREATE TABLE).
Even before I read this email I knew these assertions were false. We all make mistakes–this I know! I should point out that unlike every release of Oracle from 5.1.17 to 11gR2 I was not invited to participate in the Beta for this feature. I think a lot of Oaktable Network members were in the program–perhaps even the author of the above email snippet–but I don’t know that for certain. Had I encountered this during a Beta test I would have raised it to the Beta manager as an issue and maybe, just maybe, the feature behavior might have changed before first customer ship. Why am I blabbering on about the Beta program? Well, given the fact that even Oaktable Network members with pre-release experience with this feature evidently do not know what I’m about to show in the remainder of this post.What Is An Accident?
Better yet, what is an accident and how full of “BS” must one be to fall prey? Maybe the remainder of the post will answer that rhetorical question. Whether or not it does, in fact, answer the question I’ll be done with this blog series and move on to the exciting work of performance characterization of this new, incredibly important feature.Anatomy of a “Stupid Accident.”
Consider a scenario. Let’s say a DBA likes to use the CREATE DATABASE statement to create a database. Imagine that! Let’s pretend for a moment that DBAs can be very busy and operate in chaotic conditions. In the fog of this chaos, a DBA could, conceivably, pull the wrong database instance initialization file (e.g., init.ora or SPFILE) and use it when creating a database. Let’s pretend for a moment I was that busy, overworked DBA and I’ll show you in the following happeneds:
- I executed sqlplus from the bash command prompt.
- I directed sqlplus to execute a SQL script called cr_db.sql. Many will recognize this as the simple little create script I supply with SLOB.
- The cr_db.sql script uses a local initialization parameter file called create.ora
- sqlplus finished creating the database. NOTE: this procedure does not create a single user table.
- After the database was created I connected to the instance and forced the feature usage tracking views to be updated (thanks to Morgan’s Library for that know-how as well…remember, I’m a database platform engineer not a DBA so I learn all the time in that space).
- I executed a SQL script to report feature usage of only those features that match a predicate such as “In-%’
This screen shot shows that the list of three asserted must-happen steps (provided me by a fellow Oaktable Network member) were not, in fact, the required recipe of doom. The output of the features.sql script proves that I didn’t even need to create a user table to trigger the feature.
The following screen shot shows what the cr_db.sql script does:
The following screenshot shows the scripts I used to update the feature usage tracking views and to report against same:
Stepping on a landmine doesn’t just happen. You have to sort of be on your feet and walking around for that to happen. In the same vein, triggering usage of the separately licensed Oracle Database 12c Release 220.127.116.11 In-Memory Column Store feature/option required me to be “on my feet and walking around” the landmine–as it were. Did I have to jump through hoops and be a raging, bumbling idiot to accidentally trigger usage of this feature? No. Or, indeed, did I issue a single CREATE TABLE or ALTER TABLE DDL statement? No. What was my transgression? I simply grabbed the wrong database initialization parameter file from my repository–in the age old I’m-only-human sort of way these things often happen.
To err to such a degree would certainly not be human, would it?
The following screenshot shows the parameter file I used to prove:
- You do not need to alter parameters and bounce an instance to trigger this feature usage in spite of BS-asserting feedback from experts.
- You don’t even have to create a single application table to trigger this feature usage.
This blog thread has made me a feel a little like David Litchfield must have surely felt for challenging the Oracle9i-era claims of how Oracle Database was impenetrable by database security breaches. We all know how erroneous those claims where. Unbreakable, can’t break it, can’t break in?
Folks, I know we all have our different reasons to be fans of Oracle technology–and, indeed, I am a fan. However, I’m not convinced that unconditional love of a supposed omnipotent and omniscient god-like idol are all that healthy for the IT ecosystem. So, for that reason alone I have presented these findings. I hope it makes at least a couple of DBAs aware of how this licensed feature differs from other high-dollar features like Real Application Clusters in exactly what it takes to “use” the feature–and, moreover, how to prevent stepping on a landmine as it were.
…and now, I’m done with this series.
Filed under: oracle
Oracle Database 12c Release 18.104.22.168 – My First Observations. Licensed Features Usage Concerns – Part I.
My very first words on Oracle Database 12c Release 22.214.171.124 can be summed up in a single quotable quote:
This release is hugely important.
I’ve received a lot of email from folks asking me to comment on the freshly released In-Memory Database Option. These words are so overused. This post, however, is about much more than word games. Please read on…
When querying the dba_feature_usage_statistics view the option is known as “In-Memory Column Store.” On the other hand, I’ve read a paper on oracle.com that refers to it as the “In-Memory Option” as per this screen shot:
None of us can forget the era when Oracle referred to the flash storage in Exadata as a “Database In-Memory” platform. I wrote about all that in a post you can view here: click this. But I’m not blogging about any of that. Nonetheless, I remained confused about the option/feature this morning as I was waiting for my download of Oracle Database 12c Release 126.96.36.199 to complete. So, I spent a little time trying to cut through the fog and get some more information about the In-Memory Option. My first play was to search for the term in the search bar at oracle.com. The following screen shot shows the detritus oracle.com returned due to the historical misuse and term overload–but, please, remember that I’m not blogging about any of that:
As the screenshot shows one must eyeball their way down through 8 higher-ranking search results that have nothing to do with this very important new feature before one gets to a couple of obscure blog links. All this term overload and search failure monkey-business is annoying, yes, but I’m not blogging about any of that.What Am I Blogging About?
This is part I in a short series about Oracle licensing ramifications of the In-Memory Option/In-Memory Column Store Feature.
The very first thing I did after installing the software was to invoke the SLOB database create scripts to quickly get me on my way. The following screen shot shows how I discovered that the separately-licensed In-Memory Option/In-Memory Column Store Feature is enabled by default:
Now, this is no huge red flag because separately-licensed features like Real Application Clusters and Partitioning are sort of “on” by default. This doesn’t bother me because a) one can simply unlink RAC to be safe from accidental usage and b) everyone that uses Enterprise Edition uses the Partitioning Option (I am correct on that assertion, right?). However, I think things are a little different with the In-Memory Option/In-Memory Column Store Feature since it is “on” by default and a simple command like the one in the following screen shot means your next license audit will be, um, more entertaining.
OK, please let me point out that I’m trying as hard as I can to not make a mountain out of a mole-hill. I started this post by stating my true feelings about this release. This release is, indeed, hugely important. That said, I do not believe for a second that every Enterprise Edition deployment of Oracle Database 12c Release 188.8.131.52 will need to have the In-Memory Option/In-Memory Column Store Feature in the shopping cart–much unlike Partitioning for example. Given the crushing cost of this option/feature I expect that its use will be very selective. It’s for this reason I wanted to draw to people’s attention the fact that–in my assessment–this option/feature is very easy to use “accidentally.” It really should have a default initialization setting that renders the option/feature nascent–but the reality is quite the opposite.Summary
I have to make this post short and relegate it to part I in a series because I can’t take it to the next level which is to write about monitoring the feature usage. Why? Well, as I tweeted earlier today, the scripts most widely used for monitoring feature usage are out of date because they don’t (yet) report on the In-Memory Column Store feature. The scripts I allude to are widely known by Google search as MOS 1317265.1. Once these are updated to report usage of the In-Memory Option/In-Memory Column Store Feature I’ll post part II in the series.
Filed under: oracle
When Storage is REALLY Fast Even Zero-Second Wait Events are Top 5. Disk File Operations I/O: The Mystery Wait Event.
The SLOB code that is generally available here differs significantly from what I often test with in labs. Recently I was contorting SLOB to hammer an EMC XtremIO All-Flash Array in a rather interesting way. Those of you in the ranks of the hundreds of SLOB experts out there will notice two things quickly in the following AWR snippet:
1) Physical single block reads are being timed by the Oracle wait interface at 601 microseconds (3604/5995141 == .000601) and this is, naturally for SLOB, the top wait event.
2) Disk file operations I/O is ranking as a top 5 timed event. This is not typical for SLOB.
The 601us latencies for XtremIO are certainly no surprise. After all, this particular EMC storage array is an All-Flash Array so there’s no opportunity for latency to suffer as is the case with alternatives such as flash-cache approaches. So what is this blog post about? It’s about Disk file operations I/O.
I needed to refresh my memory on what the Disk file operations I/O event was all about. So, I naturally went to consult the Statistics Description documentation. Unfortunately there was no mention of the wait even there so I dug further to find it documented in the Description of Wait Events section of the Oracle Database 11g documentation which states:
This event is used to wait for disk file operations (for example, open, close, seek, and resize). It is also used for miscellaneous I/O operations such as block dumps and password file accesses.
Egad. A wait is a blocking system call. Since open(2)/close(2) and seek(2) are non-blocking on normal files I suppose I could have suffered a resize operation–but wait, this tablespace doesn’t allow autoextend. I suppose I really shouldn’t care that much given the fact that the sum total of wait time was zero seconds. But I wanted to understand more so I sought information from the user community–a search that landed me happily at Kyle Hailey’s post on oaktableworld.com here. Kyle’s post had some scripts that looked promising for providing more information about these waits but unfortunately in my case the scripts returned no rows found.
So, at this point, I’ll have to say that the sole value of this blog post is to point out the fact that a) the Oracle documentation specifically covering statistics descriptions is not as complete as the Description of Wait Events section and b) the elusive Disk file operations I/O wait event remains, well, elusive and that this is now part I in a multi-part blog series until I learn more. I’ll set up some traces and see what’s going on. Perhaps Kyle will chime in.
Filed under: oracle
This is the first installment in a series of posts I’m launching to share interesting use cases for SLOB. I have several installments teed up but to put a spin on things I’m going to hit two birds with one stone in this installment. The first bird I’ll hit is to introduce a friend and colleague, Bart Sjerps, who I just added to my blogroll. The other bird in my cross-hairs is this interesting post Bart wrote some time back that covers a study of ZFS fragmentation using SLOB.
As always, please visit the SLOB Resources Page for SLOB kit and documentation.
Filed under: Silly Little Oracle Benchmark, SLOB, ZFS, ZFS Performance
EMC XtremIO – The Full-Featured All-Flash Array. Interested In Oracle Performance? See The Whitepaper.
NOTE: There’s a link to the full article at the end of this post.
I recently submitted a manuscript to the EMC XtremIO Business Unit covering some compelling lab results from testing I concluded earlier this year. I hope you’ll find the paper interesting.
There is a link to the full paper at the bottom of this block post. I’ve pasted the executive summary here:Executive Summary
Physical I/O patterns generated by Oracle Database workloads are well understood. The predictable nature of these I/O characteristics have historically enabled platform vendors to implement widely varying I/O acceleration technologies including prefetching, coalescing transfers, tiering, caching and even I/O elimination. However, the key presumption central to all of these acceleration technologies is that there is an identifiable active data set. While it is true that Oracle Database workloads generally settle on an active data set, the active data set for a workload is seldom static—it tends to move based on easily understood factors such as data aging or business workflow (e.g., “month-end processing”) and even the data source itself. Identifying the current active data set and keeping up with movement of the active data set is complex and time consuming due to variability in workloads, workload types, and number of workloads. Storage administrators constantly chase the performance hotspots caused by the active dataset.
All-Flash Arrays (AFAs) can completely eliminate the need to identify the active dataset because of the ability of flash to service any part of a larger data set equally. But not all AFAs are created equal.
Even though numerous AFAs have come to market, obtaining the best performance required by databases is challenging. The challenge isn’t just limited to performance. Modern storage arrays offer a wide variety of features such as deduplication, snapshots, clones, thin provisioning, and replication. These features are built on top of the underlying disk management engine, and are based on the same rules and limitations favoring sequential I/O. Simply substituting flash for hard drives won’t break these features, but neither will it enhance them.
EMC has developed a new class of enterprise data storage system, XtremIO flash array, which is based entirely on flash media. XtremIO’s approach was not simply to substitute flash in an existing storage controller design or software stack, but rather to engineer an entirely new array from the ground-up to unlock flash’s full performance potential and deliver array-based capabilities that are unprecidented in the context of current storage systems.
This paper will help the reader understand Oracle Database performance bottlenecks and how XtremIO AFAs can help address such bottlenecks with its unique capability to deal with constant variance in the IO profile and load levels. We demonstrate that it takes a highly flash-optimized architecture to ensure the best Oracle Database user experience. Please read more: Link to full paper from emc.com.
Filed under: All Flash Array, Flash Storage for Databases, oracle, Oracle I/O Performance, Oracle performance, Oracle Performnce Monitoring, Oracle SAN Topics, Oracle Storage Related Problems