Smart Scan

Smart Scan是Exadata的主要特性之一，该特性主要依赖于于Exadata Storage Software:

[oracle@database ~]$ sqlplus  maclean/maclean

SQL*Plus: Release 11.2.0.2.0 Production on Sat Aug 18 22:46:39 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

select a.name,b.value/1024/1024 MB
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                                                                     MB
---------------------------------------------------------------- ----------
physical read total bytes                                          .3984375
physical write total bytes                                                0
cell physical IO interconnect bytes                                .3984375
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 interconnect bytes returned by smart scan                0
cell IO uncompressed bytes                                                0

SQL> set linesize 200 pagesize 2000

SQL> set autotrace on;

SQL>  select  /*+ OPT_PARAM('cell_offload_processing' 'false') */ count(*) from sales
  2   where time_id between '01-JAN-98' and '31-DEC-98'
  3   and amount_sold>=101;

  COUNT(*)
----------
     30661

Execution Plan
----------------------------------------------------------
Plan hash value: 8150843

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    22 |  1238   (2)| 00:00:15 |
|   1 |  SORT AGGREGATE             |       |     1 |    22 |            |          |
|*  2 |   FILTER                    |       |       |       |            |          |
|*  3 |    TABLE ACCESS STORAGE FULL| SALES | 14685 |   315K|  1238   (2)| 00:00:15 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_DATE('01-JAN-98')=101 AND "TIME_ID">='01-JAN-98' AND
   3 - filter("AMOUNT_SOLD"》=101 AND "TIME_ID"》='01-JAN-98' AND
              "TIME_ID"《='31-DEC-98')

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4437  consistent gets
       4433  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select a.name,b.value/1024/1024 MB
  2  from v$sysstat a , v$mystat b
  3  where
  4  a.statistic#=b.statistic#
  5  and (a.name in ('physical read total bytes','physical write total bytes',
  6  'cell IO uncompressed bytes') or a.name like 'cell phy%');

NAME                                                                     MB
---------------------------------------------------------------- ----------
physical read total bytes                                         35.484375
physical write total bytes                                                0
cell physical IO interconnect bytes                               35.484375
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 interconnect bytes returned by smart scan                0
cell IO uncompressed bytes                                                0

9 rows selected.

上面我们使用了exadata核心参数cell_offload_processing=false禁用了smart scan，下面我们来看看Smart scan的统计表现


select count(*) from sales
where time_id between '01-JAN-2003' and '31-DEC-2003'
and amount_sold=1;


PARSING IN CURSOR #8100532 len=100 dep=0 uid=93 oct=3 lid=93 tim=1345357700828975 hv=1616885803 ad='3f07a6a0' sqlid='7z3cz8ph5zf1b'
select  count(*) from sales
 where time_id between '01-JAN-98' and '31-DEC-98'
 and amount_sold>=101
END OF STMT
PARSE #8100532:c=149978,e=1430146,p=271,cr=310,cu=0,mis=1,r=0,dep=0,og=1,plh=8150843,tim=1345357700828975
EXEC #8100532:c=0,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=8150843,tim=1345357700829041
WAIT #8100532: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=79496 tim=1345357700829074
WAIT #8100532: nam='cell smart table scan' ela= 502 cellhash#=1375519866 p2=0 p3=0 obj#=79496 tim=1345357700830804
WAIT #8100532: nam='cell smart table scan' ela= 20243 cellhash#=1375519866 p2=0 p3=0 obj#=79496 tim=1345357700851709
WAIT #8100532: nam='cell smart table scan' ela= 32442 cellhash#=1375519866 p2=0 p3=0 obj#=79496 tim=1345357700884378
WAIT #8100532: nam='cell smart table scan' ela= 6315 cellhash#=1375519866 p2=0 p3=0 obj#=79496 tim=1345357700891113
WAIT #8100532: nam='cell smart table scan' ela= 17460 cellhash#=1375519866 p2=0 p3=0 obj#=79496 tim=1345357700909251



SQL> select a.name,b.value/1024/1024 MB
  2  from v$sysstat a , v$mystat b
  3  where
  4  a.statistic#=b.statistic#
  5  and (a.name in ('physical read total bytes','physical write total bytes',
  6  'cell IO uncompressed bytes') or a.name like 'cell phy%');

NAME                                                                     MB
---------------------------------------------------------------- ----------
physical read total bytes                                         53.484375
physical write total bytes                                                0
cell physical IO interconnect bytes                                 .484375
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             53.484375
cell physical IO bytes saved by storage index                             0
cell physical IO interconnect bytes returned by smart scan          .484375
cell IO uncompressed bytes                                        53.484375

9 rows selected.

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

Exadata:Smart Scan(一)

Exadata:Smart Scan(二) FAST FULL SCAN