RAC, 12c and Direct Path Reads

Yes, direct path reads again 🙂
No worries I’m already a bit bored from digging in this algorithm, so I think this is going to be my last post about it. Till they change it again of course. And yes, in 12.1.0.2 they did.

For introduction to DPR, you can check my previous post, because in here I don’t think to cover the basics.

What’s new? Basically in 12.1.0.2 now we have just a single threshold named small table threshold – STT /2% from buffer cache/, nothing else. If the table is below STT we have cached read, if the table is above STT we have direct read. That’s it, simple as that. And the reason is that we have a brand new algorithm inside the old one, which makes the situation even more complex than before.

The name of the new shiny /or stinky, will see/ algorithm is “Automatic Big Table Caching” /ABTC/. The good news is that we have some documentation about it.
Basically the idea of ABTC is to separate a portion from the buffer cache which will be used for caching tables bigger than the STT. The algorithm looks interesting and I think to write a whole post about it. In short, ABTC makes decision based on the object “temperature” or how often the object is accessed. If it’s hot it will try to cache it, if it’s cold, probably it wont.
Ok, great but by default DB_BIG_TABLE_CACHE_PERCENT_TARGET=0 or simply ABTC it’s disabled . So if we have a full scan on object bigger than STT it will be never cached. In such case we just have to set some target to the ABTC and the issue will be resolved. Sounds good, but the documentation says something, which I find interesting: “In Oracle RAC environments, automatic big table caching is only supported in parallel queries“. Heh, seriously?
That means no caching for serial queries in RAC which are performing full scan on object bigger than STT. And believe me, that’s the situation.

For example, on RAC instance I have a STT of 375 blocks and table with size of 2088 blocks, which is fully cached:

--check ABTC 
SQL> show parameter big_table

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_big_table_cache_percent_target    string      50
SQL>

--trace 
SQL> alter session set events 'trace [NSMTIO] disk highest, memory highest';

Session altered.

--perform FS
SQL> select count(*) from sdpx;

  COUNT(*)
----------
      2048

SQL>

--trace output:
NSMTIO: kcbism: islarge 1 next 0 nblks 2088 type 2, bpid 3, kcbisdbfc 0 kcbnhl 2048 kcbstt 375 keep_nb 0 kcbnbh 13601 kcbnwp 1
NSMTIO: kcbimd: nblks 2088 kcbstt 375 kcbpnb 1876 kcbisdbfc 3 is_medium 0
NSMTIO: kcbivlo: nblks 2088 vlot 500 pnb 18760 kcbisdbfc 0 is_large 0
NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
NSMTIO: kcbdpc:DirectRead: tsn: 6, objd: 92515, objn: 92495
ckpt: 1, nblks: 2088, ntcache: 2049, ntdist:39
Direct Path for pdb 0 tsn 6  objd 92515 objn 92495
Direct Path 1 ckpt 1, nblks 2088 ntcache 2049 ntdist 39
Direct Path mndb 0 tdiob 40 txiob 3 tciob 17066
Direct path diomrc 39 dios 2 kcbisdbfc 0
NSMTIO: Additional Info: VLOT=93800
Object# = 92515, Object_Size = 2088 blocks
SqlId = gb1uqwc73sutd, plan_hash_value = 4229476807, Partition# = 0

And from line 28 we can see that we have direct path read, while the table is in memory…

Lets try with parallel /in memory parallel enabled and force local/:

--perform FS
SQL> select /*+ parallel */ count(*) from sdpx;

  COUNT(*)
----------
      2048

SQL>

--trace
NSMTIO: kcbism: islarge 1 next 0 nblks 2088 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 2048 kcbstt 375 keep_nb 0 kcbnbh 13601 kcbnwp 1
NSMTIO: NSMTIO:kkfdtsc:DirectRead:
Additional Information: Cache_Attribute_Set:No, SCN_Ascending_Scan: No, Event_10354_Set: No, SqlID: g0yapz79mms9k, Id: 0
NSMTIO: kcbism: islarge 1 next 0 nblks 2048 type 2, bpid 65535, kcbisdbfc 0 kcbnhl 2048 kcbstt 375 keep_nb 0 kcbnbh 13601 kcbnwp 1
NSMTIO: kxfrCancelDR: objn:0x1694f flg:0x204601 size:2048 table:large cache:18760 affpct:80

And we can see that first “Direct Read” is reported and then we have “Cancel Direct Read”.

Lets prove it with statistics:

SQL> select value  from v$mystat where statistic#=(select statistic# from v$statname where name='physical reads direct');

     VALUE
----------
      4096

SQL> select /*+ parallel */ count(*) from sdpx;

  COUNT(*)
----------
      2048

re stselect value  from v$mystat where statistic#=(select statistic# from v$statname where name='physical reads direct');

     VALUE
----------
      4096

SQL>

Perfect, but for parallel executions only.

In single instance, the ABTC is working fine for serial executions, but what we shall do in RAC?
Hope they will fix this.

Kovachev

Advertisements

4 thoughts on “RAC, 12c and Direct Path Reads

  1. Interesting post, Nick.

    What happens if you set FORCE_FULL_DB_CACHING???

    (
    startup mount;
    alter database force full database caching;
    alter database open;
    )

      1. Try it if you have suitable environment. Would be nice to see the outcome. Currently we manage to reliably cache stuff in RAC only if we direct all teh reads and writes to one node (via DB service balancing).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s