This discussion is archived
4 Replies Latest reply: Jun 11, 2012 1:54 PM by 428027 RSS

_serial_direct_read- Loads to buffer cache?

765115 Newbie
Currently Being Moderated
I have a simple query like the below -
SELECT * FROM table1 WHERE id = 1001
I ran the query with serialdirect_read = true at the session level. Please find below statistics from autotrace.

Statistics
----------------------------------------------------------
+1 recursive calls+
+0 db block gets+
+4 consistent gets+
<font color="red"> +1 physical reads+</font>

+52 redo size+
+1566 bytes sent via SQL*Net to client+
+337 bytes received via SQL*Net from client+
+2 SQL*Net roundtrips to/from client+
+0 sorts (memory)+
+0 sorts (disk)+
+1 rows processed+

I ran the same query again (with serialdirect_read still set to true) in the same session and got the below statistics.

Statistics
----------------------------------------------------------
+0 recursive calls+
+0 db block gets+
+4 consistent gets+
<font color="red"> +0 physical reads+</font>

+0 redo size+
+1566 bytes sent via SQL*Net to client+
+337 bytes received via SQL*Net from client+
+2 SQL*Net roundtrips to/from client+
+0 sorts (memory)+
+0 sorts (disk)+
+1 rows processed+

I was exepecting that during the 2nd execution there would still be physical reads going to the disk (because direct read does not cache to the buffer). However as you can see, the query has used the buffer cache (populated from the 1st execution).

Does serialdirect_read=true, load blocks (read from disk) into the buffer cache as well?

Edited by: museshad on Jun 8, 2012 3:21 PM

Edited by: museshad on Jun 8, 2012 3:22 PM

Edited by: museshad on Jun 8, 2012 3:23 PM
  • 1. Re: _serial_direct_read- Loads to buffer cache?
    Dom Brooks Guru
    Currently Being Moderated
    The autotrace statistics do not prove that there was a direct path read in the first execution.
    All they prove is a physical read.
    Do a sql trace with waits / 10046 trace to check.
    alter session set events 'sql_trace wait=true';
    serialdirect_read does not seem to be part of the optimizer environment settings.

    See:
    http://oraganism.wordpress.com/2011/06/27/fun-with-serialdirect_read/
  • 2. Re: _serial_direct_read- Loads to buffer cache?
    Marc Fielding Journeyer
    Currently Being Moderated
    The SQL*Plus autotrace command displays a small set of the available session statistics, and, as Dom mentions, this small selection can't be used to directly confirm if direct path reads are being used. I believe the statistic you want is "physical reads direct". (Looking at wait events in a 10046 trace can also confirm the presence of direct path reads)

    That having been said, the purpose of serialdirect_read is to adjust Oracle's preference to do multiblock reads via direct path rather than through the buffer cache. Note the key word "multiblock"; your query's 4 logical I/O's look suspiciously like a series of single-block I/Os in an index and table lookup. There's precious little to be gained by using direct path for single block reads.

    For a much more detailed look at these concepts, I'd recommend Frits Hoogland's excellent presentation "about multiblock reads" which describes the logic in a much more detailed fashion: http://www.ukoug.org/what-we-offer/library/about-multiblock-reads/about-multiblock-reads.pdf

    Marc
  • 3. Re: _serial_direct_read- Loads to buffer cache?
    Mohamed Houri Pro
    Currently Being Moderated
     was exepecting that during the 2nd execution there would still be physical reads going to the disk (because direct read does not cache to the buffer). 
    Although direct path read is not an optimizer decision it ,nevertheless, depends in some way on the decision made by the optimizer on the choice this one will made to read your segment.

    1. FULL TABLE SCAN
    2. INDEX FAST FULL SCAN

    So, I don't think that you will be direct path reading if your query is not using one of the above FULL segment operation. Because a FULL TABLE SCAN can be done either via

    1. DIRECT PATH READ or
    2. DB FILE SCATTERED READ

    The decision to use one of the above read pathes depends on the information Oracle will find on the segment header block. This is why a DB FILE SEQUENTIAL read is first made(if not yet in the cache) against the segment header block to have information about the number of blocks to be read and the number of blocks below the high water mark. Based on this information and based on the fact that the CBO has decided to FULL scan the TABLE or the INDEX, direct path read (or db file scattered read) will be done.

    Bottom line (in my opinion) is that direct path read is related to FULL TABLE SCAN and INDEX FAST FULL SCAN. Ensure first that your are using one of these operations before questioning why direct path read has not been used

    Hope this helps

    Mohamed Houri

    Edited by: Mohamed Houri on 11-juin-2012 5:08
  • 4. Re: _serial_direct_read- Loads to buffer cache?
    428027 Explorer
    Currently Being Moderated
    I agree with Mohamed,
    And if you really want to see the offload in action, remove the predicate and perhaps use some aggregate function.
    It will do the trick.

    Regards,
    Helio Dias
    http://heliodias.wordpress.com

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points