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.