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

Hi Andrea,

It might be a simple array delete, for example: `forall... delete...` in pl/sql.

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE
http://orasql.org

пт, 24 дек. 2021 г., 13:11 Andrea Monti <ilsuonogiallo_at_gmail.com>:

> 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