ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Exadata V2 Smart Scan Test

Exadata V2 Smart Scan Test

原创 Linux操作系统 作者:DBA_oracle_java 时间:2013-09-29 14:27:09 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-02

  • 博文量
    31
  • 访问量
    117025