开发者社区> lfreeali> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

[20140802]cursor_sharing=similar.txt

简介: [20140802]cursor_sharing=similar.txt --晚上看了http://www.dbaxiaoyu.com/archives/2248,在 cursor_sharing='similar'的情况下,会出现N多子光标(如果查询字段有直方 --图的情况下).
+关注继续查看

[20140802]cursor_sharing=similar.txt

--晚上看了http://www.dbaxiaoyu.com/archives/2248,在 cursor_sharing='similar'的情况下,会出现N多子光标(如果查询字段有直方
--图的情况下).实际上oracle在以后的版本会淘汰调cursor_sharing=similar的情况.

SCOTT@test01p> @ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> alter session set cursor_sharing='similar';
Session altered.

SCOTT@test01p> create table t as select * from dba_objects;
Table created.

SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);
PL/SQL procedure successfully completed.

SCOTT@test01p> select column_name,histogram from DBA_tab_cols where owner=user and table_name='T' and column_name='OBJECT_ID';
COLUMN_NAME          HISTOGRAM
------------------- ---------------
OBJECT_ID            NONE
--并没有建立直方图在字段OBJECT_ID.

select /*TEST*/ object_name from t where object_id=10;
select /*TEST*/ object_name from t where object_id=11;
select /*TEST*/ object_name from t where object_id=12;

SCOTT@test01p> select sql_text,version_count,address from v$sqlarea where sql_text like 'select /*TEST*/ object_name from t%';
SQL_TEXT                                                      VERSION_COUNT ADDRESS
------------------------------------------------------------- ------------- ----------------
select /*TEST*/ object_name from t where object_id=:"SYS_B_0"             1 000007FF5AFC6B98
--version_count=1

SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1 for columns object_id size 254',no_invalidate=>false);
PL/SQL procedure successfully completed.

SCOTT@test01p> select column_name,histogram from DBA_tab_cols where owner=user and table_name='T' and column_name='OBJECT_ID';
COLUMN_NAME          HISTOGRAM
-------------------- ---------------
OBJECT_ID            HYBRID

--HYBRID 第1次看到????

SCOTT@test01p> alter system flush SHARED_POOL;
System altered.

select /*TEST*/ object_name from t where object_id=210;
select /*TEST*/ object_name from t where object_id=211;
select /*TEST*/ object_name from t where object_id=212;

SCOTT@test01p> select sql_text,version_count,address from v$sqlarea where sql_text like 'select /*TEST*/ object_name from t%';
SQL_TEXT                                                      VERSION_COUNT ADDRESS
------------------------------------------------------------- ------------- ----------------
select /*TEST*/ object_name from t where object_id=:"SYS_B_0"             1 000007FF59630E70

-- 12c下cursor_sharing='similar',并没有出现子光标并不奇怪,说不定oracle已经淘汰了cursor_sharing='similar'这种情况,实际上
-- 我猜测使用的是cursor_sharing='force'.

-- 在11g下重复测试:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> alter session set cursor_sharing='similar';
Session altered.

SCOTT@test> create table t as select * from dba_objects;
Table created.

SCOTT@test> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);
PL/SQL procedure successfully completed.

SCOTT@test> select column_name,histogram from DBA_tab_cols where owner=user and table_name='T' and column_name='OBJECT_ID';
COLUMN_NAME          HISTOGRAM
-------------------- ---------------
OBJECT_ID            NONE

select /*TEST*/ object_name from t where object_id=10;
select /*TEST*/ object_name from t where object_id=11;
select /*TEST*/ object_name from t where object_id=12;

SCOTT@test> select sql_text,version_count,address from v$sqlarea where sql_text like 'select /*TEST*/ object_name from t%';
SQL_TEXT                                                      VERSION_COUNT ADDRESS
------------------------------------------------------------- ------------- ----------------
select /*TEST*/ object_name from t where object_id=:"SYS_B_0"             1 00000000B136C1D0
--version_count=1

SCOTT@test> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1 for columns object_id size 254',no_invalidate=>false);
PL/SQL procedure successfully completed.

SCOTT@test> select column_name,histogram from DBA_tab_cols where owner=user and table_name='T' and column_name='OBJECT_ID';
COLUMN_NAME          HISTOGRAM
-------------------- ---------------
OBJECT_ID            HEIGHT BALANCED

--建立了直方图。
select /*TEST*/ object_name from t where object_id=210;
select /*TEST*/ object_name from t where object_id=211;
select /*TEST*/ object_name from t where object_id=212;

SCOTT@test> select sql_text,version_count,address from v$sqlarea where sql_text like 'select /*TEST*/ object_name from t%';
SQL_TEXT                                                      VERSION_COUNT ADDRESS
------------------------------------------------------------- ------------- ----------------
select /*TEST*/ object_name from t where object_id=:"SYS_B_0"             1 00000000B1AA9010

-- 11.2.0.3版本cursor_sharing='similar',并没有出现子光标并不奇怪,说不定oracle已经淘汰了cursor_sharing='similar'这种情况,实际上
-- 我猜测使用的是cursor_sharing='force'.作者出现问题的是11.2.0.1版本。估计oracle已经淘汰cursor_sharing='similar'的情况。

-- 再多做一些测试,在10.2.0.4下重复测试:

10G> @ver
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

10G> alter session set cursor_sharing='similar';
Session altered.

10G> create table t as select * from dba_objects;
Table created.

10G> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1 for columns object_id size 254',no_invalidate=>false);
PL/SQL procedure successfully completed.

10G> select column_name,histogram from DBA_tab_cols where owner=user and table_name='T' and column_name='OBJECT_ID';
COLUMN_NAME          HISTOGRAM
-------------------- ---------------
OBJECT_ID            HEIGHT BALANCED

--建立了直方图。
select /*TEST*/ object_name from t where object_id=210;
select /*TEST*/ object_name from t where object_id=211;
select /*TEST*/ object_name from t where object_id=212;

10G> select sql_id,sql_text,version_count,address from v$sqlarea where sql_text like 'select /*TEST*/ object_name from t%';
SQL_ID        SQL_TEXT                                                      VERSION_COUNT ADDRESS
------------- ------------------------------------------------------------- ------------- ----------------
17a488u6rzrjr select /*TEST*/ object_name from t where object_id=:"SYS_B_0"             3 000000007C993A28
           
--可以发现出现了子光标,version_COUNT=3.如果大量的这些文字变量执行,会产生了大量的子光标。

10G> @share 17a488u6rzrjr
old  15:           and q.sql_id like ''&1''',
new  15:           and q.sql_id like ''17a488u6rzrjr''',
SQL_TEXT                       = select /*TEST*/ object_name from t where object_id=:"SYS_B_0"
SQL_ID                         = 17a488u6rzrjr
ADDRESS                        = 000000007C993A28
CHILD_ADDRESS                  = 00000000772D5918
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = select /*TEST*/ object_name from t where object_id=:"SYS_B_0"
SQL_ID                         = 17a488u6rzrjr
ADDRESS                        = 000000007C993A28
CHILD_ADDRESS                  = 0000000077164660
CHILD_NUMBER                   = 1
--------------------------------------------------
SQL_TEXT                       = select /*TEST*/ object_name from t where object_id=:"SYS_B_0"
SQL_ID                         = 17a488u6rzrjr
ADDRESS                        = 000000007C993A28
CHILD_ADDRESS                  = 000000007C8CD510
CHILD_NUMBER                   = 2
--------------------------------------------------
PL/SQL procedure successfully completed.

--奇怪!并没有看见说明不能共享的原因。


--总结:
至少11.2.0.3已经淘汰了cursor_sharing='similar',实际上使用的是force,11G下force+ACS组合很好地解决以前的问题。避免这种情况最佳方法是:
1. 很好合理的使用绑定变量。我一直认为这个是衡量一个项目好坏的一个重要的标准。
2.在需要的字段建立直方图,而不是由oracle的自动统计收集来分析数据,或者修改缺省method_opt参数。


==补充测试:10.2.0.4下:
10G> @ver
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

10G> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1 ',no_invalidate=>false);
PL/SQL procedure successfully completed.

10G> select column_name,histogram from DBA_tab_cols where owner=user and table_name='T' and column_name='OBJECT_ID';
COLUMN_NAME          HISTOGRAM
-------------------- ---------------
OBJECT_ID            NONE

select /*TEST*/ object_name from t where object_id=210;
select /*TEST*/ object_name from t where object_id=211;
select /*TEST*/ object_name from t where object_id=212;

10G> select sql_id,sql_text,version_count,address from v$sqlarea where sql_text like 'select /*TEST*/ object_name from t%';
SQL_ID        SQL_TEXT                                                      VERSION_COUNT ADDRESS
------------- ------------------------------------------------------------  ------------- ----------------
17a488u6rzrjr select /*TEST*/ object_name from t where object_id=:"SYS_B_0"             1 000000007EAF6A60
--可见没有建立直方图没有问题。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
JavaScript---网络编程(11)--DHTML技术演示(4)-单选框/下拉菜单/添加文件-2
JavaScript---网络编程(11)--DHTML技术演示(4)-单选框/下拉菜单/添加文件
28 0
JSP编译成Servlet(三)JSP编译后的Servlet
JSP编译后的Servlet类会是怎样的呢?他们之间有着什么样的映射关系?在探讨JSP与Servlet之间的关系时先看一个简单的HelloWorld.jsp编译成HelloWorld.java后会是什么样。
1262 0
[20140418]使用dgmgrl管理dataguard(6).txt
[20140418]使用dgmgrl管理dataguard(6).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.
1099 0
[20140418]使用dgmgrl管理dataguard(7).txt
[20140418]使用dgmgrl管理dataguard(7).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.
709 0
[20140218]关于SDO_GEORASTER的问题.txt
[20140218]关于SDO_GEORASTER的问题.txt 如果你跟踪一个DDL建表的过程,使用10046,如果看到跟踪文件包含如下内容: SCOTT@test> @ver BANNER -------------------------...
930 0
cursor_sharing设置为similar 的弊端
将cursor_sharing设置为similar会产生许多问题:1、对于语句中包含的范围查询(如between, 2、影响11g Adaptive Cursor sharing特性和CBO优化器3、Similar可能产生的一个父游标, 多个子游标,其性能比多个...
636 0
ExtJS 4 动态加载的演示例子
官方提供四个围绕于Ext JS类加载机制的例子供大家演示 、下载 (例子已汉化),如下图所示:   进入在线演示:http://playen.ajaxjs.com/playen/ext/LoaderDemo/index.html 下载:http://playen.ajaxjs.com/playen/ext/LoaderDemo/LoaderDemo.zip 起初页面加载的只是基本调用的代码,而实际的代码必须经过动态请求从服务端获取回来。
758 0
+关注
lfreeali
熟悉oracle数据库性能优化,rman备份,数据库恢复技术.
2121
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载