1

We are analyzing sql statements on an Oracle 12c database. We noticed that the following statement improved by running several times. How can it be explained that it improves by executing it a second and third time?

SELECT COUNT (*) 
  FROM asset
 WHERE     (    (    (status NOT IN ( 'x1', 'x2', 'x3'))
                 AND ( (siteid = 'xxx')))
            AND (EXISTS
                    (SELECT siteid
                       FROM siteauth a, groupuser b
                      WHERE     a.groupname = b.groupname
                            AND b.userid = 'xxx'
                            AND a.siteid = asset.siteid)))
       AND ( (assetnum LIKE '5%'));
  • First run: 24 Sec.
  • Second run: 17 Sec.
  • Third run: 7 Sec.
  • Fourth run:7 Sec.
  • Tuned by using result cash: 0,003 Sec.
r0tt
  • 379
  • 3
  • 20
  • 2
    Have you flushed shared_pool and buffer_cache before running query second, third and so on? Oracle caches results of queries. – Kacper Oct 11 '16 at 13:29
  • 1
    Adaptive query optimization: http://www.oracle.com/technetwork/database/database-technologies/query-optimization/overview/index.html – David Aldridge Oct 11 '16 at 14:47

2 Answers2

4

Oracle does not cache results of queries by default, but caches data blocks used by the query. Also 12c has features like "Adaptive execution plans" and "Cardinality feedback" which might enforce execution plan changes between executions even if table statistics were not re-calculated.

ibre5041
  • 4,903
  • 1
  • 20
  • 35
1

Oracle fetches data from disc into memory. The second time you run the query the data is found in memory so no disc reads are necessary. Resulting in faster query execution. The database is "warmed up".

Rene
  • 10,391
  • 5
  • 33
  • 46