Deletes In Oracle: More ‘Costly’ Than You Think

Oracle takes a rather simplistic view of the cost of deletes, which in most cases is fine. Small deletes, of say 1000 rows or less, really have no appreciable additional costs; larger deletes however can have additional overhead that isn’t taken into account by the optimizer when plans are generated. Let’s look at an example using Oracle 12.1.0.x, provided by Jonathann Lewis, to illustrate this.

Depending on the available execution path the cost of a delete can equal the cost of a tablescan, the cost of an index fast full scan, or the cost of an index range scan. The choice of which path to take is based, basically, on the cost of a ‘select rowid from … where …’ query. A table was created with 10 million rows of data with a primary key and three additional indexes; about half of the data was deleted. The execution path and apparent cost of this delete are shown below:


SQL>
SQL> delete     from    t1
  2  where      date_open < add_months(sysdate, -60);

5020333 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 3099179474

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |            |  5019K|   129M| 13318   (1)| 00:00:04 |
|   1 |  DELETE           | T1         |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| T1_DT_OPEN |  5019K|   129M| 13318   (1)| 00:00:04 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DATE_OPEN"<ADD_MONTHS([email protected]!,-60))


Statistics
----------------------------------------------------------
        798  recursive calls
    6185328  db block gets
      13493  consistent gets
     290899  physical reads
 2354882072  redo size
        878  bytes sent via SQL*Net to client
        887  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          4  sorts (disk)
    5020333  rows processed

SQL>

Look first at the execution plan; the cost of the delete is exactly the same as that reported for the index range scan executed to find the data. Again, for small deletes this doesn’t appear to be a problem, but looking at the statistics for the delete it can be seen that the redo generation is quite large, and for the number of rows processed the index maintenance would also be quite expensive. Remember that four indexes will be maintained by this delete operation, yet none of that cost appears in the execution plan. So what is Oracle doing during such a delete? For an index range scan Oracle deletes a row, then records the rowid and key values for bulk processing the index maintenance after the delete is finished. Not so with an index fast full scan or tablescan, where Oracle processes all of the indexes at the time the row is deleted from the table. The former situation results in a lower redo and undo cost since the redo and undo are generated for each block processed, not each row. Still, the index maintenance carries a rather large cost apparently unreported by Oracle.

The processing paths taken for the various execution paths can vary in execution time as well, with the index range scan taking the least amount of time when measured against an index fast full scan. For the deletes processed in this example the index fast full scan delete took approximately three times as long to complete. Looking at the consistent gets versus the db block gets it’s obvious that a lot of physical block reads were executed, due to Oracle moving around the table to find and delete the specified data, making it difficult to keep data buffered. This occurs for either of the index scan paths. The index fast full scan path, that processes the indexes at the time each row is deleted, causes even more physical read activity from the table, increasing execution time and as a result, the cost of performing such a delete.

Oracle chooses the path based on cost, and in Oracle 12.1.0.x one of those possible paths utilizes an index fast full scan. The cost is a driving factor, and when an index fast full scan is the ‘cheapest’ such a path can generate more work than either of the remaining paths (index range scan or table scan). Knowing this can help in hinting such deletes to favor either an index range scan or a table scan, to reduce the work such a delete can generate.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Latest Articles