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