Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a general question about something I'm seeing in performance tuning in Oracle. I have a query that I'm testing. It's too big and complex for me to provide a simple example, sorry! (I tried lowering it down, but lost the symptom)

The first time I run the query, it's 2 seconds. Subsequent times it's 3, 4, or even 5 seconds. This happens consistently - if I change the spacing, I get 2 seconds again, then it goes back up.

This is the opposite of what I would expect - after the first time, the database doesn't need to parse the query, and blocks should be in read into the buffer cache. The only thing I can think of is that it is writing temp data somewhere, in such a way that it is faster to write and read it than it is to just read it. But that of course makes no sense.

Does anyone know why this is happening? Or any red flags in the execution plan that would cause something like this?

FYI, this is a SELECT, with multiple joins, aggregation, and views. No internal PL/SQL.

Thank you

@Colin It's unlikely another process could be affecting this Dev machine, at least so consistently. Not sure what dynamic sampling is. Same thing as cardinality feedback?

@druzin No bind variables, but no predicate changes either - same hash.

@mustaccio Good idea, although I suspect it's something to do with implicit temp tables or caching. I'll give it a try.

share|improve this question
    
Are there other processes running on the machine? I wonder if the query takes so many resources that the process of running the query destroys the cache which is built up by other processes' queries. –  Colin 't Hart Sep 26 '13 at 18:33
    
Is the query using dynamic sampling and other means to arrive at execution plans other than just the tables' statistics? –  Colin 't Hart Sep 26 '13 at 18:34
    
Are you using bind variables in your query ? –  druzin Sep 26 '13 at 19:39
    
I guess the first thing to try would be turn autotrace on in SQL*Plus and compare its output for different executions of the query. –  mustaccio Sep 26 '13 at 20:20
    
Does "same hash" mean the same plan_hash_value? How are you generating the plan? Are you absolutely certain that you are comparing the plans that are actually used rather than the plans that Oracle expects before executing the query? –  Justin Cave Oct 28 '13 at 20:16
show 1 more comment

2 Answers

Some things I would look at:

  • Is there any performance difference between CURSOR_SHARING=EXACT and CURSOR_SHARING=FORCE.
  • Look at the wait events for this SQL_ID.

    select
        event,
        time_waited "time_waited(s)",
        case when time_waited = 0 then 
            0
        else
            round(time_waited*100 / sum(time_waited) Over(), 2)
        end "percentage"
    from
        (
            select event, sum(time_waited) time_waited
            from v$active_session_history
            where sql_id = 'SQL_ID'
            group by event
        )
    order by
        time_waited desc;
    
share|improve this answer
add comment

Are the two executions using the same plan? Are you sure? autotrace does not always show the actual plan!

Did you try this?

SELECT ... your query...;
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

(twice one after another)

I am guessing that the DB chose one plan for the first execution, then based on the run decided to use another one. What happens if you wait some time and then execute the same query again? Does it run fast or slow again?

share|improve this answer
    
This is not an answer. –  Max Vernon Nov 12 '13 at 23:14
    
It is an answer as much as the others. –  David Balažic Nov 13 '13 at 10:38
2  
sorry - I'm not trying to pick on you. I only saw your answer when looking at the review stream - since it poses several questions, it looks to me like it should be a comment on the original post instead of an answer. You are correct the other answers are just as bad. –  Max Vernon Nov 13 '13 at 14:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.