直方圖Histograms與CRUSOR_SHARING

有時候可以將cursor_sharing設定為similar來降低由於沒有使用繫結變數導致硬解析過高的問題。
可是它有個副作用，如果等值查詢的列上存在直方圖，ORACLE就會認為=後面的變數產生的CURSOR都是危險的，進而導致硬解釋。
且version_count增加。
簡單實驗如下：
建立測試表
SQL>create table test as select * from dba_objects;

分析表
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                TABNAME    => 'TEST',
                                CASCADE    => TRUE,
                                METHOD_OPT => 'FOR ALL COLUMNS SIZE auto');
END;
檢視Dba_Histograms檢視
SQL>SELECT * FROM Dba_Histograms WHERE table_name='TEST';（輸出結果略）
發現object_id列上已經存在了直方圖。

SQL>alter system set cursor_sharing='similar';
查詢test表：
SQL>select * from test where object_id=1;

透過V$SQL得到這個語句的sql_id，然後可以透過v$sqlarea的version_count欄位，觀察變化。
object_id的改變都會導致version_ count加1。
SQL>SELECT version_count FROM v$sqlarea WHERE sql_id='dd0u25bgmr2rj'；
VERSION_COUNT
-------------
            1
SQL>select * from test where object_id=15;
SQL>SELECT version_count FROM v$sqlarea WHERE sql_id='dd0u25bgmr2rj'；
VERSION_COUNT
-------------
            2
SQL>select * from test where object_id=10;
SQL>SELECT version_count FROM v$sqlarea WHERE sql_id='dd0u25bgmr2rj'；
VERSION_COUNT
-------------
            3

我們取消直方圖看看是什麼效果：
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname => 'TEST1',cascade => TRUE,method_opt => 'FOR COLUMNS OBJECT_ID SIZE 1');

繼續實驗：

SQL>select * from test where object_id=30;
SQL>SELECT version_count FROM v$sqlarea WHERE sql_id='dd0u25bgmr2rj'；
VERSION_COUNT
-------------
            3
SQL>select * from test where object_id=50;
SQL>SELECT version_count FROM v$sqlarea WHERE sql_id='dd0u25bgmr2rj'；
VERSION_COUNT
-------------
            3
SQL>select * from test where object_id=60;
SQL>SELECT version_count FROM v$sqlarea WHERE sql_id='dd0u25bgmr2rj'；
VERSION_COUNT
-------------
            3


version_count不再變化了。


有幾個概念需要解釋一下，方便菜鳥級的理解本貼：
一個語句能否被共享，需要滿足parent cursor和child cursor都可以被共享才行。

parent cursor：儲存的是sql語句的文字。
chlid     cursor：儲存的是解析計劃和環境資訊。


在本案例中，都是chlid cursor沒有被共享導致的硬解析。

version_count過高的壞處就是，可能會導致library cache latch競爭。每次執行sql前都需要到library cache中先查詢parent cursor是否可以共享，如果可以共享，繼續查詢child cursor是否可以共享，如果可以就是軟解析，否則為硬解析。這個過程裡，是需要獲得library cache latch的，如果version_count過多，就會導致搜尋過程變長，佔用library cache latch時間就會變長，這在高併發的資料庫中很可能就是災難（latch的序列機制。）

[ 本帖最後由 wei-xh 於 2010-5-2 20:42 編輯 ]