Re: "db file sequential read" latency during reads versus update activity

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
>

--
http://www.freelists.org/webpage/oracle-l