删除查询第一次运行缓慢，但第二次(相同条件)运行速度较快--如何使查询在第一次运行时快速运行？

Question

考虑表：

Table Name:ORDER
Columns: (ID (PK), ORDER_NUM, ORDER_STATUS, etc...)
Index(ORDER_IDX) exists on (ORDER_NUM, ORDER_STATUS) together.
There are various FKs too, on which Indexes exist as well.
There are about 2 million rows in the table.

考虑一下SQL查询：

DELETE from ORDER where ORDER_NUM=234234;

对于特定的ORDER_NUM值，DELETE查询第一次运行非常慢(删除200行几乎是5秒)。

但是，如果对相同的 ORDER_NUM回滚并再次运行DELETE查询，删除查询现在将在200毫秒内运行。

因此，对于提供给此查询的任何新 ORDER_NUM，查询运行得非常慢。

我能做些什么来加快第一次查询本身的速度？我必须重建索引吗？或者别的什么？

我正在从Oracle SQL客户端工具(比如TOAD/SQL-Developer)中测试这一点--在实际使用它的web应用程序中看到这种缓慢的行为之后。

EDIT>>>

集集在上的结果

第一次运行查询时

           3  user calls
           0  physical read total multi block requests
     4915200  physical read total bytes
     4915200  cell physical IO interconnect bytes
           0  commit cleanout failures: block lost
           0  IMU commits
           1  IMU Flushes
           0  IMU contention
           0  IMU bind flushes
           0  IMU mbu flush

第二次运行查询时，

           3  user calls
           0  physical read total multi block requests
           0  physical read total bytes
           0  cell physical IO interconnect bytes
           0  commit cleanout failures: block lost
           0  IMU commits
           1  IMU Flushes
           0  IMU contention
           0  IMU bind flushes
           0  IMU mbu flush

解释计划-在第一次运行和第二次运行都完全相同-如下所示：

    ID     OPERATION          NAME       ROWS    Bytes    Cost(%CPU)     Time<br>
=======================================================================================
    0      DELETE Statement               49     2891     41   (0)       00:00:01
    1      DELETE             ORDER      
    2      INDEX RANGE SCAN   ORDER_IDX   49     2891     3    (0)       00:00:01

你可以看到非常高的物理阅读，在第一次。

我能做些什么来帮助解决这种情况吗？

Answer 1

这可能是由表/索引碎片造成的，当您通过索引访问数据时，更有可能是索引。

对于表级别，只需对索引(2)执行以下两个步骤：

(1)处理表碎片：alter table "ORDER" move

(2)重建索引：alter index "<YourIndex>" rebuild

如果您正在执行大量的删除和插入，或导致行迁移的更新，这可能适用于您。

Answer 2

理解问题的关键是理解语句是如何执行的。删除是一种相对昂贵的操作，经常会导致性能问题。下面是Oracle执行DML语句的方式：

1. 执行DML的第一步是在数据库缓冲区缓存中找到所需的块(如果它们已经存在的话)，或者从datafiles中将它们复制到缓冲区缓存中(slow)。此外，还将撤销段的空块复制到缓冲区缓存中。
2. 然后，在受影响的行和索引上放置锁。
3. 在此之后，生成重做:生成描述对数据块和撤消块所做的所有更改的更改向量。对于DELETE，要写入undo块的更改向量是整行。
4. 然后，执行删除。整个行从数据块复制到撤消块，数据块中的行被删除。DELETE会产生比INSERT更多的撤销数据，例如，因为整个行的内容都会被复制(因此其他会话可以读取原始数据，或者删除可以回滚)。

您的查询几乎肯定在第二次中运行得更快，因为所有相关的块都已经在数据库缓冲区缓存中了。当然，存储在数据库缓冲区缓存中的块越多，所需的I/O就越少。确保您的SGA大小适当。

因此，对于你的问题，我们必须看一看以下几点：

• 最重要的是，查询是否使用索引？是否有效？运行删除查询的解释计划，并检查是否使用了ORDER_NUM索引以及如何访问数据。
• 表中是否有约束？如果存在带有"ON DELETE级联“的约束，则可能存在受DELETE影响的其他表。

因此，对于你的问题，看看执行计划(解释计划)可能是你最好的选择。

Answer 3

除了缓冲区缓存问题之外，提高性能的一种方法是促进具有相同ORDER_NUM的记录的物理集群。这只有在您通常通过ORDER_NUM选择记录组时才有意义，但可以通过在散列集群中创建表(以及包含ORDER_NUM的任何子表)来实现。

这样做的好处是，带有ORDER_NUM谓词的查询将访问更少的表段块，因此即使需要物理i/o，也需要更少的数据块。此外，通过确保每个缓存块包含更高比例的您感兴趣的行，可以更有效地使用缓冲区缓存，并且缓存的块将更少。