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.

How do I force a query to use bind-aware cursor sharing?

How do I force a query to use bind-aware cursor sharing?      By Allison on Feb 13, 2012 no_BI...
  • loryliu
  • loryliu
  • 2017年03月01日 16:37
  • 246

How to get cursor loc use QTP

  • 2010年08月26日 20:24
  • 14KB
  • 下载

浅谈cursor_sharing取值对SQL共享的影响(上)

声明:本篇实验思路感谢dbsnake老师指点。   在Oracle中,用户应用输入的SQL语句要进行所谓的Parse解析过程,用于生成执行计划,这也就是Query Optimizer的主要工作...

Oracle 11g 针对SQL性能的新特性(一)- Adaptive Cursor Sharing

Oracle 11g对SQL执行计划的生成过程做了很多改变,我们经常看到有客户抱怨,数据库在升级到11g以后,执行计划变得很不稳定,甚至难以预测。实际上,Oracle在最新版本中致力于让优化器变得更加...

ValueError: Attempt to have a second RNNCell use the weights of a variable scope already has weights

写在前面最近在实现seq2seq的时候遇到这样一个问题,我在同一段代码里需要定义两个不同的RNNcell,并且要分别使用dynamic_rnn去计算两个RNNCell的输出,虽然两个RNNCell我都...

warning C4996: 'strcpy': This function or variable may be unsafe. Consider using strcpy_s instead. To disable deprecation, use _

使用VS2005以上版本(VS2005、VS2008、VS2010)编译在其他编译器下正常通过的C语言程序,你可能会遇到类似如下的警告提示: 引用内容warning C4996: 'strcpy': ...

oracle11g中SQL优化(SQL TUNING)新特性之Adaptive Cursor Sharing (ACS)

oracle11g中最重要的特点之一——Adaptive Cursor Sharing (ACS),几乎完美的解决了之前版本不绑定不行,绑定也不行的尬尴问题,今天有机会对此特点再次进行研究和查阅资料,...
  • LHDZ_BJ
  • LHDZ_BJ
  • 2015年06月30日 14:10
  • 998

WPF Data Binding with LINQ to SQL

  • 2017年04月23日 13:33
  • 598KB
  • 下载

Set up JBPM5.4 Final Installer to use MS SQL Server 2008 using JTDS

A. What I Am Going To Do B. The Setup Steps C. Lets Install it   I have been usi...

初始化参数之cursor_sharing

原帖地址http://www.cnblogs.com/Richardzhu/archive/2013/01/21/2869837.html 一、Cursor_sharing简介: ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Use cursor_sharing_exact refused to SQL variable binding
举报原因:
原因补充:

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