Oracle Exadata的TABLE ACCESS STORAGE FULL執行計劃

Oracle在Exadata上增加了一個硬體Exadata Programmable Storage Server，使得在儲存系統可以變得更加智慧。以往在進行全表掃描時，即使存在過濾條件，也需要將全部資料讀到資料庫伺服器端，才能過濾掉無用的資料。但是透過這個硬體和儲存軟體的配合，使得這種過濾直接在儲存層進行，而返回給資料庫伺服器的則是查詢需要的結果。一方面在儲存直接過濾提高訪問效能，另一方面使得返回個伺服器的資料量大大下降，這也是Exadata進行全表掃描效能優異的重要原因之一。

在昨天練手的時候，記錄了一下這個執行計劃，而這個執行計劃在自己的測試環境中是不可能出現的：

SQL> select count(*) from t;

  COUNT(*)
----------
  49527761

Elapsed: 00:00:02.28

Execution Plan
----------------------------------------------------------   
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |   114K  (1)| 00:22:59 |
|   1 |  SORT AGGREGATE            |      |     1 |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| T    |   228M|   114K  (1)| 00:22:59 |
---------------------------------------------------------------------------

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

Statistics
----------------------------------------------------------    
          0  recursive calls
          0  db block gets
     418736  consistent gets
          0  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        524  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 count(*) from t where wner = 'TEST';

  COUNT(*)
----------
       611

Elapsed: 00:00:02.83

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |    17 |   115K  (2)| 00:23:07 |
|   1 |  SORT AGGREGATE            |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |  6670 |   110K|   115K  (2)| 00:23:07 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("OWNER"='TEST')
       filter("OWNER"='TEST')

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

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     418736  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到Predicate Information中，訪問和過濾條件分別是storage("OWNER"='TEST')和filter("OWNER"='TEST')，這說明限制條件被推到了儲存層執行，也正是這個原因，使得Oracle估算的訪問行數沒有太大的偏差。