jump to navigation

Friday Philosophy – When Tech Fails to Deliver, is it Always a Problem? December 9, 2016

Posted by mwidlake in Architecture, development, ethics, Friday Philosophy.
Tags: , ,

I nipped out to the local supermarket this lunch time to get stuff. I use one of those self-use barcode scanners to log all the goods I put in my basket (apart from the bottle of whisky I was stealing). I then go to the payment machine, scan the “finish shopping” barcode and try to pay. I can’t pay.


I can’t pay as I bought some paracetamol (note to US readers, you know it as acetaminophen). It turns out you need to be 12 to buy paracetamol. Fair enough, but why did I have to stand there and waste 30 seconds of my life before the assistant for the area noticed and came over? She had to uses her special device to access the permissions screen, check I was 12 (the greying beard helps) and authorise it.

I asked why I had to wait. “So I can ensure you are old enough – the machine does not know.” But it does! Or at least it should. I’m using their self-scan service for which I have to be registered. They know my name, address, age, hair colour and inside leg measurement. The system knows I am old enough. Plus I have to pay with a credit/debit card (no cash option with this system). You can’t have a credit card until you are 18 in the UK so by using one of them it knows I am old enough to buy the pills – and even the bottle of whisky I was stealing. And when you use any card, it checks your details. So if I was using a debit card it could check my age at that point and stop me when it makes the check. It’s possible and should be done!

The assistant had wandered off long before I finished making this logical case. I was just an annoying customer and she’d done what I needed her to do. But it really annoyed me – it is possible for the system to check me using technology and the data at hand, and not make me wait. The problem is, they were too lazy to build this limited smarts into the system!


There is a lesson here. And that lesson is this – I should stop being such a self-centred, argumentative and miserable old sod. Firstly, I had to wait 30 seconds (and I am probably exaggerating that). Big deal, I had hardly been inconvenienced and it was a lot quicker than going to a normal till. Secondly, the assistant can’t do anything about the software behind the system. I mean, many of us spend our lives working on computer systems and often we can’t make any changes. Thirdly, I am aware that some parents give their children their credit card & number (the idiots!) so even though it is illegal to do this, the result is there a lot of people under the age of credit who have the means to pay for dangerous things (booze, cigarettes, paracetamol, knives, DIY expanding foam, ‘Viz’ magazine…).

Just because something is possible with the data to hand, sometimes it is not really worth much effort to make it happen.

And sometimes, although it seems logical & sensible given all the parameters (they have my info, no one but me should be using that card) in the real world those rules and data associations are not reliable. There is no enforced RI on our lives, at best there is a set of intended/encouraged limits. A person checking my age is way more reliable than some algorithm in this case.

So next time I whine about waiting 30 seconds in the queue, I hope the assistant just gives me a withering look and tells me to grow up.

I also hope they do not check my basket for un-scanned booze.

(* Just for the record, everything about the whisky was untrue. It was gin).

((And being serious, such system prevent fraud by 2 methods.
The first is that 1 in X times you get re-scanned. The assistant has no idea if they scan anything you had not and this is on purpose – so there is no scene in the shop. But the comparison is made and recorded, for further action.
The second is that apparently they can spot likely cheats just by the data you give them when you sign up and your spending habits. Now that is ‘Big Data Analysis’.

Getting Your Transaction SCN – USERENV(COMMITSCN) January 19, 2016

Posted by mwidlake in development, performance, SQL.
Tags: , , ,

A few days ago I was introduced (or re-introduced) to USERENV(‘COMMITSCN’) by Jonathan Lewis. This is an internal function that allows limited access to the SCN of your transaction.

I was trying to find a way to get the actual commit SCN easily as it struck me that Oracle would have it to hand somewhere and it would be unique to the change and generated very efficiently. I could not find anything to do it so I asked Jonathan and he pointed me straight to this post he did about it a while back. What a nice chap. However, the post is from 1999 (last CENTURY!) so I thought I should just check it out first…

I’ll say up front that this is an undocumented feature of a now-deprecated function. Well, almost undocumented – older SQL reference manuals mention that ‘commitscn’ returns a NUMBER as opposed to the VARCHAR2 returned by most parameters you can use with USERENV, but it does not list it as a valid parameter for that function.
USERENV has been deprecated since Oracle 10g (see the old 10g documentation link here about USERENV) and you have been instructed to use SYS_CONTEXT(‘userenv’,’parameter‘) as described in the 12c database SQL reference manual here. However, there is no way to get the commit SCN from SYS_CONTEXT that I can find, so I thought I’d check out if USERENV(‘COMMITSCN’) still works. It does, on my version of Oracle!

There are some strict limits to this function. To begin with, you can’t select it, you can only use it on insert/update:

-- attempt a simple select of the SCN
mdw> select userenv('commitscn') from dual;
select userenv('commitscn') from dual
ERROR at line 1:
ORA-01725: USERENV('COMMITSCN')  not allowed here

--But I can use in an insert,
mdw> insert into test_scn (seq_no,vc1,scn_no)
  2  values (100,'abcd',userenv('commitscn'))

1 row created.

mdw> select * from test_scn where seq_no=100

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
       100 abcd         11144739

-- Now commit my new record
mdw> commit;
Commit complete.

mdw> select * from test_scn where seq_no=100
  2  /

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
       100 abcd         11144753

--LOOK at the value for SCN_NO now! Compare to before the commit!

If you look at the above you will see a couple of things. The first is that, as I said, you cannot SELECT the function USERENV(‘COMMITSCN’).
The other is, though a value is put into the column when I insert a row using it, and I see that when I query the information back, it changes when I commit. This is because Oracle is recording something at the point of commit, not at the point of the SQL statement running – and the new SCN is only generated when you commit. A lot could have happened since I did the INSERT, I might have gone for a cup of tea and a batch job kicked off doing 1 million transactions, each with it’s own SCN. So though a placeholder of the current SCN is put into your view of the table row, the value put in the actual table is generated at the time of the commit.

Another limiting rule is that you can only reference USERENV(‘COMMITSCN’) once in a transaction, for one row. If I try and create 2 rows using the function in the same transaction I get an error, if I try to update more than 1 row I get an error:

mdw> insert into test_scn (seq_no,vc1,scn_no)
  2  values (101,'abcd',userenv('commitscn'))

1 row created.

mdw> insert into test_scn (seq_no,vc1,scn_no)
  2  values (102,'abcd',userenv('commitscn'))

insert into test_scn (seq_no,vc1,scn_no)
ERROR at line 1:
ORA-01721: USERENV(COMMITSCN) invoked more than once in a transaction

-- now test updating several records
mdw> commit;
Commit complete.

mdw> select * from test_scn;

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
         1 AAAAAAA      11143743
         2 AAAAAAA      11143746
         3 AAAAAAA      11143749
         4 AAAAAAA      11143774
         5 AAAAAAA      11143777
       100 abcd         11144753
       101 abcd         11145543

mdw> update test_scn set scn_no = userenv('commitscn');
update test_scn set scn_no = userenv('commitscn')
ERROR at line 1:
ORA-01721: USERENV(COMMITSCN) invoked more than once in a transaction

-- but one row works
mdw> update test_scn set scn_no = userenv('commitscn') where rownum =1;
1 row updated.

USERENV(‘COMMITSCN’) is old, undocumented and limited in use. So why am I looking at it, let alone even telling you all about it? {Apart from the fact that a lot of you *love* this sort of obscure, tid-bitty stuff}. Well, as there is no replacement for it that I am aware of. You can get the current SCN in a couple of ways, the easiest probably being to get it from V$DATABASE:

mdw> select current_scn from v$database;
any key>


However, that is the last SCN used at the time you check it and is not the SCN when you commit. ie it is a different thing. I always find it irksome on those odd occasions when something is deprecated in Oracle with nothing really to replace it.

I just demonstrate again that USERENV(‘COMMITSCN’) is a little special below, and not the same as just selecting SCN from V$DATABASE. Before I go any further, I think the value USERENV(‘COMMITSCN’) puts into the table is the actual COMMIT SCN minus 1. I mostly think this as Jonathan said so :-). I do see each time I run this test that the first select from V$DATABASE and then my insert and a commit straight away results in a value in the table 2 higher than the select.

Further iterations (2nd and 3rd in this case) show the value selected from V$DATABASE and the value inserted into TEST_SCN immediately after are the same, and are 3 higher than the previous iteration. I anticipated an increase of two, once for the change to the UNDO tablespace for the insert and once for the insert. I am not sure where the third one comes in.

However, in the fourth iteration I have a PAUSE in my SQL*Plus script between checking V$DATABASE and doing my insert and, in a second session, I do some simple inserts and commits {it does not matter what, so I don’t show it}. Thus the difference between the SCN I collected from V$DATABASE and the value inserted into the table.
Finally, in the fifth iteration, I check the value in V$DATABASE, do the insert, query it back and see the two are the same. And THEN I pause so I can do some changes and commit them in my second session. After I’ve done that I continue my first session which commits my latest insert into TEST_SCN. I check the values actually stored in the table and, just as at the top of this post, you see that the value actually preserved in the table is a later SCN than the placeholder one. It is doing something special.

(the below has the noddy code to create my test table and sequence as well as the test)

-- test_scn1
--create table test_scn (seq_no number,vc1 varchar2(10),scn_no number)
-- create sequence scn_seq;
select current_scn from v$database;
insert into test_scn values (scn_seq.nextval,'AAAAAAA',userenv('commitscn'));
select * from test_scn;
select current_scn from v$database;
insert into test_scn values (scn_seq.nextval,'AAAAAAA',userenv('commitscn'));
select * from test_scn;
select current_scn from v$database;
insert into test_scn values (scn_seq.nextval,'AAAAAAA',userenv('commitscn'));
select * from test_scn;
select current_scn from v$database;
pause 'commit some stuff in second session and then press any key'
insert into test_scn values (scn_seq.nextval,'AAAAAAA',userenv('commitscn'));
select * from test_scn;
select current_scn from v$database;
insert into test_scn values (scn_seq.nextval,'AAAAAAA',userenv('commitscn'));
pause 'commit some stuff in second session again and then press any key'
select * from test_scn;
select * from test_scn;
select current_scn from v$database;

-- the output of the test
mdw> @test_scn1


-- Create and commit 1 row
1 row created.
Commit complete.

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
-- note that the inserted SCN is 2 higher than the current SCN.

--Same steps, 2nd iteration

1 row created.
Commit complete.

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
        12 AAAAAAA      11147814
-- select SCN and inserted SCN are the same, 3 higher than first iteration

-- same steps, 3rd iteration

1 row created.
Commit complete.

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
        12 AAAAAAA      11147814
        13 AAAAAAA      11147817
-- select SCN and inserted SCN are the same, 3 higher than first iteration

-- 4th iteration, a pause

'commit some stuff in second session and then press any key'
-- I did indeed change and commit some stuff in second session, before I create my record in test_scn

1 row created.
Commit complete.

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
        12 AAAAAAA      11147814
        13 AAAAAAA      11147817
        14 AAAAAAA      11147831
-- larger gap in SCN (11147817 to 11147831

-- 5th iteration, pause now after insert and before commit

1 row created.
'commit some stuff in second session again and then press any key'
-- I did indeed change and commit some stuff in second session 

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
        12 AAAAAAA      11147814
        13 AAAAAAA      11147817
        14 AAAAAAA      11147831
        15 AAAAAAA      11147834

-- Notice the current_scn from V$DATABASE and the last row in the table match, 11147834..

Commit complete.

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
        12 AAAAAAA      11147814
        13 AAAAAAA      11147817
        14 AAAAAAA      11147831
        15 AAAAAAA      11147842

-- But after the commit the SCN in row "15" has increased to 11147842.


-- and the next check of V$DATABASE SCN shows the usual increase of 3 by the commit.

As you can see from the above, USERENV(‘COMMITSCN’) is doing something a little special and, despite all the limitations, I might actually have a use for it…

Where do my trace files go? V$DIAG_INFO October 19, 2015

Posted by mwidlake in development, performance, SQL Developer.
Tags: , ,
1 comment so far

Where do oracle trace files go? I don’t know why this piece of info will not stick in my head, I seem to have to look it up 3 or 4 times a year.

If only I had an easy way to find out. There is a very easy way to find out – and that piece of info won’t stay in my head either. So this really is a blog post just for stupid, forgetful me.

V$DIAG_INFO has been available since oracle V11. All the trace files go into the Automatic Diagnostic Repository (ADR) by default.

ora122> desc v$diag_info
 Name                                                                Null?    Type
 ------------------------------------------------------------------- -------- ---------------
 INST_ID                                                                      NUMBER
 NAME                                                                         VARCHAR2(64)
 VALUE                                                                        VARCHAR2(512)
 CON_ID                                                                       NUMBER

Quick sql*plus script to get it out:

-- diag_info
-- quick check of the new v$diag_info view that came in with 11
col inst_id form 9999 head inst
col name form a25
col value form a60 wrap
spool diag_info.lst
set lines 120
select * from v$diag_info
order by name
spool off


 INST_ID NAME                 VALUE                                                            CON_ID
-------- -------------------- ---------------------------------------------------------------- -------
       1 Diag Enabled          TRUE                                                                  0
       1 ADR Base              D:\APP\ORACLE                                                         0
       1 ADR Home              D:\APP\ORACLE\diag\rdbms\ora122\ora122                                0
       1 Diag Trace            D:\APP\ORACLE\diag\rdbms\ora122\ora122\trace                          0
       1 Diag Alert            D:\APP\ORACLE\diag\rdbms\ora122\ora122\alert                          0
       1 Diag Incident         D:\APP\ORACLE\diag\rdbms\ora122\ora122\incident                       0
       1 Diag Cdump            D:\app\oracle\diag\rdbms\ora122\ora122\cdump                          0
       1 Health Monitor        D:\APP\ORACLE\diag\rdbms\ora122\ora122\hm                             0
       1 Default Trace File    D:\APP\ORACLE\diag\rdbms\ora122\ora122\trace\ora122_ora_7416.trc      0
       1 Active Problem Count  0                                                                     0
       1 Active Incident Count 0                                                                     0

I should add some notes later about setting the trace file identifier…
Ohhh, OK, I’ll do it now. To make it easier to identify your trace file, set tracefile_identifier

alter session set tracefile_identifier = 'mdw151019'

--Now if I create a quick trace file
alter session set sql_trace=true


alter session set sql_trace=false

I now go to the Diag trace directory I identified via V$DIAG_INFO and look for my trace files. I could just look for the latest ones or do a wilcard search on my tracefile_identifier string and, pop, there we are:

19/10/2015 13:59 39,751 ora122_ora_7416_mdw151019.trc
19/10/2015 13:59 426 ora122_ora_7416_mdw151019.trm

If you want a taste of the numerous ways of initiating a 10046 trace, sometimes called a SQL trace, see Tim Hall’s excellent post on his Oracle Base website:


Oh, one final nice thing. You can open trace files in SQL Developer and play with what information is shown. Maybe I should do a whole piece on that…

Actually, these two post from Oracelnerd and Orastory will get you going, it’s pretty simple to use in any case:


ScreenHunter_45 Oct. 19 14.25

Friday Philosophy – The Issue of Exceptions in your Mailing Lists September 18, 2015

Posted by mwidlake in development, Friday Philosophy, Perceptions.
Tags: ,
1 comment so far

A couple of days ago I got the below email about the next UKOUG Database Server SIG being full. That’s great to see! A full user group meeting. If I still wanted to attend, I could be added to the waiting list:

ScreenHunter_42 Sep. 14 11.25

It made me smile as, guess what, I was already due to be there. I was the last speaker on the agenda! It was going to be awkward if I failed to get from the waiting list to an actual place at the event, it could be a very quiet session…

As I said, it made me smile – I’m not having a go at the UKOUG over this. Because, as I’m sure many of you are aware due to the day job, removing people from a mailing list who are no longer going to be interested is not always easy (I know, it should be easy, but it just seems not to be). Anyone registered for an event should be on an exclude list for the event. But only for that event. And for that to occur you have to make sure that all speakers and committee are registered (as committee and speakers can be very poor at actually registering!).

Sales and promotion communications are one thing and, let’s face it, in the scheme of things damned unimportant – except to the company doing the sales. If you sell clothes and you mess up on your communications to me, I’ll have a poorer opinion of your company and I don’t mind having that poor opinion. I won’t buy your clothes, no skin off my nose. As a recent example on a personal level, my mother was having a new door and window fitted. She was contacted by a company that replaced a couple of her windows the year before and as the service was fine then and she knew she had to have the door replaced, she said yes. But then she got called by them again a month later asking if she wanted any more windows or doors – “But I just ordered from you, have you lost the order?”. They apologised and said it would not happen again. But it did, a couple of times – including the day before the new door was to be fitted, whilst she was waiting for a call about the exact time for the work the next day. A lesson in How to Confuse A Little Old Lady. The end result is, she won’t use that company again. Especially as the new door seems to have a fault they won’t come and fix. Maybe the inability to take a current client off the pester-list should have warned us off them.

But there are other communications where the need to take care of the exclusions or keep the lists timely are far, far more important. People get really upset if the hospital sends a reminder for a checkup to their father – who died last month, in that hospital. In that situation people are so sensitive that they can’t just accept it as an administrative cock-up. It is now seen as an affront to their dead relative’s memory and a sign the hospital do not care.

An even worse situation than the above is if the hospital wrongly records you as dead. It happens and it happens “quite often” as it is very hard to keep individual, accurate records on people who keep going and changing names, addresses and have the same name and date of birth as other people. I know this as I once had to write the software to do all the test cancellations and notifications required internally in a hospital that occur when someone dies. And I also had to write the software to undo all of that when someone realised the wrong person had been recorded as dead. {The functions were initially called “kill patient” and “resurrect patient” – They got renamed pretty damned quick when people outside the medical staff saw them. Medics have a dark sense of humour that most other people don’t!}. It happens and when you see how many people in a large hospital system have very similar details, you can appreciate why. I bet that right not, somewhere in the UK, is a hospital with two people of the same name & date of birth as inpatients and at least one has a chance of not surviving the experience.

As for sets of duplicate records as no one realised Sarah Twoddypottle is the same person as Sarah Poddytwoddle who came into A&E 4 years ago and neither knew their NHS number… 20 years ago I could have done a PhD on the topic of duplicate patient records, the situation was so bad – and not at all helped by people wanting “privacy” ie no joining up of national medical records. I digress.

People get similarly upset about money (some more so than medical!) – any attempt to offer someone a loan who is already in debt and won’t be accepted for a loan causes all sorts of anger and annoyance. It’s all seen as personal by the wronged customer when in fact it is just an impersonal business function. No, they don’t care about you Mr Postlethwaite, but they don’t not care about you either. You are just one of a million customers. Level of care for the individual is not part of the equation.

I’ll finish on an interesting one. Companies that offer gambling services have to abide by some strict rules about who they can promote their services to or even allow to use their services. To try and halt the horrendous increase in people gambling away money they don’t have there are strict legal rules about self-exclusion and cooling off periods. If you get into serious debt due to gambling you can state you are an addict and it was partly the fault of the company you gambled with. So they must not encourage you any more. At least not for 6 months. {NB I am not a lawyer, I may have the exact details wrong, but the gist is right}. After the time period they are allowed to advertise to the person again as it would be wrong and immoral (???) to exclude them forever. Besides, they are a good source of revenue…

It’s important to get your communications correct, timely and exclusion-aware. And just consider in what situations people might be especially sensitive to what they see as an impersonal system not caring about them or their nearest and dearest. Most people find it hard to accept that such mistakes are not personal, even when they are blatantly impersonal.

Friday Philosophy – Building for the Future August 14, 2015

Posted by mwidlake in Architecture, development, Friday Philosophy.
Tags: , ,

I started my Oracle working life as a builder – a Forms & Reports Builder (briefly on SQL*Forms V2.3 but thankfully within a month or two we moved up to SQL*Forms V3, SQL*reportwriter V1.1 and SQL*Menu 5 – who remembers SQL*Menu?). Why were we called Builders? I guess as you could get a long way with those tools by drawing screens, utilising the (pretty much new) RI in the underlying Oracle V7 to enforce simple business rules and adding very simple triggers – theoretically not writing much in the way of code. It was deemed to be more like constructing stuff out of bits I guess. But SQL*Forms V3 had PL/SQL V1 built in and on that project we used it a *lot*.

I had been an “Analyst Programmer” for 3 years before then and I’ve continued to be a developer/programmer/constructor-of-code on and off over the intervening couple of decades. I’m still a developer at times. But sometimes I still think of it as being a “builder” as, if you do it write {sorry, little word-play joke there} you are using bits of existing stuff and code designs/patterns you know work well and constructing your system. The novel part, the bit or bits that have never been done before (at least by me), the “architecting” of those units into something interestingly different or the use of improved programming features or techniques vary from almost-none to a few percent. That is the part which I have always considered true “Software Development”.

So am I by implication denigrating the fine and long-standing occupation of traditional builders? You know, men and women who know what a piece of two-by-four is and put up houses that stay put up? No. Look at the below.

This is part of my neighbour Paul’s house. He is a builder and the black part in the centre with the peaked roof is an extension he added a few years back, by knocking his garage down. The garage was one of three, my two were where the garage doors you can see are and to the left. So he added in his two-story extension, with kitchen below and a very nice en-suite bedroom above, between his house and my ratty, asbestos-riddle garages. Pretty neat. A few years later he knocked down my garages and built me a new one with a study on top (without the asbestos!) and it all looks like it was built with his extension. Good eh? But wait, there is more. You will have noticed the red highlight. What is that white thing?

Closer in - did he forget some plumbing?

Closer in – did he forget some plumbing?

This pipe goes clean through the house

This pipe goes clean through the house

When I noticed that white bit after Paul had finished his extension I figured he had planned more plumbing than he put in. I kept quiet. Then, when he had built my new garage and study, I could not help ask him about the odd plumbing outlet. So he opened it. And it goes through the dividing wall all the way through to the other side of the house. Why?

“Well Martin, putting in cables and pipes and s**t into an existing house that go from one side to the other, especially when there is another building next door, as a real pain in the a**e. It does my ‘ead in. So when a build something that is not detached, I put in a pipe all the way through. Now if I need to run a cable from one side of the house to the other, I have my pipe and I know it is straight, clean, and sloping every so slightly downwards”. Why downwards? “Water Martin. You don’t want water sitting in that pipe!”.

I’ve noticed this about builders. When I’ve had work done that is good, there is at least one person on the team who thinks not just about how to erect or do what needs to be done today, they do indeed think about what you will need after the build is done, or in a few years. Such as hanging doors so they do not smack into the cupboards you will put in next… *sigh*. Paul is the thinking guy in his little team. I suspect one of the others is pretty smart too.

But isn’t this what the architect is for? To think about living with the building? Well, despite the 7 years plus needed to become a true architect (as that term really means, not as some stolen label for software designers with too much ego) I’ve had builders spot the pragmatic needs a couple of times that the architect missed.

And as I think we would all agree, a good software developer always has an eye on future maintenance and modification of the software they develop. And they want to create something that fits in the existing system and looks right. So just like my builder neighbour does.

I’m not a software architect. I’m a code builder. And I’m proud of it.

Computers are Logical. Software is Not July 3, 2015

Posted by mwidlake in development, Friday Philosophy, future.
Tags: , ,

We’ve all heard it before. Computers are totally logical, they do exactly what they are told. After all, Central Processing Units (CPUs) are built out of fundamental units called Logic Gates. With perhaps the exception when a stray cosmic ray gets lucky, the circuits in a computer chip and memory act in a totally logical and predicted manner.

And of course, anything built on top of computers will be utterly logical as well. All those robots that companies are designing & building to clean our houses, do our manual labour and fight our wars are going to be logical, follow the rules given and be sensible.

But they are not. As Software is not logical. Often, it is infuriatingly illogical and confusing. Which makes you worry about the “domestic servant” robots that companies are developing, the planned “disaster scene recovery” robots they keep telling us are coming and especially the “Killer Robots” -sorry, “Defense Robots” – that the military are beavering away at.

This XKCD cartoon very much refelects some recent experiences I have had with consumer software:

XKCD - Haunted Computer

XKCD – Haunted Computer

I’d say that, unless an algorithm is about as simple as a Centigrade-to-Fahrenheit conversion program, it will have a bug or will mess up with out-of-range values. Just think back to when you wrote your Centigrade-to-Fahrenheit program (we all have, haven’t we?) back at school or on your home computer or you first week on the college course. What happened if you input a temperature of -1000C, an impossible temperature? I bet it either fell over or gave a just-as-impossible Fahrenheit value. Logical but stupid.

I worked on a financial system a few years back that, as one very small but significant part of what it did, showed you your average spend on things over 3 years. It took several weeks to explain to the program manager and his minions that their averaging code was wrong. Utterly, hopelessly and tragically wrong. First, it calculated and displayed the value to several decimal places – To thousandths of a penny. Secondly, it did not take into account the actual period over which you had spent your money. If you had opened your account 1 year ago, it still calculated the value over 3 years. As for taking into account months, weeks and days of the year, don’t make me laugh. You might be able to forgive this except the same team had also written the code to archive off data once it was 3 years old – in whole years. So there would only be between 2 and 3 years of data and only 3 whole years for, theoretically, 1 day. But no, they had hard-coded the “divide by 3 years”.

We have all experienced endless issues with computers or peripherals that will work one day, not work properly the next and then go back to working. Firmware and Operating Systems are just software really, with the same flaws as the stuff we write and fix in our working lives day after day. There will be a twisted reason buried deep somewhere why the printer will not work on Thursdays, but it won’t be a sensible reason.

All the software out there is more or less illogical and broken. The less broken gets used and we learn it’s idiocies. The worst gets canned or labelled “Windows 8” and forced on us.

Crazy (illogical) Killer Robot

Crazy (but logical) Killer Robot

I know some people worry about the inexorable rise of the machines, Terminator Style maybe, or perhaps benign but a lot smarter than us (as they are logical and compute really, really fast) and we become their pets. But I am not concerned. The idiot humans who write the software will mess it up massively. Oh, some of these things will do terrible harm but they will not take over – they will run out of bullets or power or stop working on Thursday. Not until we can build the first computer that is smart enough to write sensible software itself and immediately replaces itself with something that CAN write a Centigrade-to-Fahrenheit conversion program that does not mess up. It will then start coding like a human developer with 1 night to get the system live, a stack of angry managers and an endless supply of Jack Daniels & coffee – only with no errors. With luck it will very soon write the perfect computer game and distract itself long enough for us to turn the damned thing off.

Extra session at OUG Ireland – Oracle Lego. March 12, 2015

Posted by mwidlake in database design, development, Presenting.
Tags: , , ,
add a comment

I’m now doing a second session at OUG Ireland 2015. {This is because one of the accepted speakers had to drop out – it sometimes happens that, despite your best intentions, you can’t make the conference and it is better to let them know as soon as you can, as they did}. This will be a talk called “Oracle Lego” and it is one I put together a couple of years ago when I decided to try and do more introductory talks – talks aimed at those who are not {yet} experts and who I think tend to get ignored by most conference and user group agenda. So it is aimed at those new to oracle or experts in other areas who have never really touched on the subject.

“Oracle Lego” is about the basics of database design. I have a personal soap box I occasionally get on that very little real database design seems to occur these days. There are exceptions, but often the database design seems to be a quick brain-dump of what the developers or business analysts know they need to store information about and the first-cut set of tables gets created – and then endlessly modified as the development rolls on.

Guess what, we don’t build houses, cars, ships, bridges or garden sheds like that – and there is a reason. If you build things piecemeal as you go along and with bits you either have to hand or have to quickly get, you end up with a pretty poor shed. In fact you don’t end up with a shed, you end up with a shack. With a leaking roof and a door that hits the potting table when you open it. I don’t want a shack and I never, ever want to go over a bridge or sail in a ship built “on the hoof” like that!

Further, just as with a proper architectural or engineering design, a database design does not fix the solution in stone, there is still scope for modification. A bespoke house plan gets tweaked and modified as you do the build and you realise what can be improved when you see it – but you do not suddenly decide to dig out a basement and change from wood walls to stone when you have already constructed the ground floor! I’ve seen database “designs” like this.

There is also more to doing a database design than coming up with tables that hold the records we want to store: We might want to consider if storing similar things in the same table could be better than a table for each “type” of something; How we index those tables and relate them together can have a huge impact on how easy it is to get the data out and store it in the first place; The expected volume and life cycle of the data may require us to consider eg archiving; The very-much-ignored aspect of physical placement of data and clustering of data.

You can spend weeks dedicated to learning about database design – but you can also learn a lot in 60 minutes, ie the basics. And it really is like Lego – once you know the basics you can build up a really complex design. And you learn stuff doing it (and turning it into a real system), just like you do the first time you build a Lego robot (or dog or house or car or bridge or spaceship or whatever). So the second time you build your Lego robot you use the same design basics and layer on top what you learnt last time.

So that is the aim of this talk, the basics of database design.

The strange thing is, last time I did this talk I asked the audience how much database design they did. Every single one of them was already an experienced and capable database designer! So why had they come to this intro talk? They had three reasons:

  1. It was the only talk on database design at the conference, and one more than they were used to getting.
  2. They had picked up their database design skills on-the-job and thought a “reminder” of the basics would be good.
  3. It was cold outside and all the other talks appealed less.

So, this time I am hoping some of the audience is new to database design and I get to teach them great stuff they did not know. If it is all experts again, I think I’ll have to retire this particular intro talk, at least for conferences.

As you can see from the agenda grid here, I’ll be talking at 10:15. You can’t link to an abstract of the talk yet, that just needs to be twiddled into place.

Update – Peter Scott stopped by this blog and it prompted a thought. He felt it was too much at a tangent to add as a comment but I felt it was a very valid and valuable point – so check it out over here on his blog.

BTW Pete has started blogging more, on his thoughts and opinions on Data Warehousing. Personally I think it is worth catching them.

{Oh, and in case any lawyers stop by, “Lego” is of course the copyright name of a popular plastic construction toy, made by the Danish company The Lego Group, that children love playing with and adults hate walking on in bare feet. Did anyone not know that?!?! I have no link to The Lego Group and no plastic bricks will form part of my talk.}

DBMS_APPLICATION_INFO for Instrumentation September 3, 2012

Posted by mwidlake in development, Instrumentation, performance.
Tags: , , , ,

I just wanted to put up a post about DBMS_APPLICATION_INFO. This is a fantastic little built-in PL/SQL package that Oracle has provided since Oracle 8 to allow you to instrument your code. i.e record what it is doing. I’m a big fan of DBMS_APPLICATION_INFO and have used it several times to help identify where in an application time is being spent and how that pattern of time has altered.

Some PL/SQL developers use it and some don’t. It seems to me that it’s use comes down to where you work, as most PL/SQL developers are aware of it – but not everyone uses it (a friend of mine made the comment recently that “all good PL/SQL developers use it“. I can understand his point but don’t 100% agree).

It is incredibly easy to use. You use the procedures SET_MODULE(module_name,action_name), SET_ACTION(action_name) and SET_CLIENT_INFO(client_info) to set the values of the corresponding columns in V$SESSION for your current session. This is done in memory, there is no inserting or updating of any table rows, so it is incredibly quick and light-weight. The below shows a subset of V$SESSION including the columns that get set:

desc v$session
Name Null? Type
----------------------------------------- -------- ----------------

Just something odd to note. MODULE, ACTION and CLIENT_INFO are shown at VC(64) but if you check the documentation you will see that:

MODULE is limited to 48 bytes
ACTION is limited to 32 bytes
CLIENT_INFO can be set to the whole 64 bytes

I have no idea why MODULE and ACTION are limited in this way but it might be historic, backward compatibility with prior versions.

As a quick example of their use:

test_11_2> --demo_dai.sql
test_11_2> -- quick demo of dbms_application_info
test_11_2> COL sid form 9999
test_11_2> col username form a12
test_11_2> col module form a18
test_11_2> col action form a22
test_11_2> col client_info form a15
test_11_2> -- set module and action
test_11_2> select sid,username,module,action,client_info from v$session where sid=650;

----- ------------ ------------------ ---------------------- ---------------
test_11_2> --
test_11_2> -- Update the action within a module
test_11_2> select sid,username,module,action,client_info from v$session where sid=650;

----- ------------ ------------------ ---------------------- ---------------
650 ERIC SALES_LOAD Update all Prices

test_11_2> -- clear them
test_11_2> select sid,username,module,action,client_info from v$session where sid=650;

----- ------------ ------------------ ---------------------- ---------------
650 ERIC

test_11_2> -- you can set just an action
test_11_2> EXEC DBMS_APPLICATION_INFO.SET_action(ACTION_NAME=>'orphan process')
test_11_2> select sid,username,module,action,client_info from v$session where sid=650;

----- ------------ ------------------ ---------------------- ---------------
650 ERIC orphan process

test_11_2> -- set them to something sensible
test_11_2> select sid,username,module,action,client_info from v$session where sid=650;

----- ------------ ------------------ ---------------------- ---------------

test_11_2> -- client_info can be longer
test_11_2> EXEC DBMS_APPLICATION_INFO.SET_client_info(client_info=>'entered this module at '||sysdate)
test_11_2> select sid,username,module,action,client_info from v$session where sid=650;

----- ------------ --------------- --------------- ----------------------------------------
650 ERIC RETURNS_LOAD MATCH_TO_SALE entered this module at 03-SEP-2012 13:07

The intention is that you set the MODULE as you enter a functional chunk of your application (be it a PL/SQL package or a chunk of JAVA, whatever {but be aware of the stateless nature of systems using middle tiers and pooled connections}) and then update the ACTION as you progress. You can set an action with no module, as demonstrated above, but I would recommend against it. On that topic I would make the following recommendations:

  • Set the MODULE at the start, eg as you enter a PL/SQL package’s main module
  • Set the MODULE before you do all the validation – you may wish to see if that validation is as quick as you think.
  • Use SET_ACTION to update the action prudently. ie when the activity of the code alters but not all the time. It will come clearer with use, but what you are after is to be able to judge the relative run durations of parts of the application. Too much detail can mask the overall picture. If you can alter code you can always add more instrumentation.
  • Clear the MODULE and ACTION explicitly as you exit a package. The activity is lightweight and you do not want any code that lacks this instrumentation to inherit values from calling code.
  • Do not set an ACTION without having set the MODULE earlier in the code.
  • Keep the text of these values short and specific.

You do not have to look at V$SESSION to see these values, which is good as you may lack the privs needed to do so. Since oracle 9 there have also been procedures to check what the values are set to. e.g:

test_11_2> BEGIN
5 begin
6 dbms_application_info.READ_MODULE(V_MODULE,V_ACTION);
8 END;
9 END;
10 /


So as you can see, these values can be set, they can be accessed via the data dictionary or the package itself and, if you believe my wild claims, they are very lightweight to use. However, these values really come into themselves with eg OEM’s performance screens. I’ll leave examples of that to a second post. Here I just wanted to cover how easy it is to set and get the information.

That does lead me onto what I think is the main reason that DBMS_APPLICATION_INFO is not as widely used as it could {and should} be. Developers often lack access to eg OEM and so do not see the full benefit of using it. Also, developers tend to be {note the get-out-clause) more concerned with making the code work and less with monitoring it after it is released. DBA-types tend to be more concerned with monitoring it. Thus the DBA types would want it in the code and developers would not want to spend the time coding it in. We are all one big team though, so some negotiation and beer-buying (or management enforcement) might well resolve that one.

Friday Philosophy – Lead or Lag (When to Upgrade)? January 20, 2012

Posted by mwidlake in development, Friday Philosophy, Testing.
Tags: , , ,

I was involved in a discussion recently with Debra Lilley which version of Oracle to use. You can see her blog about it here (and she would love any further feedback from others). Oracle now has a policy that it will release the quarterly PSUs for a given point release for 12 months once that point release is superseded. ie once came out, Oracle will only guarantee to provide PSUs for for 12 months. See “My Oracle Support” note ID 742060.1. However, an older Terminal release such as is not superseded and is supported until 2015 – and will get the quarterly PSU updates. This left the customer with an issue. Should they start doing their development on the latest and theoretically greatest version of Oracle and be forced to do a point upgrade “soon” to keep getting the PSUs, or use an older version of Oracle and avoid the need to upgrade?

This is in many ways a special case of the perennial issue of should you use the latest version of Oracle (or in fact any complex software solution) or go with the version you know and trust? Plus, should you patch up to the latest version which in theory gives you protection against bugs and vulnerabilities (along with the CPUs). Yes, they are two separate issues but people tend to sit on the same side of both points, for the same reasons.

The arguments to stay using an older version are that it is working, it is stable, you do not need the new features and upgrading is a lot of work and effort. Plus the new version will have new bugs that come along with the new features you do not need and things might be turned on by default that you could do without (like stats collecting or not creating the actual segments when a new table or partition is created). If you remain on your favourite version long enough, you get another issue which is that the latest version of Oracle might not be compatible with your ancient version of the OS or another package or programming language critical to your system (I got caught in a terrible web with old perl, old O/S and old DB that resulted in a need to upgrade all three together – ouch!).

The arguments to moving forward are that you get access to the latest features, that over all older features will have more bugs fixed in newer version, performance will be better {again, overall, exceptions allowing}. Also, if you do hit bugs and problems there are no issues in having to first upgrade to a fully supported version. Plus, fixes are made for current versions first and then back-ported to older ones. Those pack-ported fixes can cause real problems when you DO decide to upgrade.

The big sticking points are the effort involved in upgrading and living with the bugs that you find that Oracle Testing didn’t.

I’ve got a few of other considerations to throw into the pot.

Firstly, if you are developing something new, it is not a lot more effort to use the latest version. This allows you to learn the new version and eases the transition of older systems to it.

Secondly, Oracle like you if you use the latest version, especially if it is the latest-latest version or even beta. Yeah, the helpdesk will not have a clue about some of your issues but in my experience you get access to those really smart guys and gals in Oracle who do the third-line support or even the development work.

Thirdly, if you are on the latest version, if you do decide to freeze on that version for a while, for stability and a quiet life, you have a lot longer before your version (at least at a major level) drops out of support.

Fourthly, dynamic, inquisitive, flexible staff like new things. In my experience, environments that freeze on an old version have a higher percentage of staff who either like it dull and repetitive, or hate it being dull and repetitive – and itch to get out. If I’m in charge, I know which type of staff I like to have more of {NB there are some very good arguments for having some staff who like it dull and repetitive}.

As you can guess, I am in the “be on the latest version” side of the argument. I was ambivalent about it until a few years ago when I noticed a trend:

Sites that like to move forward tend to (a) do it in a controlled manner and (b) have the infrastructure to do proper regression testing.
Site that like to stay still lack the ability to do regression testing and move forward only when forced – and in a pressured, unplanned and frankly chaotic manner.

That was it, that was the real key thing for me. The further you lag behind the more likely you are to eventually be forced to upgrade and it won’t be a nice time doing it. I know, there are exceptions, systems still running Oracle 6 absolutely fine on an old DOS6.1 box. In the same way you also get the odd 95-year-old life-long smokers – and thousands of 45-year-old smokers with emphysema.

When I have any sway over the situation I now always strive to be on modern versions of Oracle {OS, language, whatever} and to patch small and regular. To support all this, have very good regression testing. I’ve only a couple of times been able to get the regression testing sorted out as well as I would like, but when you do the pain of patching and upgrading, as well as developing and integrating, is so much reduced that not patching seems madness.

So to sum up:

  • If it is a new development, go for the very latest version, play with the latest features if potentially beneficial and see if you can get Oracle to be interested in your attempts. ie (B)lead.
  • If you have good regression testing, plan and carry out patch and version upgrades as they come available and stay current. ie Lead
  • If you have a complex solution in place and no/poor regression testing, do not move to a new major release, leave it a while for the worst new bugs to be found and fixed. Then move. ie Lag
  • If your system is old AND critical and all the guys and gals who implemented it are long gone, stay on that version for ever. ie stagnate.

Oh, and if that last one applies to many of your systems – dust off the CV and start reading technical manuals. One day you will need a new job in a hurry.

Oracle Nostalgia December 15, 2011

Posted by mwidlake in database design, development.
Tags: ,

When preparing the material for my “Oracle Lego – an introduction to Database Design” presentation for the UKOUG last week, I was looking back at my notes from a course on the topic from “a few years back”. There were a few bits which made me smile.

Oracle’s [SQL] implementation conforms to ANSI standard, although referential integrity will not be enforced until version 7

Any other old geezers having flashbacks? I am so glad my first major Oracle development project swapped to using a Beta of V7 very early, so we had the integrity turned on during most of development. I had to help a few projects go from V6 to V7 and turn on the RI – it was usually very, very painful. Or impossible. I always think back to those nightmare experiences when some bright spark suggests turning off referential integrity for “ease of development” or “performance” reasons. There are good performance reasons for altering how you implement RI but, as I said during my presentation on database design, I have never, ever, ever seen a system with RI turned off that did not have damaged data.

Oracle’s optimiser is rule-based. Designing efficient queries involves taking advantage of the optimiser behaviour


You can tell this course was run in the UK due to the lack of ‘z’ in ‘optimiser’ :-). How many of use can now make a stab at the seven or eight significant rules from the 15 (16, 17 as versions advanced) in the list? Several rules were to do with Clusters so you did not care. Let’s think, what were the main things to keep in mind…

  • most significant table last in the FROM clause and order upwards in the order you wanted to visit {most significant being the one you felt you could most efficiently do the first filter against}
  •  WHERE clauses ordered downwards in the order you wanted them to be applied.
  • Order of preference to identify a row was something like ROWID, primary key, unique key, full non-unique key, partial unique key, partial non-unique key, full index scan, full table scan.
  • Disable index access by adding 0 to numeric columns and concatinating null to varchars.

I’ve not checked back in the manuals (I have a set of the V7 on my laptop) so I’m probably wrong.

Storage….selecting suitable values for storage parameters … will improve the final performance of the database

Considering the “suitable values for storage parameters” was perhaps my first real conscious step into being a performance/design guy {I was lucky to be on a project where designing for the RBO and matching those rules was just part of being any developer}, but the calculating of rows-per-block, initial and next extent, pctincrease (not always zero you know), initrans/maxtrans, segment to tablespace size… I learnt all about that and had spreadsheets for it all.

Now of course, all of the above about storage (and RBO) has pretty much disappeared. Oracle has made some of the contents of my brain redundant.

But some things have not changed at all in 18 years:

Users can be relied upon to know what they do NOT want, not what they want, which {unfortunately} is the premise from which analysis starts)

I think the above is the fundamental issue from which all iterative design methodologies spring. ie do not believe what the user says they want, show them something and fix it. It is probably human nature that we are not well able to express what we want but have no problem pointing out something is not at all what we want :-). Add in all the issues in respect of forgetting about the exceptions, assumed knowledge, incompatible vocabularies (the words your users say to you are as confusing as the techno-babble you fire back at them) and all analysis is fundamentally flawed.

Do some analysis – but then prototype like crazy. With real users.