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@xxxxxxxxx]
*Sent:* Thursday, March 8, 2018 11:45 AM
*To:* Hameed, Amir <Amir.Hameed@xxxxxxxxx>; Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
*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
