Show Oracle sessions

Here is a small script which shows information about running Oracle sessions. You can use commented lines to filter by an Oracle instance (in case you have a RAC), OS user, session ID, process ID or an application name.

OzBsidian dark colour scheme for Oracle SQL Developer

I recently switched to Oracle SQL Developer for my PL/SQL development needs. I have mixed feeling about SQL Developer: it's been in development for nearly a decade, and it still feels very raw. Nonetheless, it has a comprehensive set of features, a fully functional PL/SQL debugger, and you can't beat a free price tag.

However, one thing I wasn't happy about is the default colour scheme. I've grown quite fond of dark colour schemes, notably I like Obsidian by Morinar. It was originally created for Eclipse, but then became massively popular and got cloned into just about every single IDE and text editor... Except for SQL Developer, that is.

I was surprised nobody cloned it for SQL Developer, so I went ahead and did it myself.

The colours are mostly match Obsidian scheme, although not exactly. For instance, the background is a bit darker. Hence the name is OzBsidian to differentiate it from the original scheme.

You can download the colour scheme with the installation instruction from my GitHub account.
Enjoy!

How to get size of all tables in an Oracle database schema

If you ever wanted to know how what's taking space in an Oracle database, or how large is the table you're working on, here's a script which answers these questions. It shows the size of all the database objects large than 10 Mb in a particular database schema.

Databases Matter

Databases are at the core of virtually all modern information technology systems. Sometimes these databases are exposed, for example, as in data warehouse systems. The centrepiece of a system like that is a powerful industrial database such as Oracle, or Sybase, or Microsoft SQL Server, or something similar. The presence of a database is pretty obvious in that case – only these powerful databases can crunch the immense amounts of data processed by data warehouses. In other cases databases are hidden. For example, Apple iTunes uses a SQLite database to store the details of music tracks on your computer. That database is not obvious; it does not advertise itself, but it's there. It makes sure that the ratings you assigned to songs are saved and can be synchronised between all your iPhones and iPads. It counts the number of times every song was played so that you don't listen to the same song twice when you put your iPhone on shuffle. Databases are everywhere. They all serve the same purpose – to store data and make its retrieval as easy and fast as possible – but they are also vastly different from each other.

Let's take another example. When you log into your Google account and bring up your Gmail inbox, all the emails you see are actually stored in the remote database. That database is called BigTable, and it contains not only your emails, but all the emails of all Gmail users in the world, and also virtually all of Google's data. While your iTunes SQLite database may be about 50 megabytes in size (and that's assuming you have A LOT of songs), Google's BigTable contains petabytes of data. That's your iTunes database times one billion.

If you think about it, it becomes obvious that these databases require vastly different approaches to the way the data are stored and retrieved: You can fit an iTunes database into memory and query it whichever way you like without a performance penalty. At the same time, no machine has been built yet that could apply the same approach to Google's BigTable.

Unfortunately, not all software developers understand that. Databases once were an inspiring topic but in recent years they went out of fashion. Software developers are geeks; they like new toys; they all want to work on something latest and greatest and cutting-edge. So many new exciting things are happening in the area of Information Technology – Web 2.0, HTML5 and Apple iOS to name just a few – that databases just fade in comparison, despite the fact that they make all these new shiny things tick. Most of the developers these days take a database just as generic data storage: “We'll just stuff the data in and we don't care what's inside.” 10 years ago SQL language was a necessary skill for database application developers. Nowadays the majority of programmers don't know SQL. They rely on frameworks such as Hibernate to produce SQL statements for them. They think that all databases are the same and therefore, if necessary, they can take one system that uses MS SQL Server as a backend and put it onto Oracle and it will work just fine.

Well, that may be true for very simple applications. The myth that all databases are the same is flawed, especially when it comes down to performance. Today cloud computing is a buzzword, and Google is the patriarch of the cloud. Google's servers process billions of requests every day, crunching petabytes of data. Yet, every request made to a Google search engine is served within seconds. This places such a high demand on the Google's database layer, that Google's engineers couldn't afford using even the most powerful of industrial databases, and they had to develop their own – the aforementioned BigTable. If you told these guys that “all databases are the same”, they would laugh into your face, and rightfully so, because Google knows that performance matters and they try to squeeze every bit of performance out of their systems.

Databases matter, and if you consider yourself a decent software developer, you need to learn how to tame them. Learn the differences between them. Learn what they are, what makes them tick, and the most importantly, how to make them tick faster, because writing applications that are slow is just bad taste.

Don't mess with LIKE

Oh boy, It looks like it's time to change the title of this blog to "A hundred ways you can screw up with Oracle". I may need a new domain name, something flashy. www.oraclewtf.com would be fine. Let me see, it is currently available. I wonder how fast cyber-squatters will jump in and snatch it now when I pronounced it. Then they will blackmail me demanding a outrageous ransom or else... (insert evil face here)

On second thought, no. I don't want to turn this blog into an Oracle-specific. There are already enough Oracle blogs out there. In fact, I think there are more of them than Oracle professionals who would read them. I don't want to bring yet another one to the world just for the sake of it. Heck, it's my place and I'm going to write about whatever I want. Hence the title, Random Thoughts.

Ok, kids, take your places. Today's lesson is (surprise, surprise!) about Oracle. We already discussed a few ways we can screw up with dates. Today we will talk about numbers. On the surface numbers look like pretty innocent data type. But once you dive a little deeper... Beware! Fearful creatures lurk beneath. And if you are not careful, they will snatch you in no time.

Take a look at this Stackoverflow.com question by James Collins.
James had a problem, the following query was slow:

SELECT a1.*
FROM   people a1
WHERE  a1.ID LIKE '119%'
       AND ROWNUM < 5

Despite column A1.ID was indexed, the index wasn't used and the explain plan looked like this:

SELECT STATEMENT ALL_ROWS
Cost: 67 Bytes: 2,592 Cardinality: 4 2 COUNT STOPKEY 1 TABLE ACCESS FULL TABLE people
Cost: 67 Bytes: 3,240 Cardinality: 5

James was wondering why. Well, the key to the issue lies, as it often happens with Oracle, in an implicit data type conversion. Because Oracle is capable to perform automatic data conversions in certain cases, it sometimes does that without you knowing. And as a result, performance may suffer or code may behave not exactly like you expect.

In our case that happened because ID column was NUMBER. You see, LIKE pattern-matching condition expects to see character types as both left-hand and right-hand operands. When it encounters a NUMBER, it implicitly converts it to VARCHAR2. Hence, that query was basically silently rewritten to this:

SELECT a1.*
FROM   people a1
WHERE  To_char(a1.ID) LIKE '119%'
       AND ROWNUM < 5

That was bad for 2 reasons:

1. The conversion was executed for every row, which was slow;
2. Because of a function (though implicit) in a WHERE predicate, Oracle was unable to use the index on A1.ID column.

If you came across a problem like that, there is a number of ways to resolve it. Some of the possible options are:

1. Create a function-based index on A1.ID column:
   

   CREATE INDEX people_idx5 ON people (To_char(ID));

2. If you need to match records on first 3 characters of ID column, create another column of type NUMBER containing just these 3 characters and use a plain = operator on it.


3. Create a separate column ID_CHAR of type VARCHAR2 and fill it with TO_CHAR(id). Index it and use instead of ID in your WHERE condition.

4. Or, as David Aldridge pointed out: "It might also be possible to rewrite the predicate as ID BETWEEN 1190000 and 1199999, if the values are all of the same order of magnitude. Or if they're not then ID = 119 OR ID BETWEEN 1190 and 1199 etc.."

Of course if you choose to create an additional column based on existing ID column, you need to keep those 2 synchronized. You can do that in batch as a single UPDATE, or in an ON-UPDATE trigger, or add that column to the appropriate INSERT and UPDATE statements in your code.

James choose to create a function-based index and it worked like a charm.

SYSDATE confusions

SYSDATE is one of the most commonly used Oracle functions. Indeed, whenever you need the current date or time, you just type SYSDATE and you're done. However, sometimes it's not all that simple. There are a few confusions associated with SYSDATE that are pretty common and, if not understood, can cause a lot of damage.

First of all, SYSDATE returns not just current date, but date and time combined. More precisely, the current date and time down to a second. If just a date is needed, TRUNC function has to be applied, that is, TRUNC(SYSDATE). For a sake of a good database design, date should not be confused with date/time. For example, if a column in a table is called “transaction_date”, it would be natural for it to contain a date, but not date/time. That may lead to a major confusion. Let's imagine there is a table BANK_TRANSACTIONS containing the following fields:

txn_no     INTEGER,
txn_amount NUMBER(14,2),
txn_date   DATE

The last field is of the most interest to us. Apparently its data type is “DATE”, but is it a date or date/time? We can't tell by just looking at the table definition. Nonetheless, it is a very important thing to know. A common case for using DATE columns is including them in date range queries. Forexample, if we wanted to get all the bank transactions from 1 January 2009 to 31 July 2009 we could write this:

SELECT txn_no,
       txn_amount
FROM   bank_transactions
WHERE  txn_date BETWEEN To_date('01-JAN-2009','DD-MON-YYYY')
                    AND To_date('31-JUL-2009','DD-MON-YYYY')

And that would be fine if TXN_DATE were a date column. But if it is a date/time, we would just have missed a whole day worth of data. And it is because, as I said, DATE data type can hold date/time down to a second. That means that for 31 July 2009 it could hold values ranging from 0:00am to 11:59pm. But because TO_DATE('31-JUL-2009', 'DD-MON-YYYY') is basically an equivalent to TO_DATE('31-JUL-2009 00:00:00', 'DD-MON-YYYY HH24:MI:SS'), all the transactions happened after 0:00am on 31 July 2009 would be missed out.

That kind of mistake is pretty common. Sometimes it's hard to tell by just looking at the data whether a particular DATE column can have date portion. Even if all the values in there are rounded to 0:00 hours, that doesn't mean that a different time value can't appear there in the future. The data dictionary can't help us here either – DATE type is always the same whether it contains time or not. (By the way, Oracle recommends using TIMESTAMP type for new projects, but that is a whole different story.)

If you are working with an existing table and you are not sure, you can use a fool-proof method like this:

SELECT txn_no,
       txn_amount
FROM   bank_transactions
WHERE  txn_date BETWEEN To_date('01-JAN-2009','DD-MON-YYYY')
                    AND To_date('31-JUL-2009','DD-MON-YYYY') + 1 – 1/24/3600

“+1 – 1/24/3600” here means “Plus 1 day minus 1 second”. That is because “1” in DATE type means “1 day”, “1/24” - 1 hour, and there are 3600 seconds in an hour.

The above expression will retrieve all the transactions from “01 January 2009 0:00am” to “31 July 2009 0:00am plus 1 day minus 1 second”, i.e. to “31 July 2009 23:59pm”.

If you are charged with designing an application and need to create a table with a DATE column, it is worth to keep yourself and others from future confusions by a simple trick: name columns that only contain date portions as “_DATE” and add “_TIME” to the name of the columns that you know will contain time components. In our case it would be prudent to call the date/time column TXN_DATE_TIME.

The second issue I'd like to discuss is much more subtle, but can do even more damage.

Imagine that you are charged with developing a report that returns all the transaction for the previous month. It looks like a job for SYSDATE! You fetch your trusty keyboard and after a few minutes of typing you come up with something like this:

SELECT txn_no,
       txn_amount
FROM   bank_transactions
WHERE  txn_date BETWEEN Last_day(Add_months(Trunc(SYSDATE),-2)) + 1
                    AND Last_day(Add_months(Trunc(SYSDATE),-1))

You create a few lines in BANK_TRANSACTIONS table, run a few unit tests to make sure your code works and check it into the source control. Job done! You congratulate yourself on the productive work and spend the rest of the day reading your friends' blogs and dreaming about your next vacation. And the next day you move on to another task and get as busy as ever.

After some time, which may be a few days or months, depending on the pace of the project, the code you wrote gets migrated into the UAT environment. And a task force of a few testers and end users is assigned to test the report you wrote. And as it often happens in UAT, they are going to test in on real data they extracted from the production system – that is, the last year's data.

Got it? Last year's.

The final stages of testing, such as UAT, have to prove that the system does what it is expected to do in conditions that resemble the production as closely as possible. And the best way to do that is to test it on the retrospective production data – the data that is proven. That makes it possible to compare the outcome to the actual production system, and thus, prove or disprove that the new system works.

That sounds reasonable. But one of the implications for you is that BANK_TRANSACTIONS table is not going to contain previous month's transactions. Hence, your report will be blank. You can't rewind back time because you hard-coded SYSDATE, which has only one meaning – “right now”. Test failed.

If you have known that when you wrote it, you wouldn't have used the SYSDATE. You would use a parameter, something like v_run_date, which you could set to whatever date you wanted. And that would do. Well, now you know.

Make it beautiful

You only need a single look at Sydney Opera House to recognise that it is a work of art. Any masterpiece is like that – you don't need to do a throughout examination of Mona Lisa's smile to realise its beauty – you see it instantly. Perfection needs no explanation, it works on subconscious level. The same applies to the software engineering too. Great code always looks good. It is always carefully formatted, indented and commented. By just looking at it you can tell that it is a work of art. Such code will always work, do what it is supposed to do and have a very few bugs. Because whoever wrote that code cared a lot about it. And you can safely assume that if anyone has put a lot of effort into making the code looking good, he has put at least as much effort into designing and debugging it. What is even more important, carefully carved code is easier to maintain. In modern software projects any single procedure gets tweaked and rewritten tens of times. If you are a programmer, good chances that even in a project that you work on right now you inherited some code that was written years ago, maybe from people who long left the company. And when you finish with it, it will not be the end of the story – the code will be passed to QA and finally to the production support. And then the cycle will start again. Hence, whatever you program, it's not just about you. You don't know how many people will be looking into your code trying to make sense of it. And you can help them immensely by making it is easy to read and understand now. Whether you will be remembered as a good programmer or cursed depends on it. So, you made an effort to write the code that works. Now make an extra step – make it beautiful.

Date conversion in Oracle part 2

It's a follow-up to the previous post. As it turned out, implicit date conversions may also prevent Oracle from doing the partition pruning. For example, if you have a table INVOICES with a range partition on INVOICE_DATE field, then expression

SELECT
...
WHERE invoice_date >= '01-MAR-09'
  AND invoice_date <  '02-MAR-09'

will not perform the partition pruning, whereas

SELECT
...
WHERE invoice_date >= TO_DATE('01/03/2009', 'DD/MM/YYYY')
 AND invoice_date <  TO_DATE('02/03/2009', 'DD/MM/YYYY')

will. Because the efficiency of partition pruning is usually why partitioning is used in the first place, the choice is obvious. But after all, I’d use

SELECT
...
WHERE invoice_date BETWEEN TO_DATE( '01/03/2009', 'DD/MM/YYYY')
                      AND TO_DATE( '01/03/2009', 'DD/MM/YYYY') + 1 - 1/24/3600

,since BETWEEN operation is specifically tailored for such situations. "1/24/3600" here represents 1 second, and the whole statement should be read as "From 01 March 2009 0:00am to 01 March 2009 11:59pm".

Date conversions in Oracle

When I was going through PL/SQL procedures written by some of my colleagues, I noticed a few mistakes made around the Oracle’s date conversion functions. There are some peculiarities about those functions that I thought everyone knew about. But I reckon if I write about it, it may help others to avoid such mistakes. I also allowed myself to outline a few rules that, if you adhere to them, will help you to write better programmes.

There are 2 functions in Oracle to convert strings to dates and back.

The first one is TO_DATE – it takes a string parameter and returns a date. Ok, it’s actually a date/time combination enclosed into a single data type – Oracle’s DATE.

The second one is TO_CHAR – does the opposite: it takes date/time as Oracle’s DATE data type and converts it to string.

Actually, these functions are bit more complex than that, but for our purpose that will do. What’s important to understand here is the distinction between date as a DATE data type and its string representation.

For example, when you type ’01-APR-09’ in the procedure’s text, that’s a string, representing a date. Pay attention here: although you meant a date, Oracle sees a string. For Oracle everything that is enclosed in single quotation marks is a string. To make it a date, we need to convert this string to a DATE data type. Such conversion can be carried out by 2 possible ways: explicitly and implicitly.

Explicit conversion is when we apply the TO_DATE function to the string:

v_date DATE;
v_date := TO_DATE('01/04/2009', 'DD/MM/YYYY');

Now v_date is a date, representing April 1^st, 2009.

Implicit conversion is when we let Oracle to perform the conversion:

v_date DATE;
v_date := '01-APR-09';

It has the same effect. Every time Oracle sees a string in place where it expects a date, it is smart enough to perform the conversion for us. "Well", you may think, - "That’s great. Oracle does it all for us, so we don’t have to do it. Life is easier, let’s go for another coffee break".

Not quite.

You see, when Oracle does such implicit conversion, it relies on some assumptions. If you read the documentation for TO_DATE and TO_CHAR functions, you’ll find that they take another optional parameter – the date format. That format tells Oracle how the string representing the date/time should be treated. If the format parameter is not specified, it is taken from NLS_DATE_FORMAT Oracle parameter. Here’s the crux: We can’t assume that this parameter will be the same on all Oracle systems. Although it is ‘DD-MON-RR’ by default and it is left like that on most Oracle systems, we can’t assume that it’s going to be like this always and everywhere. And if you rely on implicit date conversions and some DBA changes NLS_DATE_FORMAT parameter – WHAM! – All your programs will stop working.

So, a good practice and rule of thumb for you should be:

Never ever rely on implicit date conversions!Whenever you need to convert date to string or vice versa, use an appropriate TO_DATE or TO_CHAR function and always specify a date format.

Just like this:

v_date DATE;
v_date := TO_DATE('01/04/2009', 'DD/MM/YYYY');

The danger of NLS_DATE_FORMAT being changed is the biggest threat but not the only one.

Pay attention to the default date format I provided just above – ‘DD/MM/RR’. Do you notice anything suspicious? The year is 2 digits. Here Oracle tries to be smartass and tries to guess whether you mean XX or XXI century. Your only hope that it can figure out what you meant and doesn’t make a mistake. But if it mistakes – oops, welcome back the Millennium Bug. This brings us to the second rule:

Don’t be a curmudgeon – always specify the 4-digit year.

Another dangerous programming technique is trying to convert Date to Date where no conversion is necessary.

Let’s have a look at the following example, or should I say a puzzle?

DECLARE
   v_date     DATE := '01-APR-09';
   v_date_2   DATE := TO_DATE (v_date, 'DD/MM/YYYY');
BEGIN
   dbms_output.put_line (TO_CHAR (v_date_2, 'DD/MM/YYYY'));
END;

Try to guess what will be printed as a result.

If you think ‘01/04/2009’, you’ve just screwed your business critical application and have sent it two thousand years back in time. In fact, you’ll get ‘01/04/0009’.

This is where it all goes bad:

v_date_2   DATE := TO_DATE (v_date, 'DD/MM/YYYY');

And here’s why:

The first thing Oracle tries to do is to execute TO_DATE function. There is only one TO_DATE function in Oracle – the one that takes a string and converts it to a date. Despite we know that v_date is not a string, Oracle still proceeds with its logic. If you run this code, it won’t produce an error. Oracle successfully convinces itself that it sees a String where it has a Date. That happens because Oracle is able to implicitly convert that date to a string, effectively turning that line into

v_date_2   DATE := TO_DATE (TO_CHAR(v_date), 'DD/MM/YYYY');

But, as we’ve already learned, implicit date to string conversions are performed using the date format recorded in NLS_DATE_FORMAT Oracle parameter, which is by default set to ‘DD-MM-RR’. Hence, what Oracle effectively does is this:

v_date_2   DATE := TO_DATE (TO_CHAR(v_date, 'DD-MM-RR'), 'DD/MM/YYYY');

Can you spot the error already? The date formats are inconsistent! This is what you get when you don’t pay attention to the details.

So, here comes rule 3:

Avoid unnecessary conversions. Never convert dates to dates.

If you think that all this stuff is pretty confusing, that's because it indeed is. The good news is that you can avoid the confusion altogether by learning to program in a more clear, more concise way. That is a foundation of a good programming style.

Role play modelling

Virtually every software development methodology stresses a need for specifying how the software end product is expected to work before starting the programming. But the truth is that writing specifications is boring. And most programmers, being lazy people (in good meaning of "lazy". Yes, I believe there is a good meaning) try to avoid boring work. So, when the specs are written, they are often incomplete and don't cover all the possible scenarios. Moreover, as the requirements change, these specs become outdated and, hence, even less useful. I wonder if there is a way to replace the boring specs with something which is fun. One possible way to go may be a "Role play-style modelling". Just think about it. Usually software product consists of a number of modules communicating to each other. Each module does a specific task. In a typical software project, different developers are assigned to implement different modules. Now imaging that before diving into coding, you have a kind of a role-playing game, where each of the programmers is actually pretending to be a module he is going to program. It goes like that:

(Dave is a module that reads an XML file and converts it into a spreadsheet, Bob is a module that is responsible for taking the spreadsheet and sending it to users as an e-mail attachment) Dave: Trying to open the XML file. Wait a minute, what if it's not a well-formed XML, what am I supposed to do then? I should probably send an error message back. Ok, I read it and successfully transformed. Now I notify Bob that it's ready. Bob, we need to decide on this notification protocol. Now, it's your turn. Bob: Ok, first I need to create an e-mail. Where do we get the e-mail address and subject line from – should we ask the user? This is something to find out. Then I need to check that the e-mail address is well-formed and the subject line is not empty. Then I read the spreadsheet file Dave provided and attach it. Dave, how big this file can be? Dave: I reckon it's up to 1 Mb, but I need to clarify it. Bob: I need to find out if I need to compress it. Then I try to send the e-mail. If it is sent successfully, I report Ok status back to Dave. If it's failed, I report failure status with the error message. ... And so on.

That would give all the participants the understanding of how the end product should work. And, assuming the programmers are taking notes as they do it, every one will end up with a mini-spec just for themselves. Another advantage of such approach is that it would encourage people to discuss every possible situation and ask "what if" questions. And asking them is the most important part of the whole process. While there is at least one outstanding question, the spec is incomplete. So, after our game, Bob and Dave would depart on harassing whoever appropriate, like business analysts or even end users, to extract the answers from them. And once they have the answers, we would repeat the game again. And would do it over and over again, until everything unknown is ruled out. It sounds like a odd approach, but it just might be odd enough to work.

The recruitment landscape is changing...again

A few days ago I went to a presentation by an Oracle recruiter. He talked about the ways Oracle searches for the potential candidates and and hires them. One particular thing that struck me was that Australian Oracle recruitment team does not use specialised recruitment web sites. About a decade ago, looking for a job was all about reading newspaper ads. Then along came job web sites, and the recruitment landscape changed forever. I am myself of a younger generation, and looking for a job for me was never associated with newspapers. When I look for a job, I go to a web sites such as Seek or Monster.com and search there. I take as given that all the jobs I may be interested in are posted there, either directly of through recruitment agents. This approach is more convenient for both job advertisers and seekers, because it allows them to find each other more easily. And most of the activity is concentrated around a few dominant players such as aforementioned Seek and Monster. This is because of so-called network effect: such web sites are created to connect people to each other, in our case job seekers and advertisers, and because the number of possible connections is proportional to the square of participants, the probability of "getting connected" through a large job web site is much higher than through a small one. Thus, size does matter. Indeed, if I want to find a job, the first thing I do, like Willie Sutton robbing banks because "Because that's where the money is", is look at the largest local job site, because this is where the jobs are. But this is changing. As I already said, I was surprised to hear that Australian Oracle recruiters do not use job web sites. Instead, they exploit a relatively new phenomenon of Internet social network services. Social networks benefit from network effect at the scale of magnitude comparing to the traditional job sites. This is because the network size of job sites depend on the number of job advertisers paying for placing their ads. In other words, id depends on supply of ads. And if supply side does not increase, the network will not grow. On the other hand, the growth rate of a social network is not limited by either supply or demand. As long as new members keep subscribing, network will grow. Even if they stop, the existing members will still be able to establish new connections until all the members are connected to each other. And this number, as I said before, is a square of the number of members. A good example is LinkedIn, which is effectively a tool for building a professional social network. It allows you to easily find your co-workers, provided they are registered with the site, as well as connect with potential employers. So, what is there for employers? The benefit for them is that they are able to build their network, even if they are not actively hiring. And after that, when they need it, they will be able to instantly find the right candidates through their connections. It is like having a job seekers bank at their disposals, almost like a database of resumes. But this bank will update and grow by itself. This leads us to an interesting conclusion: within this new paradigm if you're not online, you cannot be found. And if you cannot be found, you can't be hired. Just because you are outside of the network. All in all, we are witnessing a birth of a new recruitment methods. Who knows where it is going to lead us. Maybe in 5 years it will be mandatory for job seekers to have a Second Life avatar and all the interviews will be conducted in virtual reality. Australian Oracle recruitment team has a blog, they also follow LinkedIn, Twitter and MySpace networks.

The Issue of Risk

Once during one of the projects I was asked what was the risk of something going wrong. I remember I answered that we, programmers, do not assess risks. We just do everything possible to prevent them. “There is no point”, I said, “to calculate the chances and the impact of a file not being in the incoming directory upon the start-up of the process if we can implement a simple check and avoid the disaster”. Needless to say, I was wrong. Nowadays I work in a bank. And one of the things I learned about banks, they all have departments responsible for managing risks. A bank in its business plays a risky game - it lends money. It is risky because there's always a possibility that it will not get them back. And banks learnt to manage these risks. They ask: "What can go wrong? What can we do to prevent that? And what can we do to minimize the impact if we won't manage to prevent it?" Software industry is much much younger than banking. But we are proud of ourselves, because we use cutting-edge technology and most modern methodologies. Yet, we haven't learnt some basic principles other industries employ for decades. We don't manage risks. I've seen a few projects screw-ups, all for various reasons: a technology chosen wasn't robust enough to support the solution, the staff's skills were not strong enough to implement the solutions, requirements were unrealistic... The cause may be different, but the result is always the same - failure, or infinite slippage of the deadline, which is the same as a failure. And in every case the failure seemed to have been unexpected. It puzzled me - why could nobody predict it? And it happens over and over again. I am a faithful reader of The Daily WTF blog. Every day it publishes failure stories like the ones I witnessed. So, if we are so smart, why does it keep happening over and over again? Because in a software project nobody asks what can go wrong. Not all risks are the same. Some risks can be assessed and eliminated. Such as a risk that the existing or planned hardware will not be able to manage the workload. This kind of risk is calculable. We can do a stress test, measure the execution time, do basic calculations and determine not only that we need a new server with more powerful CPU, but how exactly powerful it needs to be. Others are harder to predict. How would you assess a risk of your project manager being a crook and ruining a project? And, finally, there's a mother of all risks – a highly improbable event, the Black Swan, as Nassim Nicholas Taleb calls it in his book "The Black Swan: The Impact of the Highly Improbable". (I would highly recommend it to everyone who wants to learn more about risks) What, for example, is going to happen to your project if all the programmers decide to quit at the same time? Once I worked on a project that employed so called custom rules engines for performing complex batch calculations on a huge dataset - about a hundred million rows. We had a few thousand rules defined by business people, and in order to get a result we needed to apply every rule to the source dataset. As you can imagine, it was slow. Scanning a hundred million rows is not fast, especially when you need to do it a thousand times. So, a strategic decision was made to speed it up. One of our local geniuses came up with a brilliant, as he thought, idea: instead of applying a thousand rules hundred thousand times each, we will reformat the existing rules so that they form one monstrous rule, containing all thousand, which can be applied to the source dataset in one pass. And so we did. One thing, though, we didn't take into account: when combined all together, the rules formed a frankensteinian SQL expression more than 1000000 character long (that's right, more than a million). Yes, it worked, and the process became reasonably fast. But what was the price paid? Imagine that you are a support programmer. You are quite unhappy with your life, and your job in particular, because you're fed up with those stupid users and their problems. And on one evening, just as you were going to go home, you get a call. Apparently, the rules engine failed. You open the log and what do you see? A million characters long SQL and at an error message the end, saying that an error happened somewhere in there. Yep, somewhere in those million chars. You see, that's the way Oracle works. It tells you that an error has happened and tells which one (let's say, division by zero), but doesn't tell where exactly within the given SQL it has happened. So, what are you going to do, step by step? Don't know about you, but I would start writing a resignation letter. And I wasn't the only one confused. No one I asked could answer that question. You may ask, what does it have to do with risk? There is a direct connection. We had certain risk that the system being built couldn't handle the workload. The amount of that risk was known. We could calculate how much time it took to process the data set, extrapolate it to the given requirements and find out that it took 10 times more time than was acceptable. We could resolve it by buying 10 times more powerful server, or cluster of less powerful servers. In any case, the risk and the cost associated with eliminating those risks were calculable. After a strategic decision was made to solve the performance problem by much more complex rules engine, the risk became incalculable. Yes, they prevented the performance problem, but introduced another and much more serious: in a case of a single failure the system was going to go down for undefined time. Because no one would be able to fix it. And the undefined downtime would mean an unlimited loss for the business, because in a business world time is always money. So, what they did is substituted an imminent, but assessable risk, that, in fact, could be prevented, with a less probable, but potentially much more severe which that could not be assessed. I don't believe that solving a minor problem by introducing a much severe one, and hiding a possibility of a failure under the rug is a proper risk management. I'd rather called it a suicide.