Direct path read and ABTC thoughts & mysteries

geekin geekin geekin… it’s always good while is fun ( :;

But too much is not good from anything.

Lately I was having so much fun that I was hardly finding time to sleep, but now the balance is almost in place and I think this time to finish that post… or maybe it will be yet another rusty draft that wait it’s own movie style “to be continued…” Let see how enthusiastic am I this night ( :;

Goin goin, where is going and it’s back there again… as we say in Bulgaria /and probably not only in here/. So Direct Path Reads or DPR is the topic again. At the peak of my load I saw post from Frits Hoogland, describing exactly the opposite of what I say in my previous post about DPR, so I got extremely surprised. At that point I was so busy, that I didn’t had time to fix even my lab. u know another saying “the cobbler’s children have no shoes”, so the same but with oracle rdbms… anyway

Now, why did I got surprised, if we have to judge by Frits blog it’s obvious that he is not posting rubbish, so it was sure that the info is well checked before posting. But I knew how much time it took me to trace the DPR, how much time it took me to describe the way they works. I checked everything 10 times before concluding and posting… but..
But but but… sometimes when you go to the details, I mean really to the details you might forget the big picture πŸ˜‰

And this is what happen to me and Frits, in my opinion πŸ˜‰

So here is my post in which I’m describing the thresholds for DPR. The algorithm looks in that way: DPR starts at STT /small table threshold/; For HCC/OLTP compressed objects DPR stops at: 95% cached/dirty blocks; For non compressed objects DPR stops at: 50% cached/dirty blocks; Always DPR for objects above VLOT /very large object threshold/; If you are wondering what are those things you might read the post or not… /keep reading/
So this is perfectly accurate. Yep, it is. Strangely Frists post is perfectly accurate as well. But, he is testing not only on recent versions, he is testing older versions as well. And in this was the answer for everything.

Now in my post I mention that after 11.2.0.2 the algorithm starts using stats to take DPR decision and Frits also says that the things get different from 11.2.0.2. So the only difference between those two posts is that I didn’t use statistics for my objects while Frits was using… Yep that’s right, I was trying to simplify the things. And I did πŸ˜€
So the algorithm acts differently with and without statistics… the mystery is solved. Now if you have the time to follow the comments you will notice that I was wondering why we have different results, a question without answer till now. Also I was explaining that DPR algorithm is “deprecated/replaced/improved/or say it as you wish”. And yes, it’s kind of replaced by the ABTC. That’s why it will be almost pointless to go in details with DPR. It wont matter at all.

You don’t need to be internals geek or genius to know this. You just need to open the 12c NF course and you will find the information in there. The ABTC is replacing the DPR algorithm. Frits asked me to give some details about ABTC and actually if you check my post about 12c DPR and RAC you will notice that I’m willing to do it from a long time ago. Let see when I’ll become enthusiastic enough and write something.
For now in here I’ll just explain it in the most basic way: we specify the percentage of buffer cache to be used for big table caching and then oracle is trying to fit our big&hot tables in that memory area. The question is how big and hot πŸ˜‰
Strangely in my favorite Exadata book I notice that the ABTC is described as “A DW_SCAN is related to the Automatic Big Table Caching (ABTC), which has nothing to do with offloading queries to the storage servers.”.
Well, when enabled, ABTC have a lot in common… After all it will decide what to cache and what to read directly from the disk based on the ABTC cahce size, object heat and size, right? Anyway, maybe I’m too much in the details again and need to look at the big picture.

I don’t like dry posts, so I’ll put a bit of output to have some internal satisfaction.

By the way, if you test the DPR with Frits procedure, you have to be careful, because for some freaky reason that I don’t want to investigate and know is reporting different results and it’s quite unstable… strangely if you restart your database most of the issue are gone.
And by the way, if you are using my procedure, do not expect perfectly accurate output, it’s no meant to be and it wont be, because I need accurate trace files and I wont spend time rewriting in more accurate fashion. Do it ur self πŸ˜‰

And now, let the code speak by itself.

DB version and params:

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
--

KSPPINM 				      KSPPSTVL
--------------------------------------------- --------------
_small_table_threshold			      1695
_very_large_object_threshold		      500
_direct_read_decision_statistics_driven       TRUE
_serial_direct_read			      auto

My procedure without statistics:

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

Session altered.

SQL> create table sdpx(x varchar2(3700),xx varchar2(3700));

Table created.

SQL> 
--check cached blocks sdpx:
declare
v_target_blks int default 3000;
v_dpr_s int;
v_dpr_c int;
v_tmp int;
begin

--truncate the table and drop statistics /if any/
execute immediate ('truncate table sdpx drop storage');
DBMS_STATS.DELETE_TABLE_STATS('','sdpx',no_invalidate => false );

--generate data for sdpx - no compression table:
insert into sdpx select rpad('X',3100,'X'),rpad('X',3100,'X') from dual connect by rownum <= v_target_blks;

--flush buffers
execute immediate('alter system flush buffer_cache');
execute immediate('alter system flush shared_pool');
for c in (select rowid,rownum from sdpx) loop
	--perform buff read
	select count(*) into v_tmp from sdpx where rowid=c.rowid;
	--check statistics
	select value into v_dpr_s from v$mystat
	where statistic#=(select statistic# from v$statname where name = 'physical reads direct');
	--perform FTS
	select count(*) into v_tmp from sdpx;
	--check statistics
	select value into v_dpr_c from v$mystat
	where statistic#=(select statistic# from v$statname where name = 'physical reads direct');
	--no dp, show stats
	if v_dpr_s=v_dpr_c then
		v_tmp:=(c.rownum/v_target_blks)*100;
		dbms_output.put_line('SDPX: cached read at '||v_tmp||'% cached');
		exit;
	end if;
end loop;

end;
/

SDPX: cached read at 39% cached

PL/SQL procedure successfully completed.

SQL> 

Trace:
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: 4, objd: 87650, objn: 87641
ckpt: 1, nblks: 3016, ntcache: 1500, ntdist:0
NSMTIO: Additional Info: VLOT=423850
Object# = 87650, Object_Size = 3016 blocks
SqlId = 1nb5kz7dm1jf6, plan_hash_value = 4229476807, Partition# = 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:NoDirectRead:[CACHE_READ]: tsn: 4, objd: 87650, objn: 87641
ckpt: 0, nblks: 3016, ntcache: 1508, ntdist:0
NSMTIO: Additional Info: VLOT=423850
Object# = 87650, Object_Size = 3016 blocks
SqlId = 1nb5kz7dm1jf6, plan_hash_value = 4229476807, Partition# = 0

DPR till 50% cached as described in my post /look at the trace as I said/

My procedure with statistics:

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

Session altered.

SQL> create table sdpx(x varchar2(3700),xx varchar2(3700));

Table created.

SQL> 
--check cached blocks sdpx:
declare
v_target_blks int default 3000;
v_dpr_s int;
v_dpr_c int;
v_tmp int;
begin

--truncate the table and drop statistics /if any/
execute immediate ('truncate table sdpx drop storage');
DBMS_STATS.GATHER_TABLE_STATS('','sdpx',no_invalidate => false );

--generate data for sdpx - no compression table:
insert into sdpx select rpad('X',3100,'X'),rpad('X',3100,'X') from dual connect by rownum <= v_target_blks;

--flush buffers
execute immediate('alter system flush buffer_cache');
execute immediate('alter system flush shared_pool');
for c in (select rowid,rownum from sdpx) loop
	--perform buff read
	select count(*) into v_tmp from sdpx where rowid=c.rowid;
	--check statistics
	select value into v_dpr_s from v$mystat
	where statistic#=(select statistic# from v$statname where name = 'physical reads direct');
	--perform FTS
	select count(*) into v_tmp from sdpx;
	--check statistics
	select value into v_dpr_c from v$mystat
	where statistic#=(select statistic# from v$statname where name = 'physical reads direct');
	--no dp, show stats
	if v_dpr_s=v_dpr_c then
		v_tmp:=(c.rownum/v_target_blks)*100;
		dbms_output.put_line('SDPX: cached read at '||v_tmp||'% cached');
		exit;
	end if;
end loop;

end;
/

SDPX: cached read at 0% cached

PL/SQL procedure successfully completed.

SQL> 

Trace:
NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 1 (blocks), Threshold: MTT(8477 blocks),
_object_statistics: enabled, Sage: enabled,
Direct Read for serial qry: enabled(:::pgapls:::), Ascending SCN table scan: FALSE
flashback_table_scan: FALSE, Row Versions Query: FALSE
SqlId: 1nb5kz7dm1jf6, plan_hash_value: 4229476807, Object#: 87651, Parition#: 0


Immediate DPR as described by Frits post.

So, it’s all about statistics πŸ˜‰

happy geekin


Posts, toughs, jokes, sarcasm, etc. is mine, all mine. Nothing related to my current, previous and probably future employer.

Advertisements

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