Re: Select Fetching from UNDO

A couple of details that might be relevant - this is clearly a RAC setup, so how many nodes, and is the session running on the a node which DOESN'T have undotbs2 as its undo tablespace?
It is perfectly normal for a query to end up spending resources accessing undo blocks for read-consistency purposes, and possibly undo header blocks to find upper bound commit SCNs. Things can get complicated (in both RAC and single-instance) if you've got long-running DML overlapping with queries, especially if you also have direct path reads going on.

I don't find the ASH reports from the AWR particularly useful, although I might use a reported event, or session, as a cue to drill into the dba_hist_active_sess_history for the interval. In your case, I'd query the interval for the session you've shown - which, if it's one hour will be at most 360 rows (one every 10 seconds) - to check the block IDs from the undo tablespace in case that gives any insight to what's going on, and to see how the undo reads are spread by execution plan operation and object.

As a first GUESS - if you're seeing a significant fraction of the time spent on this event then you may have a lot of activity on the other node making repeated changes to many blocks that you're trying to access in a read-consistent way on this node

Regards
Jonathan Lewis

On Tue, 5 Sept 2023 at 07:44, Krishnaprasad Yadav <chrishna0007_at_gmail.com> wrote:

> Dear Gurus,
>
> we see a performance issue and Application team provided session id ,
> we tried to figure out the detail and understand that session was executing
> one select statement , going further we see that most of the reads activity
> happening on UNDO tablespace for select statement , want to understand is
> fetching all records from UNDO is normal behavior or i should look for any
> rollback too or on which aspect such situation get raised .
>
>
>
> File ID % Activity Event % Event
> --------------- ---------- ------------------------------ ----------
>
> Tablespace
> -----------------------------------------------------
> -------------------------
> 4237 5.30 db file sequential read 3.31
> UNDOTBS2
>
> gc cr disk read 1.49
>
>
> 4238 4.97 db file sequential read 2.90
> UNDOTBS2
>
> gc cr disk read 1.74
>
>
> 4258 4.06 db file sequential read 2.24
> UNDOTBS2
>
> gc cr disk read 1.66
>
>
> 4239 3.73 db file sequential read 2.24
> UNDOTBS2
>
> gc cr disk read 1.16
>
>
> 4257 3.48 db file sequential read 2.40
> UNDOTBS2
>
>
> Regards,
> Krishna
>

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