Re: query with " rowid = :B1 " condition seems to work on many rows

I Agree with Sayan, the most likely cause is ‘forall’:

https://jonathanlewis.wordpress.com/2017/03/21/deception/

Regards
Jonathan Lewis
(From my iPad mini; please excuse typos and auto-correct)

> On 24 Dec 2021, at 10:11, Andrea Monti <ilsuonogiallo_at_gmail.com> wrote:
>
> Hi all,
>
> while reading some awr reports, I noticed some weird numbers for a specific query.
>
> The query is a really simple
> DELETE from MY_SYNONYM WHERE ROWID = :B1
>
> According to awr, this sql has
> ~ 1000 rows per execution
> ~ 6000 gets per execution
> ~ 113 reads per execution
> ~ 0.13 millisec per execution
>
> The weirdest number is the first one: MY_SYNONYM translates to a single table with
>
> ~ 190 partitions
> no subpartitions
> no FK (i.e. no "on delete cascade")
> no trigger
> no lob column
> 18 columns
> 1 index (the PK using 5 columns)
> avg_row_len is 74 for each partition
>
> thus I would say that "ROWID = :B1" should identify a single row and that 6 gets per execution and nanosec-like execution time to delete a single row would be fair; given this I do not know how a "delete where rowid = " can work on multiple rows and take so long.
>
> From ASH I see that this delete is run from some PLSQL wrapped code and since the application has dozens of nested wrapped PLSQL packages I am not able to find more information about the code at the moment (moreover, this sql is only executed on the last night of each month, and I do not think I'll be either willing or able to check it line on the new year eve).
>
> So, my question is: what could possibly make AWR think that a "DELETE TABLE WHERE ROWID = :BIND" can use ~1000 rows per exec in this so basic scenario?
>
> Thanks a lot, and merry christmas!
>
> Andrea M
>
>

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