实测了以下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>
Leave a Reply