Doug's Oracle Blog

I submitted my slides last Friday so the Hotsos Symposium countdown has really started now - I'll be presenting in just under 4 weeks time. I'm looking forward to it even more than I was for a couple of reasons.

1) I noticed from Kerry Osborne's blog post that he's now doing two presentations. I was looking forward to seeing Kerry present anyway and it looks like his second presentation is about some of the AWR scripts that he's written and I find myself returning to all the time and passing on to other on-site.

2) The good folk at Hotsos took pity on me and my sick server, which has been throwing out mysterious I/O errors intermittently over the past few months while I've been trying to test Parallel Query in 11.2. In the end, I just felt that I didn't have enough decent material for me to feel comfortable presenting it so they're letting me do my Pictures presentation instead I've only ever done it once in the U.S. and there were about 10 people in the room, most of whom I knew (!), so it's still pretty new and I'm enjoying including the 11.2 stuff too. However, I'm not prepared to suffer flaky demos any more, as I did at UKOUG, so have just ordered a Dell XPS16 with one of those funky i7 CPUs and an SSD drive. Hopefully it will make things a lot smoother! It should also facilitate the 11gR2 Parallel experimentation - I don't need real-world performance or DOPs, just enough to illustrate features without my laptop grinding to a halt! So, the Parallel stuff isn't done - I'll be doing that at MOTS - but delayed for now.

As usual, I'm really looking forward to this one. I was having beers with a couple of the best techies at work the other night, neither of whom are completely convinced about the value and technical depth of conferences and I told them this is the one they should think about going to. What I forgot to mention was the number of new friends I met at Hotsos. If anyone is thinking of 'cliques', these were people who I wouldn't have known from Adam but the fact that Hotsos is small (500 attendees) and everyone who is there has a keen interest in Oracle performance means that you're likely to meet plenty of like-minded people.

Oh, and where are the technical posts? Well I interrupted the first of a possibly long series of posts on optimiser stats to write this. Those technical posts take a little longer, just getting the examples right, but things should start looking up soon. With my low posting frequency over the past few months, I'm not exactly short of things to write about.

Yes, so it's quiet round here, I'm taking longer to reply to emails and have all but given up the ghost of my Twitter account. Suffice to say my head is buried preparing presentations when I'm not travelling back up and down to Scotland, so thanks for people's patience in the mean-time. However, there were a couple of quick things I wanted to mention.

First, I know of someone who is looking for good DW developers. I would say good Oracle Database Developers really, but that needs to include good knowledge of all sorts of partitioning and partition management, stats collection and performance tuning stuff. It seems it's difficult to find the right quality of person we're looking for. So, it's unusual for me to put a job advert on here (apart from Reverse Job Adverts for myself!) but I'm hoping the people that read such (cough) an astounding blog probably includes some pretty good sorts. Jobs are in London, both contract and permanent (preferably permanent) and it's a good working environment with nice people. If you're interested, drop me a line at the usual address (I trust you'll be able to track it down because I'm not fussy about posting it) or post a comment here. Note, however, that due to my current head-in-the-sand state, that I can only promise to get back to people who we're interested in. Sorry, but I'm not running a recruitment agency here, just performing a shabby community service

Oh, and in other news, Andy Campbell finally got back to blogging! Welcome back, mate, but let's try dragging those old posts back out, eh? As well as a few good ones, of course

I know the Hotsos Symposium must be approaching because I've been in bed the last couple of days with a cold. Fortunately this one didn't arrive in the final week of preparation, but it's still been unpleasant and I could have done without it.*

Anyway, I see that the word is finally out about MOTS - The Michigan Oak Table Symposium - via Charles Hooper's excellent blog. If you haven't picked up on that one yet, enjoy! Post after post of useful technical stuff. For example, his latest one on the relationship between the different Time Model statistics, which I've discussed here in the past.

Carol Dacko contacted me about this conference that she is organising along with several others and I think it's going to be a cracker. First of all, it's the weekend before next year's Openworld so those of us heading over from Europe can combine the two conferences into one trip and they'll be quite a contrast, I reckon! Second, there's a maximum of 300 attendees, which should make for a nice informal atmosphere with plentiful opportunities for speakers and other attendees to mix and talk Oracle. Third, the list of speakers is starting to look great. One I'm really looking forward to!

Speaking of Openworld, there seems to be some confusion over the naming of this year's MOW. I think it's now called Miracle Aben Verden, which may or may not be something to do with monkeys. I have no idea what's going on there, but I'm sure it'll be another cracker, whatever it happens to be called this year! (Suppose I'd better change that graphic over in the side-bar this weekend.)

You'd think I had nothing technical to blog about. I do, just waiting until I have the desire. So much for a post-rush over the holidays

* I wonder if that tenuous link to a conference will get me off the hook for breaking one of the golden rules of blogging - thou shalt not whine about feeling ill. Nobody cares.

... in the Oracle blogosphere. Well, whether all these posts were 'today' depends on timezones, I suppose, but it's today that I read them.

My morning started with a read of this fine post by Cary Millsap. I immediately shared it around at work because Cary uses his typically strong way with words to explain something that should be obvious, but maybe isn't. I talk about the same thing on the course I teach for Oracle. Sit with the users and watch their work and measure response time the way they do!

Then when I got back to my room this evening, there were several other posts that just confirmed the value to be had these days in following Oracle blogs.

I'm pretty sure I didn't know about this useful view, which I do now, courtesy of Jonathan Lewis and I know I'll be going through this useful list of Metalink articles, courtesy of Charles Hooper.

I haven't even got to Martin's AWR post yet and I'm delighted to see that Greg Rahn has started his promised series of posts drilling down into the detail of his Openworld presentation.

That's just a selection, too! With some holiday coming up, I might get round to some of my own

On the off-chance I don't get time (I have a lot to do), I'll wish everyone a merry Christmas and a happy new year or whatever you celebrate. I suspect the Cuddly Toys will want to write their own post over at the other place.

It's early days but I think I became the joint father of a bug report this week. This is bug number 9219636.

SQL> CREATE TYPE INTEGER_ARRAY_T AS TABLE OF INTEGER
  2  /

Type created.

SQL> 
SQL> CREATE TABLE V_SESSION_VALID (
  2          SID NUMBER ,
  3          SESSION_ID NUMBER NOT NULL,
  4                  CONSTRAINT SID_PK PRIMARY KEY (SID) VALIDATE ,
  5                  CONSTRAINT SESSIONID_UK UNIQUE (SESSION_ID) VALIDATE)
  6  /

Table created.

SQL> 
SQL> CREATE TABLE SESSION_VIEW ( VIEW_ID NUMBER ,
  2          SESSION_ID NUMBER NOT NULL,
  3          ROW_COUNT NUMBER NOT NULL,
  4                  CONSTRAINT VIEWID_PK PRIMARY KEY (VIEW_ID) VALIDATE)
  5  /

Table created.

SQL> 
SQL> CREATE TABLE BATCH_SESSION (BATCH_ID NUMBER NOT NULL , ROW_ID NUMBER NOT NULL , 
                                 SID NUMBER NOT NULL )
  2  /

Table created.

SQL> 
SQL> declare
  2          l_sessions INTEGER_ARRAY_T;
  3  begin
  4          select s.SESSION_ID
  5          bulk collect into l_sessions
  6          from V_SESSION_VALID s
  7                  join SESSION_VIEW v on (s.SESSION_ID = v.SESSION_ID)
  8                  join BATCH_SESSION b on (s.SID = b.SID)
  9          where  v.ROW_COUNT > 0
 10          and b.BATCH_ID between 1 and 1000
 11          for update of s.SESSION_ID;
 12  end;
 13  /
        select s.SESSION_ID
        *
ERROR at line 4:
ORA-06550: line 4, column 2:
PL/SQL: ORA-00918: column ambiguously defined
ORA-06550: line 4, column 2:
PL/SQL: SQL Statement ignored

This works in all versions prior to 11.2.0.1, which is where it was identified during some 11gR2 RAC testing of the application I'm working on. There are actually quite a few notes about this type of error kicking around on My Oracle Support, but in most cases they aren't bugs, but a tightening of ambiguous column checking, which is a good thing. However, if your app does use ANSI join syntax, keep an eye out for 11gR2 catching out previously suspect SQL statements when you're upgrading.

However, there is no ambiguous column definition here. If I convert it to traditional Oracle join syntax, it works, as does commenting out the offending part of the code.

SQL> declare
  2          l_sessions INTEGER_ARRAY_T;
  3  begin
  4          select s.SESSION_ID
  5          bulk collect into l_sessions
  6          from V_SESSION_VALID s
  7                  join SESSION_VIEW v on (s.SESSION_ID = v.SESSION_ID)
  8                  join BATCH_SESSION b on (s.SID = b.SID)
  9          where  v.ROW_COUNT > 0
 10          and b.BATCH_ID between 1 and 1000;
 11  -- for update of s.SESSION_ID;
 12  end;
 13  /

PL/SQL procedure successfully completed.

So why do I say joint father of the bug report? Because having raised the SR, we delayed our 11gR2 upgrade work for unassociated reasons and so I didn't put the work in to provide the proper test case that Oracle Support requested. A couple of weeks later, with the SR abandoned for now, one of the DBAs mentioned a bug he'd found on 11gR2 and my first question was 'ANSI join syntax?'. Sure enough, it was the same as our example so he picked up the SR and ran with it and put together the test case here, which is also stripped of any company information. So we might have hit it first, but Cristian Banoiu put in the hard work. I'm sure he was pretty proud when it was assigned a bug number and I believe it's currently in development.