I am facing a strange problem. I have query in a BOI DLL which normally takes 3-4 seconds to execute. But sometimes it takes 200-350 seconds to execute the same query. First BOI Call in the morning and after lunch. Sometimes in between too this query takes time.
any clues as to why query slows down drastically at times?

Thanks in Advance
Ayoobi

Dikkie Dik

7th October 2005 17:07

When having Oracle as a database it looks like you fase a parsing problem: The first time a query is executed, a plan has to be build how to access the data. Som BOI's build queries on 20+ tables that can confuse the optimizer and thus take a lot of time. Depending on the version of your database I can give you some hints.

Another reason can be that before you do your query someone else ruined the Oracle cache by reading something very huge and thus removing your cached data from the database. This can happen in the early morning when a fresh start of the database occurs, but when someone else reads half of the database, it can ruin your cached data.

Hope this helps,
Dick

ayoobi

8th October 2005 08:55

We are on Oracle 9i

Hi Dik,

Thanks for your explanation ! I think now i understand this strange behaviour.

Infact, this query was quite inefficient. So hopefully improving this query should solve this problem.