Good Day team ,
>>>>>>>>>I wont be able to post any OP from the DB as its restricted, apologies .<<<<<<<<<<
Background of the issue :- Was getting constant 4031 error , when checked buffer cache was occupying 90% of the SGA .
Tried to find what occupying the buffer and found files /block of UNDO tbs.
I queries v$bh with distinct file# >> this showed 98% of them are from UNDO .
So why buffer cache loading the undo blocks . Can i assume that
1- any changes happening to DB , oracle would take a pre - image by buffering the undo blocks?
OR
2- any query that requires consistent read is fetching the data from undo blocks -→ so that oracle is loading the pre -image loaded undo -blocks in buffer for the query to read ?
Or please advise otherwise if my understanding is wrong .
qq1 -Would like to know when all the buffer cache would load the undo blocks
qq2 -when it would release it ?
qq3 -Is there any way to find which query is reading UNDO data, loaded from buffer cache