Use cursor_sharing_exact refused to SQL variable binding
来源:互联网 发布:乐视手机mac怎么修改 编辑:程序博客网 时间:2024/06/16 09:22
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.
- Use cursor_sharing_exact refused to SQL variable binding
- How to Use Temporary Variable
- Use Ultraedit to extract variable declaration
- How to Use Input-type Variable
- How to Use Output-type Variable
- 使用 cursor_sharing_exact 拒绝SQL变量绑定
- How to use bindings-generator to generate lua binding automatically
- hadoop Problem binding to :9000 : Address already in use
- Do not use shared variable to check thread status
- What is volatile variable in Java - When to use
- TensorLow: FailedPreconditionError:Attempting to use uninitialized value Variable
- How To Use SQL XML ?
- (MS SQL)HOW TO USE FETCH
- use dynamic sql to output the result
- How to use the Sql Tuning Advisor
- WSDL Binding style and use
- Use a variable reference “inside” another variable
- How to let "test2.c" use the variable in "test1.c"
- SandBox中方便、精确地选择物品
- net中通过配置WebConfig设置文件上传的属性
- POJ3245 神题 二分+DP+单调队列+堆/线段树/平衡树
- JavaBean中的<jsp:useBean>标签
- SQL语句的基本语法一
- Use cursor_sharing_exact refused to SQL variable binding
- Java 理论与实践: 正确使用 Volatile 变量
- 微软:Windows 8预览版下载量1天内突破50万
- Graphics->BitmapPixels
- SQL语句的基本语法二
- hibernate配置二级缓存出现的问题
- JavaBean中的<jsp:getProperty>标签与<jsp:setProperty>标签
- ubuntu 下安装mysql
- 海量数据面试题整理