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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>