Hmm... You Oracle Database Heretic!

This page has been permanently moved. Please CLICK HERE to be redirected.

Thanks, Craig.

Is it OK to be an Oracle Database heretic? 

Oracle Database performance tuning and analysis has come a long way in the last 20 years. First there was the “just add more resources” approach and tuning the blatantly poor SQL. Then there was ratio analysis, followed by wait event analysis, time based analysis, and unit of work time based analysis.  (Consider searching my blog for "unit of work".) In addition to the performance diagnosis and analysis evolution, the Oracle Database as a product has changed, and architectures are more diverse. Yet with all this change, some things are in many ways timeless. They relate to complexity, basic mathematical statistics, efficiency, and doctrinal purity. Over these past few weeks, I posted three different "myths." I have recently posted about complexity (better tools), basic mathematical statistics (average SQL elapsed times) and a cloud vendor's desire for inefficient systems. This post centers on breaking fundamental Oracle Database principles in the name of improved performance.

Myth #4

The fourth myth is It is heresy to consider the impact of deviating from core relational database principles. How did James Tiberius Kirk beat the Kobayashi Maru? He changed the rules. To beat the performance game, Oracle Database sometimes allows performance analysts to change the rules as well. While this is relational heresy to some, the freedom is intoxicating!

Here is an example of what I’m talking about: Would you seriously consider allowing a commit statement to immediately return as successful when the associated redo has not been written to disk?

If you have been working with Oracle databases for many years, there are probably many concepts that we will naturally agree or disagree on … even without really thinking about it. For sure, I am a big fan of simplicity and choosing consistent performance over the hope of amazing yet inconsistent performance. But sometimes there are ways to have your cake and eat it too.

Back to my example about commits: Oracle’s commit write facility provides the ability for a commit to quickly return even though the redo has not been written to an on-line redo log. The have-your-cake aspect is that this can be applied at the instance and session—and even at the statement—level. However, committed data could be lost if a failure occurs.

A few years ago, while introducing this “feature” in my Oracle Performance Firefighting class, a student proudly announced that her company uses the commit write facility. The other students were shocked and honestly appalled. I was smiling like a Cheshire cat! She went on to explain that their application was about social networking. She posed the question, “If you are adding people to your social network and during the save-your-work process there is a failure, what would you do?” Everyone replied, “Just re-enter the information.” She replied, “Exactly! So why would a company want to spend literally millions more on hardware in the unlikely event of a failure when the business solution impact is to simply have the users re-enter a few email addresses?” There was silence and then a big smile on everyone’s face!

My point is that it may be OK to break some fundamental rules as long as the business and its users are not negatively affected. But it takes a brave person to face the immediate “you're a heretic” response. So the next time you’re facing a serious performance issue, think about a few possible ways to break the rules.

Thanks for reading!

Craig.

If you enjoy my blog, I suspect you'll get a lot out of my courses; Oracle Performance Firefighting,  Advanced Oracle Performance Analysis, and my One-Day Oracle Performance Research Seminar. I teach these classes around the world multiple times each year. For the latest schedule, go to www.orapub.com . I also offer on-site training and consulting services.

P.S. If you want me to respond to a comment or you have a question, please feel free to email me directly at craig@orapub .com. Another option is to send an email to OraPub's general email address, which is currently orapub.general@comcast .net.