Hi,

I am facing a very strange issue with one of our Oracle query. The query is usually completes in a minute or two. Even the execution plan of the query is good and it works perfect most of the times, as expected. The query fetches about 1000-2000 records each day.

But on a given day, the query takes about 30-40 mins to execute completely. Upon checking the load on DB server, there are no other processes running which can impact the run time of this query. Moreover, the record counts fetched are almost same as compared to other days. There is no pattern observed as that this phenomenon occurs. it all happens once in a while.

Configuration is Oracle 10g with RAC environment on LINUX

Any suggestions are welcome.

Is the query using bind variables?
In case the query is slow, are you using variable values that are different compared to those where it runs fast?

post EXPLAIN PLAN for both slow & fast queries

It is a single query and the explain plan of the query is same. We are not changing anything in the query. The query is a part of a regular nightly ETL job.

Also, there are no bind variables used in the query. It is a straight forward SQL query.

As per our DBA recommendations, we do apply some parameter settings changes in ETL job before the query runs each day:

alter session set optimizer_index_caching=15;

alter session set optimizer_index_cost_adj=85;


But it is not helping much as we can still see a high query execution time on a given day.

Bind Peeking, Ad Hoc Queries, Stable Performance. On 10G you can only pick any two. | The Pythian Blog

My first guess is that you were hit by the problem caused by bind variable peeking. But if you don't have bind variables, that guess was apparently wrong.

I think the only chance you have, is to turn on tracing before the slow statement is executed and then analyze the trace to see where the time is spent.

"I think the only chance you have, is to turn on tracing before the slow statement is executed and then analyze the trace to see where the time is spent. "

The problem is : It is a same query and nothing like a slow and fast query. Regularly the query executes in a minute and sometime it peaks at takes 35-40 mins. We don't know, on which day will it take long time to run and on that day put the trace on. Its completely unexpected behavior and no pattern applies.

Then you need to enable tracing every time the statement is run.

please post SQL & EXPLAIN PLAN

Did you generate AWR report for the day/time this occurs?