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.

Comments

  1. Dumping SKGXP connection state: Band 4: port ID – 0x132c67c4, connection – 0x132c9040
    Dumping SKGXP connection state: Band 5: port ID – 0x132c680c, connection – 0x132cb710
    Dumping SKGXP connection state: Band 6: port ID – 0x132c6854, connection – 0x132cdde0
    Dumping SKGXP connection state: Band 7: port ID – 0x132c689c, connection – 0x132d04b0
    Dumping SKGXP connection state: Band 8: port ID – 0x132c68e4, connection – 0x132d2b80
    Reissuing requests for the box 0x132c65d8
    WAIT #8100532: nam=’cell smart table scan’ ela= 144395347 cellhash#=1375519866 p2=0 p3=0 obj#=79496 tim=1345357845979090
    WAIT #8100532: nam=’cell smart table scan’ ela= 8 cellhash#=1375519866 p2=0 p3=0 obj#=79496 tim=1345357845979169
    WAIT #8100532: nam=’cell smart table scan’ ela= 618 cellhash#=1375519866 p2=0 p3=0 obj#=79496 tim=1345357845980558
    WAIT #8100532: nam=’cell smart table scan’ ela= 226 cellhash#=1375519866 p2=0 p3=0 obj#=79496 tim=1345357845981023

    *** 2012-08-18 23:32:36.527
    Reconnecting to box 0x132c65d8 …
    Storage box 0x132c65d8 Inc: 25 with the source id 1996448482
    Box name 0 – 192.168.56.101
    OSS OS Pid – 2285
    Reconnect: Attempts: 1 Last TS: 4529986200
    Dumping SKGXP connection state: Band 0: port ID – 0x132c2b7c, connection – 0x132c2c50
    Dumping SKGXP connection state: Band 1: port ID – 0x132c2bc4, connection – 0x132be200
    Dumping SKGXP connection state: Band 2: port ID – 0x132c2c0c, connection – 0x132d66a0
    Dumping SKGXP connection state: Band 3: port ID – 0x132c23ac, connection – 0x132d8d70
    Dumping SKGXP connection state: Band 4: port ID – 0x132c23f4, connection – 0x132db440
    Dumping SKGXP connection state: Band 5: port ID – 0x132c243c, connection – 0x132c6730
    Dumping SKGXP connection state: Band 6: port ID – 0x132c24cc, connection – 0x132c8e00
    Dumping SKGXP connection state: Band 7: port ID – 0x132c2514, connection – 0x132cb4d0
    Dumping SKGXP connection state: Band 8: port ID – 0x132c255c, connection – 0x132cdba0
    Reissuing requests for the box 0x132c65d8
    WAIT #8100532: nam=’cell smart table scan’ ela= 110575097 cellhash#=1375519866 p2=0 p3=0 obj#=79496 tim=1345357956556197
    WAIT #8100532: nam=’cell smart table scan’ ela= 12 cellhash#=1375519866 p2=0 p3=0 obj#=79496 tim=1345357956556271
    WAIT #8100532: nam=’cell smart table scan’ ela= 293 cellhash#=1375519866 p2=0 p3=0 obj#=79496 tim=1345357956557073
    WAIT #8100532: nam=’cell smart table scan’ ela= 315 cellhash#=1375519866 p2=0 p3=0 obj#=79496 tim=1345357956557621

  2. overmars says

    请问下:博主上面的操作结果是在真机上面搞出来的,还是在模拟环境(虚拟机)的?

  3. askmaclean says

    SQL> select count(*) from larget;

    COUNT(*)
    ———-
    242778112

    Elapsed: 00:00:15.26
    SQL> select a.name,b.value/1024/1024 MB
    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%’ or a.name like ‘%flash cache%’); 4 5 6

    NAME MB
    —————————————————————- ———-
    physical read total bytes 50091.2344
    physical write total bytes 0
    cell physical IO interconnect bytes 5756.65722
    cell physical IO bytes pushed back due to excessive CPU on cell 0
    physical read flash cache hits 0
    flash cache inserts 0
    flash cache eviction: invalidated 0
    flash cache eviction: buffer pinned 0
    flash cache eviction: aged out 0
    flash cache insert skip: not current 0
    flash cache insert skip: DBWR overloaded 0
    flash cache insert skip: exists 0
    flash cache insert skip: not useful 0
    flash cache insert skip: modification 0
    flash cache insert skip: corrupt 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 50091.125
    cell physical IO bytes saved by storage index 0
    cell physical IO interconnect bytes returned by smart scan 5756.54784
    cell IO uncompressed bytes 50133.0078
    cell flash cache read hits 0

    22 rows selected.

    SQL> set timing on;
    SQL> select count(*) from compress_larget;

    COUNT(*)
    ———-
    242778112

    Elapsed: 00:00:03.49
    SQL> SQL> /

    COUNT(*)
    ———-
    242778112

    Elapsed: 00:00:03.43
    SQL> select a.name,b.value/1024/1024 MB
    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%’ or a.name like ‘%flash cache%’); 4 5 6

    NAME MB
    —————————————————————- ———-
    physical read total bytes .109375
    physical write total bytes 0
    cell physical IO interconnect bytes .109375
    cell physical IO bytes pushed back due to excessive CPU on cell 0
    physical read flash cache hits 0
    flash cache inserts 0
    flash cache eviction: invalidated 0
    flash cache eviction: buffer pinned 0
    flash cache eviction: aged out 0
    flash cache insert skip: not current 0
    flash cache insert skip: DBWR overloaded 0
    flash cache insert skip: exists 0
    flash cache insert skip: not useful 0
    flash cache insert skip: modification 0
    flash cache insert skip: corrupt 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 interconnect bytes returned by smart scan 0
    cell IO uncompressed bytes 0
    cell flash cache read hits 0

    22 rows selected.

    Elapsed: 00:00:00.00

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号