Entries by Doug Burns

I only noticed when I woke up this morning that paragraph breaks have disappeared on this blog. I haven't been working on the formatting, so it's pretty annoying to say the least. Then I realised what probably caused it. I was experimenting again with importing this blog to wordpress.com because I was going through my latest flirtation with a move. I don't really want to move, but there seem to be a few advantages there.

One of the things Wordpress' import utility does is disable and re-enable some blog settings. It looks like it's stuffed up my template

I'll fix it as soon as I can, but work comes first, I'm afraid.

According to a survey, watching football helps men express their feelings

(Although I think one or two people might have been less than honest when the crying issue was raised!)

After one of my Hotsos Presentations, I had a discussion with one of the delegates about how his site used parallel execution for some jobs purely to take advantage of Direct Path Reads. He suggested that he thought that was why the parallel 2 tests I ran were always significantly faster than serial. I spoke to a couple of people about this and wondered how I could test it, until Jonathan Lewis pointed out the _serial_direct_read hidden parameter. The reason those last two words are in italics is that you shouldn't set the values of hidden parameters without discussing it with support. You have been warned

As I don't run my business on my server at home, it' s safe for me to test (now that I've bitten the bullet and run a cable down to the server room - it's the first decent playtime I've had for ages). Based on a simple test of a full table-scan on a 3-disk software RAID array, there was a small improvement, but I'm not sure it justifies switching to Direct Path Reads except where Oracle would normally use them anyway.

I ran the tests multiples times, immediately after startup to pick up any timing variations, but the timings were pretty stable. I also looked through raw trace files to check that everything looked as expected.

_serial_direct_read = false - average of 6 minutes
_serial_direct_read = true - average of 5 minutes 15 seconds

So direct path was about 10-15% quicker for this very basic full scan of a 9Gb / 65 million row table on my particular configuration

Updated 23rd May

Based on comments from Alex and Jonathan, I thought it might be worth adding snippets of the first few trace lines for direct and non-direct i/o. So you can blame them for the longer post I also had to re-run the tests because some other tests I'd been running overnight had deleted the existing trace files

First the non-direct path version.

PARSING IN CURSOR #1 len=59 dep=0 uid=27 oct=3 lid=27 tim=1121447319073032 
hv=819013003 ad='7931a52c' 
select /*+ noparallel(test_tab1) */ count(*) from test_tab1
END OF STMT
PARSE #1:c=258960,e=282301,p=30,cr=490,cu=0,mis=1,r=0,dep=0,og=1,tim=1121447319073015
EXEC #1:c=1000,e=120,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1121447319073424
WAIT #1: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 
obj#=10321 tim=1121447319073538
WAIT #1: nam='db file sequential read' ela= 261 file#=6 block#=9 blocks=1 obj#=10645 tim=1121447319074867
WAIT #1: nam='db file scattered read' ela= 13255 file#=6 block#=10 blocks=128 obj#=10645 tim=1121447319090157
WAIT #1: nam='db file scattered read' ela= 12671 file#=6 block#=138 blocks=128 obj#=10645 tim=1121447319107768
WAIT #1: nam='db file scattered read' ela= 59119 file#=6 block#=266 blocks=128 obj#=10645 tim=1121447319170777
WAIT #1: nam='db file scattered read' ela= 13354 file#=6 block#=394 blocks=128 obj#=10645 tim=1121447319187905
WAIT #1: nam='db file scattered read' ela= 39190 file#=6 block#=522 blocks=128 obj#=10645 tim=1121447319231031
WAIT #1: nam='db file scattered read' ela= 44925 file#=6 block#=650 blocks=128 obj#=10645 tim=1121447319279664
WAIT #1: nam='db file scattered read' ela= 53011 file#=6 block#=778 blocks=128 obj#=10645 tim=1121447319336530
WAIT #1: nam='db file scattered read' ela= 13912 file#=6 block#=906 blocks=128 obj#=10645 tim=1121447319354510
WAIT #1: nam='db file scattered read' ela= 13537 file#=6 block#=1034 blocks=128 obj#=10645 tim=1121447319371915
WAIT #1: nam='db file scattered read' ela= 155474 file#=6 block#=1162 blocks=128 obj#=10645 tim=1121447319531089
WAIT #1: nam='db file scattered read' ela= 121519 file#=6 block#=1290 blocks=128 obj#=10645 tim=1121447319656727
WAIT #1: nam='db file scattered read' ela= 14227 file#=6 block#=1418 blocks=128 obj#=10645 tim=1121447319674802
WAIT #1: nam='db file scattered read' ela= 14165 file#=6 block#=1546 blocks=128 obj#=10645 tim=1121447319692961
WAIT #1: nam='db file scattered read' ela= 14300 file#=6 block#=1674 blocks=128 obj#=10645 tim=1121447319711088
WAIT #1: nam='db file scattered read' ela= 13211 file#=6 block#=1802 blocks=128 obj#=10645 tim=1121447319728129
WAIT #1: nam='db file scattered read' ela= 12815 file#=6 block#=1930 blocks=128 obj#=10645 tim=1121447319744562
WAIT #1: nam='db file scattered read' ela= 170935 file#=6 block#=2058 blocks=128 obj#=10645 tim=1121447319919103
WAIT #1: nam='db file scattered read' ela= 13233 file#=6 block#=2186 blocks=128 obj#=10645 tim=1121447319936146
WAIT #1: nam='db file scattered read' ela= 12734 file#=6 block#=2314 blocks=128 obj#=10645 tim=1121447319952560
WAIT #1: nam='db file scattered read' ela= 12588 file#=6 block#=2442 blocks=128 obj#=10645 tim=1121447319968803
WAIT #1: nam='db file scattered read' ela= 12784 file#=6 block#=2570 blocks=128 obj#=10645 tim=1121447319985244
WAIT #1: nam='db file scattered read' ela= 12841 file#=6 block#=2698 blocks=128 obj#=10645 tim=1121447320001782
WAIT #1: nam='db file scattered read' ela= 119155 file#=6 block#=2826 blocks=128 obj#=10645 tim=1121447320124638
WAIT #1: nam='db file scattered read' ela= 14094 file#=6 block#=2954 blocks=128 obj#=10645 tim=1121447320142658
WAIT #1: nam='db file scattered read' ela= 14177 file#=6 block#=3082 blocks=128 obj#=10645 tim=1121447320160695
WAIT #1: nam='db file scattered read' ela= 14348 file#=6 block#=3210 blocks=128 obj#=10645 tim=1121447320179073
WAIT #1: nam='db file scattered read' ela= 13571 file#=6 block#=3338 blocks=128 obj#=10645 tim=1121447320196489
WAIT #1: nam='db file scattered read' ela= 12636 file#=6 block#=3466 blocks=128 obj#=10645 tim=1121447320212753
WAIT #1: nam='db file scattered read' ela= 180444 file#=6 block#=3594 blocks=128 obj#=10645 tim=1121447320396801
WAIT #1: nam='db file scattered read' ela= 13109 file#=6 block#=3722 blocks=128 obj#=10645 tim=1121447320413796
WAIT #1: nam='db file scattered read' ela= 12731 file#=6 block#=3850 blocks=128 obj#=10645 tim=1121447320430198
WAIT #1: nam='db file scattered read' ela= 12603 file#=6 block#=3978 blocks=127 obj#=10645 tim=1121447320446431
WAIT #1: nam='db file scattered read' ela= 136330 file#=6 block#=12297 blocks=128 obj#=10645 tim=1121447320586371
WAIT #1: nam='db file scattered read' ela= 221123 file#=6 block#=12425 blocks=128 obj#=10645 tim=1121447320811727
WAIT #1: nam='db file scattered read' ela= 12813 file#=6 block#=12553 blocks=128 obj#=10645 tim=1121447320828202

Now the direct path version.

PARSING IN CURSOR #2 len=59 dep=0 uid=27 oct=3 lid=27 tim=1121446553535587 
hv=819013003 ad='792091ec'
select /*+ noparallel(test_tab1) **/ count(*) from test_tab1
END OF STMT
PARSE #2:c=1000,e=210,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1121446553535576
EXEC #2:c=0,e=180,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1121446553535975
WAIT #2: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1121446553536079
WAIT #2: nam='reliable message' ela= 178 channel context=2100923636 channel handle=2100769092 broadcast
message=2101549144 obj#=-1 tim=1121446553537492
WAIT #2: nam='enq: KO - fast object checkpoint' ela= 142 name|mode=1263468550 2=65548 0=1 
obj#=-1 tim=1121446553537742
WAIT #2: nam='direct path read' ela= 34 file number=6 first dba=10 block cnt=118 obj#=10645 tim=1121446553552515
WAIT #2: nam='direct path read' ela= 23 file number=6 first dba=128 block cnt=128 obj#=10645 tim=1121446553560209
WAIT #2: nam='direct path read' ela= 37 file number=6 first dba=256 block cnt=128 obj#=10645 tim=1121446553567431
WAIT #2: nam='direct path read' ela= 24 file number=6 first dba=384 block cnt=128 obj#=10645 tim=1121446553574642
WAIT #2: nam='direct path read' ela= 36 file number=6 first dba=512 block cnt=128 obj#=10645 tim=1121446553581789
WAIT #2: nam='direct path read' ela= 22 file number=6 first dba=640 block cnt=128 obj#=10645 tim=1121446553589104
WAIT #2: nam='direct path read' ela= 36 file number=6 first dba=768 block cnt=128 obj#=10645 tim=1121446553596289
WAIT #2: nam='direct path read' ela= 24 file number=6 first dba=896 block cnt=128 obj#=10645 tim=1121446553603503
WAIT #2: nam='direct path read' ela= 36 file number=6 first dba=1024 block cnt=128 obj#=10645 tim=1121446553610829
WAIT #2: nam='direct path read' ela= 22 file number=6 first dba=1152 block cnt=128 obj#=10645 tim=1121446553618050
WAIT #2: nam='direct path read' ela= 38 file number=6 first dba=1280 block cnt=128 obj#=10645 tim=1121446553625165
WAIT #2: nam='direct path read' ela= 21 file number=6 first dba=1408 block cnt=128 obj#=10645 tim=1121446553632616
WAIT #2: nam='direct path read' ela= 36 file number=6 first dba=1536 block cnt=128 obj#=10645 tim=1121446553639771
WAIT #2: nam='direct path read' ela= 23 file number=6 first dba=1664 block cnt=128 obj#=10645 tim=1121446553647160
WAIT #2: nam='direct path read' ela= 38 file number=6 first dba=1792 block cnt=128 obj#=10645 tim=1121446553654347
WAIT #2: nam='direct path read' ela= 21 file number=6 first dba=1920 block cnt=128 obj#=10645 tim=1121446553661575
WAIT #2: nam='direct path read' ela= 37 file number=6 first dba=2048 block cnt=128 obj#=10645 tim=1121446553668804
WAIT #2: nam='direct path read' ela= 21 file number=6 first dba=2176 block cnt=128 obj#=10645 tim=1121446553676065
WAIT #2: nam='direct path read' ela= 36 file number=6 first dba=2304 block cnt=128 obj#=10645 tim=1121446553683242
WAIT #2: nam='direct path read' ela= 21 file number=6 first dba=2432 block cnt=128 obj#=10645 tim=1121446553690664
WAIT #2: nam='direct path read' ela= 38 file number=6 first dba=2560 block cnt=128 obj#=10645 tim=1121446553697846
WAIT #2: nam='direct path read' ela= 23 file number=6 first dba=2688 block cnt=128 obj#=10645 tim=1121446553705100
WAIT #2: nam='direct path read' ela= 37 file number=6 first dba=2816 block cnt=128 obj#=10645 tim=1121446553712275
WAIT #2: nam='direct path read' ela= 22 file number=6 first dba=2944 block cnt=128 obj#=10645 tim=1121446553719487
WAIT #2: nam='direct path read' ela= 36 file number=6 first dba=3072 block cnt=128 obj#=10645 tim=1121446553726668
WAIT #2: nam='direct path read' ela= 23 file number=6 first dba=3200 block cnt=128 obj#=10645 tim=1121446553734055
WAIT #2: nam='direct path read' ela= 35 file number=6 first dba=3328 block cnt=128 obj#=10645 tim=1121446553741235
WAIT #2: nam='direct path read' ela= 23 file number=6 first dba=3456 block cnt=128 obj#=10645 tim=1121446553748471
WAIT #2: nam='direct path read' ela= 37 file number=6 first dba=3584 block cnt=128 obj#=10645 tim=1121446553755657
WAIT #2: nam='direct path read' ela= 21 file number=6 first dba=3712 block cnt=128 obj#=10645 tim=1121446553762872
WAIT #2: nam='direct path read' ela= 38 file number=6 first dba=3840 block cnt=128 obj#=10645 tim=1121446553770034
WAIT #2: nam='direct path read' ela= 12 file number=6 first dba=3968 block cnt=128 obj#=10645 tim=1121446553772397
WAIT #2: nam='direct path read' ela= 36 file number=6 first dba=4096 block cnt=9 obj#=10645 tim=1121446553778625

Later on, I might even be able to put some explanation round this! For now, it's off to work.

That Peter Robson chap is taking over my blog again. I do wish he'd get his own sorted out but, in the meantime, I'll let him squat here for a while. Over to you, Peter ...


In an earlier guest submission, I made reference to following up a rather critical review of the presentation by Scott Ambler at Collaborate 06 on agile database techniques.

Ambler included a paper to go with his presentation, which I have examined.

I stand by those original comments. In particular, I am shocked by this comment from Ambler: 'you do not need to do data modelling up front, its a waste of time, its the kiss of death'. A thoroughly irresponsible and outrageous assertion.

What was presented that day was nothing less than the recycling of a previously short-lived IT fashion, namely RAD, with a few added extras. Remember that? Rapid Application Development. I thought it odd that Ambler never made mention of the close similarity. Then perhaps not, as we never hear of it nowadays. A brief web search will confirm the similarity between the two approaches. There are differences too - check out Simon Evan's blog for a balanced comparison of the two approaches. Although he makes the case for a difference between the two, the development of agile from a RAD baseline is pretty clear. So generically, one can see them as being from the same origin.

Ambler's paper on Agile Database Techniques is insubstantial and misleading. Lets look at some of the things he says:

'The goal of the Agile Data method is to define strategies that enable IT professionals to work together effectively on the data aspects of software systems.' Really? So what's new - some of us have been doing this very thing for decades. (PS - Don't you think that word 'method' should be capitalised? )

'Enterprise issues. Development teams must consider and act appropriately regarding enterprise issues.' I cannot understand what the purpose of stating the obvious is in this context. As if any self-respecting developer would do anything other than this. One is left wondering if by making non-controversial and obvious statements, some credibility is being attached to the agile fluff by association.

'IT professionals must work together effectively, actively striving to overcome the challenges that make it difficult to do so.' Would anyone ever recommend IT staff to do other than work together effectively? Again, another padding statement of the obvious.

Here is a definition: 'An agile DBA is anyone who is actively involved with the creation and evolution of the data aspects of one or more applications.' Now some DBAs have been doing this for decades - does this mean that a) they were always agile anyway, and b) the agile DBA is in fact an old concept? The assertion is simply misleading, as though the cult of agility is trying to sequestrate long established practices and concepts as something unique to their way of seeing things.

'The critical new skills for agile DBAs enable them to work in an evolutionary manner.' Yes, working in an evolutionary manner is part of the critical skills set, but they owe NOTHING to the agile approach � they have been in place for years! Interestingly, despite referring to 'new skills', nowhere are they actually enunciated, other than under the vague umbrella of terms such as 'evolution' and 'iteration', terms which are part of the bedrock of sound data and systems analysis and modelling.

A diagram is presented. Thousand words etc. Well, in a technical diagram, if one is presenting a new approach to doing something, one would expect the symbology to be defined. There are arrows here, but nowhere are they actually defined in terms of their purpose and function. Yes, we can guess, but in a technical presentation, is that good enough? The very process of definition itself appears somewhat weak in this paper.

The interesting concept of the sandbox was introduced, but in such a way as to leave open the question of whether it is an agile feature or not. Of course it is not. It is defined - as '... a fully functioning environment in which a system may be built, tested, and/or run.' 'Environment'? Unless that word is defined, the overall definition is simply vague to the point of uselessness. Why does the word 'isolated' not feature? Why not discuss the relevance of VM to the sandbox approach?

This paper is shallow, uninformative, and completely overlooks if not denies the serious and careful work done by conscientious IT professionals over many years. I take great exception to the way the advocates of new fads spend time rubbishing all that has gone before. Whatever Ambler may say, or allow his reader to infer, the agile approach is most certainly not the IT panacea he would have us believe.

For another examination of agile software in general, look at Wikipedia. Read down, and you will find a discussion of those areas where agile software may not be so suitable :

- Large scale development efforts (greater than 20 developers)
- Distributed development efforts (non-co-located teams)
- Mission- and life-critical efforts

Have a look at what Niall Litchfield has to say on the subject - I absolutely agree with him.

Speaking personally, I would prefer not to fly in an aircraft in which the computer systems were built by a team of agile programmers. This is not so flippant as it may seem. Ed Yourdon, in a talk to a meeting of New York City SPIN (that's the Software Process Improvement Network - yes really!), [see blog by Francis Hwang] noted the increasing litigation directed at IT systems. One of the sure defences against litigation is complete documentation, and it is this area which would make a failed agile system highly vulnerable. The agile crowd don't dismiss documentation out of hand, but would reduce considerably the amount that is provided with a system.

It seems to me that Ambler has taken some of the core values of data methodologies, wrapped them up in a flashy covering, and presented them as something new, while castigating the very sources from which he cherry picks. This is to be regretted, particularly when the IT industry as a whole is struggling to establish truly professional standards. To paraphrase a comment from a friend while discussing this subject, he suggested that the presentation style had more in common with an agile used car salesman than a serious IT professional - a point of view on which I simply could not possibly comment ...

I noticed a post on the Oracle-L list by Connor McDonald, who is a UKOUG stalwart and Oak Table member, about FreeNAS. It struck me how often I've been in a situation at work when I've been touting around storage vendors to get some very low cost, very high capacity storage for disk backups. It's often a short term requirement. Judging by Connor's comments, it sounds like it was a big success for him and might be worth bearing in mind the next time you're in that situation. Anyway, here are his unedited comments.

"Unrelated to use of Oracle, but during a recent series of large conversions, we had need to take several backups of our database - couple of thousand bucks got us some cheapy ATA and USB disks on a PC, whacked in an FreeNAS iso image on CD and voila! A few terabytes of NFS storage...it worked an absolute treat."