RE: "db file sequential read" latency during reads versus update activity
Date: Thu, 8 Mar 2018 19:44:29 +0000
Message-ID: <DM5PR1101MB23471C471AA1BA250C6EE0E7F4DF0_at_DM5PR1101MB2347.namprd11.prod.outlook.com>
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,
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:
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
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.
Amir
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 08 2018 - 20:44:29 CET