sreenathkm
IS-IT--Management
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
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