Open Insights 13 – Oracle Heavy Lifting

Heavyweight Internet Group Newsletter

Issue 13 – Oracle Heavy Lifting

November 1, 2005

by Sean Hull

Founder and Senior Consultant

Heavyweight Internet Group

Welcome to our free monthly newsletter, discussing news, developments and business best practices at the intersection of Oracle and Open Source software.

Please forward to interested friends and colleagues. Subscription information can be found at the end.

We are now writing material regularly for our sister site Oracle + Open Source so check there for more frequent updates.

Oracle Heavy Lifting

In consulting, I have the unique opportunity to both think deeply about technology, how it works, and what it can do, and also about business questions, what does a business need to do, and how will technology help it achieve greater returns. I just finished pouring over Tom Kyte’s new Expert Oracle title. It is an excellent book, full of plenty of deep insights about Oracle technology. As I was reading the chapters, I noted in particular which features required enterprise edition, and which are included in standard edition. You can find my chapter by chapter review on Amazon.

As I’m engaged with many different types of clients and businesses, I have the opportunity to see Oracle in various real-world situations, solving real business problems. I came to a profound realization that most enterprises decide how much to spend on Oracle in proportion to (a) the business problem & use they are putting it to and (b) how expensive the hardware and consulting services are. In other words the expense is relative to the budget constraints, and in proportion to the problem it is solving. However, with my technology hat on I realized that though it might seem like a rational decision from the business side, you may not be doing all the heavy lifting you think you’re doing. Put another way Enterprise Edition may not be what you need, or at least more than you’re using.

Without going into a tremendous amount of technical detail, I just wanted to outline the situations where Enterprise Edition would likely be worth the money, and when it may be overkill. When Standard Edition sits at roughly 10-15% of Enterprise Edition, that’s certainly something a business should consider.

A traditional way to separate database types is into two categories, Data Warehouse and OLTP. A Data Warehouse is characterized by very few users, doing large long running queries. OLTP or OnLine Transaction Processing is characterized by hundreds of concurrent users, such as a database which serves as a backend datastore to a website. An Oracle database can be tuned to prioritize and use resources most efficiently for each type of database.

So what does this have to do with Enterprise or Standard Edition. Well as it turns out Enterprise Edition proves a very attractive option for Data Warehouses. There are features such as streams, transportable tablespaces, materialized views, advanced analytical functions, and compression to name a few. None of these features are included in Standard Edition. Additionally there are other EE options which are attractive for Data Warehouses including Partitioning, and various ways to parallelize operations to help a single session consume the resources of the entire machine. These parallel operations can speed up dataloads, rebuilds, batch jobs, and large queries against even terabytes of data.

But what about Transactional environments (OLTP)? Many businesses use Oracle as a backend datastore for their online presence, such as banks, bookstores, airlines, and just about any other dynamic website. Well there are some features such as Fine Grained Access, Virtual Private Database, and Secure Roles which aren’t available in Standard Edition. There are also the Tuning, Change Management, and Diagnostics packs that are add-on options only available with Enterprise Edition. But many folks are just looking for speed and high availability features. Will Enterprise Edition help in this area? Well you only have Data Guard in Enterprise Edition, but databases can be cloned, and that process can be scripted, and although not seemless, and an integrated feature as Data Guard is, something similar can be done in Standard Edition. So how about the Parallel Features, and the Partitioning option. Will those help with Transactional databases? Let’s start with partitioning, it is a feature which may or may not be heavily used in your environment, but if your application is OLTP, chances are you’re not getting dramatic query improvements, because your queries are characterized by small frequent accesses by lots of concurrent users. Now partitioning will help you with maintenance and availability, but in that sense it serves more as a DBA tool, than a performance boost overall. What about the parallel operations. Again mostly for DBA operations, one-off rebuilds and dataloads can be made to run quite a lot faster but the day to day operation of your database may not be dramatically impacted by this feature.

With Oracle’s recent announcement of it’s Express Edition of 10g as well as it’s purchase of Innobase a commercial component of MySQL providing solid transactional support, your options are wider and more complex than ever before. Your best bet navigating this complex landscape is to get an accessment of your current infrastructure, or do sufficient research before investing in that new project. Like buying a car, ask lots of questions, don’t believe everything you hear, and kick the tires before you buy. Both Standard and Enterprise Edition can be downloaded for single-instance development use & testing off of Oracle Technology Network.

Technical Articles at IHEAVY.COM

Coming soon… A Hitchhiker’s Guide to Oracle 10g RAC

Note: The following articles will be returning to soon. We’ve done some redesign and rebuilding, and have to add these again…

Tracking the Wily Proxy Hackers

Asterisk Calling Card Applications

MySQL Disaster Recovery

Dummy’s Guide to Linux firewalls

Wireless Truth or Dare

Mirroring for the Impatient