我的查询第二次运行得更快，我该怎么做呢？ [英] My query runs faster the second time around, how do i stop that?

问题描述

我正在oracle 10中运行查询从B中选择A，其中C = D
B有数百万条记录且C上没有索引

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

第一次运行它需要大约30秒，第二次运行查询需要大约1秒钟。

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

显然它正在缓存一些东西，我希望它能阻止它，每次我运行查询时我希望它需要30秒 - 就像它第一次运行一样。

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.

• 为了使问题可读，我过度简化了我所遇到的问题。

谢谢

推荐答案

清算用于衡量性能的缓存是可能的，但非常笨拙。

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

跟踪调优工作的性能的一个非常好的衡量标准是计算查询执行期间的读取块数。最简单的方法之一是使用带有autotrace的sqlplus，如下所示：

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>

输出

...
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.

另一个方法是使用增加的统计信息运行查询，即使用提示 gather_plan_statistics ，然后从游标缓存中查看查询计划：

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'));

读取的块数在列缓冲区中输出。

---------------------------------------------------------------------------------------------------------------------
| 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 |
---------------------------------------------------------------------------------------------------------------------

这篇关于我的查询第二次运行得更快，我该怎么做呢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！