Continuing the example of the previous blog post (is it possible to Smart Scan GTTs?), I am investigating if Smart Scans against GTTs can benefit from Flash Cache, or “optimised reads”. The easiest way to see this in your session is to calculate the change in session counters based on v$mystat or v$sesstat. Other tools might also provide information about the amount of data read from Flash Cache, but there are quite often extra licenses required …
Calculating the delta in session counters can be done in many ways, for example using Tanel’s session snapper. For clearly defined test cases where I can control beginning and end of the execution I prefer to use another great script for the purpose. My thanks go to Adrian Billington for providing the Oracle community with the most useful “mystats” package which I’m using here. You can – and should – get it from oracle-developer.net.
Let’s check I can get flash cache read hits on GTT scans – which would help performance of repeated queries against the segment. And since GTTs reside on TEMP, I had a few doubts whether flash cache read hits were possible.
What better than to test?
Testing helps removing ambiguity (and documenting the test result helps me remember things!), so here’s the code used:
set lines 120 tab off trimspool on verify off timing on -- it will be crucially important to use the /*+ append */ hint -- as you will see in the next post about GTTs on Exadata insert /*+ append */ into gtt select * from t4 where rownum < 400000; commit; -- not testing if I can get Smart Scans, I know there are :) alter session set "_serial_direct_read" = always; -- make sure the query can be ever so slightly more complex and always return rows col min_id new_v min_id col max_id new_v max_id select min(id) min_id from gtt; select max(id) max_id from gtt; -- hoping to trigger flash cache population here select /* using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id; select /* using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id; select /* using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id; select /* using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id; select /* using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id; select /* using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id; -- and measuring effect, if any @mystats start s=s set echo on select /* using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id; set echo off @mystats stop t=1
Have a look at the code in mystats.sql to see what the options mean. Essentially I’m asking it to record session statistics only (start s=s) and after the execution list only those counters that have changed (t=1 where t is for threshold). I hacked the script a little to order by statistic name rather than the default.
The observation
It appears I can get Flash Cache hits. The relevant statistic name is “cell flash cache read hits”, and the fact that physical IO was optimised can also be seen in “physical read requests optimized” and “physical read total bytes optimized” amongst others. Here is the relevant output from mystats:
------------------------------------------------------------------------------------------ 2. Statistics Report ------------------------------------------------------------------------------------------ Type Statistic Name Value ------ ---------------------------------------------------------------- ---------------- ... STAT cell blocks processed by cache layer 66,667 STAT cell blocks processed by data layer 66,667 STAT cell blocks processed by txn layer 66,667 STAT cell flash cache read hits 315 STAT cell num smartio automem buffer allocation attempts 1 STAT cell physical IO bytes eligible for predicate offload 546,136,064 STAT cell physical IO interconnect bytes 112,504 STAT cell physical IO interconnect bytes returned by smart scan 96,120 STAT cell scans 1 ... STAT physical read IO requests 523 STAT physical read bytes 546,152,448 STAT physical read requests optimized 315 STAT physical read total IO requests 523 STAT physical read total bytes 546,152,448 STAT physical read total bytes optimized 329,252,864 STAT physical read total multi block requests 521 STAT physical reads 66,669 STAT physical reads cache 2 STAT physical reads direct 66,667 ...
I usually check the cell blocks processed by% layer first to get an idea about the “depth” of the Smart Scan-losing blocks in transaction and data layers wouldn’t be great. There’s been 1 Smart Scan against my segment (the GTT isn’t partitioned) recorded in “cell scans”, so that covers that aspect as well.
Out of the 523 “physical read IO requests” 315 were optimised so that’s a pretty good ratio of approximately 60% of the IO requests coming from Flash Cache. The other statistics represent the same ratio in bytes rather than I/O requests.
Martins Blog 