Better Logic for better solutions

WordPress 3.0.1 has been released. Happy upgrading…

Cheers

Tim…

I can see this post degenerating into a rant, so I would like to preemptively appologize to anyone involved in the production of this exam. I’m guessing it’s a real pain to develop these exams, especially when some ass like me starts moaning about them. Added to that, I’m guessing the word “Expert” means slightly different things to different people…

I’ve been barking on recently that in my opinion, the most important skill required by any PL/SQL developer is SQL, with knowledge of PL/SQL itself coming in second place. Having recently taken the “Oracle Database 11g: Advanced PL/SQL (1Z0-146)” exam (mentioned here), I thought it was a little hypocritical not to sit the “Oracle Database SQL Expert (1Z0-047)” exam as well, so this morning I did just that.

Here are some of my thoughts on the exam, in no particular order of importance:

• Regular Expressions: I think it is important that people understand what regular expressions can do and when it is appropriate to use them, but I don’t think it is necessary to test people on the meta-characters themselves. That’s what the docs are for.
• Analytic Functions: No sign of them in my questions from the pool. Surely analytic functions are more important than regular expression meta-characters.
• The majority of the exhibits were pointless. It seems like they were placed there to waste the time of people with bad exam technique, rather than to assist in answering the question. This was especially true of the schema diagrams, which I only referred to once when the datatype of one of the columns was important.
• Several of the questions could be answered without reading the question at all, as the incorrect answers jumped out at you because they contained blatantly incorrect statements.
• Several of the questions included the “ANY” and “ALL” comparison conditions, which are barely mentioned in the documentation (here)*. I guess these are only included in Oracle because the are part of ANSI SQL. I can’t remember ever using them in Oracle or seeing them being used by others. I have come across them in mySQL so I knew what they were for, which was fortunate.
• There were lots of questions that included DML against inline views rather than directly against tables. It got to the point where I felt like, “If it’s got braces in it I’m going to tick it”.

I very quickly turned into a grumpy old man and started to rush through the exam, spending most of my time thinking about writing this blog post, rather than the exam itself.

In the end I got 96%, which I guess means I got 3 questions wrong out of the 70. Serves me right for rushing it so I could come home and bitch about it.

So I am now an “Oracle Database: SQL Certified Expert” as well as a grumpy old shite…

Cheers

Tim…

* Updated thanks to Pierre’s comment.

Turn Coat (book 11 of The Dresden Files) is another non-stop barrage of action and magic. It ties up some loose ends from previous books, but also leaves a few dangling. That is the last in the series (in paperback). These books are like catnip, so I’m planning to manage my withdrawal by reading Unseen Academicals, which is out in paperback now.

Cheers

Tim…

I was reading this article the other day and it reminded me of a conversation I had at ODTUG about mySQL. I know nothing about Oracle’s plans for mySQL and I’m only a casual user of mySQL (for about 6 years), so don’t consider this an inside scoop, it’s just some idle whittering…

If Oracle truly wanted to kill mySQL how should they do it? My answer was keep adding features. Why?

• Adding features would keep everyone off their back because they would be seen as actively supporting mySQL. This would allow them to blamelessly kill mySQL… Mwahaha (in a DR Evil style).
• Ease of Installation: mySQL is really easy to get hold of because it comes with a couple of clicks from pretty well every Linux distro. As the feature set and flexibility of mySQL increases, it’s not unlikely that the installation process could/would become more complicated. If it were complicated enough to not allow a 1 click install during OS installation, then the ease of installation is gone. So keep adding features until you lose the ease of installation.
• Speed: For basic workloads mySQL is very fast. I’m guessing the vast majority of mySQL installations have a very simple workload profile, like the zillions of blogs, forums and wikis out there. Remember, there is a difference between the number of operations and the complexity of those operations. As you add features you are increasing the amount of code in the engine, which invariably slows it down, but more importantly you are also increasing the expectation of the users. As users start throwing increasingly complex workloads at the engine, it gets proportionally harder to keep delivering top performance.
• Ease of Use: mySQL is considered a very easy engine to use. I could argue that point, but lets run with it. So we add a bunch of features and people start to use them. Oh dear. Stops looking quite so simple to dip your toe in…
• Cheap: mySQL is free (sort of). I don’t see this as a major issue. Look at the companies who spend the real money. They just want something that does the job and are prepared to pay for it. Oracle has never been about the low hanging fruit. Fast forward a few years and I doubt we will see any DBs in SMBs. They will all be managed services by Oracle, Amazon, Google etc. For those where “free” is an issue, adding features can complicate the product to the point where support becomes a necessity. It’s no longer free then…

That’s what I would do.

Cheers

Tim…

Inception… Wow! Do yourself a favor, go see it.

Cheers

Tim…

I was doing some SQL tuning on mySQL today and I learned a few interesting things.

Let’s take an example of a simple query where we have an OR of two mutually exclusive conditions, each on indexed columns.

SELECT *
FROM   t1
WHERE  user_id = 1
OR     location_id = 2;

In mySQL there is a feature called an Index Merge that, as the name suggests, runs a scan of the two indexes and merges the results together. Kinda neat.

I guess it’s not disimilar to what Oracle does with bitmap conversions, but it looks a little less scary in the middle of a massive plan than something like this. .

-----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |    20 |   260 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T1               |    20 |   260 |     6   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                  |       |       |            |          |
|   3 |    BITMAP OR                     |                  |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | T1_LOCATION_ID_I |       |       |     1   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                  |       |       |            |          |
|*  7 |      INDEX RANGE SCAN            | T1_USER_ID_I     |       |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

As it turned out, the query I was tuning was too complicated to take advantage of an index merge, so I tried converting it to use a UNION ALL. Remember, the conditions are mutually exclusive, so there was no need to worry about duplicates. Taking the example of the previous query, it would look something like this.

SELECT *
FROM   t1
WHERE  user_id = 1
UNION ALL
SELECT *
FROM   t1
WHERE  location_id = 2;

The real query I was tuning performed much better with a UNION ALL, but I thought I better check to see if mySQL had any gotchas associated with UNION and UNION ALL. It turns out that both UNION and UNION ALL can create internal temporary tables to get the job done. I suppose it’s not that different to Oracle doing sorts in memory or pushing them out to disk, but it did kinda freak me out when I first read it.

Although I could never see myself devoting that much time to another engine, it is quite interesting seeing how they get the job done.

Cheers

Tim…

I didn’t come from a computer science background, so when I started working with computers I spent a lot of time asking questions. I knew nothing, so there was nothing to lose by asking others for directions. As my knowledge progressed I found myself less able to ask questions of others because of pride. I didn’t want people to know I didn’t know the answer. It took a few years before I was confident enough to say, “I don’t know”, and ask others for their opinions again. Looking back at it you have to laugh…

I was reminded of this issue recently when someone asked me some questions about storage best practices. The first thing I suggested was to speak with the storage vendor and ask what recommendations and whitepapers they have. Googling aside, this seemed like a pretty reasonable starting point to me. Their response was essentially, “I don’t want to look stupid in front of the vendor”. Sound familiar?

I’ve been there and know exactly how it feels. Hell, it’s still happening (here). Swallow your pride and start asking questions again. You’ll be surprised how well people respond when you do. Sometimes because you are stroking their ego, but mostly because they are genuinely interested in the technology and like to chat about it. We are geeks and geeks love to talk about geeky stuff.

Of course, remember to look for information from credible sources and always test what you’re told. If you can’t prove it, it isn’t real.

Cheers

Tim…

PS. As a result of the storage discussion I knocked up a quick overview of a couple of performance measuring tools from Oracle (here).

Small Favour (book 10 of The Dresden Files) is about as intense as they come. It’s like most of the elements of the previous 9 books have been thrown into a blender and produced a perfect cocktail. It’s just an endless onslaught of Fairie Queens, Fallen Angels, Gangsters and now Harry has the attention of an Archangel. It’s brilliant madness.

Cheers

Tim…

I went to the cinema with one of my friends, who is about 29 weeks pregnant. Needless to say it was her decision to watch Eclipse. It is being promoted as the “best so far” and the most “boyfriend friendly so far”, which are both true statements, but don’t let that fool you. It is complete toilet.

Pros:

• The actress playing Victoria has been replaced by Bryce Dallas Howard. That’s not to say the previous actress was bad, but that I think BDH is hot.
• The wolves are very cool. In one scene the dopey lead girl is standing next to Jacob when he’s in wolf form and he’s as tall as her. My immediate reaction was to think of Mouse from The Dresden Files. I sat there for a few minutes thinking how cool it would be to have a massive supernatural dog. It made the film a little more bearable.
• There were some fight scenes, but they were a lot shorter and more anti-climactic than the trailer would have you believe.
• I got the impression that Taylor Lautner was constantly trying not to laugh. I guess when your role in the film is “Hot guy that never wears clothes” it’s kinda hard to take it seriously. It made the film less annoying to think that perhaps the cast realize the film is total crap too.

Cons:

• It’s crap.
• Robert Pattinson can only do two expressions. For the majority of the time he does an expression that I like to call “coma”. When it comes to scenes that require some emotional content he switches seamlessly to “constipated”. I think he’s got a long career in hospital dramas playing coma patients or in constipation remedy adverts on TV.
• Kristen Stewart is one down from Robert, as she only has one expression, which I will call “botox”. I suppose really it is a lack of expression. Perhaps this is the best acting in history, as I don’t think any of the greats could portray “expressionless” so consistently over 3 films. I think she should focus on “constipated” then she can join Robert in his future career.
• Although this film is definitely the best of the lot, it hasn’t had much competition. It is bad.
• Did I mention it is crap?

Interestingly enough, my pregnant mate thought the film was OK, but preferred Predators.

Cheers

Tim…

I was having a cleanup yesterday and I killed my FriendFeed account because I don;t really use it. I was then notified by a couple if disgruntled followers that they were using it to follow my posts.

So I decided to ressurect my FriendFeed account. If you are into that sort of thing you can get it here:

• http://friendfeed.com/oraclebase

Cheers

Tim…

I just found out I’ve been accepted on to the Oracle Games Console beta program. It was a bit of a shock as I’ve got in trouble a few times for leaking information about it. I guess the powers that be have decided it’s safer to bring me into the fold than leave me out in the cold, blabbing about stuff.

For legal reasons I really can’t say anything about the kit itself, but it’s awesome…

I really think OpenWorld 2010 will be the debut. There were rumours of announcements before, but it’s looking really good for this September. One of the big worries has always been a lack of games, but the XBox and PS3 emulators are amazing. The upscale in video playback from the XBox games is incredible…

Cheers

Tim…

Lewis Cunningham recently mentioned on twitter that he had taken this exam and it reminded me I had been meaning to take it ever since it was announced in beta, but never actually got round to it. A quick search of my blog revealed it’s nearly 2 years since I mentioned I wanted to sit this exam (here).

So yesterday I sat and passed the exam and here are some thoughts on it (without mentioning specifics about the questions)…

This is the first time I’ve sat an OCP exam done by Pearson VUE and I was pleasantly surprised. The test interface is a little cleaner and more modern looking that previous exams I’ve taken. There was one minor glitch in that every time there were two exhibits they were shown in the wrong order, such that when the question asked you to look at Exhibit 1, the content it was referring to was in Exhibit 2 and vice versa. For most questions this was obvious, but there was one that if you followed the instructions to the letter would have made the question impossible to answer correctly. Of course, if you had seen the glitch on the other questions it wasn’t a leap to assume the same problem was present on this question also.

The SQL and PL/SQL exams use lots of exhibits and some can be quite big. In a number of cases the questions can be answered without referral to the exhibits if you know your stuff because the incorrect answers reference “facts” about PL/SQL that are blatantly not true. Even when you do have to refer to the exhibits, I would suggest you treat it like you are debugging code and read the question and possible answers before reading the exhibit. Why? You don’t start debugging code before you know what the errors are. In the same way, if you know the possible answers you can ignore the majority of code in the exhibit and focus on the area of interest. Reading the whole exhibit would take ages, so be efficient about it. Ignore the fact the question tells you to look at the exhibits first and you will answer the questions much faster.

The exam is very much 11.1 focussed, so there are a few questions where the “correct” answer is somewhat dubious if you have been using 11.2 for a while. Keep in mind the 11.1 state of play and don’t try and be too clever. That said, for the most part the 11.1/11.2 issue is irrelevant.

The context of a question is a very important thing and one that I think the OCP questions sometimes lack. As an example, one question asks you which of the 4 answers can be produced by a specific built in package. The correct answer as far as I’m concerned is all four, but it is rare that questions need all answers to be checked, so this is where you need to put on your PL/SQL hat. As a direct call from PL/SQL only two answers are correct, but if you call the API via a query all four answers are possible. So you have to make a judgement call as to what context the examiners are using. I figured, as it’s a PL/SQL exam, I should ignore SQL and that turned out to be the correct move.

There were only two questions that I didn’t know the complete answer to directly, but in both cases, using a process of elimination I was pretty confident I had a reasonable shot at the correct answer. Even if you don’t know every fact relating to a subject you can usually elimitate some of the answers, which will up your odds. Don’t panic.

The exam is scheduled for 90 mins and I was done in a little over 30 mins. The examiner gave me a bit of a look when I came out that early, and another when he saw my 100% score. I just smiled and told him I’d been doing PL/SQL for 16 years and had been teaching the contents of this exam for over 2 years. It would have been a little tragic if I hadn’t come close to 100% really…

So I guess in the next few days I’ll recieve my “Oracle Advanced PL/SQL Developer Certified Professional” bumf. I quite fancy doing something else now. I obviously spent too many years in education and I need an exam fix every so often.

Cheers

Tim…

Predators is not massively different to the 1987 Arnie film Predator. That’s right, it was over 20 years ago. The reason for the people being in a jungle is different, and the jungle is on another planet, but it is essentially a group of people running round a forest tryng not to get killed by aliens. There is not really a lot of scope for it to be that different. Even with that limitation in mind, I thought it was a pretty good action film. It doesn’t break any new ground, but it’s got to be better than wasting 2 hours watching that Eclipse crap.

Cheers

Tim…

If I sell my car, I have no right to complain if the next owner decides to sell it to someone else.

So Monty, do the world a favour and shut the hell up. If you cared that much about mySQL you wouldn’t have sold it to Sun in the first place.

I suggest you take a fork of the mySQL source and set up a new company based on that… Oh too late, you already have…

Your arguments sound like a child complaining in the playground. Grow up.

Tim…

I was a little hesitant when I started reading White Night (Book 9 of The Dresden Files). Proven Guilty had been so good I though this book could only be a let down. Fortunately it wasn’t. Non-stop action, overcoming insurmountable odds and a liberal dose of magic thrown in. Sweet.

Cheers

Tim…

I’ve just got back home from ODTUG. Unfortunately my bag didn’t make it. Continental have played a blinder on this trip. I had an 8 hour delay on the way there and they lost my bag on the way back. Fingers crossed it will turn up soon.

My first happy birthday (I’m 41 today) message came from the passport control officer as I arrived in the UK at 07:00 this morning. I didn’t sleep on the plane so I’m off to bed now to try and play catch-up.

Thanks to everyone who made ODTUG possible. I had a great time and hope to be back next year.

Cheers

Tim…

Day 5

It was hard to get out of bed this morning. The timetable for the day was also pretty daunting. Most of the sessions were half-day, which at this late stage of the conference makes me wanna run off screaming.

I chose to go with “Data Warehouse Performance Best Practices – Parts I – III” by Maria Colgan and Jean-Pierre Dijcks, both contributors to the Data Warehouse Insiders Blog. Much of the information from this presentation is already available from the blog, and some variations of the information from these webinars, so don’t worry if you couldn’t make it.

Most of my work has been on OLTP systems so I’m always eager to know where theory and practice diverge as far as DW are concerned. For me this presentation had a really good mix in that respect. It was very easy to listen too, sparked a lot of questions and gave people some compelling reasons to consider 11gR2. All in all, a very enjoyable way to spend 3 hours.

Next stop the airport.

Cheers

Tim…

Day 4

“Analytic Functions Revisited” by Alex Nuijten was a great presentation. I’m sure lots of people left the presentation thinking, “Where has this stuff been hiding all these years?” Incidently, Alex won the $500 prize in the PL/SQL Challenge last night. Well done dude.

I caught the last couple of minutes of “Why Isn’t Oracle Using My Index” by Jonathan Lewis. One of his suggestions was to take the lazy approach and go with the default parameters and functionality, which will work for most stuff, then spend your time focusing on the stuff it doesn’t work for. A man after my own heart.

“Thinking Clearly About Performance” by Cary Millsap was a combination of the messages Cary’s been pushing for the last few years. It was essentially a whistle-stop tour of his views on performance and why we should care. Very clean, very slick and very interesting. If you’ve been following #odtug you will have seen it described as , “The best spent 60 minutes of my 40 years of learning”, by one of the delegates.

“Performance Instrumentation” by Karen Morton focussed on instrumenting your PL/SQL applications using ILO from Method R, which amongst other things encapsulates the functionality of the DBMS_APPLICATION_INFO and DBMS_SESSION packages. One of the really neat things about ILO is it manages the call stack so the contents of the V$SESSION view is always correct, even when you are nesting procedure calls. This can be a pain if you are using the DBMS_APPLICATION_INFO package directly. Karen is a great speaker (I love her accent) and it’s so cool when someone presents on a subject you already know well, but still makes you feel like a kid with a new toy.

“Creating Sub-Zero Dashboard Plug-In for APEX with Google Visualizations” by Roel Hartman did what it said in the title. He showed how to create plugins using the Google Visualization APIs, which was a bit over my head, then how to use the plugins he’d made, which looked real simple. As the repository of APEX pluggins grows it’s going to get easier and easier to produce very stylish apps.

Cheers

Tim…

Day 3

I made it through a panel session without talking constantly. I sat at the back of the “Database Development Panel” and kept my gob shut like a good boy.

The next two talks, “Oracle Extended SQL Trace Data for Developers” by Cary Millsap and “Co-operating with the Database” by Jonathan Lewis, were more developer focused. Being a DBA/Developer type myself, there weren’t many surprises for me, but I guess a lot of the straight development crowd got plenty to scribble down.

I’ve got quite a few clashing sessions tomorrow and I’m getting the early onset of conference burnout. Having the ACE Directors meeting before the conference makes it seem like a very long event. Early to bed tonight I think.

Cheers

Tim…

I’m not going to write about everything, but just post a few select highlights.

Day 1

There had been a timetable change that I had missed, so I arrived late for the “Messed up Apps: A Study of Performance Anti-patterns” talk by Cary Millsap. He knows his stuff, is a great presenter and he’s very pretty (in-joke).

Next it was “Take a Load Off: Load Testing your Web Applications: Oracle APEX, JDeveloper, Web Services, Anything” by Chris Muir. Chris demonstrated JMeter and SOAPUI for stress (and regression) testing web apps. I’m vaguely familiar with JMeter, but SOAPUI was new to me, so this was a really useful presentation. Chris has a very casual presenting style, which I like. It feels kinda like you are chatting about a product, rather than being preached to. Definitely get to one of his sessions if you can.

For the love of God, don’t let me into panel sessions, or gag me. It doesn’t matter if I’m on the panel or in the audience (as was the case here) I just can’t shut up. The “Experts Panel” in the “Performance, Scalability, and Security” track had the misfortune of my attendance. I’m sorry.

Day 2

“How to Write Efficient SQL” by Jonathan Lewis was a very cool session. I’m no Jonathan Lewis, but I’m pretty happy with my SQL and SQL tuning skills. When I’m writing and tuning SQL it’s all about shapes. Some shapes work and some shapes don’t. I can’t really explain what I am doing to others, but it works for me. Jonathan on the other hand can explain exactly what he is doing and why. Almost like a repeatable recipe for success. Very good. After the presentation I was talking with Alex Gorbachev and he suggested I read The Checklist Manifesto. On a similar vein, I’ve had some dealings with NLP in the past so I know how important it is to model successful outcomes, so maybe my next task should be to understand my tuning methodology, rather than assuming I will never lose it. It will probably end up looking like a poor-man’s version of Jonathan’s.

“Accessing the Oracle Database from Google (Apps, App Engine, Spreadsheets)” by Anjo Kolk was an eye opener. Some of the infrastructure Google has in place is still in its infancy, but it certainly makes you wonder what we might be doing in a few years. Maybe I can ditch Enterprise Manager Grid Control 11g in favour if iGoogle.

The “Database Development Sundown Session” was another example of why I should shut the hell up in a panel session. That said, one of my more cynical comments did elicit a hand shake from Bryn Llewellyn (PL/SQL Product Manager at Oracle).

One of the coolest moments of the day was during the reception with Oracle ACE Directors in the evening. The moment in question was when Victoria Lira and Lillian Buziak got a “Contributors of the Year” style award. If you are outside the Oracle ACE program you may not know who Victoria and Lillian are, but almost everything the ACE program achieves would be impossible without their organization skills. I’m very pleased they were recognized by this award.

Cheers

Tim…