在metalink ID 1169017.1文档中,提到建议客户不设置cursor_sharing=
similar,因为存在许多性能问题,该参数将在12g中废除该参数,具体给出的理由
如下:
1、对于语句中包含的范围查询(如between,
计不适合使用;
2、影响11g Adaptive Cursor sharing特性和CBO优化器
3、Similar可能产生的一个父游标, 多个子游标,其性能比多个父游标情况更加糟
糕(EXACT或 FORCE);
基于以上描述,在10g/11g进行以下的测试:
1、修改参数
alter session set cursor_sharing=similar;
2、创建测试表:
create table t1 as
select rownum id,
rpad('x',30) big_value
from all_objects
where rownum<=10;
3、统计分析
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade=>true,
estimate_percent=>null,
method_opt=> 'for all columns size 1');
--method_opt=> 'for columns id size 10');
end;
/
3、进行等式测试
select /*+ mytest */ count(*) from t1 where id=2;
select /*+ mytest */ count(*) from t1 where id=3;
select /*+ mytest */ count(*) from t1 where id=4;
select /*+ mytest */ count(*) from t1 where id=5;
select /*+ mytest */ count(*) from t1 where id=6;
select /*+ mytest */ count(*) from t1 where id=7;
select /*+ mytest */ count(*) from t1 where id=8;
select /*+ mytest */ count(*) from t1 where id=9;
select /*+ mytest */ count(*) from t1 where id=10;
4、查看各子游标情况
select count(hash_value) copies,
substrb(SQL_TEXT,1 ,80) SQL_TEXT
from v$sql
where substrb(sql_text,1,80) like '%mytest%'
group by substrb(sql_text,1,80)
order by copies asc;
COPIES SQL_TEXT
---------- --------------------------------------------------------------------------------
1 select /*+ mytest */ count(*) from t1 where id=:"SYS_B_0"
1 select /*+ mytest */ count(*) from t1 where id=:"SYS_B_0"
5、查看标志位状态
select
hash_value, IS_OBSOLETE,
IS_BIND_SENSITIVE,
IS_SHAREABLE,
substrb(SQL_TEXT,1 ,80) SQL_TEXT
from v$sql
where substrb(sql_text,1,80) like '%mytest%';
输出结果正常:
IS_OBSOLETE = N
IS_BIND_SENSITIVE=N
IS_SHAREABLE=Y
6、对范围查询测试(如between, < 等)
select /*+ mytest1 */ count(*) from t1 where id <2;
select /*+ mytest1*/ count(*) from t1 where id <3;
select /*+ mytest1*/ count(*) from t1 where id <4;
select /*+ mytest1*/ count(*) from t1 where id <5;
select /*+ mytest1*/ count(*) from t1 where id <6;
select /*+ mytest1*/ count(*) from t1 where id <7;
select /*+ mytest1*/ count(*) from t1 where id <8;
select /*+ mytest1*/ count(*) from t1 where id <9;
select /*+ mytest1*/ count(*) from t1 where id <10;
7、执行步骤4和步骤5检测
步骤4的输出结果:
COPIES SQL_TEXT
---------- --------------------------------------------------------------------------------
1 select /*+ mytest1 */ count(*) from t1 where id <:> 8 select /*+ mytest1*/ count(*) from t1 where id <:>
步骤5输出结果:
IS_OBSOLETE = N
IS_BIND_SENSITIVE=Y
IS_SHAREABLE=Y
8、对t1.id进行直方图统计分析,重新测步骤3脚本,并查看变化
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade=>true,
estimate_percent=>null,
--method_opt=> 'for all columns size 1');
method_opt=> 'for columns id size 10');
end;
/
9、重新执行步骤3测试,输出结果:
select /*+ mytest */ count(*) from t1 where id=2;
.....................
.....................
COPIES SQL_TEXT
---------- --------------------------------------------------------------------------------
1 select /*+ mytest */ count(*) from t1 where id=:"SYS_B_0"
8 select /*+ mytest */ count(*) from t1 where id=:"SYS_B_0"
IS_OBSOLETE = N
IS_BIND_SENSITIVE=Y
IS_SHAREABLE=Y
其结果发生了很大的变化
结论:在范围查询和直方图统计中不适合设置similar,并且我们应该从现在开始忘记该参数similar设置的存在。
文档参看:metalink ID 1169017.1 “ANNOUNCEMENT: Deprecating the cursor_sharing='SIMILAR' setting
similar,因为存在许多性能问题,该参数将在12g中废除该参数,具体给出的理由
如下:
1、对于语句中包含的范围查询(如between,
计不适合使用;
2、影响11g Adaptive Cursor sharing特性和CBO优化器
3、Similar可能产生的一个父游标, 多个子游标,其性能比多个父游标情况更加糟
糕(EXACT或 FORCE);
基于以上描述,在10g/11g进行以下的测试:
1、修改参数
alter session set cursor_sharing=similar;
2、创建测试表:
create table t1 as
select rownum id,
rpad('x',30) big_value
from all_objects
where rownum<=10;
3、统计分析
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade=>true,
estimate_percent=>null,
method_opt=> 'for all columns size 1');
--method_opt=> 'for columns id size 10');
end;
/
3、进行等式测试
select /*+ mytest */ count(*) from t1 where id=2;
select /*+ mytest */ count(*) from t1 where id=3;
select /*+ mytest */ count(*) from t1 where id=4;
select /*+ mytest */ count(*) from t1 where id=5;
select /*+ mytest */ count(*) from t1 where id=6;
select /*+ mytest */ count(*) from t1 where id=7;
select /*+ mytest */ count(*) from t1 where id=8;
select /*+ mytest */ count(*) from t1 where id=9;
select /*+ mytest */ count(*) from t1 where id=10;
4、查看各子游标情况
select count(hash_value) copies,
substrb(SQL_TEXT,1 ,80) SQL_TEXT
from v$sql
where substrb(sql_text,1,80) like '%mytest%'
group by substrb(sql_text,1,80)
order by copies asc;
COPIES SQL_TEXT
---------- --------------------------------------------------------------------------------
1 select /*+ mytest */ count(*) from t1 where id=:"SYS_B_0"
1 select /*+ mytest */ count(*) from t1 where id=:"SYS_B_0"
5、查看标志位状态
select
hash_value, IS_OBSOLETE,
IS_BIND_SENSITIVE,
IS_SHAREABLE,
substrb(SQL_TEXT,1 ,80) SQL_TEXT
from v$sql
where substrb(sql_text,1,80) like '%mytest%';
输出结果正常:
IS_OBSOLETE = N
IS_BIND_SENSITIVE=N
IS_SHAREABLE=Y
6、对范围查询测试(如between, < 等)
select /*+ mytest1 */ count(*) from t1 where id <2;
select /*+ mytest1*/ count(*) from t1 where id <3;
select /*+ mytest1*/ count(*) from t1 where id <4;
select /*+ mytest1*/ count(*) from t1 where id <5;
select /*+ mytest1*/ count(*) from t1 where id <6;
select /*+ mytest1*/ count(*) from t1 where id <7;
select /*+ mytest1*/ count(*) from t1 where id <8;
select /*+ mytest1*/ count(*) from t1 where id <9;
select /*+ mytest1*/ count(*) from t1 where id <10;
7、执行步骤4和步骤5检测
步骤4的输出结果:
COPIES SQL_TEXT
---------- --------------------------------------------------------------------------------
1 select /*+ mytest1 */ count(*) from t1 where id <:> 8 select /*+ mytest1*/ count(*) from t1 where id <:>
步骤5输出结果:
IS_OBSOLETE = N
IS_BIND_SENSITIVE=Y
IS_SHAREABLE=Y
8、对t1.id进行直方图统计分析,重新测步骤3脚本,并查看变化
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade=>true,
estimate_percent=>null,
--method_opt=> 'for all columns size 1');
method_opt=> 'for columns id size 10');
end;
/
9、重新执行步骤3测试,输出结果:
select /*+ mytest */ count(*) from t1 where id=2;
.....................
.....................
COPIES SQL_TEXT
---------- --------------------------------------------------------------------------------
1 select /*+ mytest */ count(*) from t1 where id=:"SYS_B_0"
8 select /*+ mytest */ count(*) from t1 where id=:"SYS_B_0"
IS_OBSOLETE = N
IS_BIND_SENSITIVE=Y
IS_SHAREABLE=Y
其结果发生了很大的变化
结论:在范围查询和直方图统计中不适合设置similar,并且我们应该从现在开始忘记该参数similar设置的存在。
文档参看:metalink ID 1169017.1 “ANNOUNCEMENT: Deprecating the cursor_sharing='SIMILAR' setting
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/354732/viewspace-690405/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/354732/viewspace-690405/