[SOLVED] Oracle SQL query improves performance on second and third execution

Oracle SQL query improves performance on second and third execution

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.

Solution

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.