Re: Select Fetching from UNDO
Date: Wed, 6 Sep 2023 11:32:08 +0100
Message-ID: <CAGtsp8nN0+kqqUZD820LWwy7ALX1ZkbJRd321nP6e8H==bqMOw_at_mail.gmail.com>
You need to run a query that does SOMETHING LIKE:
select
sql_id, sql_plan_line_id, sql_plan_operation, sql_plan_options,
event
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 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
On Wed, 6 Sept 2023 at 10:45, Krishnaprasad Yadav <chrishna0007_at_gmail.com>
wrote:
> Hi Jonathan,
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
order by
/
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.
Jonathan Lewis
>
> 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-lReceived on Wed Sep 06 2023 - 12:32:08 CEST