Someone asked a similar question on OTN a few years ago about an update.
The principle is the same for a delete: FORALL (i.e. array bulk statement.
See: https://jonathanlewis.wordpress.com/2017/03/21/deception/
Regards
Jonathan Lewis
On Thu, 18 Nov 2021 at 18:01, Rich J <rich242j@xxxxxxxxx> wrote:
Hey all,
In Oracle 19.6, a DELETE statement was run that deleted exactly 2M rows
(out of 45M total in the table), which was something like:
delete from "MYSCHEMA"."MYTABLE" where somecol01 = 'BAD' and somecol02 =
10101
It ran as expected. This DB is the source in a logical replication (3rd
party software). The replication software created statements to run in the
destination DB like:
delete from "MYSCHEMA"."MYTABLE" t where rownum = 1 and "PKCOLUMN"=:V001
OK, so one SQL statement on the source gets translated (via transaction
logs?) into 2M statements, which are based on the table's PK, which exists
on both the source and destination. Right?
Expecting some lag as the individual DELETE statements on the destination
catch up to the source, Enterprise Manager 13.4 shows me that 172
executions have so far taken place, each one taking about 9 seconds. Nine
seconds to delete a single row based on the one column that comprises the
table's PK?!? At that rate, all 2M rows would take *months* to complete.
Panicking, I started gathering information when the DELETEs finished.
Here's what EM says about the SQL execution stats (hopefully readable):
Total Per Execution Per Row
Executions 306 1 <0.01
Elapsed Time (sec) 2,112.56 6.90 <0.01
CPU Time (sec) 611.06 2.00 <0.01
Buffer Gets 174,704,209 570,928.79 87.35
Disk Reads 2,501,795 8,175.80 1.25
Direct Writes 0 0.00 0.00
Rows 2,000,000 6,535.95 1
306 total executions, each deleting about 6536 rows. Do the math, yup,
that's 2M rows. I verified with an AWR report that the DELETE statement
was indeed executed 306 times. (and the time/exec dropped from 9s to under
7s, as shown)
My question: How can a DELETE using the sole column of a PK remove 2M
rows from a table by being executed only 306 times? I verified that the
DELETE explain and execution plans both stated they were using the PK's
index with INDEX UNIQUE SCAN. Am I misunderstanding what an "execution" is
here?
Thanks,
Rich
