Amir,
This would be a great use-case to use either ASH or extended SQL
tracing, as insight into the datafiles being accessed might help answer
your question, and that is only available via tracing. Since you have
minimized the buffer cache, then there is every reason to believe that
some of those db-file-sequential-reads will go to the UNDO tablespace,
which would strongly indicate activity due to commit cleanouts. Either
ASH or a trace will display wait times and so it should be possible to
see if waits are increasing while heavy I/O on UNDO is happening.
Can you rerun and query V$ACTIVE_SESSION_HISTORY or enable tracing, and
focus on the P1 value (i.e. file# for I/O events)?
Obviously, SQL tracing provides more complete data, but at the cost of
perhaps polluting the results with more write I/O to filesystem-buffered
trace files, but it is nice to have alternatives. ASH provides
less-complete sampled data but is more easily obtained with less impact
on results, and tracing provides more-complete data but is less-easily
obtained with possible impact on results.
Life is good.
Hope this helps!
Thanks!
-Tim
On 3/8/18 07:54, Hameed, Amir wrote:
I am running SLOB to gauge latency of the storage array. When I configure my test for SELECT only run (no updates), I see average latency of *db file sequential read* consistently around 3ms. However, as I start to add UPDATE activity to the run, I see average latency of *db file sequential read* go up in the vicinity of 5ms. Is the cause of this increase in latency due to the *commit cleanouts* phenomena? The database version is 12.1.0.2. My DB_CACHE size is set to 128M to drive physical IOs and I am running my tests with 64 concurrent users.
Thanks,
Amir