Use cursor_sharing_exact refused to SQL variable binding

原创 2011年11月05日 16:44:44
 In the use cursor_sharing parameters, may cause some SQL children cursor too high, cause competition, the competition may include library cache lock of all kinds of Child Cursor distribution, release, scanning and lock.

For certain SQL can through the cursor_sharing_exact hint, forced SQL hard parse, avoid too many children cursor the negative impact on performance

The following is 10.2.0.1 in one of the simple test, two inquires the, have two children cursor:


[oracle@rac2 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 5 15:36:22 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn honcho/honcho
Connected.
SQL> create table t1 as select * from dba_objects;        

Table created.

SQL> commit;

Commit complete.

SQL> alter session set cursor_sharing=similar;

Session altered.

SQL> select count(*) from t1 where owner='HONCHO';

  COUNT(*)
----------
         2

SQL> c/HONCHO/SYS
  1* select count(*) from t1 where owner='SYS'
SQL> L
  1* select count(*) from t1 where owner='SYS'
SQL> /

  COUNT(*)
----------
     22957

SQL> select SQL_TEXT from v$sql where sql_text like 'select count(*) from t1%';

SQL_TEXT
--------------------------------------------------------------------------------
select count(*) from t1 where owner=:"SYS_B_0"
select count(*) from t1 where owner=:"SYS_B_0"

 

Once again inquires , produce the three children cursor:

SQL> select count(*) from t1 where owner='SYSTEM';

  COUNT(*)
----------
       454

SQL> select sql_text from v$sql where sql_text like 'select count(*) from t1%';

SQL_TEXT
--------------------------------------------------------------------------------
select count(*) from t1 where owner=:"SYS_B_0"
select count(*) from t1 where owner=:"SYS_B_0"
select count(*) from t1 where owner=:"SYS_B_0"

SQL> select SQL_TEXT,version_count from v$sqlarea where sql_text like 'select count(*) from t1%';

SQL_TEXT                                                     VERSION_COUNT
------------------------------------------------------------ -------------
select count(*) from t1 where owner=:"SYS_B_0"                           3

 

Designated cursor_sharing_exact hint, let SQL refused to force binding:

SQL> select /*+ cursor_sharing_exact */ count(*) from t1 where owner='SCOTT';

  COUNT(*)
----------
         6

SQL> select sql_text,version_count from v$sqlarea where sql_text like 'select count(*) from t1%';

SQL_TEXT                                                     VERSION_COUNT
------------------------------------------------------------ -------------
select count(*) from t1 where owner=:"SYS_B_0"                           3

SQL> select sql_text,version_count from v$sqlarea where sql_text like 'select%from t1%';

SQL_TEXT                                                                         VERSION_COUNT
-------------------------------------------------------------------------------- -------------
select /*+ cursor_sharing_exact */ count(*) from t1 where owner='SCOTT'                      1
select count(*) from t1 where owner=:"SYS_B_0"                                               3

SQL> select /*+ cursor_sharing_exact */ count(*) from t1 where owner='SYSMAN';

  COUNT(*)
----------
      1321

SQL> select sql_text,version_count from v$sqlarea where sql_text like 'select%from t1%';

SQL_TEXT                                                                         VERSION_COUNT
-------------------------------------------------------------------------------- -------------
select /*+ cursor_sharing_exact */ count(*) from t1 where owner='SYSMAN'                     1
select /*+ cursor_sharing_exact */ count(*) from t1 where owner='SCOTT'                      1
select count(*) from t1 where owner=:"SYS_B_0"                                               3

Cursor_sharing is a need to be very careful parameters, which may bring a lot of negative effects.

ionic+极光推送:Error: Variable(s) missing (use: --variable APP_KEY=value).

ionic 在安装极光推送插件 cordova plugin add jpush-phonegap-plugin –variable API_KEY=xxxxxx 时遇到这个报错, 解决方案: ...
  • zhangbw2016
  • zhangbw2016
  • 2017年08月24日 16:17
  • 433

WPF Binding相关的一些常见方式总结(八)

列表控件绑定到集合: 前端:
  • easybjy
  • easybjy
  • 2015年03月06日 16:49
  • 438

Android Data Binding 技术

Data Binding即数据绑定,Data Binding 库实现在布局文件中实现数据绑定申明,使数据的变化引起视图的自动更新,减少了逻辑代码,在Android中可以很方便的实现MVVM的开发模式。...
  • jmq_0000
  • jmq_0000
  • 2015年06月28日 00:21
  • 8326

CS0165号错误是什么

原文地址:https://support.unity3d.com/hc/en-us/articles/206470966-What-is-CS0165-症状当尝试进入Play模式时,我得到了如下错误:...
  • hoxily
  • hoxily
  • 2017年02月22日 10:16
  • 250

[常见错误 及 解决方案系列]USE " cc.exports.* = value " INSTEAD OF SET GLOBAL VARIABLE"

原创文章,欢迎转载,转载请注明:文章来自[寒江孤叶丶的Cocos2d-x之旅系列] 博客地址:http://blog.csdn.net/qq446569365 Cocos2d-x 3.5的lua项...
  • qq446569365
  • qq446569365
  • 2015年04月10日 11:43
  • 4924

USE " cc.exports.* = value " INSTEAD OF SET GLOBAL VARIABLE"

原文地址:http://blog.csdn.net/dl15600383645/article/details/46581835 Cocos2d-x 3.5的lua项目生成后,变成了MVC模...
  • u010536615
  • u010536615
  • 2016年11月19日 16:58
  • 1252

【iOS】Autosynthesized property警告解决方案

warning: Autosynthesized property 'myTimer' will use synthesized instance variable '_myTimer', not ...
  • knockheart
  • knockheart
  • 2014年02月09日 23:54
  • 4085

收集表的统计信息时并发过高

一台数据库服务器报 cpu loader 过高,查看当前等待事件,发现对应sql: /* SQL Analyze(1) */ select /*+ full(t) parallel(...
  • huangliang0703
  • huangliang0703
  • 2015年12月11日 13:11
  • 833

PHP Notice: Use of undefined constant variable

Notice: Use of undefined constant variable PHP开发问题解决
  • zxncvb
  • zxncvb
  • 2017年08月07日 10:29
  • 136

lua常见异常 USE " cc.exports. = value " INSTEAD OF SET GLOBAL VARIABLE"

[常见异常 及 解决方案系列]USE " cc.exports. = value " INSTEAD OF SET GLOBAL VARIABLE" www.MyException.Cn...
  • zwcwu31
  • zwcwu31
  • 2016年06月15日 17:44
  • 2673
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Use cursor_sharing_exact refused to SQL variable binding
举报原因:
原因补充:

(最多只允许输入30个字)