在之前的文章中，我們詳細瞭解了Oracle 11g的Result Cache特性和機理。本篇我們討論一下使用Table Annotation來控制資料表Result Cache使用的功能。

10、Table Annotation與Result Cache

在之前的介紹中，無論是直接的設定引數Result_Cache_Mode，還是手工的在SQL語句中新增RESULT_CACHE的Hint，在實際中都有一些問題。

Oracle推薦的Result Cache應用場景，有兩個前提：一個是從前端來的高頻度SQL語句，而且語句結構相同。另一個是資料表只讀Read Only特性強烈。透過引數Result_Cache_Mode，我們直接就對所有資料表採用了結果集合快取，顯然不滿足條件，純Read Only的資料庫也是不常見的。

手工新增Result Cache Hint，的確是一種方便的手段。如果在測試階段發現了這樣的問題，的確可以手工修改程式碼，加上Hint語句。但是，如果系統已經上線投產，沒有條件新增Hint，我們怎麼辦呢？

單獨從調優的角度看，的確有很多選擇，比如成熟的SQL Profile、SPM等。其實，我們是可以使用Table Annotation功能進行Result Cache設定。

Table Annotation是Oracle對資料表的一種屬性設定，如同Storage一樣，很多特性我們都可以設定在Table的層面上。

首先，我們構建資料表和環境資訊。

SQL> create table t as select * from dba_objects;

Table created

SQL> create index idx_t_owner on t(owner);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

當前result cache引數維持在預設Default值情況。

SQL> show parameter result_cache_mode;

NAME TYPE VALUE

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

result_cache_mode string MANUAL

資料表屬性，我們都是可以透過dba_tables檢視來看到。進入11g之後，這個檢視中新增了一個result_cache資料列。

SQL> desc dba_tables;

Name Type Nullable Default Comments

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

OWNER VARCHAR2(30) Owner of the table

TABLE_NAME VARCHAR2(30) Name of the table

TABLESPACE_NAME VARCHAR2(30) Y Name of the tablespace containing the table

（篇幅原因，有省略……）

RESULT_CACHE VARCHAR2(7) Y The result cache mode annotation for the table

預設情況下，資料表的Result Cache功能是關閉的，取值為Default。

SQL> select result_cache from dba_tables where wner='SCOTT' and table_name='T';

RESULT_CACHE

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

DEFAULT

我們實驗了一個SQL。

SQL> explain plan for select /*+demo*/count(*) from t where wner='SCOTT';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1232703844

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

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

| 0 | SELECT STATEMENT | | 1 | 6 | 6 (0)| 00:00:01

| 1 | SORT AGGREGATE | | 1 | 6 | |

|* 2 | INDEX RANGE SCAN| IDX_T_OWNER | 2205 | 13230 | 6 (0)| 00:00:01

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

Predicate Information (identified by operation id):

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

2 - access("OWNER"='SCOTT')

14 rows selected

真正執行SQL之後，我們在shared pool中可以找到共享遊標。

SQL> select sql_id, version_count, executions from v$sqlarea where sql_text like 'select /*+demo*/count(*) from t%';

SQL_ID VERSION_COUNT EXECUTIONS

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

60d8dpwmpacv4 1 1

SQL> select * from table(dbms_xplan.display_cursor(sql_id => '60d8dpwmpacv4',format => 'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID 60d8dpwmpacv4, child number 0

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

select /*+demo*/count(*) from t where wner='SCOTT'

Plan hash value: 1232703844

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

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

| 0 | SELECT STATEMENT | | | | 6 (100)|

| 1 | SORT AGGREGATE | | 1 | 6 | |

|* 2 | INDEX RANGE SCAN| IDX_T_OWNER | 2205 | 13230 | 6 (0)| 00:00:01

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

Predicate Information (identified by operation id):

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

2 - access("OWNER"='SCOTT')

Column Projection Information (identified by operation id):

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

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

44 rows selected

我們使用alter table命令就可以修改資料表的屬性。

SQL> alter table t result_cache (mode force);

Table altered

SQL> select result_cache from dba_tables where wner='SCOTT' and table_name='T';

RESULT_CACHE

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

FORCE

Result cache功能兩個取值，一個是Default，一個Force。如果設定為force，表示開啟了result cache功能。相同的SQL語句，我們檢查一下最佳化器和shared pool情況。

--相同的SQL語句

SQL> explain plan for select /*+demo*/count(*) from t where wner='SCOTT';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1232703844

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

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

| 0 | SELECT STATEMENT | | 1 | 6 | 6

| 2 | SORT AGGREGATE | | 1 | 6 |

|* 3 | INDEX RANGE SCAN| IDX_T_OWNER | 2205 | 13230 | 6

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

Predicate Information (identified by operation id):

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

3 - access("OWNER"='SCOTT')

Result Cache Information (identified by operation id):

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

1 - column-count=1; dependencies=(SCOTT.T); attributes=(single-row, ordered);

"

最佳化器已經發現了SQL使用Result Cache的情況，並且修改了執行計劃。Shared Pool情況如下：

--執行了兩次

SQL> select /*+demo*/count(*) from t where wner='SCOTT';

COUNT(*)

----------

19

SQL> select /*+demo*/count(*) from t where wner='SCOTT';

COUNT(*)

----------

19

--sql id相同，但是原有的執行計劃已經被廢止；

SQL> select sql_id, version_count, executions from v$sqlarea where sql_text like 'select /*+demo*/count(*) from t%';

SQL_ID VERSION_COUNT EXECUTIONS

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

60d8dpwmpacv4 1 2

實際的快取結果執行計劃如下：

SQL> select * from table(dbms_xplan.display_cursor(sql_id => '60d8dpwmpacv4',format => 'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID 60d8dpwmpacv4, child number 0

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

select /*+demo*/count(*) from t where wner='SCOTT'

Plan hash value: 1232703844

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

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

| 0 | SELECT STATEMENT | | | | 6

| 2 | SORT AGGREGATE | | 1 | 6 |

|* 3 | INDEX RANGE SCAN| IDX_T_OWNER | 2205 | 13230 | 6

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

Predicate Information (identified by operation id):

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

3 - access("OWNER"='SCOTT')

Column Projection Information (identified by operation id):

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

1 - COUNT(*)[22]

2 - (#keys=0) COUNT(*)[22]

Result Cache Information (identified by operation id):

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

1 -

51 rows selected

從cache pool中，我們的確可以看到物件。

SQL> select id, type, status, cache_id, name from v$result_cache_objects;

ID TYPE STATUS CACHE_ID NAME

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

0 Dependency Published SCOTT.T SCOTT.T

1 Result Published a6h12b8a88gkk2as3cng select /*+demo*/coun

j03t16 t(*) from t where ow

ner='SCOTT'

綜上實驗：我們沒有修改SQL語句，只是透過資料庫層面的修改，就開啟了Result Cache功能。Table Annotation在這些場景下是非常實用的。

11、結論

最後，我們從系統架構和設計角度看看Result Cache功能。Result cache是從資料庫層面上提供的一種結果集合快取方法。所有的快取都會有兩個特點：一個是資料冗餘，藉助空間來換取時間和效率。另一個是資料儘可能的往前端“推動”，減少中間傳輸環節。

從這樣的角度看，我們感覺Result Cache本質上也有“運維階段彌補”的味道。如果我們是一個成熟的設計，這樣的快取應該是透過應用快取等手段進行處理，而不是轉化為高頻度的SQL執行。所以，Result Cache的處理，其實更多的場景是後期運維人員的效果彌補工作。

在實際中，一定要注意Result Cache使用的兩個前提：Read Only和高頻度資料訪問。同時，SQL結果集合數量儘量不要很多，因為會消耗shared pool空間。

聊聊Oracle 11g的Result Cache（四）

相關文章