query with " rowid = :B1 " condition seems to work on many rows
Date: Fri, 24 Dec 2021 11:11:28 +0100
Message-ID: <CAAQVbZaLPix5xyEQW2LZ2uaCdk890j5gf8_yG-xRPN+7PgrCZA_at_mail.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
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-lReceived on Fri Dec 24 2021 - 11:11:28 CET