ITPub博客

首页 > 应用开发 > IT综合 > 有关参数cursor_sharing=similar的测试

有关参数cursor_sharing=similar的测试

原创 IT综合 作者:warehouse 时间:2009-11-13 15:25:46 0 删除 编辑

这是一个很重要的参数,也是一个不太容易理解的参数:

--下面是来自biti对该参数的测试和解释:

http://www.wangchao.net.cn/bbsdetail_60551.html

当列上存在histograms ,则每次是不同的值的时候都产生硬解析 ,若不存在 histograms,则不产生硬解析。换句话说,当表的字段被分析过存在histograms的时候,similar 的表现和exact一样,当表的字段没被分析,不存在histograms的时候,similar的表现和force一样。这样避免了一味的如force一样转换成变量形式,因为有histograms的情况下转换成变量之后就容易产生错误的执行计划,没有利用上统计信息。而exact呢,在没有histograms的情况下也要分别产生硬解析,这样的话,由于执行计划不会受到数据分布的影响(因为没有统计信息)重新解析是没有实质意义的。而similar则综合了两者的优点。

不过在10g当存在动态取样统计信息时有时候我们可能会看到貌似意想不到的结果...

[@more@]

SQL> analyze table tt delete statistics;

表已分析。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter cursor

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 30000
session_cached_cursors integer 0
SQL> alter session set cursor_sharing=similar;

会话已更改。
SQL> show parameter optimizer_mode

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
optimizer_mode string
ALL_ROWS
SQL> alter session set optimizer_mode=choose;

会话已更改。

SQL> col name format a30
SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
---------- ---------- ------------------------------
311 159 parse count (hard)
2131 159 parse count (total)

SQL> select count(*) from tt;

COUNT(*)
----------
11546

SQL> select count(*) from tt where id=1;

COUNT(*)
----------
3

SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
---------- ---------- ------------------------------
313 159 parse count (hard)
2139 159 parse count (total)

SQL> select count(*) from tt where id=2;

COUNT(*)
----------
1

SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
---------- ---------- ------------------------------
313 159 parse count (hard)
2141 159 parse count (total)

SQL> select count(*) from tt where id=4;

COUNT(*)
----------
11541

SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
---------- ---------- ------------------------------
313 159 parse count (hard)
2143 159 parse count (total)

SQL> col sql_text format a40
SQL> select sql_text ,parse_calls from v$sql where sql_text like 'select count(*
) from tt where id=%'
2 ;

SQL_TEXT PARSE_CALLS
---------------------------------------- -----------
select count(*) from tt where id=:"SYS_B 3
_0"


SQL>
--上面的试验我们设置了参数optimizer_mode为choose,而且删除了tt表上
的统计信息,此时cursor_sharing=similar其实发挥的是force的作用,这个时候
把所有的常量都转换为邦定变量了,确确实实共享了游标,减少了hard parse
--====================================
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。
--修改参数optimizer_mode=all_rows
SQL> alter session set optimizer_mode=all_rows;

会话已更改。

SQL> alter session set cursor_sharing=similar;

会话已更改。

SQL> col name format a30
SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
---------- ---------- ------------------------------
306 159 parse count (hard)
2156 159 parse count (total)

SQL> select count(*) from tt where id=1;

COUNT(*)
----------
3

SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
---------- ---------- ------------------------------
309 159 parse count (hard)
2165 159 parse count (total)

SQL> select count(*) from tt where id=2;

COUNT(*)
----------
1

SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
---------- ---------- ------------------------------
310 159 parse count (hard)
2168 159 parse count (total)

SQL> col sql_text format a40
SQL> select sql_text ,parse_calls from v$sql where sql_text like 'select count(*
) from tt where id=%';

SQL_TEXT PARSE_CALLS
---------------------------------------- -----------
select count(*) from tt where id=:"SYS_B 1
_0"

select count(*) from tt where id=:"SYS_B 1
_0"


SQL>
--上面试验显示参数optimizer_mode=all_rows之后,尽管tt表上依然没有statistics,
但是此时所有的常量都变成了邦定变量,但是他们没有共享cursor,而是每条sql都存在
hard parse,什么原因呢?
--=============================
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter optimizer_mode

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
SQL> show parameter cursor_sharing

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> alter session set cursor_sharing=similar;

会话已更改。

SQL> alter session set optimizer_dynamic_sampling=0;

会话已更改。

SQL> col name format a30
SQL> col value format 99999
SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
------ ---------- ------------------------------
311 159 parse count (hard)
2159 159 parse count (total)

SQL> select count(*) from tt where id=1;

COUNT(*)
----------
3

SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
------ ---------- ------------------------------
322 159 parse count (hard)
2270 159 parse count (total)

SQL> select count(*) from tt where id=4;

COUNT(*)
----------
11541

SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
------ ---------- ------------------------------
322 159 parse count (hard)
2272 159 parse count (total)

SQL> select count(*) from tt where id=3;

COUNT(*)
----------
1

SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
------ ---------- ------------------------------
322 159 parse count (hard)
2274 159 parse count (total)

SQL> col sql_text format a40
SQL> select sql_text ,parse_calls from v$sql where sql_text like 'select count(*) from tt where id=%';

SQL_TEXT PARSE_CALLS
---------------------------------------- -----------
select count(*) from tt where id=:"SYS_B 3
_0"


SQL>
--optimizer_dynamic_sampling设为0之后屏蔽了系统对tt的statistics动态取样,此时
cursor_sharing=similar又发挥了force的作用,把所有的常量都变成了绑定变量,而且他们
共享了cursor,减少了hard parse
--===================================
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter optimizer_mode

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
SQL> alter session set cursor_sharing=similar;

会话已更改。

SQL> analyze table tt compute statistics;

表已分析。
SQL> col name format a30
SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
------ ---------- ------------------------------
326 159 parse count (hard)
2441 159 parse count (total)
SQL> set autotrace on exp
SQL> select count(*) from tt where id=1;

COUNT(*)
----------
3


执行计划
----------------------------------------------------------
Plan hash value: 3133740314

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 9 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL| TT | 2887 | 5774 | 9 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ID"=1)

SQL> show parameter dynamic

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
optimizer_dynamic_sampling integer
2
SQL> set autotrace off
SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
------ ---------- ------------------------------
347 159 parse count (hard)
2565 159 parse count (total)

SQL> select count(*) from tt where id=4;

COUNT(*)
----------
11541
SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
------ ---------- ------------------------------
347 159 parse count (hard)
2567 159 parse count (total)

SQL> select count(*) from tt where id=5;

COUNT(*)
----------
0

SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
------ ---------- ------------------------------
347 159 parse count (hard)
2569 159 parse count (total)

SQL>
--=============================
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> analyze table tt delete statistics;

表已分析。

SQL> show parameter dynamic

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
SQL> show parameter optimizer_mode

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
SQL> show parameter cursor_sharing

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> alter session set cursor_sharing=similar;

会话已更改。

SQL> col name format a40
SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
------ ---------- ----------------------------------------
326 159 parse count (hard)
2347 159 parse count (total)

SQL> set autotrace on exp
SQL> select count(*) from tt where id=1;

COUNT(*)
----------
3


执行计划
----------------------------------------------------------
Plan hash value: 3133740314

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 9 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| TT | 3 | 39 | 9 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ID"=1)

Note
-----
- dynamic sampling used for this statement

SQL> set autotrace off
SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
------ ---------- ----------------------------------------
351 159 parse count (hard)
2620 159 parse count (total)

SQL> select count(*) from tt where id=2;

COUNT(*)
----------
1

SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
------ ---------- ----------------------------------------
352 159 parse count (hard)
2623 159 parse count (total)

SQL> select count(*) from tt where id=3;

COUNT(*)
----------
1

SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
------ ---------- ----------------------------------------
353 159 parse count (hard)
2626 159 parse count (total)

SQL> col sql_text format a40
SQL> select sql_text ,parse_calls from v$sql where sql_text like 'select count(
*) from tt where id=%';

SQL_TEXT PARSE_CALLS
---------------------------------------- -----------
select count(*) from tt where id=:"SYS_B 1
_0"

select count(*) from tt where id=:"SYS_B 1
_0"

select count(*) from tt where id=:"SYS_B 1
_0"


SQL> show parameter dynamic

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
SQL> alter session set optimizer_dynamic_sampling=0;

会话已更改。

SQL> select count(*) from tt where id=4;

COUNT(*)
----------
11541

SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
------ ---------- ----------------------------------------
359 159 parse count (hard)
2639 159 parse count (total)

SQL> select count(*) from tt where id=5;

COUNT(*)
----------
0

SQL> select a.value, s.sid, b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'parse count (total)',
6 'parse count (hard)'
7 )
8 and s.sid=(select distinct sid from v$mystat)
9 order by name;

VALUE SID NAME
------ ---------- ----------------------------------------
359 159 parse count (hard)
2641 159 parse count (total)

SQL> select sql_text ,parse_calls from v$sql where sql_text like 'select count(
*) from tt where id=%';

SQL_TEXT PARSE_CALLS
---------------------------------------- -----------
select count(*) from tt where id=:"SYS_B 1
_0"

select count(*) from tt where id=:"SYS_B 1
_0"

select count(*) from tt where id=:"SYS_B 1
_0"

select count(*) from tt where id=:"SYS_B 2
_0"

SQL_TEXT PARSE_CALLS
---------------------------------------- -----------


SQL>
--tt上只要没有统计信息而且激活了动态取样,此时oracle会
动态取样(其实最主要是列上有了统计信息),列上如果有统计信息
则 cursor_sharing=similar发挥的作用其实是exact,如果列上没有统计
信息, cursor_sharing=similar发挥的作用是force

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1028699/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5098436