I'll Pass On The Black Box

As others have pointed out over the past few days, I sometimes tend to drill deep into details (check the last blog post for a great example). They're right, but I have good cause for the drilling. It's because I don't buy into the black box.

What's the black box? It's the idea that you can successfully work with a technology without understanding it. It's a conscious decision to shield those people building and using technology from the "complexities" of the technology they're using.

I'm not a fan of the black box concept. In fact, I'm pretty insistent on building up my own knowledge and the knowledge of those around me in regards to the technologies we use. One example: My children all started to drive cars in their teens. Before allowing them to drive, I insisted that they learn the basic technology of a car. Check and add oil, change the air filter, inspect the battery posts, change the tires...that sort of thing. Because they're much more likely to be good and independent drivers if they know how the car works, especially when the steam starts to come out from under the hood on a hot summer night miles from civilization. None of my kids drove until they passed my test.

My personal opinion here: most failures with technology-related projects are rooted in a lack of fundamental knowledge about the technology used. I see it constantly and you probably do too.

Another example: think about how often we talk about protecting developers from the complexities of the database. Really? I'm thinking I want the developers to be experts on the database, so that it can be leveraged to build great solutions.

I feel the same way about Integrated Development Environments (IDEs). Love the efficiency of the IDE concept, but now we're in danger of creating a generation of developers who only know IDEs. Such developers will eventually run into something they're unable to solve; they'll have to find an "expert" who can work outside the IDE. To build great solutions, we must understand the detailed technology behind the IDE.

The bottom line: I think you have to understand the technology you're using before you can use it well. So I'll pass on the black box myself. How about you?

SQL Tuning - Low Hanging Fruit

Helped an old client out with some SQL tuning issues today. They've moved some of their E-Business mod plsql customizations over to APEX in preparation for an upgrade to R12.1.3, but they ran into some performance issues. Turned out to be a very basic issue but, with some many customers in the midst of undertaking this kind of thing, I thought it might be helpful to have a basic review of SQL processing. Because close to 70 percent of the tuning issues I encounter with Oracle databases are caused (at least in part) by issues with SQL statements.

So, without getting too deep into the weeds here, let's look at what happens when a SQL statement is processed for the first time: hard parsing and execution.

Parsing generally consists of the following steps:

1. Check the incoming SQL statement for syntax errors

2. Validate referenced objects for existence and accessibility

3. Generate basic parse tree structure

4. Determine the best execution query plan for the statement based on the Cost Based Optimizer (and CBO is way beyond the scope of this discussion - find more here).

After parsing, the statement is executed according to the plan. While that sounds pretty mundane, there is an important process from a performance perspective that takes place during execution: a cursor (a pointer to the parsed plan that can be shared and executed by multiple other SQL statements, even in multiple sessions) is created and stored in the library cache.

Now why is a cursor so important to the performance of SQL statements? Because ti smooths the way for subsequent SQL statements that are identical in nature. The parsing of a statement is where all the heavy lifting is done - it's a CPU-intensive operation and a drain on database server resources. But when the database server is able to match a submitted SQL statement to a cursor in the library cache, most of the heavy lifting of the hard parse is eliminated…utilizing an already parsed plan on a different query is called a soft parse. It's a lot like using an existing wheel design rather than reinventing the wheel.

So, while all this technical overview stuff is well and good, how can you leverage it? Well, one idea that immediately comes to mind (and one of my favorite checkpoints when tuning SQL statements) is using bind variables. A bind variable is a placeholder in a query. An example:

Coding in a hard value yields a select statement like:

Select * from Supplier where Supplier_ID = 654;

If I worked a LOOP into my query to cover a range of hard coded values, my SQL statement would go through a hard parse each time it ran through a cycle of the loop: painfully slow and potential reducing my system to the speed of molasses…running uphill…in the winter.

If using a bind variable, I would write the Select statement as follows:

Select * from Supplier where Supplier_ID = :suppid

I'm using ":suppid" as my bind variable. Now when I use LOOP logic, I can generate a range of values for my query results. Each cycle of the loop will now leverage the cursor created during the hard parse of the first cycle of the query. Significant performance gains over the hard-coded statement.

How much improvement? Your mileage will vary according to your hardware and software environment. But just for the sake of discussion… My Oracle database test bed is on a CentOS Linux VM, running on a duo-core iMac with 4GB of ram. If compare the performance results of two queries similar to the examples shown above, the bind variable yields results about 10 times faster than the hard-coded example. What that should tell you is that the statement with the hard-coded value spends much, much, much more time parsing than actually executing my query. Again, your mileage could vary significantly.

So, where is this story going? When my old client (and friend) called for a little quick help today with some performance tuning, it didn't take long for me to start checking his SQL. Want to guess the first issue I found? Yup, statements with hard-coded values. After tweaking the code to use bind variables, we picked up performance by orders of magnitude. Easy win, low-hanging fruit, just by applying a fundamental concept.

And we won't even talk today about the scalability gains picked up by reducing the latching time through avoiding hard parses.

So, for all you E-Business customers with mod plsql and other SQL-based customizations out there (and I KNOW that there are bunches of you out there…we've all done it to some extent), check your code as one of your upgrade preparation steps. Hard coded values, full table scans…you might be amazed at the low-hanging fruit you can pick.

Got some good tuning stories? Share in the comments.

Fusion Applications - By The 3's

My thoughts are coming at me in sets of three today…

I've been getting peppered with folks asking about whether or not they should take on Fusion Applications. Some are current Oracle customers and some are not…but I'm not sure that matters too much. The way I see it, different people will describe the value proposition for Fusion Apps in different ways, but it really all falls into finding value in one or more of three categories: the User Experience, the Applications Platform, and the Technology Adoption.

By the way, I freely admit that I stole the three categories from a slide shown repeatedly at OpenWorld this year, but the thoughts are my own...

User Experience

Anybody who reads here on a regular basis or talks to me for more than five minutes knows that I see the user experience (UX) as the biggest win in Fusion Apps. Three big reasons:

1) The UX is designed around shallow navigation, meaning the stuff you need to see is generally displayed on one page…fewer clicks. Lead to higher productivity for the people making their living with the apps.

2) The user interface is uses or is similar to tools we already know: the browser, Excel, instant messaging. Familiar look and feel…more productivity benefits.

3) Business Intelligence is cooked right into the apps. No leaving ERP apps to take a look at BI apps…but set that little blessing aside for a moment. The big win here is that you can leverage the BI elements to start managing by exception. Present the transaction exceptions to the analyst or specialist or whatever the correct title is this week rather than making them search for those exceptions…and let them fix those exceptions. Hmmm, analysis and management of exceptions…isn't that really the high-value activity we get from those analysts or specialist? Wow, this could actually free those folks up to do the job the boss wanted them to do when they were hired!

Applications Platform

Some folks (including me at times) really get pumped up at the thought that Fusion Apps are built with Oracle's Fusion development tools and run on Fusion MIddleware. But, to be honest, the folks with the checkbooks (the CFOs, the CEOs) couldn't care less. But they will care about three things:

1) Industry standards, which can actually work against vendor lock-in (including the redstack itself). One of the swell things about industry standards is that I can replace Oracle's industry-standard compliant components with another industry-standard compliant component…even some of that appealing open-source stuff. Replace JDeveloper with Eclipse. I've had good luck with using ActiveBPEL Designed with Oracle's Apps Server. And my E-Business test bed runs on CentOS Linux. So there's competition at the component level, and that's a good thing for both pricing and progress.

2) SOA. I personally like the SOA approach. We can get into discussions about Web Services versus REST and all sorts of nifty techie stuff, but that's not where the real appeal is for me. It's about reuse. One of the best ways to reduce costs in any effort is to build something once and use it many times. That's what services are all about. Build a service once and reuse it in many different processes for many different types of consumers. Play out this scenario to your CFO and watch the eyebrows arch with interest.

3) Getting back to industry standards, but with a point that merits individual consideration: loose coupling. The idea here is that if I build integration between two subsystems and one of those subsystems changes, the other subsystems and the integration I built will still work just fine. That's a huge cost saver when you're upgrading, or ripping and replacement, modules in your system. The applications platform in Fusion Apps leverages industry standards and SOA to make loose coupling the rule rather than the exception. More cost savings, greater flexibility - another nice case for the CFO's consideration.

Technology Adoption

Now, I know what you're thinking…what's the value to you just because Oracle is adopting some cool technology? In fact, doesn't that cost you money in the form of upgrading/relicensing? Hang with me through these three points and you'll get a feel for my perspective:

1) Think Modularity, which really ties back to that loose coupling argument I made earlier. Because of the technology upgrade, which supports loose coupling and industry standards, I can now uptake Fusion Applications on a modular basis: the apps are designed to co-exist with applications you already have in place. So if you have PeopleSoft HR, but like the Talent Management application in the Fusion Apps Human Capital Management Suite, it's doable. Ditto for Fusion Apps Project Portfolio Management with E-Business Financials. We're talking incremental uptake…moving forward in small steps. Less expensive, less disruptive moves forward than a "big bang"…appealing in this economic environment.

2) Cloud or SaaS. Oracle's been pounding this drum pretty hard lately, so I don't need to spend much time here. But think of this: If I can uptake incrementally, and I have interest in transitioning to a Cloud (maybe even inside my own firewall) or to Software-as-a-Service (either with Oracle or a 3rd-party), maybe it makes sense for me to go there incrementally? See the notes above on less expensive, less disruptive, etc.

3) Choices. Technology adoption gives you choices…falling behind the curve limits those choices. A non-Oracle example…my daughter bought an iPhone 3G a few months ago. Her idea was to jump on an iPhone and save a few bucks. Problem is that, just within a few months, the technology has passed her by: many apps won't run on her 3G iPhone (the apps need a 3Gs or a 4i). Fewer choices. Ditto for early adopters of Android phones, so don't blame it on Apple…it's just the nature of technology. My old Intel Pentium laptop is only good for Linux now…it can't run the latest Windows releases anymore. Fewer choices as technology moves on. Now here's the thing…as the choices wane, the higher the cost (effort plus dollars) of getting current - even if the dollar cost eventually drops, the effort continues to grow. Had I not upgraded that laptop several years ago, the effort of upgrading and moving all that data would be tremendous. Upgrading to E-Business 12.1.x from 11.5.10 is significantly less effort than upgrading from 11.5.4. Technology adoption provides more choices, and the competition amongst the choices keeps the cost of each choice lower - Adam Smith's "invisible hand" in action.

So, in a post-Oracle OpenWorld 10…after the Fusion Apps product launch, that's how I see it. Now your mileage could vary - you'll have to figure out the value in your own words for your specific situation. I just hope I gave you something to think about…in threes!