Re: EM/AWR counting of SQL executions

The numbers suggest it must be bulk dml with arrays of pkcolumn values bound per execution.

Sent from my iPhone

On 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