有關引數cursor_sharing=similar的測試
這是一個很重要的引數，也是一個不太容易理解的引數：
--下面是來自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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE中Cursor_sharing引數詳解Oracle
- 介面測試 - 引數測試
- 初始化引數遊標之cursor_sharing
- 【測試】Android Studio 相關下載及引數Android
- Oracle JDBC ResultSet引數測試OracleJDBC
- 介面測試-引數校驗
- 有關測試開發的點在哪
- MySQL5.7統計資訊更新的相關引數解釋和測試MySql
- .net持續整合測試篇之Nunit引數化測試
- 滲透測試學習之報告測試引數五
- Jmeter模板化引數併發測試JMeter
- [20180308]測試ARG_MAX引數.txt
- 有關oracle external table的一點測試。Oracle
- 效能測試 有關 service mesh 的問題
- 關於sklearn下class_weight引數的一點原始碼閱讀與測試原始碼
- 介面測試並不只是測試引數和返回值
- 記學習滲透測試之報告測試引數二
- 記學習滲透測試之報告測試引數一
- 記學習滲透測試之報告測試引數四
- 記學習滲透測試之報告測試引數三
- [20190409]latch get 引數where and why測試.txt
- Python測試框架pytest命令列引數用法Python框架命令列
- JSR規範，系統引數測試大全JS
- pytest介面測試之fixture傳引數request
- 求引數遍歷疊加與結果之間強關係的探索測試思路
- 有沒有關於敏捷測試的書籍推薦呢？敏捷測試
- MySQL 5.6 innodb_io_capacity引數效能測試MySql
- [20220228]測試Max_idle_blocker_time 引數.txtBloC
- ab壓力測試命令及引數詳解
- 介面測試--獲取動態引數進階
- python疑問5：位置引數，預設引數，可變引數，關鍵字引數，命名關鍵字引數區別Python
- thinkphp，onethink都沒有測試相關的內容PHP
- 有關介面測試的用例設計問題
- ORACLE並行相關的引數Oracle並行
- Spark的相關引數配置Spark
- 【Java面試】資料庫連線池有什麼用？它有哪些關鍵引數？Java面試資料庫
- 測試資料放 yaml 檔案，不同介面存在關聯引數怎麼做更好YAML
- Java對比有引數和無引數Java
- OTDR光時域反射儀測試引數設定反射