RAPID DIAGNOSIS OF PERFORMANCE ISSUES USING ASH ORACLE
If there is a complaint from the user about a potential performance issue, then read this article.
Identify the performance hit start time and end time first with the victim (application developer or user)
Three things to focus on,
- Performance down started when? – 10-09-21 03:21:23
- Performance down ended when? 10-09-21 03:29:30
- Active session count
Our main goal is to identify the SQL statement which causes slowness on the database
- Identify the sample id with the help of time
kish@exdbx<>select min(sample_id),max(sample_id)
from v$active_session_history
where to_char(sample_time,'dd-mm-yy hh24:mi:ss') between
'10-09-21 03:20:00'
and
'10-09-21 03:30:00';
MIN(SAMPLE_ID) MAX(SAMPLE_ID)
-------------------------------------------------- --------------------------------------------------
1399711 1400300
Check the wait class with highest waits from ASH samples with the help of sample id’s collected in previous step
kish@exdbx<>select *
from
(
select session_id,wait_class_id,wait_class,count(*) TC
from v$active_session_history
where sample_id between 1399711 and 1400300
group by session_id,wait_class_id,wait_class
order by 4 desc
)
where rownum < 6; 2 3 4 5 6 7 8 9 10
SESSION_ID WAIT_CLASS_ID WAIT_CLASS TC
---------- ------------- --------------- --------------------------------------------------
72 1740759767 User I/O 297
74 1740759767 User I/O 252
12 4108307767 System I/O 114
36 1740759767 User I/O 72
19 4108307767 System I/O 72
We now know that USER I/O has significant waits
Map the wait_class_id of previous step to narrow down wait event
We observe significant cell wait events from the below results
kish@exdbx<>select event_id,event,count(*) TC
from v$active_session_history
where sample_id between 1399711 and 1400300
and wait_class_id=1740759767
group by event_id,event
order by 3 desc; 2 3 4 5 6
EVENT_ID EVENT TC
----------- ---------- --------------------------------------------------
2614864156 cell singl 566
e block ph
ysical rea
d
443865681 cell multi 45
block phys
ical read
3975960017 cell list 10
of blocks
physical r
ead
Map the event_id to get the sql_id of the statement to find the bottleneck. As you can see there are multiple waits for particular sql_id ‘cvx718m1y52mm‘ which is not normal
kish@exdbx<>select *
from
(
select sql_id,count(*) TC
from
v$active_session_history
where sample_id between 1399711 and 1400300
and
event_id=2614864156
group by sql_id
having count(*) > 1
order by 2 desc
)
where rownum < 6; 2 3 4 5 6 7 8 9 10 11 12 13 14
SQL_ID TC
------------- --------------------------------------------------
cvx718m1y52mm 324
acc988uzvjmmt 95
bzscyq07w79ab 32
dajx1dddzy3a1 17
96w2cbx7ntmtv 12
Check if there were blocking sessions for the query
kish@exdbx<>select BLOCKING_SESSION_STATUS,BLOCKING_SESSION
2 from v$active_session_history
3 where sql_id='cvx718m1y52mm'
4 and BLOCKING_SESSION is not null;
BLOCKING_SESSIO BLOCKING_SESSION
--------------- ----------------
VALID 20
VALID 20
VALID 20
VALID 20
VALID 20
VALID 20
VALID 20
VALID 20
VALID 20
VALID 20
VALID 20
VALID 20
12 rows selected.
Isolate the SQL statement which caused the slowness and bring the database down to its knees! An update statement is the reason behind the performance issue which caused blockings and should be tuned for better performance
kish@exdbx<>select sql_text from V_$SQLTEXT_WITH_NEWLINES where sql_id='cvx718m1y52mm';
SQL_TEXT
---------------
d(ORDER_ID,3)=1
update SALES_TA
B_COPY nologgin
g set NULL_COLU
MN = 'ABC' wher
e mo