【cursor_sharing】cursor_sharing参数设置exact,similar,force的区别 - 数据库编程

parse count (failures) 55

硬解析的次数没有发生变化

BALLONTT@PROD> 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（子游标）。

在来看一下两次查询语句的执行计划：

BALLONTT@PROD> 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设置：

BALLONTT@PROD> alter system flush shared_pool;

System altered.

BALLONTT@PROD> alter system flush shared_pool;

System altered.

BALLONTT@PROD> alter system set cursor_sharing=similar;

System altered.

BALLONTT@PROD> show parameter cursor_sharing;

NAME TYPE VALUE

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

cursor_sharing string SIMILAR

收集一下统计信息和直方图：

BALLONTT@PROD> exec dbms_stats.gather_table_stats('BALLONTT','T', cascade => true,METHOD_OPT =>'FOR COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

BALLONTT@PROD> 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

进行测试：

BALLONTT@PROD> select count(*) from t where id='d';

COUNT(*)

----------

BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';

NAME VALUE

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

【cursor_sharing】cursor_sharing参数设置exact,similar,force的区别(四)