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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s