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

As Sayan and Jonathan said, this will be a forall delete. FYI if the SQL is still in v$sql then you can use program_id to identify the plsql object (it’s a dba_object object_id). There’s also a program_line# column which will be the correct line number in the code after you’ve unwrapped it. If multiple procedures have the same code then this will likely only give you the one that most recently hard parsed it.

If it’s not in v$sql, then v$active_session_history and dba_hist_active_sess_history both have 4 plsql% columns which will identify the package and subprogram (use dba_procedures for that) to give you the block of code that called this SQL which should limit the amount of SQL you need to read through.

Thanks,
Andy

On Fri, 24 Dec 2021 at 10:16, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> 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