Exadata V2 Smart Scan Test

Exadata V2 Smart Scan Test

测试数据库版本11.2.0.1

1.创建测试表

create table smart ( x int );

SQL> begin                                     

     for i in 1 .. 100000                       

     loop                                      

     execute immediate                         

     'insert into smart values ( :x )' using i;    

     end loop;  

     commit;                               

     end;                                      

     /

 

2.不适用smart scan

#查看cell_offload_processing参数

    

SQL> show parameter cell_offload_processing 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

cell_offload_processing              boolean     TRUE

SQL> alter session set cell_offload_processing=FALSE;

SQL> alter session set "_serial_direct_read"=TRUE;

SQL> set autotrace on

SQL> set timing on

SQL> set linesize 100 pagesize 1400

SQL> select count(*) from smart where x>99999;

  COUNT(*)

----------

         1

Elapsed: 00:00:00.53

Execution Plan

----------------------------------------------------------

Plan hash value: 1093990360

------------------------------------------------------------------------------------

| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |       |     1 |    13 |    44   (3)| 00:00:01 |

|   1 |  SORT AGGREGATE            |       |     1 |    13 |            |          |

|*  2 |   TABLE ACCESS STORAGE FULL| SMART |     2 |    26 |    44   (3)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("X">99999)

Note

-----

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

Statistics

----------------------------------------------------------

        416  recursive calls

          0  db block gets

        278  consistent gets

        285  physical reads

          0  redo size

        422  bytes sent via SQL*Net to client

        419  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          4  sorts (memory)

          0  sorts (disk)

          1  rows processed

###执行如下查询

SQL> set autotrace off

select sql_id,

       physical_read_bytes            A,

       io_interconnect_bytes          B,

       io_cell_offload_eligible_bytes C,

       io_cell_offload_returned_bytes D

  from v$sql

 where sql_text like '%from smart%'

   and sql_text not like '%v$sql%';

SQL_ID                 A          B          C          D

------------- ---------- ---------- ---------- ----------

5cg09khkxsxd8          0          0          0          0

0vsx3f3q12jbx    2334720    2334720          0          0

可以看到A(physical_read_bytes )和B（io_interconnect_bytes)。而C,D则为0.

SQL> select sql_text from v$sql where sql_id='0vsx3f3q12jbx';

SQL_TEXT

----------------------------------------------------------------------------------------------------

select count(*) from smart where x>99999

3.使用 smart scan

SQL> alter session set cell_offload_processing=TRUE;

SQL> alter session set "_serial_direct_read"=TRUE;

SQL> alter system flush buffer_cache;

SQL> alter system flush shared_pool;

SQL> set autotrace on 

SQL> set timing on

SQL> set linesize 100 pagesize 1400

SQL> select count(*) from smart where x>99999;

  COUNT(*)

----------

         1

Elapsed: 00:00:01.18

Execution Plan

----------------------------------------------------------

Plan hash value: 1093990360

------------------------------------------------------------------------------------

| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |       |     1 |    13 |    44   (3)| 00:00:01 |

|   1 |  SORT AGGREGATE            |       |     1 |    13 |            |          |

|*  2 |   TABLE ACCESS STORAGE FULL| SMART |     2 |    26 |    44   (3)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - storage("X">99999)

       filter("X">99999)

Note

-----

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

Statistics

----------------------------------------------------------

          5  recursive calls

          0  db block gets

        223  consistent gets

        282  physical reads

          0  redo size

        422  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> set autotrace off

select sql_id,

       physical_read_bytes            A,

       io_interconnect_bytes          B,

       io_cell_offload_eligible_bytes C,

       io_cell_offload_returned_bytes D

  from v$sql

 where sql_text like '%from smart%'

   and sql_text not like '%v$sql%';

SQL_ID                 A          B          C          D

------------- ---------- ---------- ---------- ----------

5cg09khkxsxd8          0          0          0          0

5cg09khkxsxd8      24576      24576          0          0

0vsx3f3q12jbx    2334720    2334720          0          0

0vsx3f3q12jbx    2310144    1066192    1245184       1232     

这里的A和B是不等的,CD为非0值，说明发生了smart scan

SQL> select sql_text from v$sql where sql_id='0vsx3f3q12jbx';

SQL_TEXT

----------------------------------------------------------------------------------------------------

select count(*) from smart where x>99999

select count(*) from smart where x>99999

Elapsed: 00:00:00.00

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26169542/viewspace-773592/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26169542/viewspace-773592/