Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2010
    Posts
    4

    Unanswered: Unusual high query execution time

    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.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    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?

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post EXPLAIN PLAN for both slow & fast queries
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Aug 2010
    Posts
    4
    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.

  5. #5
    Join Date
    Aug 2010
    Posts
    4
    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.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by rahul_pat1748 View Post
    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.
    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.

  8. #8
    Join Date
    Aug 2010
    Posts
    4
    "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.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by rahul_pat1748 View Post
    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.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    please post SQL & EXPLAIN PLAN
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Awr?

    Quote Originally Posted by rahul_pat1748 View Post
    ... Etc ...
    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.
    Did you generate AWR report for the day/time this occurs?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •