Doug's Oracle Blog

... 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.

Miracle Open World is on the horizon. Mogens and Annette talk about in this video (Oh, and be sure to watch until the end ). I think I'm supposed to be presenting at this one but I know places will be in short supply, so we'll see.

14th-16th April at Legoland, Billund, Jutland, Denmark. Be there or be square.

Some features in this post require a Diagnostics Pack license.

I think there a quite a few decent Oracle blogs around. There are links to some of them over there on the right. But by far my favourite this year has been Kerry Osborne's. I think there are a number of reasons.

1) We have similar interests in Oracle performance in general and the Diagnostics Pack features in particular.

2) His posts are nearly always technical but not just for the sake of it or to impress. I like to learn new and deep stuff as much as the next guy, but if the number of times I send links to colleagues who are trying to solve a problem is any indication of technical value, then Kerry's is almost at the top of my list.

3) I think he writes really well, in a readable, conversational style and always shows examples that are rigorous and reflect most people's day-to-day IT reality.

4) He shares useful scripts. I'm going to revisit that point in a future post about some of the tools I find useful.

I know I've mentioned Kerry's blog before, but no apologies for recommending it again. It's how I'd like my blog to be, really. Not jealous; just inspired! Oh, and for the avoidance of any doubt, we're not mates or anything, but I may buy him a couple of beers at Hotsos to thank him for those occasions when his stuff has helped.

But there is a technical point in here somewhere. I noticed his recent post about tracking Oracle parameter changes in AWR and it reinforced nicely a point I make when I'm teaching people about AWR. Never under-estimate how useful it is in DBA trouble-shooting of problems which are entirely unrelated to performance. AWR is often relevant for solving problems where a history might be useful. I've used the parameter change example in the past myself - who set this parameter, when and why? It won't answer most of those questions, but once you know the when, it might help answer the other questions too. Another use I've found is in looking back at when precisely (well, to the nearest hour) the free space in a tablespace dropped dramatically so that we could try to narrow the reasons why.

All I would say is that if you have the licenses and something like this crops up, check to see if there's a DBA_HIST view that might help. (Oh, and if you don't, just use Statspack instead. Doesn't have quite as much information, but pretty close. Or just write your own similar utility)

Kerry also talks about AWR retention and I know a lot of people increase the retention from 7 days to something like 35 days, to cover an entire monthly workload cycle. It depends on your system, of course. However, I am a big fan of long AWR retention periods, much longer than I find people using, because the history of a system is so useful when tracking performance changes over time. I've lost count of the number of times when I'm looking at gradual performance degredation over time and wished I could have some more AWR data to hand.

As for sizing, there's a pretty simple solution - just use the supplied $ORACLE_HOME/rdbms/admin/utlsyxsz.sql script. What I like about it is that it supplies very sensible defaults for your system, based on existing data and gives you pretty detailed information about what is going on inside your SYSAUX tablespace. It's one of those little, apparently simple things that I find people are still unaware of for some reason.

Oh, actually, there was one more non-technical post I'd forgotten. With such a rush between the course I taught in Bratislava, a quick trip to London for work (and some hairy performance problems) and UKOUG, I never got round to mentioning how the course trip went.

First of all, I loved the brief look at Bratislava I had and I said to Mads that it's definitely a place I'd like to go back and visit for longer.

Second, I was very well cared for by the local Oracle people. In fairness, they're always pretty good but it was one of the most enjoyable teaching experiences I had - excellent venue and equipment, great lunch and good feedback on how the course had gone, so many thanks to all those involved. There was a slight hiccup in that I hadn't been updated that the course had been moved from the Oracle offices to a hotel venue because of the high number of registrations, but that was soon fixed by a quick taxi trip!

Third, although a pretty big class (nearly 30, I think), it was a really good atmosphere and there were lots of great questions before and after the class so thanks to the attendees for being a smart, challenging and extremely friendly bunch. Oh, and apologies to the few people I *still* owe answers. I'll be getting to them soon.

All in all, one of my favourite teaching experiences and I look forward to going back there one day with a different course.

A couple of my usual lousy photos. In fairness, they're from a poor camera phone and are taken very quickly so as not to disrupt the class. They're mainly to remind me of people I've met on courses.






I have a feeling I might take a break from the courses for a little while, so it was nice to finish the year-and-a-half stint on a high note