【cursor_sharing】cursor_sharing參數設定exact,similar,force的差別

Oracle中為了提高sql的執行效率，需要減少硬解析，實作shared cursor共享，最常見的方法是使用綁定變量，但很多時候由于各種原因未能在開發初期使用綁定變量，對于減少硬解析的目的，退而求其次地方法是設定cursor_sharing.

1.準備環境

實驗環境

[email protected]> 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 Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

使用腳本插入資料後：

[email protected]> desc t;

Name Null? Type

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

ID VARCHAR2(5)

NAME NUMBER(38)

BALLO[email protected]> select id,count(*) from t group by id;

ID COUNT(*)

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

d 6

a 10000

b 20000

c 20

[email protected]> create index ind_id on t(id);

Index created.

2.取值為exact時（預設）：

[email protected]> show parameter cursor_sharing;

NAME TYPE VALUE

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

cursor_sharing string EXACT

[email protected]> select count(*) from t where id='b';

COUNT(*)

----------

20000

[email protected]> select count(*) from t where id='d';

COUNT(*)

----------

[email protected]> select sql_text,sql_id,version_count,executions from v$sqlarea

where sql_text like 'select count(*) from t where id=%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select count(*) from t where id='d' b0gfs7u9r55rv 1 1

select count(*) from t where id='b' fqurbumy7bsg6 1 1

可以看到兩條查詢語句沒有使用綁定變量，有各自對應的sql_id，子遊标數均為1個。兩個sql查詢沒有任何關系。

檢視兩次sql的執行計劃：

[email protected]>select * from table(dbms_xplan.

display_cursor('b0gfs7u9r55rv',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID b0gfs7u9r55rv, child number 0

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

select count(*) from t where id='d'

Plan hash value: 3666266488

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

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

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

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

PLAN_TABLE_OUTPUT

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

|* 2 | INDEX RANGE SCAN| IND_ID | 14 | 28 | 1 (0)| 00:00:01 |

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

第一次查詢利用了索引。

[email protected]> select * from table(dbms_xplan.display_cursor('fqurbumy7bsg6',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID fqurbumy7bsg6, child number 0

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

select count(*) from t where id='b'

Plan hash value: 2966233522

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

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

| 0 | SELECT STATEMENT | | | | 15 (100)| |

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

PLAN_TABLE_OUTPUT

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

|* 2 | TABLE ACCESS FULL| T | 19783 | 39566 | 15 (0)| 00:00:01 |

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

第二次查詢利用了全表掃描

我們在更直覺地來看一下兩次sql查詢後的硬解析統計情況：

[email protected]> select count(*) from t where id='a';

COUNT(*)

----------

10000

[email protected]> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

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

parse time cpu 2133

parse time elapsed 4463

parse count (total) 54889

parse count (hard) 6579（硬解析數目）

parse count (failures) 52

[email protected]> select count(*) from t where id='c';

COUNT(*)

----------

[email protected]> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

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

parse time cpu 2134

parse time elapsed 4464

parse count (total) 54895

parse count (hard) 6580（硬解析數目）

parse count (failures) 52

硬解析數目再次加1

因為資料的巨大差異性，導緻了對兩次查詢有不同的執行計劃，這也說明在cursor設定為exact時，兩條sql語句如果存在一點不同，就不會共享cursor，而進行兩次硬解析。

3.設定為force時

Oracle對輸入的SQL值，會将where條件取值自動替換為綁定變量。以後在輸入相同的結構SQL語句時，會進行cursor sharing共享遊标

[email protected]> alter system set cursor_sharing=force;

System altered.

[email protected]> show parameter cursor_sharing;

NAME TYPE VALUE

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

cursor_sharing string FORCE

清除一下share pool中已緩存的cursor

[email protected]> alter system flush shared_pool;

System altered.

檢視硬解析情況：

[email protected]> select count(*) from t where id='b';

COUNT(*)

----------

20000

[email protected]> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

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

parse time cpu 2163

parse time elapsed 4506

parse count (total) 55097

parse count (hard) 6668

parse count (failures) 52

[email protected]> select count(*) from t where id='d';

COUNT(*)

----------

[email protected]> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

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

parse time cpu 2164

parse time elapsed 4507

parse count (total) 55101

parse count (hard) 6669

parse count (failures) 52

硬解析加1了，這不應該呀！！

[email protected]> select sql_text,child_number from v$sql where sql_text like 'select count(*) from t where id%';

SQL_TEXT CHILD_NUMBER

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

select count(*) from t where id='d' 0

select count(*) from t where id='b' 0

可以看到并沒有使用綁定變量，force的設定沒有生效。

解決辦法：應在設定cursor_sharing前，執行兩次下面語句：

alter system flush shared_pool;

[email protected]> alter system flush shared_pool;

System altered.

[email protected]> alter system flush shared_pool;

System altered.

[email protected]> alter system set cursor_sharing=force;

System altered.

設定好了，接着進行sql測試

[email protected]> select count(*) from t where id='d';

COUNT(*)

----------

[email protected]> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

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

parse time cpu 2216

parse time elapsed 4572

parse count (total) 55867

parse count (hard) 6910

parse count (failures) 55

[email protected]> select count(*) from t where id='b';

COUNT(*)

----------

20000

[email protected]> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

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

parse time cpu 2216

parse time elapsed 4572

parse count (total) 55869

parse count (hard) 6910

parse count (failures) 55

硬解析的次數沒有發生變化

[email protected]> select sql_text,sql_id,version_count,executions from v$sqlarea

2 where sql_text like 'select count(*) from t where%';

SQL_TEXT SQL_ID VERSION_COUN EXECUTIONS

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

select count(*) from t where id=:"SYS_B_0" g82ztj8p3q174 1 2

可以看到兩次查詢使用了綁定變量，将謂詞的值用sys_B_0代替。該語句執行了兩次，有一個child cursor（子遊标）。

在來看一下兩次查詢語句的執行計劃：

[email protected]> select * from table(dbms_xplan.display_cursor('g82ztj8p3q174',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID g82ztj8p3q174, child number 0

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

select count(*) from t where id=:"SYS_B_0"

Plan hash value: 3666266488

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

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

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

| 1 | SORT AGGREGATE | | 1 | 4 | | |

PLAN_TABLE_OUTPUT

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

|* 2 | INDEX RANGE SCAN| IND_ID | 6 | 24 | 1 (0)| 00:00:01 |

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

兩次的查詢使用了同一個執行計劃：索引掃描。這就是force的設定。

對與參數cusor_sharing設定為force時，根據實驗，我們可以得出下列結論：

Oracle對輸入的SQL值，會将where條件取值自動替換為綁定變量。以後在輸入相同的結構SQL語句時，會進行cursor sharing共享遊标；
在第一次進行自動替換綁定變量的時候，Oracle會依據bind peeking取值，擷取到一個執行計劃，對應成子遊标；
在以後的SQL語句中，隻要出現父遊标可共享的情況，Oracle都會強制使用生成的唯一子遊标進行sharing。不去在乎是不是對SQL是最優的執行計劃；
FORCE取值的規則思想很簡單，對SQL語句進行強制的綁定變量替換。使用第一次的bind peeking值生成執行計劃，之後全部使用這個執行計劃。這種方式實作了遊标共享，避免出現大量的library cache硬解析，限制一個父遊标的version_count數量。
如果這種SQL語句本身是“Good SQL”，也就是條件列分布比較平均，沒有出現過大的偏移分布。我們認為這種FORCE是很有益的。但是如果資料列分布不平均，這樣借用第一次輸入的bind peeking生成并且共享執行計劃就很成問題。我們說，在cursor_sharing取定FORCE遇到的潛在問題，和我們使用綁定變量時候使用的bind peeking值問題是相同的。

4.設定為similar時

當cursor_sharing設定為SIMILAR的時候，Oracle對沒有使用綁定變量的SQL字面語句都會進行處理，将where條件後自動替換為綁定變量；并在執行語句時，對每一個條件設定值，都會生成一個新的child cursor子遊标，比對一個新的執行計劃。

同上面的force設定：

[email protected]> alter system flush shared_pool;

System altered.

[email protected]> alter system flush shared_pool;

System altered.

[email protected]> alter system set cursor_sharing=similar;

System altered.

[email protected]> show parameter cursor_sharing;

NAME TYPE VALUE

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

cursor_sharing string SIMILAR

收集一下統計資訊和直方圖：

[email protected]> exec dbms_stats.gather_table_stats('BALLONTT','T', cascade => true,METHOD_OPT =>'FOR COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

[email protected]> select column_name, num_distinct, NUM_BUCKETS, HISTOGRAM from dba_tab_col_statistics

2 where wner='BALLONTT' and table_name='T';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM

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

ID 4 4 FREQUENCY

NAME 10000 1 NONE

進行測試：

[email protected]> select count(*) from t where id='d';

COUNT(*)

----------

[email protected]> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

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

parse time cpu 2412

parse time elapsed 4792

parse count (total) 62015

parse count (hard) 8059

parse count (failures) 64

[email protected]> select count(*) from t where id='b';

COUNT(*)

----------

20000

[email protected]> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

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

parse time cpu 2412

parse time elapsed 4792

parse count (total) 62017

parse count (hard) 8060

parse count (failures) 64

硬解析次數加1

[email protected]> select sql_text,sql_id,child_number,executions from v$sql where sql_text like 'select count(*) from t where id%';

SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS

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

select count(*) from t where id=:"SYS_B_0" g82ztj8p3q174 0 1

select count(*) from t where id=:"SYS_B_0" g82ztj8p3q174 1 1

[email protected]> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_Text like 'select count(*) from t where id%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select count(*) from t where id=:"SYS_B_0" g82ztj8p3q174 2 2

使用了綁定變量，但是兩次查詢生成了兩個子遊标(child cursor)，即對應兩個執行計劃。

為了更清楚地看到兩次sql使用了兩個不同的子遊标，即對應着使用了兩個執行計劃，我們再來分步查詢一次：

[email protected]> select count(*) from t where id='d';

COUNT(*)

----------

[email protected]> select sql_text,sql_id,child_number from v$sql where sql_text like '%select count(*) from t where id%';

SQL_TEXT SQL_ID CHILD_NUMBER

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

select count(*) from t where id=:"SYS_B_0" g82ztj8p3q174 0

檢視執行計劃：

[email protected]> select * from table(dbms_xplan.display_cursor('g82ztj8p3q174',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID g82ztj8p3q174, child number 0

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

select count(*) from t where id=:"SYS_B_0"

Plan hash value: 3666266488

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

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

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

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

PLAN_TABLE_OUTPUT

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

|* 2 | INDEX RANGE SCAN| IND_ID | 1 | 2 | 1 (0)| 00:00:01 |

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

執行計劃為索引路徑

再次查詢：

[email protected]> select count(*) from t where id='b';

COUNT(*)

----------

20000

[email protected]> select sql_text,sql_id,child_number from v$sql where sql_text like 'select count(*) from t where id%'

2 ;

SQL_TEXT SQL_ID CHILD_NUMBER

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

select count(*) from t where id=:"SYS_B_0" g82ztj8p3q174 0

select count(*) from t where id=:"SYS_B_0" g82ztj8p3q174 1

檢視子遊标（chilid_number=1）的執行計劃：

[email protected]> select * from table(dbms_xplan.display_cursor('g82ztj8p3q174',1,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID g82ztj8p3q174, child number 1

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

select count(*) from t where id=:"SYS_B_0"

Plan hash value: 2966233522

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

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

| 0 | SELECT STATEMENT | | | | 15 (100)| |

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

PLAN_TABLE_OUTPUT

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

|* 2 | TABLE ACCESS FULL| T | 20081 | 40162 | 15 (0)| 00:00:01 |

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

執行計劃為全表掃描，生成了一個新的執行計劃。

由此我們可以得到結論：

當cursor_sharing設定為SIMILAR的時候，Oracle對沒有使用綁定變量的SQL字面語句都會進行處理，将where條件後自動替換為綁定變量；
在執行語句是，對每一個條件設定值，都會生成一個新的child cursor子遊标，與父遊标相對應。當一個語句輸入的時候，如果之前存在過相同條件值的SQL子遊标，就共享該子遊标。否則生成一個新的child cursor，生成一個比對的執行計劃；
SIMILAR是FORCE的進化版。在SIMILAR模式下，Oracle對遊标共享的條件變得比較敏感。如果綁定變量值發生變化，就意味着執行計劃可能存在不比對的情況。是以索性Oracle對每一個新的值都bind peeking一下，生成執行計劃。而執行計劃遊标的共享隻在相同綁定變量的時候才發生。
這個與FORCE相比，cursor_sharing=SIMILAR的确緩解了由于bind peeking單次帶來的執行計劃不比對問題。但是會引入兩個新問題：

如果對應條件列的取值相對較少，這樣生成執行計劃的個數起碼是可以控制的。如果是一種連續取值情況或者對應取值很多，必然引起parent cursor對應的child cursor數目增多，每次從child cursor列中周遊的時間增加，latch和pin發生的時間增多。這也是similar取值是一個常見的問題；
生成child cursor的标準不是是否執行計劃相同，而是綁定變量值相同。這樣如果資料分布較為平均，所有值對應的執行計劃都是相同的。那麼生成很多的子遊标執行計劃必然是相同的。這樣又會帶來性能和其他一些問題。

在謂詞條件的列後沒有直方圖時，其作用和force類似，會共享子遊标（可以删除where條件後列上的直方圖重複最後一步實驗驗證）

Cursor_sharing設定為similar，存在一些BUG，即使是force也同樣有BUG存在。例如導緻EXPDP的導出時間變成，物化視圖的更新變慢等。Oracle 并不建議你設定此參數。在12C中ORACLE将廢除similar的設定。

ballontt

2013/12/25

---The End---

微網誌：weibo.com/ballontt 如需轉載，請标明出處和連結，謝謝！

【cursor_sharing】cursor_sharing參數設定exact,similar,force的差別

ballontt

2013/12/25

---The End---

繼續閱讀

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

賦予使用者dba權限--解釋以上：

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

為什麼要選擇UniDAC

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

windows不能在本地計算機上運作oracleDbConsoleorcl

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離

Oracle 批量查詢傳入List 傳回List

【cursor_sharing】cursor_sharing參數設定exact,similar,force的差別

ballontt 2013/12/25

---The End---

繼續閱讀

ballontt

2013/12/25