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

In the extended SQL trace data, you can see exactly which block (file ID and block ID) each read manipulates. You also can see the ID of the object being manipulated.

So, you can know exactly which blocks of which objects are being read. All of them.

Cary Millsap
Method R Corporation
Author of *Optimizing Oracle Performance <http://amzn.to/OM0q75>* and *The Method R Guide to Mastering Oracle Trace Data, 2nd edition <http://amzn.to/1U7q8X1>*

On Sun, Mar 11, 2018 at 11:10 AM, Tim Gorman <tim.evdbt_at_gmail.com> wrote:

> >> The "db file sequential read" is an index read.
>
> The event "db file sequential read" indicates a single-block random-access
> synchronous read operation on a datafile. Even during indexed scans, db
> file sequential reads alternate between index blocks, table blocks, and
> undo blocks, and during full table-scans they can start off the operation
> by reading segment headers, for example.
>
>
>
>
> On 3/10/18 20:01, Mladen Gogala wrote:
>
> Well, when you have updates, your index block may need to be rebuilt. The
> "db file sequential read" is an index read. If the block has been updated,
> not only does the block need to be rebuilt, the index block also needs to
> be rebuilt. It takes time, even if both are cached in memory. However, the
> precise answer to your question is not possible. For the precise answer,
> one would need to profile the running code and see exactly where the time
> is spent. Only Oracle developers can do that. You may try with strace, to
> see whether there is another IO that somehow creeps in.
>
> Regards
>
> On 03/08/2018 09:54 AM, 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
>
>
>
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
>
>

--
http://www.freelists.org/webpage/oracle-l