ITPub博客

首页 > 数据库 > Oracle > 初始化参数游标之cursor_sharing

初始化参数游标之cursor_sharing

原创 Oracle 作者:wuweilong 时间:2020-07-22 15:13:34 0 删除 编辑

初始化参数之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.01


2.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。  


三、总结:


FORCE和SIMIALR最大的区别在于,FORCE会把所有的谓词用变量代替,并且不管变量的值如何,一律重用第一条SQL语句,而SIMILAR会根据谓词的不同,来重新选择SQL的执行计划。


如果一个系统,它存在变量绑定的问题,并且这种问题已经影响到了系统的性能,这时候可以考虑将参数cursor_sharing的值设置为SIMILAR或FORCE来改善这种局面,不过在改成SIMILAR或FORCE都可能带来一些Bug以及很多未知的东西,所以需要慎用。


最后需要说明一点,对于OLTP系统,如果绑定变量情况不好的话,也许可以考虑通过设置这个参数来缓解一下问题;对于是在OLAP系统上,这个参数应该设置成EXACT,并且不应该使用绑定变量,因为在OLAP系统中,SQL的解析对于SQL的执行来看,话费的代价几乎可以忽略,而正确的SQL执行计划才是OLAP数据库最需要关注的。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20674423/viewspace-2706178/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE Associate; 12cOCM;11gOCM;MySQL OCP;PGCA;PGCE;KCA; KCP; 本人拥有十年以上运营商,广电传媒,能源,制造等行业DBA从业经验; 有着丰富的管理及运维经验,经历过国家级重点项目,省级标杆项目,新型试点项目

注册时间:2010-01-08

  • 博文量
    775
  • 访问量
    2495812