ORACLE

Today I had the classical phone call :
“Hi, what did ,you do yesterday? Because tere are no datas in my report”
- Hu…nothing I swear, you know I do nothing at work.But I’m on it.

Indeed, one job is still pending in the ETL.

A quick look at the monitoring tool show that a query is still running.

ecart
Looks like it shouldn’t. That explains why there is nothing in this table.

A query that was reasonable is suddenly lasting forever. So next step is :
Look for a change of plan. Luckilly I have a tool that stores past plans ans I can compare with the new one.

Yes, AWR is licensed and as long as you don’t want (or can’t afford) to buy the enterprise edition, you need to find another way.
My choice was Ignite8 by Confio. Pretty goos tool.

Then I chose to compare the two explain plans and you can notice that the CBO is now using 2 indexes it was not using before.

 

avec_index

sans_index

They are not new, created a long time ago. So for now I can’t figure out why it suddenly chose to pick it up but it did.

So let’s try to prevent it to use the one with the most operation cost.

I did that by making the index unusable and that did the trick. Back to normal execution time.

So far I don’t know if the index is used by other query so I rebuilt it and took the hint approach to forbi the CBO to use it :

SELECT /*+ NO_INDEX(D IDX_F0911_COPIE_0) */  A.”CD_TIERS”,…

Tere are still things to investigate but the important thing is : users can access their report which was the top priority.

Share

To cloud or not to cloud!

http://www.forbes.com/sites/oracle/2012/10/09/larry-ellison-doesnt-get-the-cloud-the-dumbest-idea-of-2013/

OH MY GOD…

20120927-223127.jpg

Very usefull and interesting.

Should be forwarded to every system administrator before he falls in deduplication traps…or eventually after

http://www.oracle.com/webapps/dialogue/ns/dlgwelcome.jsp?p_ext=Y&p_dlg_id=10070854&src=7011670&Act=164

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…

WHAT CAN WE CONCLUDE :

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.
WHY?
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…