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@xxxxxxxxx>
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
