Re: Select Fetching from UNDO

Hi Jonathan,

Thanks for the sharing .

Surely i will use this to investigate for further .

Regards,
Krishna

On Wed, 6 Sept, 2023, 16:03 Jonathan Lewis, <jlewisoracle_at_gmail.com> wrote:

> You need to run a query that does SOMETHING LIKE:
>
> select
> sql_id,
> sql_plan_Line_id,
> sql_plan_operation,
> sql_plan_options,
> event,
> count(*)
> from
> dba_hist_active_sess_history
> where
> ... the DBID, Instance, sample_time, sid and serial match the information
> you've been given
> group by
> sql_id, sql_plan_line_id, sql_plan_operation, sql_plan_options,
> event
> order by
> sql_id, sql_plan_line_id
> /
>
> (Formatting is left up to you)
>
> This will indicate which SQL_IDs are responsible for most of the "gc cr
> disk read" and "db file sequential read" time, and which operations in
> their execution plans are most significant.
> Any statements that are "expensive" will have had their whole plan
> captured in the AWR, so you should be able to make sense of the operations
> that you find from this query.
> You could drill in a little further on the interesting SQL_IDs by
> reporting only those by adding the current_obj# to the list of column in
> the select and group by.
>
> You may find that you can't get anything sensible from this type of query,
> though - unless the query takes a lot of time the same count will be too
> small to be statistically significant.
>
> Regards
> Jonathan Lewis
>
>
>
>
>
> On Wed, 6 Sept 2023 at 10:45, Krishnaprasad Yadav <chrishna0007_at_gmail.com>
> wrote:
>
>> 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