One fortunate thing about me is my age. Or rather, how old I was in the 1980’s. I was at school in the 80’s, I did my ‘O’ Levels (taken at age 16) in 1984. One of my ‘O’ levels was in Computer Studies. This was before Windows and Excel and Word and all that office software, before the internet was in existence (TCP/IP was only standardized in 1982!) and phones were all tethered to the wall with a cable. What were we taught in Computer Studies? Programming. That and a bit about hardware, but mostly it was programming.

This beast had about 48k of memory and hi-res 320*192 pixels

In the first year of my two-year course we had just two computers to use between us, both RM 380Z’s I think, so we wrote out our programs by hand and worked through them logically to try and get them as good as we could before our turn came to put them into the computer and run them. This was of course painful, but our programs initially really were of the terrible simple “take in the temperature in Centigrade and convert it to Fahrenheit” scale of things, before we went all crazy with power and wrote a program that would ask you which way you wanted to do the conversion. We all had a 5-1/4 inch floppy to store our programs on but were told to take great care of it as they would not be handing out more! Needless to say, it was not long before most of us were turning up at the “computer room” (the schoolroom with the two machines and the broken lock) at lunch times and after school in order to get more time on the machines. The only problem was that in our first year of ‘O’ level we had to contend with the older kids from the year above us and they did not like us oiks turning up to use “their” computers. At least we were introduced early to a key concept of a career in IT – working outside and beyond the standard office hours.

For some of my class mates, they had other options. The early 80’s were also when home computers burst onto the scene and some friends had ZX81s, Vic 20s, Acorn Atoms, Dragons or, gasp, commodore 64s. My older brother had a ZX Spectrum, with the 48K ram pack.

My brother’s spectrum, access denied to little me

However, the Spectrum was not available to me as my miserable bastard of a brother would not let me near it. You might think this was reasonable as he was the older brother and it was his computer. But it was plugged into my portable TV and my cassette tape recorder. This was what you had to do with these early home computers as almost none came with a “monitor” and most with no storage device. Certainly no internal hard disc! When you turned off the computer, whatever program you were running immediately evaporated out of memory and you had to load it up from tape next time you turned the computer on – given that you had saved it to tape first. Games came on cassette tapes so before you could play, you had to load it. For. 10. minutes. I think one person had access to a machine that could use our precious single 5-1/4″ floppy but he was not happy as that computer was rubbish for games.

{For anyone reading this who is less than 35 years old, before downloads we had CD’s, which you know about. Before CDs we had vinyl records, which you may or may not know about as they became cool for a while again. Before CDs we also had cassette tapes, which you may not know about, which were about the same size as a credit card but about 1.5* as thick as an iPhone. You could record between 30 (C30) minutes and 2 hours (C120) of music onto them, over and over again, and they would fit in your pocket. But then the tape would either eventually stretch (especially if it was a C120) leading to very waily, odd playback, or your cassette player would “eat” the tape and you would spend 30 minutes with scissors and tweezers pulling it out of the machine. Ahhhh, the memories.}

The joy of pre-recorded and blank cassette tapes

Where was I? Oh yes. My brother used my TV and my cassette player which, together, had cost more than his computer. He felt he could just use my stuff as he wanted but I could not use his computer. If I was watching my TV when he wanted to use his spectrum, he would get really obnoxious and aggressive until I “agreed” he could use it. But I was never allowed to use his spectrum (well, not when he was around to know, anyway). You might pick up that this could have led to a little bit of sibling angst and an ongoing feud that continued into adulthood? Too damned right!

RS 480z – we had SIXTEEN of them, and a shared disc

Getting back to the main thread, unlike most of my class mates I was limited to using the two computers at school. However, when I came back to school after the summer break between my fist and second year of doing my ‘O’ levels I came back to find they had finished building the dedicated computer room. It had air conditioning, a working lock on the door, about 16 RS-480Z computers and a smell something like melted plastic, new carpet and nail varnish – which never went. There was also some sort of shared storage, I think it was another 480z with a hard disc in it that all the other machines could see. So we all had a machine to use during lessons, the ability to save and load the programs all the time and programming really did become the thing we did. Due to the afore mentioned brother issues I still ended up doing some programming at lunch times and after school, fighting off the oiks from the year below…

My first real program that I remember doing, which I felt was more than a glorified calculator or pathetic painting of a house in lines and boxes, was a program that played Naughts and Crosses, or Tic-Tac-Toe as our US friends would (in my opinion, crazily) say. I don’t recall how long it took but I do remember my teacher telling me I was maybe being a bit “optimistic” when I said I wanted the computer to play rather than just letting two humans play against each other. I got it to work. Go Me!

As I said, I can’t really remember much more about what programs I wrote, it is a long time ago and my memory is poor. But I do remember that most of the course was about understanding programming and hardware, such as it was back then. As the years went by into the 90’s and 2000’2 I’m told the programming side first reduced and then almost {if not totally} disappeared to be replaced with being taught how to use computers and packages. ie Windows, Excel, Word and the like. So I grew up and was at the “right” age when home computers came along and schools in the UK taught at least some of us to program.

I still have one of these somewhere…

I got a ‘B’ in the final exam, which was OK. So that set me on the road to programming and my career? Well, no. After my ‘O’ level, I did buy my own computer, an Amstrad CPC 464 (maybe not a great decision!) and I did do a bit of assembler and programming, but mostly to try and hack into the games I was playing. By now I was doing my science ‘A’ levels, there was no option of an ‘A’ level in computing and no computing element to my ‘A’ levels. Then I went on to college to study biology, where computers were not really used much outside of the computer science department. There was a terrible programming course I went on as part of the Zoology half of my degree but it taught us less than my ‘O’ level and computers were just not a part of the Genetics half of the degree I did – which looking back at it, stuns me. We used to work out the general size and layout of plasmid genomes (bits of bacteria) by hand on paper. I got sick of doing it and wrote a program on my CPC 464 to do it and, for a few weeks, academic life was easy. My tutor got really mad when he found out I had written a program to do it. I don’t know why and I don’t think he could tell me why, he just felt I was cheating. Oh well.

Of course, once I left college life took another turn and I landed back in the world of computers {back then they would take people with no programming skills but good logic/maths and train you from zero}. But I already knew how to program and that gave me a bit of help in my first job. I could spend more time than my colleagues could worrying about how to be an adult rather than how to start programming. I might be able to program, I’m still struggling with being an adult.

Update – as Niall points out in his comment, things are now improving at schools AND colleges. What originally prompted this was a discussion I had with a friend who had just finished his computing course at college. The course was about computer games. And had involved no programming elements at all, it was all about design, marketing, testing, running a business… I was stunned. I then made some comment about at least he would have learnt about programming at school, but no, he had not. I was stunned again. But he knew that it was coming back – as covered so well by Niall.

I suspect the Raspberry Pi has helped too, though most people I know who have bought one are, well, mid-40’s people who did programming at school…

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:

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:

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.