mysql类exadata功能_Exadata: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.