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

  • From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
  • To: "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>, "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 12 Mar 2018 19:10:11 +0000

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jonathan Lewis
Sent: Sunday, March 11, 2018 7:03 AM
To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf 
of Hameed, Amir <Amir.Hameed@xxxxxxxxx<mailto:Amir.Hameed@xxxxxxxxx>>
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



Other related posts: