Top 3 Oracle Features of the Decade

Yesterday I made an early break for the annual New Year resolutions post.

However, there’s another end-of-year post that’s up for consideration in this particular year and, rather than looking forward to the coming year, that’s a look back over the past 10, 2000-2010.

So, I ask you what are your top 3 Oracle RDBMS features over the past decade.

Oracle 8i was 1999 so we’re really talking about:

  1. 9iR1 in 2001
  2. 9iR2 in 2002
  3. 10gR1 in 2003
  4. 10gR2 in 2005
  5. 11gR1 in 2007
  6. 11gR2 in 2009

A Top 3 is very, very challenging when you think of some of the enhancements which have come along.

Obviously it depends on your own, personal perspective – naturally, mine are going to have a Development bias.

Each version fixes a significant number of bugs from the previous release, but I seem to remember 8i being a rush job for the buzzwords ahead of the new millenium (the early patches at least), 9iR1 a hatchet job and 9iR2 being a relative bastion of stability.

Then again it’s also a long time ago now and my memories may be unreliable.

The New Features Guides of 9i (links above) lists amongst others:

  • LogMiner improvements, DataGuard, RAC, Flashback Query, Some online redefinition & reorganisation features, VPD enhancements, Automatic Undo Management, dynamic memory management, spfles, RMAN improvements, native XML functionality and XMLDB, the MERGE statement, TIMESTAMPs and INTERVALs, CASE statement, External Tables, associative arrays/index-by tables indexed by VARCHAR2, Streams, CDC, Index Skip Scans, ANSI SQL, OMF, multiple blocksizes, dynamic sampling, table compression, subquery factoring, pipelined table functions, etc.

A list of new features from 10g (or my list at least) is shorter, but even so it seems a bigger hitter in terms of weighty marketing-savvy acronyms and features:

  • ADDM, ASH, ASM, AWR, Automatic SQL Tuning, DataPump, Job Scheduler, SQL Access Advisor, HTMLDB, Online Table Redefinition, Oracle XE, DBMS_XPLAN…

11g has always felt like 10gR3. Nevertheless, there have been still some impressive features therein:

  • Adaptive Cursor Sharing, Result Cache, Database Resident Connection Pool Caching, Invisible Indexes, SecureFiles, Binary XML…

And then recently Exadata and the Oracle Database Machine (although I think it’s too soon to make any judgement on these and if they have a big impact then it will be mostly in the next decade).

For the summaries above, I’ve missed out a lot (probably accidentally – let me know – but I was rushing towards the end).

But I’ve focused on the initial introductions of headline new features.

However, I would argue that the gradual evolution of some of the main features and functionality has had some of the biggest impact as we are now at the end of the decade compared to the end of 1999. In addition, some of the tweaks and internals have also had a big impact whilst avoiding the headlines – mutexes for example.

Think about how the CBO has moved on over the past ten years.

Or how HTMLDB has morphed and moved on to APEX.

Or how parallel capabilities have developed. Or partitoning.

Maybe you like your GUIs like OEM.

Or the evolution of IDEs has been revolutionary for you e.g. SQL Developer, or the explosion of functionality in third party tools like Toad or Quest Code Tester for Oracle.

Or the combination of several features – external tables plus pipelined functions are pretty cool for loading in data, for example. Or partitioning plus parallel.

All of these are up for consideration.

What’s not up for consideration is stuff that predates the decade. There are several features which I thought of while doing this and which turned out to be introduced in 8i or even before. Man, tempus fugit…

So, my Top 3 is based on what I do or use in Oracle most days (or even what I don’t have to do anymore) because of features introduced or evolved over the past decade. The evolution of collection functionality in SQL and PLSQL came close. A bit further behind was some of the XML capabilities. A whole host of ineligible features from older versions were scrubbed out. However, in no particular order, my vote goes to:

  • Analytics – It’s amazing how many time I end up using analytic functions. As Tom Kyte says “Analytics rock, Analytics roll”. Countless multipasses of data have been avoided with these babies.(Analytics ruled ineligible as an 8i introduction, dammit)
  • The WITH clause aka Subquery Factoring – Most of my sql, if it has any sort of complexity, ends up using this feature; fantastic for breaking up and understanding a complex bit of sql that you’ve never seen before; great at encouraging you to think in SETS.
  • AWR / ASH / Wait model evolution – Shame AWR & ASH are licensed separately and there are alternatives for earlier Oracle versions or if you don’t want to fork out the big bucks. But so useful for diagnosing performance problems, particularly retrospectively and/or identifying session problems which might have drowned previously in a system-level Statspack.
  • DBMS_XPLAN – Another feature that I use day in, day out. So easy to get explain plans, actual plans from memory or from AWR. Invaluable!

I would imagine that if you were a production DBA then the evolution of RMAN and Flashback has been fantastically useful and time saving.

These are not things that I’ve tended to use much in my role.

But if something’s revolutionised your decade, let me know.


5 Responses to Top 3 Oracle Features of the Decade

  1. Gary says:

    Analytics were present in 8i, so shouldn’t really be included. However I’d vote for the integration of the SQL parser into PL/SQL in 9iR1. 8i was very frustrating in not allowing valid SQL in procedures.

    Apex and the embedded PL/SQL gateway was a major milestone, but pipped to my number 2 spot by XE. Small footprint, free Oracle for my desktop.

  2. Noons says:

    To me, these are fundamental:

    Analytics, without a doubt: they help coding so much it’s not even fun

    The evolution of partitioning and its integration with the CBO: ssential for any high volume dbs, as well as data archiving

    RMAN evolution together with the transportable tablespaces and data pump: they made moving data between dbs for testng and development so much easier it’s a no contest

    Now, if Oracle would fix some small details…
    Like some of the most egregious stability bugs as well as things like “why can’t we grant truncate without granting drop any table as well”, life would be truly marvelous!

  3. dombrooks says:

    Gary – thanks but damn you. Not another one that was from 8i. I’ll have to have a rethink.

    If I keep focused on features that I use day in day out now, then I might have to sub in DBMS_XPLAN.
    But it makes my list a bit lightweight.

    XE is an interesting one. I don’t think of it as having had a particularly big impact.

    Noons – thanks. It doesn’t surprise me about RMAN. If I was being objective, it would be right up there along with partitioning and other functionality wich has steadily developed over time.

  4. Pingback: DBMS_XPLAN display issues « OraStory

  5. Graham says:

    We can only have 3 choices? that’s TOUGH!

    I’ve got to have flashback in there, I don’t use it often, but when I do it saves soooo much effort.

    AWR is a must for me too.

    Finally I’ll take the improvments in Dataguard over the years.

    What’s most interesting to me is that if you’d asked me the same question whilst at my previous job (more dev than prod) I would have given you a completely different set of features.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: