本篇内容介绍了“怎么理解数据库的初始化参数cursor_sharing”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
一、Cursor_sharing 简介：
这个参数是用来告诉Oracle在什么情况下可以共享游标，即SQL重用。
Cursor_sharing参数有3个值可以设置：
1)、EXACT：通常来说，exact值是Oracle推荐的，也是默认的，它要求SQL语句在完全相同时才会重用，否则会被重新执行硬解析操作。
2)、SIMILAR：similar是在Oracle认为某条SQL语句的谓词条件可能会影响到它的执行计划时，才会被重新分析，否则将重用SQL。
3)、FORCE：force是在任何情况下，无条件重用SQL。
备注：上面所说的SQL重用，仅仅是指谓词条件不同的SQL语句，实际上这样的SQL基本上都在执行同样的业务操作。
二、在Cursor_sharing参数值不同的时对SQL的影响：
2.1 创建实验环境：
----首先创建一张woo表----
WOO@woo> create table woo (id int,name varchar2(10));
Table created.
Elapsed: 00:00:00.06---产生一些数据----
WOO@woo> insert into woo values(1,'aa');
1 row created.
Elapsed: 00:00:00.00
WOO@woo> insert into woo values(2,'bb');
1 row created.
Elapsed: 00:00:00.00
WOO@woo> insert into woo values(3,'cc');
1 row created.
Elapsed: 00:00:00.00
WOO@woo> insert into woo values (4,'dd');
1 row created.
Elapsed: 00:00:00.00
WOO@woo> commit;
Commit complete.
Elapsed: 00:00:00.00
WOO@woo> select * from woo;
ID NAME
---------- ----------
1 aa
2 bb
3 cc
4 dd
Elapsed: 00:00:00.01---创建下面实验将要用到的三张表----
WOO@woo> create table woo_exact as select * from woo;
Table created.
Elapsed: 00:00:00.01
WOO@woo> create table woo_similar as select * from woo;
Table created.
Elapsed: 00:00:00.01
WOO@woo> create table woo_force as select * from woo;
Table created.
Elapsed: 00:00:00.00---查看当前session的trace文件的路径----
WOO@woo> SELECT d.Value || '/' || Lower(Rtrim(i.Instance, Chr(0))) || '_ora_' ||
2 p.Spid || '.trc' AS "trace_file_name"
3 FROM (SELECT p.Spid
4 FROM V$mystat m, V$session s, V$process p
5 WHERE m.Statistic# = 1
6 AND s.Sid = m.Sid
7 AND p.Addr = s.Paddr) p,
8 (SELECT t.Instance
9 FROM V$thread t, V$parameter v
10 WHERE v.Name = 'thread'
11 AND (v.Value = 0 OR t.Thread# = To_Number(v.Value))) i,
12 (SELECT VALUE
13 FROM V$parameter
14 WHERE NAME = 'user_dump_dest') d;
trace_file_name
-------------------------------------------------------
/DBSoft/diag/rdbms/woo/woo/trace/woo_ora_37746.trc
Elapsed: 00:00:00.012.2 cursor_sharing=exact的情况：
WOO@woo> alter session set cursor_sharing=exact;
Session altered.
Elapsed: 00:00:00.00
WOO@woo> alter session set sql_trace=true;
Session altered.
Elapsed: 00:00:00.00
WOO@woo> select * from woo_exact where id=1;
ID NAME
---------- ----------
1 aa
Elapsed: 00:00:00.00
WOO@woo> select * from woo_exact where id=2;
ID NAME
---------- ----------
2 bb
Elapsed: 00:00:00.01
WOO@woo> select * from woo_exact where id=3;
ID NAME
---------- ----------
3 cc
Elapsed: 00:00:00.00
WOO@woo> select * from woo_exact where id=1;
ID NAME
---------- ----------
1 aa
Elapsed: 00:00:00.00----从下面的查询可以看出执行了两次硬解析----
WOO@woo> select sql_text from v$sql where sql_text like 'select * from woo_exact where%';
SQL_TEXT
---------------------------------------------------------------------------------------
select * from woo_exact where id=1
select * from woo_exact where id=3
select * from woo_exact where id=2
Elapsed: 00:00:00.05
NAME VALUE
---------------------------------------------------------------- ----------
ADG parselock X get attempts 0
ADG parselock X get successes 0
parse time cpu 326
parse time elapsed 307
parse count (total) 56211
parse count (hard) 1681
parse count (failures) 10
parse count (describe) 0
8 rows selected.cursor_sharing=similar的情况：
WOO@woo> alter session set cursor_sharing=similar;
Session altered.
Elapsed: 00:00:00.00
WOO@woo> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.13
WOO@woo> select * from woo_similar where id=1;
ID NAME
---------- ----------
1 aa
Elapsed: 00:00:00.01
WOO@woo> select * from woo_similar where id=4;
ID NAME
---------- ----------
4 dd
Elapsed: 00:00:00.00
WOO@woo> select * from woo_similar where id=8;
no rows selected
Elapsed: 00:00:00.00----在这里可以看到执行两次SQL查询，只进行了一个硬解析----
WOO@woo> select sql_text from v$sql where sql_text like 'select * from woo_similar where %';
SQL_TEXT
--------------------------------------------------------------------------------------------------------
select * from woo_similar where id=:"SYS_B_0"
Elapsed: 00:00:00.02
WOO@woo> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
ADG parselock X get attempts 0
ADG parselock X get successes 0
parse time cpu 374
parse time elapsed 352
parse count (total) 57024
parse count (hard) 2006
parse count (failures) 10
parse count (describe) 0
8 rows selected.
Elapsed: 00:00:00.00
WOO@woo>对于SIMILAR的情况，如果CBO发现被绑定变量的谓词还有其他的执行计划可以选择时，如果谓词条件的值有变化，就将会产生一个新的子游标，而不是重用之前的SQL；如果谓词没有其他的执行计划可选择，则忽略谓词的值，重用之前的SQL。
上面的例子还不能足以说明该情况，接着下面的模拟：
cursor_sharing=force的情况
WOO@woo> alter session set cursor_sharing=force;
Session altered.
Elapsed: 00:00:00.00
WOO@woo> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.07
WOO@woo> alter session set sql_trace=true;
Session altered.
Elapsed: 00:00:00.02
WOO@woo> select * from woo_force where id=1;
ID NAME
---------- ----------
1 aa
Elapsed: 00:00:00.00
WOO@woo> select * from woo_force where id=4;
ID NAME
---------- ----------
4 dd
Elapsed: 00:00:00.00
WOO@woo> select * from woo_force where id=1;
ID NAME
---------- ----------
1 aa
Elapsed: 00:00:00.00----从下面的查询中可以看出只进行了一次硬解析，而且使用了绑定变量----
WOO@woo> select sql_text from v$sql where sql_text like 'select * from woo_force where %';
SQL_TEXT
-------------------------------------------------------------------------------------------
select * from woo_force where id=:"SYS_B_0"
Elapsed: 00:00:00.02
WOO@woo> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
ADG parselock X get attempts 0
ADG parselock X get successes 0
parse time cpu 379
parse time elapsed 355
parse count (total) 57385
parse count (hard) 2145
parse count (failures) 10
parse count (describe) 0
8 rows selected.
Elapsed: 00:00:00.01总结：force是在任何情况下，无条件重用SQL。
“怎么理解数据库的初始化参数cursor_sharing”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
