World Cup Trivia

Well, I'm finally back from my honeymoon, and with the wedding out of the way I may finally get some time to write blog entries again. But wait, hang on, it seems like there's a World Cup (footy that is) to take up almost all of my evenings. Ah well, no blogging just yet then!

Still, there's no matches on at the moment (not until Friday anyway) and so to quench my need for football I decided to go on a trivia hunt. And thus was born Honest Bob's World Cup Trivia facts... I can't guarantee the accuracy of any of these facts as all were researched on the internet ;-)

Of the 17 events (prior to Germany 2006), the hosts have won 6 times, with only Sweden being a losing host finalist (1958, losing to Brazil).

Argentina and Brazil are the only countries to win outside of their own continent. Brazil have managed to win on every continent the competition has been played:

• Asia: South Korea / Japan (2002)


• North America: USA (1994), Mexico (1970)


• South America: Chile (1962)


• Europe: Sweden (1958)

Not only that, but Brazil have managed to qualify for every world cup tournament.

However, they are the only team that have won the competition to have NOT won it as hosts.

Although a German team have won the competition 3 times, they've never managed it as a combined Germany, only ever as West Germany.

In total only 7 teams have won the competition out of 207 countries who have competed for qualification, and 78 who have made it to the world cup proper. The 7 winners are:

• Brazil (5 times)


• Italy (3 times)


• West Germany (3 times)


• Uruguay (2 times)


• Argentina (2 times)


• England (once)


• France (once)

The competition has been utterly dominated by European and South American teams, with only two teams outside of the two continents having made it to the semi-final stages. USA (1994) and South Korea (2002) both being hosts when they managed it.
The fastest goal in a world cup match was scored by Hakan Sukur (Turkey), 11 seconds after kick off against South Korea in the 2002 tournament.

However, that time is beaten when you also take into account the qualifying matches. David Gualtieri (San Marino) scored after 8 seconds against England in their ill-fated qualification attempt for the 1994 Finals. England needed to win by 7 clear goals and have Holland lose their match against Poland. England only managed to win 7-1, though it mattered little as Holland eventually won their game 3-1.

In the whole of that qualification group (10 games) San Marino managed to score only one other goal and conceded 46.

The 1950 World Cup was not decided by a final. Rather it was a league contested by 4 teams, with the match between Uruguay and Brazil (2-1) being the decisive match and therefore generally regarded as 'the final'.

The only person to have played both World Cup Football and World Cup Cricket is Viv Richards - West Indies at cricket (obviously) and for Antigua in their 1974 World Cup Qualifying matches, which ultimately ended in failure.

Best Practice?

Yeah yeah, it's been very quiet on the Bob front recently... I have an excuse though, it's only a week and a half to my wedding, so I've been a busy busy boy. Plus I've got the task of completely rewriting our development manual, so that's taking all of my creative juices. I'm running on empty.

But, when you get sent a link like this, you just have to share:


If you're anything like me, you'll absolutely hate the phrase 'Best Practice'. I cringe every time I hear it. It suggests to me that the speaker has stopped learning... "This is the best practice there could possibly be, so there's no point in trying to improve it". In addition it has the connotation that "This is the best practice for all situations, whatever it may be". Sorry, but that just doesn't work.

So anyway, a site that calls itself Fairly Good Practices was always going to pique my interest...

In my spare time I contribute to an OS CMS...

Whenever I see a CV cross my desk containing the words 'Open Source Content Management System', I shiver. Visibly.

So what was I supposed to do with the news that my manager had decided to write one?

Well, it turns out that I needed to take a look at the home page, then take a look at then code, and finally decide that it's not actually that bad.

Obviously, the last thing the world needs is yet another Wiki, but as it's actually very very lightweight, looks ludicrously easy to set up, and is very well structured, then why not make space for just one more?

Obviously, he needs a slap for not writing unit tests, but the plug in authentication and storage classes are spot on.

In fact, I'd like to present it as an example of how PHP code can look when its not produced by an idiot. In addition, the CSS driven layout is a great example of content and presentation separation. All in all it's starting from a very nice position. Hopfeully it'll continue in the same way.

There's the odd little bit of refactoring to do, but once it's on sourceforge or freshmeat I'll help him with that...

Rest assured, it won't appear on my CV...

P.S. Momentous occasion: This my 100th blog entry
P.S.S Momentous occasion 2: It's also my boss's 30th birthday. Don't you hate it when your boss is younger than you!

Auto-increment in Oracle

I'm sure that 100s of references exist all over the web for this, but someone asked me today how to do this, and so it's trivial for me to add it onto this blog...

In MySql you have an 'auto-increment' column. Does Oracle have one, and if not, how do you implement one in Oracle?

Well, we tend to say it's best to wrap up your INSERT statements in procedures and then manually grab the sequence number yourself... but if not:

You might notice the rather useful RETURNING clause in the insert statements. This bit seems to be missing from most internet examples I found...

CREATE SEQUENCE rob_tmp_seq
/

CREATE TABLE rob_tmp_tab ( id NUMBER, descr VARCHAR2(200) )
/

ALTER TABLE rob_tmp_tab ADD CONSTRAINT rob_tmp_tab_pk PRIMARY KEY ( id )
/

CREATE TRIGGER rob_tmp_tab_trig BEFORE INSERT ON rob_tmp_tab FOR EACH ROW
DECLARE
BEGIN
  --
  IF :new.id IS NULL THEN
    SELECT rob_tmp_seq.NEXTVAL
    INTO   :new.id
    FROM   DUAL;
  END IF;    
  --
END;
/

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
  --
  vn_number   NUMBER;
  --
BEGIN
  --
  INSERT INTO rob_tmp_tab( descr )
  VALUES                 ( 'This is a description' )
  RETURNING id INTO vn_number;
  --
  DBMS_OUTPUT.PUT_LINE( 'Created a record with the automatically assigned ID: ' || vn_number );
  --
  INSERT INTO rob_tmp_tab( id, descr )
  VALUES                 ( rob_tmp_seq.NEXTVAL, 'This is a description' )
  RETURNING id INTO vn_number;
  --
  DBMS_OUTPUT.PUT_LINE( 'Created a record with the ID grabbed from sequence manually ID: ' || vn_number );
  --
  INSERT INTO rob_tmp_tab( id, descr )
  VALUES                 ( 150, 'This is a description' )
  RETURNING id INTO vn_number;
  --
  DBMS_OUTPUT.PUT_LINE( 'Created a record with the ID specified manually ID: ' || vn_number );
  --
END;
/

Measuring Performance

A couple of months ago we started to get reports into our service desk that the larger of our databases were suffering from performance problems. Nothing catastrophic, just that the speed of the applications accessing those databases were starting to slow down.

As we'd added new versions of our system into the live environment fairly recently, the development team was asked to join into the discussion of how to solve the problem. This was a little unusual, as we don't normally get involved in the support of the live systems... be that firefighting or strategic support. And it wasn't that our applications were under suspicion, it was just that it made political sense to get us involved.

Rather than jump straight into the database, we took a holistic look at the performance of the applications, taking in as many components as we could: from client machines, through network infrastructure down to individual datafiles on the database server. In the end we came to the usual, almost inevitable conclusion... the database was the bottleneck, and it wasn't any single aspect of the database.

One thing we did notice however, was that the memory settings for the database server appeared to be unusual. So we advised to the DBA that these be looked at straight away. The DBA's suggested approach to this was
"Change the settings one at a time on live, if it doesn't break the system then we're probably on the right lines. Because it's dangerous doing this, we can only make one change per day and then wait for any calls on the service desk before we make the next change"

The rest of us didn't think that sounded like a safe way to go, so I suggested a new approach.

I put forward the idea of taking a production quality server that we could run in isolation, and importing the database owned by the brand that had the most urgent problems. We would then write an application that would stress that database in line with the behaviour of the live system, we would monitor the system's peroformance during the test and record our findings. I argued that once we had that in place we could produce a benchmark, make a change and rerun the test. This would then tell us how the system's performance characteristics had changed. We could then make the next proposed change, run the test again. Then make another change...

Having this tool in place would mean that we could thoroughly test the performance of the system in relation to our changes without ever having to rollout a change into a live environment. Once we were sure that we had made a step change in the performance of the system we could roll that change onto the live system reasonably safe in the knowledge that we would always have a positive impact on the system.

The rest of the team agreed and we set about producing our performance test rig.

Our requirements were:

• The test should be completely repeatable. Meaning that, as far as is reasonably possible, the test should perform the same queries, at the same time, with the same parameters, every time the test runs.


• When the test is running, the database being tested should appear to the DBA like the live version of the system under a particularly high level of stress.


• The connections to the database should be made in the same style as the live system. E.g. emulations of connections from a web application should appear, make their requests and then disappear. Connections from a client machine application should appear at the start of the test and then remain connected for the duration of the test.


• It should be easy to increase / decrease / change the characteristics of the test load.


• It should be possible to run the test by entering a single command into a suitably set-up machine. It should then be possible to re-run the test by entering the same single command.


• Running the test should include reporting on the performance characteristics.

I hope to go into the design of the solution in another blog entry soon...

We've since put the performance test rig together, and using it we managed to very closely replicate what was hapenning on live. Each test takes an hour to run and emulates a load equal to around double to the normal live load. We ran several test runs, each time changing a single memory setting until such a time as we had a decent change in performance.

We measured the performance differences by checking the average, minimum and maximum run times of each of our sets of emulation queries (we had a set for each of our applications, sometimes a set for each module in the application). The test rig also produces two stats pack snashots for each test, one at the start and another at the end. All the test results are then copied into a seperate database for long term storage and reporting.

Once we had the test rig up and running it took a day to tune the memory settings for our biggest database. We were able to run 5 tests in that day and end up with a very clear picture of the effects our changes would make on the live system.

The changes involved slashing the shared pool to less than a quarter its original size, taking the PGA target up by a factor of four and doubling the buffer cache. We then rolled the complete set of changes out onto the live database in one go.

It more than doubled the overall performance of the database. Exactly as predicted.

Since then the performance test rig has been earmarked for a few other jobs:

• Balancing our read : write ratio on the local disk controllers.


• Testing and tuning a Network Attached Storage device as a replacement for local disks.


• Checking the impact of adding new indexes on heavily used areas of the system.


• Investigation into possible designs for a new realtime "MIS light" application module.


• Testing if regular defragmentation of our DBs give us an increase in performance.


• Examination of how block sizes affect the performance of the system.

Basically, because the test rig properly emulates what happens on the live system, it can be used to investigate any change that would impact the performance of the system, be that hardware or software. And it allows us to make that investigation quickly and without any impact on any live system.