Query explain plan statistics oracle optimizer

Very common situation although here it happened on a developpement server, that is without the smell of fear and the ‘ I am right behind your shoulder waiting for it’

So this morrning I check my backups and what a surprise database is frozen, no more room for archived logfiles because of this never ending battle against sys admins who are reluctent to give the required, needed, requested and scarce ressource : storage. To me it appears that 80% of downtimes are related to this…just guessing.

So I have to start again the process of loading data into the Datawarehouse…

Thanks to my (very effective and far cheaper than tuning packs monitoring tool, Ignite), I can quickly identify the offending query that used to take seconds and now takes more than an hour.

I check the explain plan and notice 2 cartesian products. If I remember well it’s not supposed to be a good thing unless you’re trying to generate on purpose lines for a reporting need.

So I check the explain plan to compare with the days before.
BINGO. No cartesian products. Still don’t know what happened but before running into what could be an expensive and long study, let’s stop for a while and think. What changed?

Nothing. Oh wait, yes, once again a crash in the night because of lack of space.

Wait… what time was it, during the night? And isn’t a job supposed to gather stats at the same time?

And what among other things can cause a radical change of plan? Bad or lack of statistics.

So, bad habit of mine, I wil make an assumption, the optimizer will act better with godd statistics and I think it is my problem here. A quick test to see if my guess is right. I gather stats on the 3 tables involved,and then again the explain plan…ET VOILA…no more cartesian products. I can only guess that now The plan is more accurate, let’s execute the query…it ran in a few seconds…


1. It’s important to be able to browse in the past of the database to see how it was doing, by the means of tools, scripts, oracle console (assuming you have the tuning packs licenced and that you are runing an Enterprise Edition).

2. It’s worth thinking a little and mix knowledge of the concepts with some logic and method to sometimes be able to solve quickly problems that can be hard to understand, some query can be very complex to tune.

3. The root cause is as most of the times, system related, to my opinion. One can argue that you have to forecast your storage needs…but try to argue with your system admin, it’s tiresome, I know for sure I was one of them…long time ago…

The fact that it took less than half an hour to make the diagnostic, find a solution and that it requires, I humbly think, solid knowledge, methods and logic, will remain hidden to almost everybody.
My guess is that it’s more natural and easy to think that if it took this time, it was this simple rather to think anything else.

So next time make it last…no just kidding…or not…