DELETE QUERY Runs Slow First Time, but second time onwards (for same condition) runs fast - How to make the query fast at first time running?

Question

Consider Table:

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.

Consider SQL Query:

DELETE from ORDER where ORDER_NUM=234234;

For a particular ORDER_NUM value, the DELETE Query runs very slow first time (almost 5 seconds to delete 200 rows).

But if I rollback and run DELETE Query again for same ORDER_NUM, the DELETE QUERY now runs in 200 milliseconds.

Therefore, for ANY new ORDER_NUM supplied to this query - the query runs very slow.

What can I do to fasten the query first time itself? Do I have to rebuild indexes? Or anything else?

I am testing this from a Oracle SQL Client Tool (like TOAD/SQL-Developer) - after seeing this slow behavior within the web application where it is actually used.

EDIT>>>
Results of SET AUTOTRACE ON

FIRST TIME when QUERY is RUN

           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

SECOND TIME When Query is RUN

           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

The EXPLAIN Plans - in both FIRST and SECOND RUN is exactly same - shown below:

    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

You can see Very High Physical Reads, during the First Time.
Can I do anything at all to help with this situation?

Answer 1

The key to understand your problem is to understand how statements are executed. DELETE is a relatively expensive operation and often leads to performance problems. So here is how Oracle executes a DML statement:

1. The first step in executing DML is to find the required blocks in the database buffer cache (if they are already there) or copy them into the buffer cache from the datafiles (slow). In addition to that, an empty block of an undo segment is also copied into the buffer cache.
2. Then, locks are placed on the affected rows and indices.
3. After that, redo is generated: Change vectors describing all the changes done to the data block and undo block are generated. For a DELETE, the change vector to be written to the undo block is the entire row.
4. Then, the DELETE is carried out. The whole row is copied from the data block to the undo block and the row in the data block is deleted. DELETE generates much more undo data than an INSERT for example, because the contents of the whole row are copied (so other sessions can read the original data or the deletion can be rolled back).

Your query almost certainly runs faster the second time because all the relevant blocks are already in the database buffer cache. Of course, the more blocks can be held in the database buffer cache, the less I/O is needed. Make sure your SGA is sized appropriately.

So for your problem, we have to look at the following points:

• Most importantly, does your query use your index? Is the index VALID? Run the EXPLAIN PLAN for your DELETE query and check if the index for ORDER_NUM is used and how your data is accessed.
• Are there any CONSTRAINTS on the table? If there are CONSTRAINTS with "ON DELETE CASCADE", there might be other tables affected by your DELETE.

So for your problem, looking at the Execution Plan (EXPLAIN PLAN) might be your best bet.