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