jump to navigation

Friday Philosophy – The Tech to Do What You Need Probably Exists Already November 30, 2012

Posted by mwidlake in Friday Philosophy, Perceptions.
Tags: , , ,

How many of you have read the Oracle Concepts manual for the main version you are working on?

This is a question I ask quite often when I present and over the last 10 years the percentage number of hands raised has dropped. It was always less than 50%, it’s been dropping to more like 1 in 10 and Last year (at the UKOUG 2011 conference) was the nadir when not a single hand was raised. {Interestingly I asked this at the Slovenian User Group 3 months ago and something like 40% raised their hand – impressive!}.

Why do I feel this is important? Well, do you know all the technology solutions available across just the core RDBMS with no cost options?. No, you don’t, you (and I) really don’t. If you read the concepts manual, even just skimming it, you will be reminded of a whole load of stuff you have only dim memories of and perhaps you will even see some features that passed you by when they were first introduced.

Of course, you would need to read a few more manuals to get the full picture, such as the PL/SQL Packages and Types References, as so much good stuff is introduced via built-in packages, and the SQL Language Reference, as SQL has been extended quite a lot over the last couple of versions. Dull reading indeed but I’d estimate that if you read those three you would be aware of 90%+ of the Oracle technologies that are available to you out-of-the-box and considerably more than all but a handful of Oracle Experts. You’d know more than I as (a) I have not skimmed the PL/SQL one for years and (b) I have a rotten memory.

My point it, you can’t consider using Oracle technologies you don’t know about or remember – and they could be just what you need to fix the problem you see in front of you.

I’ll give a couple of examples.

Problem, physical IO is too high, your storage system is bottle-necked.
Answers, Reducing Physical IO:
-First up, Index Organized tables. Some of you will be aware that I am very keen on IOTs and the reason I am is that I’ve used them to physically group data that the application needed to select over and over again. It can make a massive improvement to that sort of system. They are rarely used.
-Clustered Tables. Even less used, in fact has anyone reading this used them in anger in the last 10 years? Great for situations where you need parent-children or parent-children-type-1+parent-children-type-2 data. I confess, I have not used them in anger for years.
-Move table (and order them as you do it!) and re-build indexes to remove “dead” space. This one got a bad name, especially the index rebuilds, as people were doing it needlessly without appreciating what the intention was, but now I hardly ever see it done – even when it is of benefit.
-Compress your tables and indexes. With normal Oracle compress (no need for HCC). In tests I’ve done I pretty much always see a drop in physical IO and run time. Being candid, I can’t remember doing any tests and NOT seeing an improvement but I usually only test when I expect and improvement and I don’t want to give anyone the impression it will always help.

All the above were available in Oracle 7 or 8 and all have improved over the versions.

Problem, you want to carry out some long,complex data processing in PL/SQL and if a step fails, be able to handle it and carry on.
– Savepoints. You can rollback to a savepoint, not just to the last commit. In this way you can break the task into chunks and, if something towards the end fails, you roll back one step (or several, your choice) and call an alternative routine to handle the exception.
– Autonomous Transactions. You want to record that an error occurred but not fail the original action or not save anything it has done to date. An autonomous transaction runs in it’s own sub-session and commits in it do not effect the calling session.
– Temporary Tables. You can put your working information in them as you progress and if you need to bomb out (or some evil DBA kills your session for running too long) the temp table contents just disappear. No clean up needed.

Maybe the above is not so fair, I have not been a proper PL/SQL developer for a while now, but I hardly see the above used. Especially Savepoints. I can’t remember not having savepoints available (hmm, maybe Oracle 6) and Autonomous Transactions and Temporary tables are Oracle 8 (I thought Temporary tables might be 9 but Tim Hall’s OracleBase says 8)

Another thing I have noticed over the years is that so often I will read up on some oracle feature I know little about, only for it to come up in the next few weeks! There is a psychological aspect to this, that we only remember these “coincidences” and not the more common situations where we read up on something which does not subsequently come up before we forget about it, but I think that it is also that we tend to use only a few solutions to solve the problems we see and adding another solution to our list means the chances are high it will be suitable for something soon.

OK, so it would help us all to read the manuals (or other Oracle technical books) more. Now the big problem is finding the time.



1. Gary Myers,SydOracle (@sydoracle) - December 1, 2012

I confess I haven’t reread the Concepts manual for 11gR2. But in me defense, I do look at the “What’s New” in a choice selection of the books, plus I follow a bunch of blogs.
And for the second point, I think you are missing the EXCEPTION clause and, in newer versions, DML error logging. And if autonomous transactions aren’t favoured, there’s good old UTL_FILE to record errors in flat files. Several of our packages have an exception handler that sends an email. Honestly can’t remember when I last used a savepoint.

mwidlake - December 1, 2012

If we are confessing then I admit that I don’t feel I read blogs enough at present – so much good stuff out there.

Thanks for the extra solutions for scenario two. I wasn’t trying to be comprehensive but it’s good to have more options. Ahh, UTL_FILE, I have fond memories of spending a night in a very fraught office converting their DBMS_OUTPUT “exceptions processing” code that was putting out over 1 million characters and blowing up to use UTL_FILE. Back then you could only write out to destinations stated by that initialisation parameter…quick google…UTL_FILE_DIR of course.

2. Mike Cox - December 1, 2012

I keep coming across teams still using what is essentially Oracle 7 or even 6 functionality but on a 9/10/11 platform, it seems such a simple thing to just read the new features docs that come with each release but very few people seem to do it.

Dread to think how many times I’ve gained brownie points with ‘you do know that a bulk operation would make that 10 times faster’ or ‘given you are I/O bound, have you considered compression ?’

One of the key problems seem to be that so few have read any of the official oracle documentation or are even aware of it, I always keep a disc/usb key with a full set of docs going back to 8 and have them accessible whenever I am.

I should qualify that when I learnt Oracle (not sure if it was v3 or v4) the full documentation set for everything was about a dozen manuals so a lot easier to cope with than the huge library that comes with recent versions. If I was new to Oracle today I probably wouldn’t know where to start, unless someone pointed me to the concepts manual.

Another thing I find useful, even for technologies/areas I know ‘off by heart’ is to re-read sections of manuals, if Im going to do some development work with dbms_sql for instance I’ll re-read the chapter, there’s usually an optional parameter or sub-program that I’ve ignored before and filed aways as ‘why would I want to do that ?’ but its now the golden bullet I was looking for.

In an ideal world we need to be aware of all the features of the technology and the implications, compression is great but what about the cpu impact ? Heterogeneous connectivity sounds great but how does it perform in use etc etc

As you say, so much to do, so little time !

mwidlake - December 2, 2012

That’s interesting Mike, sites that are the opposite – they use a method that worked 10 or even 20 years ago and stick to it. I guess it is the flip side of the same coin – not seeing what is available.The bulk processing in PL/SQL stepped forward so much after (oh heck, maybe even IN) 8i and I did not keep pace because my work life was elsewhere, But when my worklife dropped me back into PL/SQL development I looked at bulk processing and pipeline functions and thought “OK, how do I use them”?.
I really do think that this is a key difference between “ok at the job” and “very good at the job”. I know I am not “smarter” than so many others but I keep progressing as I ask “what is now available” and “why does this work this way” – Just like you, Mike. You taught me well.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: