Re: EM/AWR counting of SQL executions

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_at_gmail.com> 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
>

--
http://www.freelists.org/webpage/oracle-l