Exadata 之 卸载、智能扫描（摘录加总结）

Exadata 之 卸载、智能扫描

Offloading is the approach that was used to solve the problem of excessive time spent moving

irrelevant data between the tiers. Offloading has three design goals, although the primary goal far

outweighs the others in importance:

Reduce the volume of data transferred from disk systems to the database servers.

Reduce CPU usage on database servers.

Reduce disk access times at the storage layer.

减少数据量是主要诉求和目标。

 

 

智能扫描包括：

• 表和索引扫描：扫描是在Exadata Storage Server 内部执行的，而不是通过将所有数据传输到数据库服务器来执行的。

• 谓词过滤：仅请求的行（而不是表中的所有行）返回到数据库服务器。

• 列过滤：仅请求的列（而不是表中的所有列）返回到数据库服务器。

• 联接过滤：使用Bloom 过滤器的联接将转移到Exadata Storage Server 进行处理。

 

智能扫描要求

• 智能扫描不受优化器控制，但它受查询优化结果的影响。

• 查询特定的要求：

• 智能扫描只可用于完整的表或索引扫描。

• 智能扫描只能用于直接路径读取：

• 直接路径读取会自动用于并行查询。

• 直接路径读取可以用于串行查询。

• 默认情况下不使用它们进行小型表的串行扫描。

• 使用_serial_direct_read=TRUE 可强制执行直接路径读取。

• 其他的一般要求：

• 必须在数据库中启用智能扫描。

• 段必须存储在进行了适当配置的磁盘组中。

 

智能扫描不能用于以下情况：

• 对群集表的扫描

• 对索引组织表的扫描

• 对压缩索引的快速完整扫描

• 对反向键索引的快速完整扫描

• 表已启用行级别依赖性跟踪

• 正在提取ORA_ROWSCN pseudocolumn

• 优化器希望扫描按ROWID 顺序返回行

• 命令为使用NOSORT 的CREATE INDEX

• 选中或查询LOB 或LONG 列

• 正在执行SELECT ... VERSIONS 闪回查询

• 根据虚拟列评估谓词

• 查询中引用的列数超过255

• 数据已加密且基于单元的解密已禁用

 

在SQL 执行计划中监视智能扫描

• 相关的初始化参数：

• CELL_OFFLOAD_PROCESSING

• TRUE|FALSE

• 启用或禁用智能扫描及其他智能存储功能

• 可使用ALTER SESSION 或ALTER SYSTEM 在会话级别或系统级别动态修改

• 可使用OPT_PARAM 提示在语句级别指定

• CELL_OFFLOAD_PLAN_DISPLAY

• NEVER|AUTO|ALWAYS

• 允许执行计划显示负载已转移的谓词

• 可使用ALTER SESSION 或ALTER SYSTEM 在会话级别或系统级别动态修改

 

一个直观的例子：

SQL> set timing on

SQL> alter session set cell_offload_processing=false;

 

Session altered.

 

Elapsed: 00:00:00.00

SQL> select count(*) from TESTITEM

  2   where TID > 10000000 ;

 

  COUNT(*)

----------

  59020614

 

Elapsed: 00:00:44.91

SQL>

SQL> alter session set cell_offload_processing=true;

 

Session altered.

 

Elapsed: 00:00:00.00

SQL> select count(*) from TESTITEM

  2   where TID > 10000000 ;

 

  COUNT(*)

----------

  59020614

 

Elapsed: 00:00:13.18

 

DBMS_XPLAN里使用+projection可以显示Projection的信息，这个不是Exadata特有的：

The DBMS_XPLAN package can display information about column projection, although by default it

does not. The projection data is stored in the PROJECTION column in the V$SQL_PLAN view as well.

SQL> select * from

  2   table(dbms_xplan.display_cursor('&sql_id','&child_no','+projection'));

Enter value for sql_id: 5fqzxu4j3kr9h

Enter value for child_no:

old   2:  table(dbms_xplan.display_cursor('&sql_id','&child_no','+projection'))

new   2:  table(dbms_xplan.display_cursor('5fqzxu4j3kr9h','','+projection'))

 

PLAN_TABLE_OUTPUT

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

SQL_ID  5fqzxu4j3kr9h, child number 0

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

select count(TID) from TESTITEM

 

Plan hash value: 1265917747

 

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

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

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

|   0 | SELECT STATEMENT            |             |       |       |   944K(100)|          |       |       |

|   1 |  SORT AGGREGATE             |             |     1 |     7 |            |          |       |       |

 

PLAN_TABLE_OUTPUT

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

|   2 |   PARTITION RANGE ALL       |             |   124M|   833M|   944K  (1)| 03:08:49 |     1 |    56 |

|   3 |    TABLE ACCESS STORAGE FULL| TESTITEM |   124M|   833M|   944K  (1)| 03:08:49 |     1 |    56 |

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

 

Column Projection Information (identified by operation id):

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

 

   1 - (#keys=0) COUNT("TID")[22]

   2 - "TID"[NUMBER,22]

   3 - "TID"[NUMBER,22]

 

如何验证智能扫描确实发生

the normal  execution plan output produced by the DBMS_XPLAN package will not show you whether a Smart Scan was

used or not

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

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

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

| 0 | SELECT STATEMENT | | | | 44486 (100)| |

| 1 | SORT AGGREGATE | | 1 | 11 | | |

|* 2 | TABLE ACCESS STORAGE FULL| SKEW | 32M| 335M| 44486 (1)| 00:08:54 |

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

Predicate Information (identified by operation id):

2 - storage("COL1">0)

filter("COL1">0)

Notice that the optimizer chose a TABLE ACCESS STORAGE FULL operation and that the predicate

section shows a storage() predicate associated with step 2 of the plan. Both of these characteristics

indicate that a Smart Scan was possible, but neither provides a definitive verification. In fact, the

statement in this listing was not executed with a Smart Scan.

 

有如下相关的手段：

A 10046 trace

B Performance Statistics (v$sessstat)

C 卸载操作的关键指标：IO_CELL_OFFLOAD_ELIGIBLE_BYTES（节约的数据量）和IO_INTERCONNECT_BYTES（返回的数据量）：

SQL>  select sql_id, IO_CELL_OFFLOAD_ELIGIBLE_BYTES eligible,

  2   IO_INTERCONNECT_BYTES actual,

  3   100*(IO_CELL_OFFLOAD_ELIGIBLE_BYTES-IO_INTERCONNECT_BYTES)/IO_CELL_OFFLOAD_ELIGIBLE_BYTES "IO_SAVED_%", sql_text

  4   from v$sql where sql_id in ('5fqzxu4j3kr9h');

SQL_ID          ELIGIBLE     ACTUAL IO_SAVED_%           SQL_TEXT

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

5fqzxu4j3kr9h 2.8231E+10 1724405736 93.8917937  select count(TID) from TESTITEM

 

D Sql 监控：

select dbms_sqltune.report_sql_monitor (session_id=>849,report_level=>'ALL',type=>'TEXT') from dual;

Session Snapper；

 

禁用存储索引：

alter system set "_kcfis_storageidx_disabled"=true;

 

布隆过滤：

alter session set "_bloom_predicate_pushdown_to_storage"=true;

If you look closely at the Predicate Information of the plans, you will see that the

SYS_OP_BLOOM_FILTER(:BF0000,"A"."PK_COL") predicate was run on the storage servers for the second

run. The offloaded version ran faster because the storage servers were able to pre-join the tables, which

eliminated a large amount of data that would otherwise have been transferred back to the database

servers.

 

关于_serial_direct_read：

在11g中因为有了Adaptive Direct Read，即使_serial_direct_read的值为false，也可能direct path read。

So a Smart Scan may be used even if we use _SERIAL_DIRECT_READ=false .

参考：http://space.itpub.net/758322/viewspace-718274

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

转载于:http://blog.itpub.net/758322/viewspace-741863/