We have night batch process slowly taking time and when we check the AWR report we see most of the time DB File Sequential read and when checking the query its going for index scan that directs me to check the I/O performance of the system.
I am glancing how i went throught AWR.
check the Avg wait(ms) for "db file sequential read" in Top 5 Timed Foreground Events" session and there Avg wait is 27,Then further go through "Wait Event Histogram"
In below "Wait Event Histogram" you can see the % of waits are more b/w 32ms and 1s buckets for above top wait events and further walk through the AWR on tablespace I/O.
You can see the Av Rd(ms) and Av Buf Wt(ms) is more.
From this it look we have I/O issue making slowness of the database.
and when we check the OS level for the disk it gives 100% disk busy.
Further to fix the issue we have to check the for datafile I/O session in AWR and if possible move the datafile to another disk where there is low i/o.
As well as we have to tune the top I/O related queries.
Consider increasing DB buffer cache.
Reference: How to Tell if the I/O of the Database is Slow (Doc ID 1275596.1)
Please go thought the metalink note for more details.
No comments:
Post a Comment