宣告：本篇實驗思路感謝dbsnake老師指點。

在Oracle中，使用者應用輸入的SQL語句要進行所謂的Parse解析過程，用於生成執行計劃，這也就是Query Optimizer的主要工作。在Parse中，有兩種具體型別，被稱為“hard parse”（硬解析）和“Soft parse”（軟解析）。

“實現執行計劃shared cursor共享，減少硬解析”是我們OLTP系統最佳化一個重要方向。但是，讓Oracle真正實現SQL共享不是一件容易的事情，受到很多其他因素的影響。最常用的方式是使用繫結變數，讓SQL字面值保持一致。如果應用端沒有使用繫結變數，一種做法是設定系統引數cursor_sharing，將SQL語句中的條件進行繫結變數替換。本篇將從cursor_sharing可選值含義入手，討論分析幾種取值的確切含義和應用場景。以及為什麼很多資料中都是對cursor_sharing設定望而卻步。

1、 環境準備

我們在Oracle 10g下準備一個相對偏值的資料表。

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

在Oracle 10g裡，預設cursor_sharing取值為EXACT，表示不開啟SQL字面取值繫結變數替換功能。

SQL> show parameter cursor_sharing;

NAME TYPE VALUE

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

cursor_sharing string EXACT

SQL> select name, value from v$parameter where name='cursor_sharing';

NAME VALUE

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

cursor_sharing EXACT

使用指令碼生成資料表資料。

SQL> create table t (id1 varchar2(10), id2 varchar2(10), id3 varchar2(10));

Table created

SQL> create index idx_t_id1 on t(id1);

Index created

SQL> select object_id from dba_objects where wner='SYS' and object_name='T';

OBJECT_ID

----------

54307

SQL> select id1, count(*) from t group by id1;

ID1 COUNT(*)

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

P 8000

D 10000

A 10

G 5

2、 統計量收集

這裡單獨談談統計量收集的問題。從上面實驗資料的情況看，資料表T的id1列是一個資料極度偏移的資料列。在Oracle統計量中，通常選擇直方圖histogram進行偏度描述。

注意：在Oracle 9i中，直方圖預設使用dbms_stats是不會收集的，需要手工的制定method_opts引數。在Oracle 10g之後，使用“column auto”作為method_opts引數的預設取值。

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');

PL/SQL procedure successfully completed

SQL> select column_name, num_distinct, NUM_BUCKETS, HISTOGRAM from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM

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

ID1 4 1 NONE

ID2 4 1 NONE

ID3 4 1 NONE

注意，預設是沒有生成直方圖的。主要原因在於需要使用一次id1作為條件列。

//使用一次條件列；

SQL> select count(*) from t where id1='D';

COUNT(*)

----------

10000

//重新收集一下統計量；

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');

PL/SQL procedure successfully completed

//發現統計量收集

SQL> select column_name, num_distinct, NUM_BUCKETS, HISTOGRAM from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM

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

ID1 4 4 FREQUENCY

ID2 4 1 NONE

ID3 4 1 NONE

當我們使用過一次id1條件之後，再次手機統計量，使用預設的auto引數，就生成id1列的頻度直方圖。

這裡也就揭示了Oracle在收集統計量直方圖auto選項的含義。當我們指定auto之後，Oracle會自動判斷是否對資料列生成直方圖、生成直方圖bullet的個數。如果這個列從來就沒有出現在SQL條件列中，也就不會被收集直方圖。

3、EXACT——不進行條件列替換

EXACT是cursor_sharing引數的預設選項，表示含義是不進行SQL條件自動繫結變數替換。

SQL> select name, value from v$parameter where name='cursor_sharing';

NAME VALUE

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

cursor_sharing EXACT

SQL> alter system flush shared_pool;

System altered

我們發出兩句SQL，分別使用資料取值差異很大的id1值。

SQL> select /*+ cursor_sharing_exact_demo */ count(*) from t where id1='P';

COUNT(*)

----------

8000

SQL> select /*+ cursor_sharing_exact_demo */ count(*) from t where id1='A';

COUNT(*)

----------

10

此時，父子游標library cache中情況如下：

SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like 'select /*+ cursor_sharing_exact_demo */%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ cursor_sharing_exact_demo */ count(*) from t where id1='P' 6trn7v99dngaj 1 1

select /*+ cursor_sharing_exact_demo */ count(*) from t where id1='A' dpcnym3gs7psp 1 1

在EXACT下，不會發生SQL字面值改寫的情況。如果兩個SQL的其他部分相同，只是where條件的取值有差異，Oracle是會將這兩個語句作為兩個單獨SQL進行硬解析，分別生成執行計劃。下面嘗試將兩個執行計劃抽取出。

SQL> select * from table(dbms_xplan.display_cursor('6trn7v99dngaj',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID 6trn7v99dngaj, child number 0

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

select /*+ cursor_sharing_exact_demo */ count(*) from t where id1='P'

Plan hash value: 2966233522

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

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

| 0 | SELECT STATEMENT | | | | 9 (100)| |

| 1 | SORT AGGREGATE | | 1 | 2 | | |

|* 2 | TABLE ACCESS FULL| T | 8000 | 16000 | 9 (12)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("ID1"='P')

Column Projection Information (identified by operation id):

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

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

43 rows selected

SQL> select * from table(dbms_xplan.display_cursor('dpcnym3gs7psp',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID dpcnym3gs7psp, child number 0

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

select /*+ cursor_sharing_exact_demo */ count(*) from t where id1='A'

Plan hash value: 555228874

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

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

| 0 | SELECT STATEMENT | | | | 1 (100)| |

| 1 | SORT AGGREGATE | | 1 | 2 | | |

|* 2 | INDEX RANGE SCAN| IDX_T_ID1 | 10 | 20 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID1"='A')

PLAN_TABLE_OUTPUT

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

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

43 rows selected

由於資料偏移的原因，藉助直方圖，Oracle對兩個SQL生成的執行計劃還存在差異。換句話說，在cursor_sharing為EXACT的情況下，只要SQL字面值存在差異，就不會進行任何SQL shared cursor。

本篇中我們主要介紹了cursor_sharing環境和EXACT取值含義，下篇我們集中介紹cursor_sharing引數的另兩個取值：FORCE和SIMILAR含義。

淺談cursor_sharing取值對SQL共享的影響（上）

相關文章