Skip to Main Content
  • Questions
  • Access using ROWID is slow in a DELETE statement

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Girish.

Asked: December 10, 2021 - 12:55 pm UTC

Last updated: January 13, 2022 - 1:05 pm UTC

Version: 12.1

Viewed 10K+ times! This question is

You Asked

Hi,

Access by ROWID is supposed to fastest of all and when same is used in below DELETE statement. It is taking time.
There are also "db file sequential read" and "free buffer waits" wait events.

1)Why statement is taking time ?

2)Does reorganizing table help this in performance ?

3)Access using Primary Key would be better than ROWID ?

DELETE FROM TABLE_NAME 
WHERE
 ROWID = :B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      665      0.02       0.05          0          0          0           0
Execute    665   1210.36    7942.56    1468292    1021600   28002629     1012638
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1330   1210.39    7942.61    1468292    1021600   28002629     1012638

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  TABLE_NAME (cr=3878 pr=3599 pw=0 time=4650404 us)
      3500       3076      26500   TABLE ACCESS BY USER ROWID TABLE_NAME PARTITION: ROW LOCATION ROW LOCATION (cr=3874 pr=5 pw=0 time=38879 us cost=1 size=49 card=1)
      
Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                   2483420        1.59       6092.19
  latch: cache buffers lru chain                379        0.31          6.71
  latch: redo allocation                          7        0.01          0.04
  buffer busy waits                               2        0.00          0.00
  latch: object queue header operation            3        0.00          0.00
  resmgr:cpu quantum                            215        0.13          6.09
  free buffer waits                            1103      105.70       1090.89
  latch free                                     38        0.20          0.83
  wait list latch free                            1        0.00          0.00
  latch: cache buffers chains                     2        0.00          0.00
  log file switch (private strand flush incomplete)
                                                  1        0.02          0.02  


Thanks,
Girish


and Chris said...

Table access by rowid is the fastest way to find ONE row. You're removing a fair chunk of data. A smidge over 1 million rows according to that report. Essentially it seems you're fetching rows one at a time to remove them.

DELETE also is typically the slowest operation. So I'm not surprised this is taking a while.

That said over two hours to remove 1 million rows is excessively slow - something's not right. A couple of things that may be contributing to this:

* You have over 28 million current mode block gets - so for each row you remove, the statement does 28 I/O ops.
* The max wait for db file sequential read is 1.5 seconds - which is incredibly long! Are the disks working correctly?

All that said, often the fastest way to remove lots of data is NOT to delete at all and use some other method. So I would rethink the process.

As your table is partitioned - can you use truncate/drop partition?

I discuss various other options for fast "delete" at:

https://blogs.oracle.com/sql/how-to-delete-millions-of-rows-fast-with-sql

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Why are so many rows deleted?

Mike Tefft, January 10, 2022 - 8:45 pm UTC

Your trace shows 665 executions of a "delete by rowid" but over 1 million rows deleted. This seems to be a contradiction. A delete by ROWID should delete only one row *in that table*....

Is your table a parent of one or more foreign-key constraints, with "ON DELETE CASCADE" specified?
Connor McDonald
January 11, 2022 - 3:49 am UTC

nicely spotted

forall

A reader, January 12, 2022 - 2:04 pm UTC

To answer question this delete is called in a forall loop, therefore when it deletes looks like it is not making use of forall and that is incorrect coding
Chris Saxon
January 12, 2022 - 2:22 pm UTC

FORALL executes the statement once for each element in the array. The key difference is it does this in one call to the SQL engine, avoiding lots of context switches.

Do you have any ON DELETE CASCADE foreign keys defined on the table?

ON DELETE CASCADE

A reader, January 13, 2022 - 3:09 am UTC

We do not have any on delete cascade FK. We have only ON DELETE FOR EACH ROW trigger defined on this table
Chris Saxon
January 13, 2022 - 1:05 pm UTC

Aha! That would also add to the work the delete does! There's a good chance this is why your statement takes so long.

In the ideal world you'd move its contents into the delete procedure and remove the trigger. This isn't always possible though.

Instead of a FOR EACH ROW trigger, can you make a compound trigger that:

- Collects the necessary information in the FOR ROW procedure
- Does the work in the FOR STATEMENT part?

See an example at https://oracle-base.com/articles/11g/trigger-enhancements-11gr1#compound_triggers

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.