My query runs faster the second time around, how d

Question

i am running a query in oracle 10 select A from B where C = D B has millions of records and there is no index on C

The first time i run it it takes about 30 seconds, the second time i run the query it takes about 1 second.

Obviously it's caching something and i want it to stop that, each time i run the query i want it to take 30s - just like it was running for the first time.

• i am over-simplifying the issue that i am having for the sake of making the question readable.

Thanks

Answer 1

Clearing the caches to measure performance is possible but very unwieldy.

A very good measure for tracking achieved performance of tuning efforts is counting the number of read blocks during query execution. One of the easiest way to do this is using sqlplus with autotrace, like so:

set autotrace traceonly
<your query>

outputs

...
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        363  bytes sent via SQL*Net to client
        364  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

The number of blocks read, be it from cache or from disk, is consistent gets.

Another way is running the query with increased statistics i.e. with the hint gather_plan_statistics and then looking at the query plan from the cursor cache:

auto autotrace off
set serveroutput off
<your query with hint gather_plan_statistics>
select * from table(dbms_xplan.display_cursor(null,null,'typical allstats'));

The number of blocks read is output in column buffers.

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name           | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |      3 |        |     1 (100)|          |      3 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE  |                |      3 |      1 |            |          |      3 |00:00:00.01 |       3 |
|   2 |   INDEX FULL SCAN| ABCDEF         |      3 |    176 |     1   (0)| 00:00:01 |    528 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------------------

Answer 2

See this question...

It shows how to clear caches for data and execution plans, but also expands on whether it's a good idea or not.

Answer 3

The obvious answer is for each test case to run the query multiple times, and throw out the first result.

Its not easy to completely replicate the conditions of the first query run, because of the various caches involved: some are Oracle caches (cursor, buffer, etc.); some are OS (disk cache, depending on Oracle config); some are hardware (SAN, RAID, disk).

Rebooting the database server before each trial will probably come pretty close to consistent conditions.

Answer 4

It might help you

http://www.mssqltips.com/tip.asp?tip=1360

CHECKPOINT;
GO DBCC DROPCLEANBUFFERS;
GO