As of Oracle Database 11g, serial full table scans can use direct reads (see Metalink note 793845.1). This is basically done for improving the performance of large full table scans. In my opinion, the most important pros and cons of this feature are the following…

Pros (which are related to the fact that the buffer cache is not used):

  • Reduced latch utilization
  • The size of the physical I/Os does not depend on the blocks that are in the buffer cache

Cons:

  • A segment checkpoint must precede the first direct read
  • Repeated delayed block cleanouts

In this post, as the title suggests, I would like to focus on the last one. The reason is simple. I cannot remember having already read something about that topic.

Let’s start by showing you an example where delayed block cleanouts are performed…

  • Create a test table and insert some data into it (note that the table uses 10000 database blocks):
SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(500)) TABLESPACE test_8k;

SQL> INSERT INTO t
  2  SELECT rownum, rpad('*',500,'*')
  3  FROM dual
  4  CONNECT BY level <= 140000;

SQL> COMMIT;
  • Generate some blocks that require delayed block cleanout:
SQL> UPDATE t SET id=id*10;

SQL> COMMIT;
  • Flush the buffer cache (this is necessary because many blocks are in the buffer cache and, therefore, direct reads would not be used for reading the test table):
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
  • Execute two queries on the test table and store the session statistics in a helper table:
SQL> CREATE TABLE s AS
  2  SELECT 0 AS run, name, value
  3  FROM v$statname NATURAL JOIN v$mystat;

SQL> SELECT count(*) FROM t;

  COUNT(*)
----------
    140000

SQL> INSERT INTO s
  2  SELECT 1, name, value
  3  FROM v$statname NATURAL JOIN v$mystat;

SQL> SELECT count(*) FROM t;

  COUNT(*)
----------
    140000

SQL> INSERT INTO s
  2  SELECT 2, name, value
  3  FROM v$statname NATURAL JOIN v$mystat;
  • Show some statistics for the two executions:
SQL> SELECT *
  2  FROM (
  3    SELECT run, name, value-lag(value) OVER (ORDER BY name, run) AS value
  4    FROM s
  5    WHERE name IN ('cleanouts only - consistent read gets',
  6                   'session logical reads',
  7                   'physical reads',
  8                   'physical reads direct')
  9  )
 10  PIVOT (sum(value) FOR run IN (1 AS RUN_1, 2 AS RUN2_2))
 11  ORDER BY name;

NAME                                          RUN_1     RUN2_2
---------------------------------------- ---------- ----------
cleanouts only - consistent read gets          9466       9399
physical reads                                10112      10004
physical reads direct                         10003      10003
session logical reads                         19850      19538

As you can see from the execution statistics, both runs did almost the same amount of (direct) reads, logical reads and cleanouts. Especially the last is strange. In fact, usually, the cleanouts are performed only once for each block. In this case, however, the same cleanouts are performed for every execution of the query.

This is clearly due to the direct reads.

To confirm that, let’s rerun the test without using serial direct reads. For that purpose, event 10949 is enabled.

SQL> TRUNCATE TABLE s;

SQL> ALTER SESSION SET EVENTS '10949 TRACE NAME CONTEXT FOREVER';

SQL> INSERT INTO s
  2  SELECT 0 AS run, name, value
  3  FROM v$statname NATURAL JOIN v$mystat;

SQL> SELECT count(*) FROM t;

  COUNT(*)
----------
    140000

SQL> INSERT INTO s
  2  SELECT 1, name, value
  3  FROM v$statname NATURAL JOIN v$mystat;

SQL> SELECT count(*) FROM t;

  COUNT(*)
----------
    140000

SQL> INSERT INTO s
  2  SELECT 2, name, value
  3  FROM v$statname NATURAL JOIN v$mystat;

SQL> SELECT *
  2  FROM (
  3    SELECT run, name, value-lag(value) OVER (ORDER BY name, run) AS value
  4    FROM s
  5    WHERE name IN ('cleanouts only - consistent read gets',
  6                   'session logical reads',
  7                   'physical reads',
  8                   'physical reads direct')
  9  )
 10  PIVOT (sum(value) FOR run IN (1 AS RUN_3, 2 AS RUN2_4))
 11  ORDER BY name;

NAME                                          RUN_3     RUN2_4
---------------------------------------- ---------- ----------
cleanouts only - consistent read gets          9399          0
physical reads                                 9919          0
physical reads direct                             0          0
session logical reads                         19592      10228

As you can see from the statistics, the major difference between the second and the third run is the number of direct reads. The interesting thing is to notice how the third run finally did a “regular” cleanout and, therefore, the subsequent run, the fourth one, was able to directly read the (cleaned) blocks. Because of that the number of logical reads of the fourth run was much lower.