幾種常見重新硬解析的原因

經常有客戶提說某個SQL的執行計劃變差了，導致出現了性能問題，進而就問為啥解析出了新的
執行計劃。首先可以肯定突然出現了新的執行計劃表明sql進行了重新硬解析(注意重新硬解析不一定
產生新的執行計劃)，那么為啥好好的sql需要重新硬解析呢？今天我們就列舉幾種常見的原因：

1.自動收集統計信息
為了保證sql的最佳執行性能，oracle需要找到一個最優的執行計劃，基于CBO模式的優化器必須
要知道最新的統計信息，例如條數，block數量，某個字段的選擇率等，所以oracle每天凌晨都會運行一
個自動收集統計信息的job，來收集那些變化超過10%的表的最新統計信息，收集完成之后，理所當然
要對新來的sql進行使用，所以就需要進行硬解析。oracle 收集某個表統計信息后默認是不會立即invalid
所有相關的cursor，因為這樣做太暴力，會引發硬解析相關的性能問題，所以巧妙的設計了一下，當某個相關sql執行的時候發現一個依賴對象最近收集過統計信息，便隨機的打個一個時間戳，這個時間戳是
5個小時內某個時間戳，等到下次sql解析的時候若是發現了這個時間戳就會和當前時間進行比較，若是超過就說明已經到期，立即進行硬解析，否則還繼續進行軟解析。

2.沒有符合條件的child cursor 典型的如bind mismatch (其他原因可以參考v$sql_shared_cursor)
當某個sql使用了綁定變量的時候,ORACLE 會記錄cursor第一次硬解析時候的綁定變量的相關metadata，
當后續解析的時候便會進行檢查，若是發現綁定變量類型或長度不匹配就會進行重新解析，下面我們就用一個小例子驗證一下：

CREATE TABLE MAOB_T AS SELECT FROM DBA_TABlES ;
VAR B1 char(20);
EXEC :B1 := 'MAOB';
SELECT COUNT() FROM MAOB_T WHERE TABLE_NAME=:B1;

查看cursor情況
Select sql_id,child_number,first_load_time from V$SQL WHERE SQL_TEXT LIKE '%COUNT%MAOB_T%'
4v22rgk83gjnc 0 2017-12-15/22:52:46 <<可以看到新cursor已經生成
把綁定變量類型變成varchar2，sql文本不變，再次執行：
VAR B1 VARCHAR2(20);
EXEC :B1 := 'MAOB';
SELECT COUNT(*) FROM MAOB_T WHERE TABLE_NAME=:B1;

執行上述語句后再次查看
Select sql_id,child_number,first_load_time from V$SQL WHERE SQL_TEXT LIKE '%COUNT%MAOB_T%'
4v22rgk83gjnc 0 2017-12-15/22:52:46
4v22rgk83gjnc 1 2017-12-15/22:52:46
可以看到已經有兩個只cursor,進一步查看cursor不能share的原因:

Select sql_id,child_number,bind_mismatch from v$sql_shared_cursor WHERE ROWNUM<10 and sql_id='4v22rgk83gjnc'
sql_id,child_number bind_mismatch
4v22rgk83gjnc 0 N
4v22rgk83gjnc 1 Y <<<bind_mismatch
可以看到由于綁定變量的原因造成的mismatch，所以硬解析產生了第二個子cursor。

3.oracle11g 提供了自適應游標功能（Adaptive Cursor Sharing），如果表上的字段存在直方圖并且數據存在傾斜的場景下，那么對于傳入不同的數據就會造成oracle重新嘗試硬解析。具體內容，可以參考另外一篇博客。
https://blogs.oracle.com/database4cn/oracle-11g-sql-adaptive-cursor-sharing

4.cursor 已經被ageout 內存
我們都知道，oracle 對于內存管理機制和很多OS管理內存機制一樣，都采用了LRU(Least recently used,最近最少使用)算法，
cursor作為一種library cache 中的對象也不例外，若是某個sql解析需要share pool內存時，發現free list 上并沒有合適大小的內存塊（chunk）
就會觸發清理機制，那么之前cursor 申請的chunk就依據LRU算法規則被清理掉，這種就是age out，需要注意的是，這個是oracle的
機制，并不是OS上的swap機制，一旦某個cursor的被ageout出shared pool，那么下次執行這個sql的時候就是重新硬解析。

5.除了上述幾種oracle自身的機制造成重新硬解析之外，也存在人為因素操作造成的可能
例如人為收集統計信息，人為執行了flush shared_pool 操作，手工調用dbms_shared_pool.purge 來清理某個cursor
執行了ddl 語句等。