Friday, January 28, 2011

Of Architects, Astronauts and Engineers

I had some nice comments on my last post about the database developer role. Most believed there is still a place for database developers, and not necessarily the graveyard. An organisation that is small enough (and hasn't out-sourced everything) can allow someone to spread out out either a DBA or developer role to combine the two. In my experience this is harder in large organisations where DBAs tend to be put under the 'infrastructure' umbrella while developers are grouped into projects or applications. 

Some suggested the title of 'Architect'. I have mixed feelings on this term, but @ddelmoli summed up my thoughts with his tweet "I don't know what db architects do. All I know is they're rarely around when the SQL is tough or the db needs recovery". 

I'm all in favour of a good achitecture and design. It won't work otherwise. But to my mind it is a first step and there's an important job in turning that design into a working system. Joel Spolsky talks of 'Astronaut Architects' who have believed the hype of the latest and greatest. And then leave you to implement some new architecture where the software is still in 0.X version. Or in some cases, 16.X because they are releasing new versions every month, each highly incompatible with the last. 

And then there are the 'Cookie Cutter Architects' who use the same design for everything, changing the names to protect the innocent. You'll quickly find that in their haste to reuse and recycle, they've missed some essential facet of the business that makes the solution unworkable.

And that's the key. If it doesn't work, it doesn't count. 

I recall a year or so ago seeing a student on the train with a folder bearing the prominent sticker "Engineers make it work". I know when I've delivered a screen or batch program, and watched it run in Production, there's a great feeling of least when it doesn't fall over. I've never felt that when I've produced a design document. Then its more of a feeling of "Great, that is out of the way. Let's get things happening." 

So I definitely prefer the term 'database engineer'. I think that's what I want to be when I grow up

Wednesday, January 26, 2011

Is there still room for a database developer

I'm looking forward to Quest's Guy Harrison coming to present at our Sydney Oracle Meetup in three weeks. It's aimed at developers, but I'm hoping some DBAs turn up too. Otherwise we may have a small audience.

I often find myself in an odd position. I think of myself as a developer, or more precisely an Oracle database developer. I used to be an Oracle developer, but then they got involved in ADF (which I leave to the likes of Chris Muir) and I suspect Java will dominate more in future. The OCP track emphasizes PL/SQL development though I like the thought of the "SQL Expert" exam. I've done Oracle Forms work too, and that is distinct from PL/SQL development too even though it also involves writing PL/SQL.

However I work for a consultancy and am in the "Application Development" space, mostly inhabited by Java and Dot.Net people. They have this perception that anyone who understands a database is a DBA. The concept of a "database developer" is alien to them and somewhat repulsive too, I think.

I'm not a DBA. I've done a couple of roles that involve very basic DBA tasks, like installing a database and creating tablespaces. But I couldn't look someone like Nuno in the eye if I claimed to be a DBA. Pythian employs DBAs. Most of the attendees of the Sydney Oracle Meetup are DBAs. They use OEM and RMAN and Grid things and know about ASM and log shipping, DR and replication. I'm not one of them.

A few weeks back the "Database" proposal for StackExchange got renamed at the last minute (ie after people had committed it) to "DBA". Again, it pushes the concept that anyone who knows anything about databases must be a DBA. Don't get me wrong. Most DBAs do know a lot about databases. It would be nice to say 'all' but as a DBA knows there's a big difference between three nines and five nines and you'd never say 100%. But not every database expert is a DBA.

So I'm a bit concerned about my 'space'. The current TIOBE index has PL/SQL down at number 25 with less than a 0.6% rating. A little over a year ago it was at 0.9% I'm not convinced of the validity of the measurement, but I don't know of a better one. It again suggests a de-emphasis on "database developer" as a role although Transact-SQL had a big jump.

I'm spending some time trying to understand Java. Not specifically the code. I've worked on a few small Java programs and individual programs are relatively easy to understand (or they should be). Its how they all fit together in an application that is tricky. Currently I'm in the phase of wondering why there's so much configuration involved, but that may well be because there is proportionally more config for a small app than a big one.

Sunday, January 23, 2011

Oracle XE screenshot tweeted

For those who follow blogs but not twitter, Kris Rice posted a screen shot from an 11g Express Edition test.

Still no delivery date, but there never is until it actually happens. They don't need an OpenWorld or anything to announce it. 

The interesting items are that 

1. "64 bit" is there at the top. Sure, five years ago they could just deliver a 32-bit version. But the memory limits on that mean that most desktops will be 64-bit soon, let alone server deployments. We're all being upgraded to Windows-7 at our offices next month, with a 64-bit deployment. 32-bit is end-of-life unless you are on a netbook.
32-bit is limited to 4GB (or 2GB/3GB usable in Windows depending on various settings). It would be nice if the 64-bit support meant that they were removing the memory limit or massively upgrading it. I think I'm being too greedy though.

2. Looks like they'll match the XE version number to the mainstream versions, which would be useful. It probably makes things cleaner from the documentation point of view as well.

Previous 11gXE mentions have given it a 10GB database size limit and that Apex won't be included by default, but can be installed. I suspect it will still exclude the JVM, but would be happy to be proven wrong.

Anyway, I'll keep my fingers crossed and maybe we'll get some extra Oracle lovin' for Valentine's Day

Friday, January 14, 2011

You can't always trust 11g column-level invalidation

Valentin Nikotin has posted some valuable examples on the PL/SQL Challenge Blog regarding the 11g enhancements for column-level dependencies being used to invalidate store program units.

In his first example, he uses a function-based index to create a hidden column on a table and a stored procedure referring to the hidden column. When the index is dropped the hidden column is dropped but the procedure is not invalidated. Instead it errors upon execution and is only invalidated when an attempt is made to recompile it. Generally I wouldn't expect a stored procedure (or anything) to refer to a hidden column with a system generated name, plus the 'failure' is pretty much what you'd expect anyway. I don't class this as an important issue.

The next two examples are nastier though.

In the second example, he has a procedure called "MU" which contains a query that uses "MU" as a table alias. He then uses qualified naming so that a variable in a query is represented by "MU.Z". When the column "Z" is added to the table used in the query, the procedure should be invalidated since "MU.Z" should now refer to the table column (though the "MU" alias) rather than the procedure variable. This invalidation doesn't occur so the program behaviour only changes when the procedure is subsequently compiled (either manually or by some later change that does cause an invalidation).

In the third example, a PL/SQL record variable is used that has the same name as a table. The query in the procedure initially refers to a field in the record. When a column of the same name is added to a table, the procedure should need recompilation so that the table column overrides the PL/SQL record variable. However again the invalidation isn't automatically performed and the procedure's behaviour would change unaccountably at some later time.

The effects are similar either way, so I'll just include a script for the latter (as record variables are more widely used that qualified variable names).


create table inval_test (id number, val varchar2(10));
insert into inval_test values (1,'Blue');
create or replace procedure inval_test_proc is
  type t_rec is record
     (insert_id number, insert_val varchar2(10));
  inval_test t_rec;
  v_cnt number;
  inval_test.insert_id := 1;
  inval_test.insert_val := 'Blue';
  select count(*) into v_cnt
  from inval_test
  where id = inval_test.insert_id
  and val = inval_test.insert_val;
  dbms_output.put_line('Count is:'||v_cnt);


begin inval_test_proc; end;
alter table inval_test add insert_val varchar2(10);
begin inval_test_proc; end;
alter procedure inval_test_proc compile;
begin inval_test_proc; end;

The behaviour changes when the procedure is manually compiled, rather than when the column is added to the table. That compilation may happen weeks or months after the table change, making it difficult to determine the cause of the new bug.

The fundamental problem is that PL/SQL syntax doesn't differentiate between the specification of a schema object (table column, function etc) and PL/SQL variables, and that the globally defined schema object takes precedence over the locally scoped variable.

The only reasonable workaround is to use a variable naming strategy to distinguish between the two.

Just to blow my own trumpet a bit (and it's my blog, so I'm allowed), I pointed out potential pitfalls in this functionality when it was announced. For my money the problem is a big one to fix without changing some fundamentals. With editions in the frame for high-availability upgrading, I'd prefer at least an option on 'optimistic' or 'pessimistic' invalidation models.

Tuesday, January 11, 2011

On Coincidences

I've just returned from a few days holiday down in the Nation's capital, Canberra. Home to Richard Foote and Marcelle Kratochvil, our local Oracle ACE's. It's about 300 kms from our home, which is a bit of a long drive (especially if you are stuck between an eight-year old and a five year old - who turned six today).

On day one, we went to Parliament House and happened to bump into the parents of one of my daughter's team mates from last year's soccer. We did the mutual expressions of surprise, muttering "Small world" and went on our way.

The day after, in the playground of the Camping/Cabin park, a boy shouts out "Hello Adam" to my son. That boy was in my son's indoor soccer team last season. No expressions of surprise there as eight year olds don't don't consider the liklihood of bumping into neighbours after travelling a couple of hundred miles.

A couple of days later, at the War Memorial, my mother-in-law (on holiday with us from Bristol, England and over 10,000 miles from home) sees a couple of her neighbours. Small world again.

The point is, with a large enough data set and transaction volumes, even things that are pretty unlikely will crop up on a regular basis. But if anyone suggests testing for them before hand, those test cases will be dismissed as "too unlikely".

As for Canberra, the Questacon will keep kids amused for five to six hours. The Art Gallery, less than an hour (but I was more than ready to leave there by then anyway). The National Museum can take another day, even with a chunk of exhibits missing for redevelopment (including Phar Lap's heart - a famous Australian horse from New Zealand poisoned by the Americans). The Parliament Building can be done in a couple of hours unless you've really got a thing for Australian politics (or get into discussing Harold Holt conspiracy theories). A boat trip on the lake is about an hour, but the kids got to drive so gets a bonus point.

The War Memorial museum is a bit much for young kids, but I could easily do three or four hours a day for an entire week.

By coincidence and not design, we were there for Summernats. We avoided it mostly, though there were a few blinged up cars at the holiday park.

But for the eighth time the winner of the national burnout masters was....Gary Myers. Congratulations to my namesake.