Re: EM/AWR counting of SQL executions

That perfectly explains it. I've used collections in PL/SQL before, but had not noted how that looked from this perspective. The performance increase makes additional sense now.

Thanks Dominic and Jonathan!
Rich

On Thu, Nov 18, 2021 at 3:17 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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