STRANGE BEHAVIOUR FROM ORACLE-VERY CRITICAL ISSUE

[sreenathkm]

Hi ,
As the Fiscal Quarter ending for my company is approaching fast I am facing strange problems out of my Oracle Database.

I have few fact tables which are of around 100M - 200M.And in one of my BO reports I am joing 3 such fact tables
with 4 other dimension tables and giving a report.All this days this report is used to throw out the
results in around 2.5hours, but now interesting the same report with absolutely no change in the query or at
database is never returning even after 5,6 hours.Even the volume of data has also not changed considerably.

In my last couple of hours working I observed a intereting fact...I had deleted my statistics for a fact table
and was re-computing the statistics, interestingly when the statistics is deleted my explain shows a very good plan
but after the statistics is computed it is making a Hash Join, which I know is creating the issue.I didnt even know that
my users are trying all round the clock for the report and in the phase where my statistics for this table are deleted
they got the report and after the re-computation of statistics, the problem has come to the start where it is never returning.

I have however analyzed all the underlying tables and indexes.

Can I please have ur suggestions in this regard,which will help me a lot at this juncture.

Thanks in advance

Sreeenath
sreenathkm@hotmail.com

 

[ek03]

Try putting a hint in the query - /*+rule*/. This will prevent Oracle from using the statistics when creating the execution plan.

 

[sreenathkm]

HI EKO3,

Thanks for the reply.. I have already tried RULE but that doesnt seem to be helping as it is still going for a Hash Join.

 

[ek03]

Hi.
If you know what the plan is you'd like to see, you can specify it in a hint - to use a nested loop join, you can use the use_nl(table_a, table_b) hint. Have you tried doing this? (It will join the tables in the order listed inside the parenthesis.)

 

[fmorel]

Hours for querying 100M - 200M seems to be too much !
You may need some helpful indexes.
Would you post queries and explain plans ?