Re: Query ASH for Undo Blocks

What's the scale of the problem? i.e. how long does the query run, how complex is the query/plans, how much of the time relates to the index range scan, how many buffer gets does that index range scan step take, how many do you think it should take. How frequently doe the query run, how repeatable is the experiment?

How are you working out that that index range scan is doing more buffer gets than you expect - have you enabled SQL Monitor for the SQL_Id?

Regards
Jonathan Lewis

On Mon, 13 Jun 2022 at 11:35, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> Hi,
>
> there is a plan with an Index Range scan that generates way more
> buffer_gets than you would expect.
> Would it be correct to query active session history using CURRENT_FILE#
> and CURRENT_BLOCK# to check against DBA_EXTENTS
> to find out if some buffer gets are coming from undo?
> The query does work, but i am not sure if the result is showing the
> indented answer.
>
> Thanks
>
> Lothar
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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