biti_rainy
關於cursor_sharing=similar
我們先看看在表沒有分析無統計資料情況下的表現
SQL>[color=red] alter session set cursor_sharing = similar; [/color]
Session altered.
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4948
parse time elapsed 4468
parse count (total) 170148
[color=red]parse count (hard) 1619 (硬分析次數)[/color]
parse count (failures) 80
SQL>[color=red] select count(*) from t where object_id = 1000; [/color]
COUNT(*)
----------
0
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4948
parse time elapsed 4468
parse count (total) 170172
parse count (hard) 1620
parse count (failures) 80
SQL> /
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4948
parse time elapsed 4468
parse count (total) 170176
[color=red]parse count (hard) 1620 [/color]
parse count (failures) 80
SQL> [color=red]select count(*) from t where object_id = 1000; [/color]
COUNT(*)
----------
0
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4948
parse time elapsed 4468
parse count (total) 170178
[color=red]parse count (hard) 1620 [/color]
parse count (failures) 80
SQL> [color=red]select count(*) from t where object_id = 1001; [/color]
COUNT(*)
----------
0
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4948
parse time elapsed 4468
parse count (total) 170180
[color=red]parse count (hard) 1620(即使object_id發生變化依然沒有硬解析)[/color]
parse count (failures) 80
我們再來看分析表和欄位資訊後的表現
SQL>[color=red] analyze table t1 compute statistics for table for columns object_id; [/color]
Table analyzed.
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4973
parse time elapsed 4495
parse count (total) 170982
[color=red]parse count (hard) 1640 [/color]
parse count (failures) 80
SQL>[color=red] select count(*) from t1 where object_id = 5000; [/color]
COUNT(*)
----------
0
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4973
parse time elapsed 4495
parse count (total) 170984
[color=red]parse count (hard) 1641 [/color]
parse count (failures) 80
SQL>[color=red] select count(*) from t1 where object_id = 5000; [/color]
COUNT(*)
----------
0
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4973
parse time elapsed 4495
parse count (total) 171008
[color=red]parse count (hard) 1641 (重複執行沒發生變化)[/color]
parse count (failures) 80
SQL>[color=red] select count(*) from t1 where object_id = 5001; [/color]
COUNT(*)
----------
0
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4973
parse time elapsed 4495
parse count (total) 171010
[color=red]parse count (hard) 1642 (當object_id變化的時候產生硬分析)[/color]
parse count (failures) 80
SQL>
SQL> select sql_text,child_number from v$sql where sql_text like 'select count(*) from t1 where%';
SQL_TEXT
--------------------------------------------------------------------------------
CHILD_NUMBER
------------
select count(*) from t1 where object_id = :"SYS_B_0"
0
select count(*) from t1 where object_id = :"SYS_B_0"
1
可以看出若存在object_id的 histograms ,則每次是不同的 值 的時候都產生硬解析 ,若不存在 histograms ，則不產生硬解析 。換句話說，當表的欄位被分析過存在histograms的時候，similar 的表現和exact一樣，當表的欄位沒被分析不存在histograms的時候,similar的表現和force一樣。這樣避免了一味地如force一樣轉換成變數形式，因為有hostograms的情況下轉換成變數之後就容易產生錯誤的執行計劃，沒有利用上統計資訊。而exact呢，在沒有hostograms的情況下也要分別產生硬解析，這樣的話，由於執行計劃不會受到資料分佈的影響（因為沒有統計資訊）重新解析是沒有實質意義的。而similar則綜合了兩者的優點。
原文參考
[url]http://blog.csdn.net/biti_rainy/archive/2004/07/12/learn_oracle_20040712_3.aspx[/url]
更多內容參考
[url]http://blog.csdn.net/biti_rainy[/url]