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

Maclean Liu says

2012/08/19 at 14:33

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

回复
overmars says

2012/08/20 at 10:32

请问下：博主上面的操作结果是在真机上面搞出来的，还是在模拟环境（虚拟机）的？

回复
askmaclean says

2012/08/30 at 11:16

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

回复

Exadata:Smart Scan(一)

Comments

Comment 取消回复