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.


Get every new post delivered to your Inbox.

Join 152 other followers