Thursday, July 05, 2012

Serial direct path reads in 11GR2 and Exadata environments

Serial direct path reads were first introduced in Oracle 11G which were noticed by many customers (in both positive and negative ways) who upgraded from the earlier releases. Back then I did a quick write up on the subject to try and tackle some of the key variables which affect the behavior.

To recap, the following were observed for 11G:
  • Serial direct path reads start at _small_table_threshold*5.
  • Serial direct path reads stop when 50% of the table blocks are cached.
  • Serial direct path reads stop when 25% of the table blocks are dirty.
Since then, many people noticed that 11GR2 seems to be more aggressive on the thresholds so I decided to re-run the tests and see what changed. I'm also going to run the same tests on the Exadata to see whether it changes anything compared to a normal 11GR2 database.

To refresh the memory, here is how the methodology works (all code is available in the 11G post).

Start threshold

The function grows a segment in a loop, performing a full table scan and using physical reads direct statistic as an indicator. Every iteration buffer cache is flushed to make sure cached blocks do not affect the numbers.

Stop cached threshold

A loop is used to cache more and more blocks from a table followed by a full table scan. Again, physical reads direct statistic is used to see when serial direct path reads no longer happen. Buffer cache is flushed at the beginning of the test.

Stop dirty threshold

Similar to the above but blocks are updated instead of just being cached.

Results

 Non-ExadataExadata
Start threshold_small_table_threshold_small_table_threshold
Stop cached50%50%
Stop dirty25%25%

Summary

11GR2 threshold is indeed more aggressive and starts at _small_table_threshold (i.e. five times lower compared to 11G). There seems to be no change to the rest of the numbers. Behavior appears to be the same on both Exadata and non-Exadata systems.

Bottom line is 11GR2 starts doing serial direct path reads earlier and stops doing these at the same time as 11G did.

All testing were done on 11.2.0.3

2 comments:

  1. Anonymous2:30 p.m.

    Hello,
    some additional info in here:
    http://progeeking.com/2014/02/25/the-big-q-direct-path-and-cell-offloading/

    ReplyDelete
  2. Anonymous4:50 a.m.

    Hi ALex great job.

    The fact that you found about 11gR2 is more aggressive and starts doing direct path read at _small_table_threshold vs _small_table_threshold *5 on 11gR1
    is now considered as a bug .
    See Bug 18498878 - SMALL TABLE DOES NOT CACHE PROPERLY AFTER UPGRADE TO 12.1

    The bug is present on 11.2.0.2, 11.2.0.3, 11.2.0.4 and 12.1

    The bug do not affect 10gR2, 11.2.0.1 and 12.2

    Regards,
    Chris

    ReplyDelete