Month: August 2010

Top 10 Reasons to Develop in a VM

The cost of providing Virtual Machines to all your developers can be quite high, especially in terms of initially setting it all up (e.g. a typical developer may require two VMs running concurrently, one for the database server, one for the app server; their desktops will require enough grunt to run these while they also run their dev tools, or they’ll need at least two computers on their desks; also, you’ll need a means of scaling down the data volumes if your database is too big); but you will gain a whole lot more productivity, sanity, happiness and love from your developers.

10. Fail safe.

If everything goes terribly wrong, simply restore to snapshot (duh, obviously).

9. Handle shifting conditions outside your project.

As often as I can (e.g. after a few weeks if a lot of development has gone on), I re-run the scripts on a VM based on a fresh copy of Prod – any changes that anyone else has made without my knowing it, which affect my scripts adversely, get picked up early.

8. Upgrade scripts never raise an error (unless something unexpected occurs).

It is normal for a typical upgrade script to raise many errors (e.g. “object not found” when running a standard “DROP x, CREATE x” script). However, I wrap any command like this with a custom exception handler that swallows the errors that I know are expected or benign. That way, when I hand over my upgrade scripts to the DBA, I can say, “if you get any error messages, something’s gone wrong”, which is a bit better than handing over a list of error messages they can safely ignore. Even better, I can add WHEN SQLERROR EXIT to the top of my upgrade script, so it exits out straightaway if the upgrade fails at any point.

7. Sanity Restore.

You’ve been beating your head against the wall for five minutes, and no-one’s around to add a second eye to your problem; you’re starting to wonder if the bug was something you introduced, or has always been there; and you can’t just log into production to test it. VM to the rescue – undo all your changes by restoring to an earlier snapshot, then see if your problem was a pre-existing issue.

6. Other Developers.

Let’s face it. Things would go a lot smoother if not for all the efforts of other developers to impede your progress by making random changes in the dev environment. Am I right? Well, with your private VM this is no longer a problem. Of course, with a private VM, if anything goes wrong, it’s incontrovertibly your fault now…

5. “Did you turn it off and on again?”

Finally, no need to nag the DBA to bounce the database server, or flush the shared pool, or in fact anything that requires more access than you’d usually get as a lowly developer. Need to increase that tablespace? Drop a couple hundred tables? No problem.

4. Real size estimates.

This works really well when you’re working with relatively small databases (i.e. where an entire copy of prod can be practically run in a VM). Run your upgrade script, which fails halfway through with a tablespace full error. Restore to snapshot, resize the appropriate datafile, re-run the upgrade. Rinse, repeat until no more “out of space” errors; now you know with a high degree of confidence how much extra space your upgrade requires, and for which datafiles.

3. Reduced down-time.

Dev server down? Being upgraded? Been appropriated by another department? No worries – your dev team can continue working, because they’ve got their VMs.

2. Did I say “Fail Safe” already?

I can’t emphasize this one enough. Also, the other side of “Other Developers” is that you are an Other Developer; and the mistakes you will, inevitably, make will never see the light of day (and draw everyone’s ire) if they’re made in your private VM.

1. Smug.

My last deployment to Test of a major release of an application executed perfectly, 100%, correct, first time. I couldn’t believe my eyes – I was so accustomed to my scripts failing for random reasons (usually from causes outside my control, natch). It was all thanks to my use of VMs to develop my fault-tolerant upgrade scripts. I was smug.

Generate DML/DDL/QUERY from SQL – the easy way

This is a comment on’s article “Generate DML/DDL/QUERY from SQL” – comments are not enabled on their blog so here’s my addendum.

The following query is offered as a means of generating a script to ONLINE all datafiles in a database:

select 'ALTER DATABASE DATAFILE '''|| name || ''' online ;'
from v$datafile;

I suggest an alternative method, which is both easier to write and easier to maintain:

select REPLACE(q'[
]','#NAME#',name) from v$datafile;

This way, the syntax within the DDL is unmuddied by the syntax required by the query to generate it. It’s immediately obvious that only single quotes will surround the name of the datafile in the generated DDL.

If  you’re on a pre-10g database, you can still use this method, but you’ll need to revert to the old quote-escape:

select REPLACE('
','#NAME#',name) from v$datafile;

InSync10 Day 2

Another good day in Melbourne. Heard Richard Foote talk about Indexing New Features in Oracle 11g release 1 and 2. One thing he demonstrated was the creation of an index on only part of a table – normally I’d use a function-based index for this sort of thing, but his technique results in an index that is useful without adding strange predicates to all relevant queries in the application; it involves creating a globally partitioned index, in an UNUSABLE state, then rebuilding only selected partitions. This could be very useful for customers who have the partitioning option.

Of interest to me was Discovering the Power to Save the Planet, presented by Robin Eckermann (Smart Grid Australia) – having worked for a short time at Western Power, it was interesting to hear his perspective on the future of the generation and distribution of power. He compared the state of the art in power to broadband, as it was 15 years ago – and asserts that the smart grid will enable all sorts of new applications for customers to regulate their demand intelligently, and is essential for the coming wave of electric cars.

After that was Steven Feuerstein‘s second talk, “Golden Rules for Developers“, which was well worth a good listen. I recommend you download and read the powerpoint if you missed it. If you take even just one of his recommendations (e.g. Don’t Repeat Anything, Don’t Take Shortcuts, Build On A Foundation, Don’t Code Alone), I think you will improve the quality of your code, reduce the cost of maintenance for your employer/client, and be much more satisfied with your work. I certainly intend to – I’ve been guilty of “starting from scratch” many times – I do carry around a portable hard drive with a large collection of bits and pieces I’ve collected along the way, but nothing I can just plug in and use with confidence. Steven also gave another PL/SQL talk at the end of the day, this time for DBAs, and that was interesting to me (as a developer). If you’re a DBA, but think that you have no need for PL/SQL, think again.

After that, during lunch, Steven announced the winners of the previous day’s quiz – and wouldn’t you know it, I won :)

Never satisfied

So I followed the great advice here to use the new COLLECT function in Oracle 10g to solve the common string-aggregation-in-SQL problem. I chose this solution over the others because it sorts the results as well, which was a user requirement. This is because the COLLECT function optionally takes an ORDER BY clause – even though the 10g documentation forgot to mention this. The 11g documentation was updated to include the additional syntax option, as well as the fact that COLLECT supports the DISTINCT (or UNIQUE) keyword as well to remove duplicates – testing indicates that this works in 10g as well.

This means that if I define a suitable type varchar2_ntt and a suitable function ntt_to_string, I can write a query like this:

SELECT dept,
            COLLECT(ename ORDER BY ename)
          AS varchar2_ntt)
       ) AS ename_list
FROM emp
GROUP BY dept;

That works fine. But now I want to combine this with some analytic functions, like this:

            COLLECT(ename ORDER BY ename)
          AS varchar2_ntt)
       ) AS ename_list,
          OVER (PARTITION BY dept
                ORDER BY sal DESC) AS topdog
FROM emp;

This doesn’t work because (a) the COLLECT requires a GROUP BY; and (b) the analytic function cannot be used along with a GROUP BY. What I’d expect to be able to do is use an analytic version of the COLLECT:

                    PARTITION BY dept
                    ORDER BY ename)
          AS varchar2_ntt)
       ) AS ename_list,
          OVER (PARTITION BY dept
                ORDER BY sal DESC) AS topdogFROM emp;

Us SQL developers are never satisfied, are we?

At this stage my best solution for this is to use a CTE:

WITH q AS (SELECT dept, ename, sal FROM emp)
       (SELECT ntt_to_string(
                   COLLECT(q2.ename ORDER BY q2.ename)
                 AS varchar2_ntt)
        FROM q q2
        WHERE q2.dept = q.dept
       ) AS ename_list,
          OVER (PARTITION BY dept
                ORDER BY sal DESC) AS topdog

InSync10 Day 1

After a scrumptious breakfast at the Armoury I headed in what I believed was the general direction of the Melbourne Convention Centre – after making a wrong turn I eventually spotted a footbridge over the river that rung a bell from my GoogleEarthing; after taking some photos I was finally at InSync10.

The first session was Connor McDonald’s 11g Features for Developers, which was an eclectic mix of bits and pieces you won’t get from reading the New Features Guide or from Oracle Marketing, along with some gratuitous use of photos of his kids.

Steven Feuerstein didn’t present next, instead he made us think by running a Developer Quiz. Much like the PL/SQL Challenge (at which, by the way, you should sign up this instant if you haven’t already), it was fun and challenging, and I suspect everyone learned at least one new thing. Me, I learned what SUBSTR returns if the 2nd parameter (which normally starts at 1) is zero. As always, Steven was completely open to criticism, and with Connor and Tom in the room he certainly didn’t get off scot free :)

As it happened, I happened to disagree on one question, which was regarding the USING clause and how many bind variables must be supplied to a given statement. One of the responses (from memory) was that “you must always supply as many bind variables as there are placeholders”. I knew that if the statement being executed was SQL, the number of bind variables must match the number of placeholders, even if some of them have the same names (e.g. INSERT INTO emp VALUES (:a, :b, :a, :b) would require four bind variables). However, I also knew that if the statement is a PL/SQL block, each unique placeholder requires a different bind variable – if the placeholder appears more than once in the block, you don’t repeat the bind variable in the USING clause. I therefore ticked this answer as “correct” – if, for example, the block was BEGIN call_something(:a, :b, :a, :b); END;, you would have to provide two bind variables, because that is how many distinct placeholders there are in the block.

There was some discussion about this, because the answer was marked incorrect – according to Steven the number of placeholders in the block above is four, not two – and I agree that the meaning of a “placeholder” is different to a “bind variable”, although I usually speak as if to conflate the two ideas. However, I still hold to the opinion that a “placeholder” in the context of a PL/SQL block is a reference to this: :a, and I would say that the one placeholder :a appears twice in the PL/SQL block. I believe I have the documentation to back me up:

If the dynamic statement represents a PL/SQL block, the rules for duplicate placeholders are different. Each unique placeholder maps to a single item in the USING clause. If the same placeholder appears two or more times, all references to that name correspond to one bind argument in the USING clause. In Example 7-7, all references to the placeholder x are associated with the first bind argument a, and the second unique placeholder y is associated with the second bind argument b.

(emphasis added) Source: Using Duplicate Placeholders with Dynamic SQL

This is really just an argument over semantics, so no big deal. Some of the other questions had much more interesting discussion, so it was well worth attending. If you’re in Perth on Friday, Steven is running it again (I won’t be able to attend, unfortunately). I presume he will be using different questions…

After that I went for a walk through Melbourne, since it was sunny outside. The climate in Melbourne, I discovered, is a tad different to Perth. Wandering along the riverside, I ended up experiencing a blast of all four seasons within the space of an hour – a lovely spring breeze, a somewhat warmish summer, then a cold blustering windy autumn (a bit out of order that) – there was a few seconds where it was difficult to remain upright – followed by a sudden rainstorm. I managed to find shelter under one of the many bridges that cross the river, waited for about ten minutes, then was able to walk back to the centre without getting any wetter. In fact, by the time I got back to the convention centre it was sunny again.

After a light lunch it was my turn to talk, and I think my presentation on Apex Themes and Templates went quite well. I appreciated the comments and questions that came back, and had some further discussion with a few people afterward as well, which was good.

I forwent Connor’s excellent Partitioning presentation which I’ve heard before, instead heard Kyle Hayle – Database Performance Made Easy – demonstrate the virtues of database tuning using a tool such as the one he’s produced at Embarcadero. I haven’t made use of many graphical tuning tools before, preferring just “the numbers”, but Kyle made an excellent case for the use of pictures instead of words for not only visualising the workload on the database (such as presented by Oracle’s Enterprise Manager, which Kyle had a hand in), but also for visualising the structure of a query. Personally, I’ve grown accustomed to using the traditional explain plan and I suspect I’ll probably continue to, but the Embarcadero product does have some features that automate some of the work I’d normally do by hand (such as examining the constraints on the tables and obtaining filter percentages).

Last of all, Tom Kyte presented The Best Way, in which he laid to rest for once and for all the answer to the age-old (and oft-repeated) question, “what is The Best Way to …?”. Finally, we can stop arguing over which way is worthy of being called Best Practice, and get on with the job ;)

Went out for a nice dinner at a small japanese restaurant, which had a great cozy atmosphere, and on the way back to the hotel was surprised by these great explosions of flame from these pillars. I could feel the heat from hundreds of meters away. At the end, a quick stop at a store allowed me to procure what I’d been coveting all day: Farmer’s Union Iced Coffee.