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

Even though you've reduced the buffer cache, it has not been eliminated, so most of the I/O to the UNDO is likely to be cached anyway.  Perhaps reducing it further still -- from 128M to 4M -- might be worthwhile?  Obviously, it will change the outcome of the SLOB benchmark, but it might force more cache misses?  For a test like this, you're no longer looking for timing results, but looking for I/O behavior.

Also, I wonder if querying X$BH would help here?  I am getting out of my comfort zone as I've done very little Oracle troubleshooting for almost 4 years now, and I don't have a database at the moment to test on...

On 3/8/18 12:44, Hameed, Amir wrote:
>
> Thanks Tim. I traced one of the sessions and from the raw trace file,
> I checked the *file#* value and found entries of the undo data files.
> I did a bit of a math and it showed that ~ 0.19% (417 out of 223216)
> of IOs were done against the UNDO data files. So, the activity against
> UNDO does not look that much unless I am misinterpreting it. I believe
> the way SLOB works is that it inserts one row in each data block. The
> /average count/ of the *commit cleanouts* activity from the AWR report
> was ~ 69k. So, how do we correlate these many *commit cleanouts* to a
> tiny fraction of IOs against the UNDO?
>
> Thanks,
>
> Amir
>
> *From:*Tim Gorman [mailto:tim.evdbt_at_gmail.com]
> *Sent:* Thursday, March 8, 2018 11:45 AM
> *To:* Hameed, Amir <Amir.Hameed_at_xerox.com>; Oracle-L Freelists
> <oracle-l_at_freelists.org>
> *Subject:* 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