Not sure why , but the first email I dropped on this question was bounced
back from oracle-l, so trying again.
Hello experts,
We have a database with Oracle version 11.2.0.4. We are getting complaints
of slowness in one of our jobs mainly for ~15minutes duration on a daily
basis. And there is no plan change or any Undo reads for the underlying
sqls and also there is not one sql which we can singled out for that job
duration and that job consists of many small queries(selects, inserts
running many thousand times in that duration). But while seeing/comparing
the AWR for that ~15minutes period VS another ~15minutes just before that,
we are seeing a few odd waits , they are a combination of
Configuration(free buffer waits) followed by concurrency(buffer busy, index
contention etc) foreground waits. We do see comparatively higher sessions
during this ~15minutes window as compared to normal time.
In the AWR under section "IOStat by Function summary" the DB writer avg
response time is logged as ~103 ms vs in normal period it stays ~35ms. And
then checking v$iostat_file , we see it has ASYNCH_IO set as OFF for both
"data file" and "temp file" which I have seen in many databases normally
set to ON. Also in dba_hist_active_sess_history the top waits events are
showing as below. I have attached excel with specific sections of AWR
during the issue period and normal period in two different tabs.
1)So , is it correct that by turning this ASYNCH_IO "ON" for data/temp
file , will cater all these issues because these all seem to be triggering
from the slow DB writer performance?
Or
2)Should we ask the application team to reduce the total number of
sessions(maybe by decreasing the max connection limit) which are submitting
to the Database at that point to reduce contention?
*Below is count of waits from Dba_hist_active_sess_history for that
~15minutes window:-*
Top two(log file sync and db file async I/O submit) are not associated with
application user/session.
EVENT COUNT(*)
log file sync 260
db file async I/O submit 188
free buffer waits 72
66
65
63
62
db file sequential read 56
write complete waits 54
54
db file sequential read 51
Regards
Pap
Attachment:
AWR_Issue_Vs_Normal_Period.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
