有關引數cursor_sharing=similar的測試

warehouse發表於2009-11-13

這是一個很重要的引數,也是一個不太容易理解的引數:

--下面是來自biti對該引數的測試和解釋:

當列上存在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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章