Doug's Oracle Blog

Those pesky technical posts are so much more difficult to write, particularly when you realise you're about to confuse two different deadlock issues in the one post! But they're coming soon ... I promise! In the meantime, I should mention some upcoming presentations.

The first is later today and part of the ongoing series of excellent webinars hosted by the good people at Red Gate Software. This one will be on SQL Monitoring - by far the most interesting new performance analysis feature in Oracle 11g. I've presented on SQL Mon many times now so, as well as covering the basics and some of the most important features for me personally, I'll try to cover a few of the quirks and annoyances as well. It's such a practical feature that I use almost every working day that I think everyone should know more about it. It's at 4pm UK time on Wednesday and you can register here. Particularly for my Australian friends ;), Red Gate always records the sessions and the video usually appears very soon afterwards.

In April I'll be attending two of my favourite conferences in the same week - the Finnish and Norwegian Oracle User Group seminars, on their respective boats! I'm writing two new presentations for these.

The first is based on the client work I've been doing over the past 6 months or so and is called 'Fast ETL processes using Native Oracle Features'.

This is the type of presentation I haven't done for a while and should be clearly labelled Case Study or User Experiences and I'm a firm believer that its these presentations that really make a User Group conference, not just the deeply techie stuff. It will definitely be slightly technical but will be based on real work, real design requirements, real compromises, real organisational challenges (you get the idea), rather than experimentation. Just to be clear, I still like the latter, but we need some balance here. I'm also quite proud of some of the things the team have achieved in a short period of time, despite those challenges, so want to crow about it a little ;), particularly as I think that other people might be able to pick up on some of the ideas, even as a reminder of just how well Oracle can work when you use it well.

The second is about a subject dear to my heart - ASH Analytics. Although I've done the 'Pretty Pictures' version, (and I still think that is utterly compelling), I'd also like to do a slightly more technical version.

I'm not sure many of you will make it on to two boat cruises in the space of a week (although I'm sure you would be more than welcomed) but maybe I (won't) see some of you later today ...

OK, so there's a 365/1 chance of this happening (actually, I suppose Leap Years change the odds slightly) but when I decided to have a quick look at just how atrocious my blogging frequency has been over the past year, I thought I'd take a quick look back at old entries.

It's not something I do often (... and, yes, I know dates on my blog posts would be nice. It isn't like people haven't told me. I've just had other stuff to do and that one is filed under 'Migrate to WordPress'. But that isn't the point here.)

Regardless of the lack of clear dates on posts, I can inform you reliably that my very first post was on 10th February, 2005. I don't know what possessed me to try blogging again today, but it's precisely 8 years to the day since I started .... (As usual, it's hardly mind-blowing or informative )

I've been doing a lot of work around large volume data loads into an Oracle 11.2 database recently using External Tables and Parallelism and, despite the fact it's all used well-known techniques, I think it's probably worth a couple of posts to re-emphasise how successful using the right tools for the right job can be.

But first, we ran into a particular problem that threw us off-track for a few hours and this post covers that issue. I have a feeling that at least one person one day will land at this post via Google or from a vague memory of me writing about it and the hassle it saves them will make me smile!

Our application is written using a combination of C# and PL/SQL so, even as we've been implementing more functionality in the database, we still depend on scheduling software calling C# that in turn calls PL/SQL. As I was developing the data loading code, I simplified testing for myself by knocking together a basic .sql script that called the various PL/SQL procedures in the correct order. Everything looked great so I checked in my code and gave the appropriate call specifications to the C# developers to write wrapper procedures. Once that was done, we prepared to run the proper schedule and be amazed and delighted by the new performance improvements.

Unfortunately, the whole thing ran like a dog (... a rather old, sweet but overweight dog with a bad case of asthma).

When I investigated, everything was running serially. I initially thought I'd screwed something up so tried to work out what I'd done wrong but, no matter what I tried, the code used parallelism reliably when called from the basic test harness script but, as soon as we called it from the C# application, it would go back to serial. I wondered about session-level parameter settings or different user accounts but there were no identifiable differences there.

Because the performance difference was so great and we were under a lot of pressure to deliver data to the other teams, I was quite worked up by this and frustrated and there was very little out there on Google but perhaps I should have checked My Oracle Support in the first place ....

Attempting to Execute a Parallel DML Statement From an ODP.NET Application Using the APPEND or PARALLEL Hint Results in Serial Execution [ID 1370527.1]

Ah! That looked pretty similar to what we were seeing. It turned out to be a combination of the way that the Oracle RDBMS works and the default configuration of Oracle Data Provider for .Net (ODP.Net), which sets the enlist property to true. To quote the support doc - "This makes OCI calls which allows the the DML or transactions to become or be promoted to a distributed transaction." and, as documented in the generic RDBMS documentation, distributed transactions can't use Parallel DML.

As we had no requirement to use distributed transactions, the simple solution was to set enlist=false as a property in the connection string.

Bingo! Everything started running in parallel again ...

It was long but seemed to pass me by somehow.

My presentation didn't go as well as it has in the past.

Oak Table World was great fun and impressively organised.

It was great seeing so many friends again but I'd need a month-long conference to accommodate all the time I want to spend with them.

The hospitality from people like the folks at Pythian, The Oracle TechNet ACE team, Moans Longballs Nogood and too many others to mention was much appreciated.

I need to not drink for a while!

The Hives were amazing.

That's all. I'd rather write some technical posts. So thanks to Tim Hall for doing my job for me ...

... well it is for me, anyway.

I know this isn't a universal feeling, but I always look forward to going over to San Francisco for Openworld. There are so many people I'll see that I don't see often enough, lots of cool presentations (if you know where to look) and I expect this year to be a year full of announcements in the areas I care about (and probably in others, too!). I'm gutted that I'll miss the ACE Director briefing because I think I'd be particularly interested this year, but the reality is that it's difficult getting any days away from my current project, never mind a week and a half.

On top of my main agenda presentation (Tuesday, 5pm, Moscone West 3014), I've decided to do a quick 10-minute talk on Tuesday lunchtime at Oracle Closed World, an alternative event organised by Kyle Hailey with assistance from various Oak Table types and sponsored by a number of the best-known Oracle consulting companies. As well as an agenda featuring some of the best speakers around, check out Marco Gralike's cool video on the home page

This year my main task seems to be as a Party Planner, though. That's something I never thought would happen which has given me a new-found respect for all of those poor people who organise the myriad parties that will be going on that week! Still, the great thing about it is that there is a Party at the end

Can't wait ...