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/，如需轉載，請註明出處，否則將追究法律責任。