实测了以下Exadata smart scan对于INDEX STORAGE FAST FULL SCAN似乎实际并不生效，详见以下测试。不仅普通的B*tree index也包括compressed index、reverse key index等类型。
Why is my Exadata smart scan not offloading?
Exadata Smart Scan and Index Access
上面2篇文章介绍了了类似的XD offload 对index fast full scan不生效的问题， 相关的BUG有：
Bug 8257122 – Exadata smart scan caching does not work for INDEX FAST FULL scan (Doc ID 8257122.8)
以下测试了对NORMAL INDEX和bitmap index fast full scan的OFFLOAD情况：
SQL> select blocks,bytes/1024/1024/1024 from dba_segments where segment_name='LARGE_TABLE';
BLOCKS BYTES/1024/1024/1024
---------- --------------------
7127040 54.375
Elapsed: 00:00:00.19
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.19
SQL> select count(*) from LARGE_TABLE where object_id between 1000 and 20000;
COUNT(*)
----------
486735872
Elapsed: 00:00:23.29
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.19
SQL> select /*+ OPT_PARAM('cell_offload_processing' 'false') */ count(*) from LARGE_TABLE where object_id between 1000 and 20000;
COUNT(*)
----------
486735872
Elapsed: 00:03:24.22
SQL> create index pk_lt on large_table (object_id,data_object_id) tablespace larget parallel nologging;
Index created.
Elapsed: 00:01:14.18
SQL> alter index pk_lt noparallel;
Index altered.
SQL> exec dbms_stats.gather_table_stats('SYS','LARGE_TABLE',cascade=>TRUE, estimate_percent=>100, degree=>8);
PL/SQL procedure successfully completed.
Elapsed: 00:13:12.61
select a.name,b.value
from v$sysstat a , v$mystat b
where
a.statistic#=b.statistic#
and (a.name in ('physical read total bytes','physical write total bytes',
'cell IO uncompressed bytes') or a.name like 'cell phy%' );
NAME VALUE
---------------------------------------------------------------- ----------
physical read total bytes 0
physical write total bytes 0
cell physical IO interconnect bytes 0
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 0
cell physical IO bytes saved by storage index 0
cell physical IO bytes sent directly to DB node to balance CPU 0
cell physical IO interconnect bytes returned by smart scan 0
cell IO uncompressed bytes 0
10 rows selected.
alter system flush buffer_cache;
set timing on;
set linesize 200 pagesize 2000
select /*+ FULL(LARGE_TABLE) */ count(*) from LARGE_TABLE where object_id between 1000 and 20000;
COUNT(*)
----------
486735872
Elapsed: 00:00:23.30
TABLE ACCESS STORAGE FULL| LARGE_TABLE | 403M| 1925M| 1935K (1)|
SQL> select a.name,b.value
2 from v$sysstat a , v$mystat b
3 where
a.statistic#=b.statistic#
and (a.name in ('physical read total bytes','physical write total bytes',
'cell IO uncompressed bytes') or a.name like 'cell phy%' ); 4 5 6
NAME VALUE
---------------------------------------------------------------- ----------
physical read total bytes 5.8303E+10
physical write total bytes 0
cell physical IO interconnect bytes 6055421032
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 5.8303E+10
cell physical IO bytes saved by storage index 0
cell physical IO bytes sent directly to DB node to balance CPU 0
cell physical IO interconnect bytes returned by smart scan 6055396456
cell IO uncompressed bytes 5.8321E+10
10 rows selected.
Elapsed: 00:00:00.01
SQL>
SQL> alter system flush buffer_cache;
set timing on;
set linesize 200 pagesize 2000
System altered.
SQL> SQL>
SQL>
SQL> explain plan for select count(*) from LARGE_TABLE where object_id between 1000 and 20000;
Explained.
Elapsed: 00:00:00.02
SQL> @?/rdbms/admin/utlxplp
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 800139279
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 356K (1)| 00:00:14 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX STORAGE FAST FULL SCAN| PK_LT | 403M| 1925M| 356K (1)| 00:00:14 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("OBJECT_ID"<=20000 AND "OBJECT_ID">=1000)
filter("OBJECT_ID"<=20000 AND "OBJECT_ID">=1000)
15 rows selected.
Elapsed: 00:00:00.02
SQL> select count(*) from LARGE_TABLE where object_id between 1000 and 20000;
COUNT(*)
----------
486735872
Elapsed: 00:02:01.66
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_39539.trc
SQL> select a.name,b.value
2 from v$sysstat a , v$mystat b
3 where
a.statistic#=b.statistic#
and (a.name in ('physical read total bytes','physical write total bytes',
'cell IO uncompressed bytes') or a.name like 'cell phy%' ); 4 5 6
NAME VALUE
---------------------------------------------------------------- ----------
physical read total bytes 1.3300E+10
physical write total bytes 0
cell physical IO interconnect bytes 1.3300E+10
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 0
cell physical IO bytes saved by storage index 0
cell physical IO bytes sent directly to DB node to balance CPU 0
cell physical IO interconnect bytes returned by smart scan 0
cell IO uncompressed bytes 0
10 rows selected.
Elapsed: 00:00:00.01
END OF STMT
PARSE #47310019587768:c=2000,e=2137,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=800139279,tim=1353385966411213
EXEC #47310019587768:c=1000,e=67,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=800139279,tim=1353385966411365
WAIT #47310019587768: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1353385966411401
WAIT #47310019587768: nam='cell single block physical read' ela= 511 cellhash#=451279719 diskhash#=3519799300 bytes=8192 obj#=25183 tim=1353385966414839
WAIT #47310019587768: nam='cell multiblock physical read' ela= 16720 cellhash#=451279719 diskhash#=3519799300 bytes=1048576 obj#=25183 tim=1353385966433058
WAIT #47310019587768: nam='cell multiblock physical read' ela= 2965 cellhash#=451279719 diskhash#=3519799300 bytes=1048576 obj#=25183 tim=1353385966440986
...........................
select count(*) from LARGE_TABLE where owner like '%SY%';
QL> explain plan for select count(*) from LARGE_TABLE where owner like '%SY%';
Explained.
Elapsed: 00:00:00.00
SQL> @?/rdbms/admin/utlxplp
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3706014413
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 19017 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | BITMAP CONVERSION COUNT | | 26M| 127M| 19017 (1)| 00:00:01 |
|* 3 | BITMAP INDEX STORAGE FAST FULL SCAN| BIT_LT | | | | |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - storage("OWNER" LIKE '%SY%' AND "OWNER" IS NOT NULL)
filter("OWNER" LIKE '%SY%' AND "OWNER" IS NOT NULL)
16 rows selected.
Elapsed: 00:00:00.01
SQL> set linesize 200 pagesize 2000
SQL> select count(*) from LARGE_TABLE where owner like '%SY%';
COUNT(*)
----------
362643456
Elapsed: 00:00:01.07
SQL> select a.name,b.value
2 from v$sysstat a , v$mystat b
3 where
a.statistic#=b.statistic#
and (a.name in ('physical read total bytes','physical write total bytes',
'cell IO uncompressed bytes') or a.name like 'cell phy%' ); 4 5 6
NAME VALUE
---------------------------------------------------------------- ----------
physical read total bytes 173424640
physical write total bytes 0
cell physical IO interconnect bytes 173424640
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 0
cell physical IO bytes saved by storage index 0
cell physical IO bytes sent directly to DB node to balance CPU 0
cell physical IO interconnect bytes returned by smart scan 0
cell IO uncompressed bytes 0
10 rows selected.
Elapsed: 00:00:00.02
SQL>
Comment