Re: Select Fetching from UNDO

Hi Jonathan,

Thanks for your reply ,

Yes , the session was running from node 1 and fetching data from node 2 of UNDO .

Thank you for the lead , i will trying to get data and analyze further to understand about the undo block .

however i didnt understood this section "and to see how the undo reads are spread by execution plan operation and object."

can you share something how i can validated that undo reads are spread in execution operation .

Regards,
krishna

On Wed, 6 Sept 2023 at 14:12, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

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