Take the 2-minute tour ×

if a query in oracle takes the first time it is executed 11 minutes, and the next time, the same query 25 seconds, with the buffer being flushed, what is the possible cause? could it be that the query is written in a bad way?

set timing on;
set echo on
set lines 999;

insert into elegrouptmp select idcll,idgrpl,0 from elegroup where idgrpl = 109999990;
insert into SLIMONTMP (idpartes, indi, grecptseqs, devs, idcll, idclrelpayl)
    select rel.idpartes, rel.indi, rel.idgres,rel.iddevs,vpers.idcll,nvl(cdsptc.idcll,vpers.idcll)
    relbqe rel,
    elegrouptmp ele,
    vrdlpers vpers
    left join cdsptc cdsptc on
            (cdsptc.idclptcl = vpers.idcll and
             cdsptc.cdptcs      = 'NOS')
    rel.idtits = '10BCPGE ' and
    vpers.idbqes = rel.idpartes and
    vpers.cdqltptfc = 'N' and
    vpers.idcll = ele.idelegrpl and
    ele.idgrpl = 109999990;

alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush global context;

select /* original */ mvtcta_part_SLIMONtmp.idpartes,mvtcta_part_SLIMONtmp.indi,mvtcta_part_SLIMONtmp.grecptseqs,mvtcta_part_SLIMONtmp.devs,
from SLIMONtmp mvtcta_part_SLIMONtmp
left join vrdlpers mvtcta_part_vrdlpers1 on
   mvtcta_part_vrdlpers1.idbqes = mvtcta_part_SLIMONtmp.idpartes
   and mvtcta_part_vrdlpers1.cdqltptfc = 'N'
   and mvtcta_part_vrdlpers1.idcll = mvtcta_part_SLIMONtmp.idcll
left join compte mvtcta_part_compte1 on
   mvtcta_part_compte1.idcll = mvtcta_part_vrdlpers1.idcll
   and mvtcta_part_compte1.grecpts = substr (mvtcta_part_SLIMONtmp.grecptseqs, 1, 2 )
   and mvtcta_part_compte1.seqc = substr (mvtcta_part_SLIMONtmp.grecptseqs, -1  )
   and mvtcta_part_compte1.devs = mvtcta_part_SLIMONtmp.devs
   and (mvtcta_part_compte1.devs = ' ' or ' ' =  ' ')
   and mvtcta_part_compte1.cdpartc not in ( 'L' , 'R' )
left join mvtcta mvtcta on
   mvtcta.idcll = mvtcta_part_SLIMONtmp.idclrelpayl
   and mvtcta.devs = mvtcta_part_SLIMONtmp.devs
   and mvtcta.grecptseqs = mvtcta_part_SLIMONtmp.grecptseqs
   and mvtcta.flagdonei <> 0
   and mvtcta.devs = mvtcta_part_compte1.devs
   and mvtcta.dtvall > 20101206
where 1=1
order by mvtcta_part_compte1.devs,
share|improve this question
please post the query and the explain plan –  Mat Mar 18 '11 at 9:11
and also add the exact Oracle rdbms version number. To make things easier: execute the query both times while sql_trace is running. Perform a commit after the first run and study the trace file. The rowsource operations should give a clue. –  ik_zelf Mar 18 '11 at 10:03

6 Answers 6

up vote 4 down vote accepted


The thing is, flushing the DB Buffers, like this ...

alter system flush shared_pool

... wipes the Oracle data store but there are other places where data gets cached. For instance the chances are your OS caches its file reads.

EXPLAIN PLAN is good as a general guide to how the database thinks it will execute a query, but it is only a prediction. It can be thrown out by poor statistics or ambient conditions. It is not good at explaining why a specific instance of a query took as much time as it did.

So, if you really want to understand what occurs when the database executes a specific query you need to get down and dirty, and learn how to use the Wait Interface. This is a very powerful tracing mechanism, which allows us to see the individual events that happen over the course of a single query execution. Each version of Oracle has extended the utility and richness of the Wait Interface, but it has been essential to proper tuning since Oracle 9i (if not earlier).

Find out more by reading Roger Schrag's very good overview .

In your case you'll want to run the trace multiple times. In order to make it easier to compare results you should use a separate session for each execution, setting the 10046 event each time.

share|improve this answer
Flushing the shared pool doesn't affect the buffer cache AT ALL. Shared pool is where things like open cursors and parsed queries are stored. The buffer cache that holds actual data buffers is completely different. To flush the data buffers you would ALTER SYSTEM FLUSH BUFFER_CACHE. Aside from that, the point about the OS cache is valid. –  Dave Costa Mar 18 '11 at 13:03
+1, the OS filesystem cache is an important point. –  DCookie Mar 18 '11 at 14:26

What else is happening on the box when you ran these? You can get different timings based on other processes chewing resources. Also, with a lot of joins, performance will depend on memory usage (hash_area_size, sort_area_size, etc) and availability, so perhaps you are paging (check temp space size/usage also). In short, try sql_trace and tkprof to analyze deeper

share|improve this answer

Sometimes a block is written to the file system before it is committed (a dirty block). When that block is read later, Oracle sees that it was uncommitted. It checks the open transaction and, if the transaction isn't still there, it knows the change was committed. Therefore it writes the block back as a clean block. It is called delayed block cleanout.

That is one possible reason why reading blocks for the first time can be slower than a subsequent re-read.

share|improve this answer

Could be the second time the execution plan is known. Maybe the optimizer has a very hard time finding a execution plan for some reason. Try setting

alter session set optimizer_max_permutations=100;

and rerun the query. See if that makes any difference.

share|improve this answer

"bad" is a rather emotional expression - but broadly speaking, yes, if a query performs significantly faster the second time it's run, it usually means there are ways to optimize the query.

Actually optimizing the query is - as APC says - rather a question of "down and dirty". Obvious candidate in your examply might be the substring - if the table is huge, and the substring misses the index, I'd imagine that would take a bit of time, and I'd imagine the result of all those substrin operations are cached somewhere.

share|improve this answer

Here's Tom Kyte's take on flushing Oracle buffers as a testing practice. Suffice it to say he's not a fan. He favors the approach of attempting to emulate your production load with your test data ("real life"), and tossing out the first and last runs. @APC's point about OS caching is Tom's point - to get rid of that (non-trivial!) effect you'd need to bounce the server, not just the database.

share|improve this answer

Your Answer


