Re: "db file sequential read" latency during reads versus update activity
Date: Mon, 12 Mar 2018 22:39:07 +0000
Message-ID: <LO1P123MB0977402DCF836269F13A0C01A5D30_at_LO1P123MB0977.GBRP123.PROD.OUTLOOK.COM>
It was a rhetorical question to point you in the right direction - but since you've taken it literally:
a) I asked about volume and DURATION
b) figures about the test including updates are meaningless if the figures for read-only activity aren't available for comparison.
Don't forget, also, that if you're pushing the system out of its comfort zone the the variation in I/O times will become greater so you need to see if you can infer anything from the wait event histogram for I/O events.
As an extra detail in the problem of why read time changes don't forget that increased CPU usage can result in longer times spent in the run queue, which can result in longer times being reported as the db file sequential read time even when the actual disk response times haven't changed, so check for (a) CPU utilisation (b) changes in latch activity - particularly spinning (c) buffer busy waits & write complete waits, (d) db block changes and session logical I/O.
Based on the figures supplied - if you're doing 4,000+ write requests per second that's going to have an impact on read response times: it's a significant percentage of your read requests.
Regards
Jonathan Lewis
From: Hameed, Amir <Amir.Hameed_at_xerox.com> Sent: 12 March 2018 19:10:11
To: Jonathan Lewis; Oracle-L Freelists
Subject: RE: "db file sequential read" latency during reads versus update activity
Jonathan,
The IO profile section is shown below from the AWR report:
IO Profile Read+Write/Second Read/Second Write/Second
~~~~~~~~~~ ----------------- --------------- ---------------
Total Requests: 26,686.2 22,378.0 4,308.2
Database Requests: 26,079.6 21,907.7 4,172.0
Optimized Requests: 0.0 0.0 0.0
Redo Requests: 134.5 0.0 134.5
Total (MB): 232.2 191.4 40.8
Database (MB): 218.0 184.0 33.9
Optimized Total (MB): 0.0 0.0 0.0
Redo (MB): 6.8 0.0 6.8
Database (blocks): 27,901.2 23,557.0 4,344.2
Via Buffer Cache (blocks): 27,890.2 23,549.3 4,340.9
Direct (blocks): 11.0 7.7 3.4
Thanks
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Sunday, March 11, 2018 7:03 AM
To: Oracle-L Freelists <oracle-l_at_freelists.org>
Subject: Re: "db file sequential read" latency during reads versus update activity
If you're doing updates then you will be generating redo which has to be written, and modifying datablocks and undo blocks which eventually have to be written. How does the number and duration of write operations compare with the number of read operations - it's perfectly feasible that you're reads are slower because they're joining a queue with writes.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Hameed, Amir <Amir.Hameed_at_xerox.com<mailto:Amir.Hameed_at_xerox.com>> Sent: 08 March 2018 14:54
To: Oracle-L Freelists
Subject: "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.
Thanks,
Amir
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 12 2018 - 23:39:07 CET