GTT in Exadata are eligible to SmartScan

By Franck Pachot

.
I wanted to check if Exadata predicate offloading can occur on Global Temporary Tables. Actually, I thought it did not and I was wrong. I was ready to post that as an hypothesis for https://community.oracle.com/thread/3903836 but, before any post to forums, I try to test what I say because I may be wrong, or things may have changed from versions to versions. Here I will show how it’s easy to quickly test an hypothesis. And yes, you can even test SmartScan behavior on your laptop.

Let’s create a Global Temporary Table with some rows:

SQL> create global temporary table DEMOGTT on commit preserve rows as select * from dba_objects;
Table created.
SQL> commit;
Commit complete.

The point here is to use the Filter Predicate LIBrary that is shipped in every oracle installation, even non-Exadata ones, for simulation:

SQL> alter session set "_rdbms_internal_fplib_enabled"=true cell_offload_plan_display=always "_serial_direct_read"=always;
Session altered.

I’ve also forced Serial Direct Read to be sure to do direct path reads.
Then I select from it with a highly selective predicate:

SQL> set autotrace trace
SQL> select object_id from DEMOGTT where object_name like 'X%';
498 rows selected.
 
Execution Plan
----------------------------------------------------------
Plan hash value: 962761541
 
-------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |  1381 | 40049 |   459   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| DEMOGTT |  1381 | 40049 |   459   (1)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage("OBJECT_NAME" LIKE 'X%')
       filter("OBJECT_NAME" LIKE 'X%')
 
Note
-----
   - Global temporary table session private statistics used
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1720  consistent gets
       1684  physical reads
        128  redo size
       9983  bytes sent via SQL*Net to client
        915  bytes received via SQL*Net from client
         35  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        498  rows processed
 
SQL> set autotrace off

Thanks to cell_offload_plan_display=always I can see that the optimizer build a plan that can use predicate offloading (the ‘STORAGE’ full table scan).
Autotrace tells me that I’ve read 1684 blocks from storage. I check my session cell statistics.

SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'cell%' and value>0 order by 1;
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
cell IO uncompressed bytes                                         13795328
cell blocks processed by cache layer                                   1684
cell blocks processed by data layer                                    1684
cell blocks processed by txn layer                                     1684
cell physical IO interconnect bytes                                27770880
cell scans                                                                2
cell simulated physical IO bytes eligible for predicate offload    13795328
cell simulated physical IO bytes returned by predicate offload        10552

All the 1684 physical reads were processed by the storage cell layers which means that offloading occurred.

Conclusion

When you are used to it, it’s often easy to build a very small test case to validate any assumption. With this example you know that ‘direct path read temp’ are eligible to SmartScan.