_direct_read_decision_statistcs_driven, _small_table_threshold and direct path reads on partitioned tables in 11.2.0.3 (Part 2)

This is continuation of my last post regarding direct path reads on partitioned tables in Oracle 11.2.0.3.

To recap, the behavior I observed is that direct path reads will be performed if number of blocks for all partitions that will be accessed exceeds _small_table_threshold value. That is if a table is consisted of 10 partitions each having 100 blocks and if a query goes after two of the partitions, direct path reads will be performed if _small_table_threshold is lower than 200.

Also regardless of how much data has been cached(in the buffer cache)  for each of the partitions, if direct path reads are to be performed, all partition segments will be directly scanned. So, it is all or nothing situation.

I also indicated that _direct_read_decision_statistics_driven parameter was set to TRUE (default) for the tests done in my earlier post.

What is _direct_read_decision_statistics_driven anyway? According to the parameter description, it enables direct path read decision to be based on optimizer statistics. If the parameter is set to FALSE Oracle will use segment headers to determine how many blocks the segment has. (read Tanel Poder’s blogpost for more information)

Let’s see how queries that access table partitions (full scan) behave if _direct_read_decsision_statiscs_driven parameter is set to FALSE in 11.2.0.3. My expectation was that it should be the same as if it was set to TRUE. I thought that once Oracle gets information about the number of blocks in each of the partitions it would use the same calculation as if the parameter was set to TRUE. Let’s see.

But, before moving forward a small disclaimer: Do not perform these tests in production or any other important environment. Changing of undocumented parameters should be done under the guidance of Oracle Support. The information presented here is for demonstration purposes only.

I will use the same table, TEST_PART, that I used in my earlier post.

I started with flushing the buffer cache (to make sure none of the partitions has blocks in the cache).

I set _direct_read_decision_statistcs_driven parameter to false and ran a query that selects data from PART_1 partition only. Each of the partitions contains 4000 rows stored in 65 blocks, plus one segment header block.

_small_table_threshold in my sandbox environment was set to 117.

SQL>  alter session set "_direct_read_decision_statistics_driven"=FALSE;

Session altered.

SQL> SELECT count(1) FROM test_part WHERE col1 in (1);

  COUNT(1)
----------
      4000

As expected, no direct path reads were performed (I used my sese.sql script that scans v$sesstat for statistics that match given keyword)

SQL> @sese direct

no rows selected

Now let’s see what happens with a query that accesses the first two partitions. Remember if _direct_read_decision_statistcs_driven parameter is set to TRUE, this query would perform direct path reads because the number of blocks in both partitions, 130 (2x65) exceeds
_small_table_threshold(117) parameter.

SQL> select count(1) from test_part where col1 in (1,2);

  COUNT(1)
----------
      8000

SQL> @sese direct

no rows selected

No direct reads. Definitely different compared to when _direct_read_decision_statistcs_driven was set to TRUE.

How about for a query that accesses three partitions:

 SQL> select count(1) from test_part where col1 in (1,2,3);

  COUNT(1)
----------
     12000

SQL> @sese direct

no rows selected

Still no direct path reads.

How about if we access all 7 partitions:

SQL>  select count(1) from test_part where col1 in (1,2,3,4,5,6,7);

  COUNT(1)
----------
     28000

SQL> @sese direct

no rows selected

No direct path reads.

So what is going on? Seems when _direct_read_decision_statistcs_driven is set to FALSE, Oracle makes decision on partition by partition basis. If the number of blocks in the partition is less or equal than _small_table_threshold buffer cache will be used, otherwise direct path reads.

What if some of the partitions were already cached in the buffer cache?

In the next test I’ll:

• Flush the buffer cache again
• Set _direct_read_decision_statistcs_driven is set to FALSE
• Run a query that accesses the first two partitions
• Decrease the value for _small_table_threshold to 60
• Run a query that accesses the first three partitions.
• Check if direct path reads were performed and how many

With this test I’d like to see if Oracle will utilize the buffer cache if the segment data is cached and the number of blocks in partition is greater than _small_table_threshold.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter session set "_direct_read_decision_statistics_driven"=FALSE;

Session altered.

SQL> select count(1) from test_part where col1 in (1,2);

  COUNT(1)
----------
      8000

SQL> @sese direct

no rows selected

At this point, PART_1 and PART_2 partitions should be entirely in the buffer cache. If you want, you could query X$KCBOQH to confirm this (from a different session logged in as SYS).

SQL> conn /as sysdba
Connected.
SQL> select  o.subobject_name, b.obj#, sum(b.num_buf)
2 from    X$KCBOQH b, dba_objects o
3 where   b.obj#=o.data_object_id
4     and o.object_name='TEST_PART'
5 group by  o.subobject_name, b.obj#
6 order by 1;  

SUBOBJECT_NAME                       OBJ# SUM(B.NUM_BUF)
------------------------------ ---------- --------------
PART_1                             146024             66
PART_2                             146025             66

As expected, both partitions are in the buffer cache.

Now let’s change decrease _small_table_threshold to 60 and run a query that scans the first three partitions:

SQL>  alter session set "_small_table_threshold"=60;

Session altered.

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> select count(1) from test_part where col1 in (1,2,3);

  COUNT(1)
----------
     12000

alter session set events '10046 trace name context off';

SQL> @sese direct

       SID         ID NAME                                                    VALUE
---------- ---------- -------------------------------------------------- ----------
         9         76 STAT.consistent gets direct                                65
         9         81 STAT.physical reads direct                                 65
         9        380 STAT.table scans (direct read)                              1

Here they are, 65 direct path reads, one table scan (direct read) which means one of the partitions was scanned using direct path reads. Which one? Yes, you are right, the one that is not in the buffer cache (PART_3 in this example).

If you query X$KCBOQH again you can see that only one block of PART_3 is in the cache. That is the segment header block.

SQL> conn /as sysdba
Connected.
SQL>  select  o.subobject_name, b.obj#, sum(b.num_buf)
2 from    X$KCBOQH b, dba_objects o
3 where   b.obj#=o.data_object_id
4     and o.object_name='TEST_PART'
5 group by  o.subobject_name, b.obj#
6 order by 1;  

SUBOBJECT_NAME                       OBJ# SUM(B.NUM_BUF)
------------------------------ ---------- --------------
PART_1                             146024             66
PART_2                             146025             66
PART_3                             146026              1 <===

This means that when _direct_read_decision_statistcs_driven is set to FALSE, in 11.2.0.3, Oracle uses totally different calculation compared to the one used when the parameter is set to TRUE (see in my earlier post).

Moreover, seems Oracle examines each of the partitions separately (which I initially expected to be a case even when _direct_read_decision_statistcs_driven is set to TRUE ) and applies the rules as described in Alex Fatkulin’s blogpost. That is, if any of the following is true, oracle will scan the data in the buffer cache, otherwise direct path reads will be performed: 

•  the number of blocks in the segment is lower or equal than _small_table_threshold 
•  at least 50% of the segment data blocks are in the buffer cache
•  at least 25% of the data blocks are dirty 

The conclusion so far is that in 11.2.0.3, you may observe different behavior for the queries that access table partitions using FTS if you decide to change _direct_read_decision_statistcs_driven parameter.

I will stop here. I ran the same tests against 11.2.0.4 and 12.1.0.1 and noticed some differences in the behavior compared to the one I just wrote about (11.2.0.3). I will post these results in the next few days.

Stay tuned...