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…


I am french. So why in english?

It would be weigh more convenient to me if I was writing this in my mother tongue, in french, so why bother?

Because we have to face it, our empire is not ruling the world anymore, at least since napoleon ;)

So if I want people to be able to read me or if I want to share and to learn by having feedbacks from the big majority, it has to be in english.

So thank you for your understanding, I’ll do my best to be clear.

Excellent webinar by Randolf Geist :
“When it comes to writing efficient queries there are a few key concepts that need to be understood. One of them is the Oracle Cost-Based Optimizer (CBO). Although it’s called a cost-based optimizer it’s actually not the cost we need to focus on primarily to understand why the optimizer makes certain decisions.

In this webinar, you will learn the basics of the CBO, see why it is crucial that the optimizer’s picture of the data fits reality, why cardinality and selectivity estimates matter so much, and which key concepts the optimizer’s model surprisingly doesn’t cover (yet).

Live demos throughout the presentation will enable you to see the CBO in action, and you will learn about leading the optimizer in the right direction when your knowledge of the data is better than the optimizer’s.”

A live Q+A session with Randolf Geist follows the presentation.

Certifié Oracle OCP et Microsoft MCSA+ s




Experienced ORACLE DBA and Datawarehouse architect:

Choosing the architecture, installations and migrations, ensuring the high availability and tuning of ORACLE Databases.

Defining the business needs with user, extracting transforming and loading the datas in a DatawareHouse. Creating and maintainging models and reports.