在OLTP系統中，要求對頻繁執行的SQL使用綁定變量(唯一值少的字段，如type、status等，數據分布不均是常見情況，這種字段不建議使用綁定變量)。

在使用綁定變量時，有時開發人員對使用的字段類型不是很清楚，比如銀行賬號、電話號碼、全數字型的序列號等，可以定義成number類型，也可以定義成varchar2類型。如果字段類型與綁定變量使用的數據類型不匹配，就會發生隱式類型轉換。

常見的有害隱式類型轉換發生在下面情況：varchar2_col =:number_variable

即varchar2類型的字段，使用的綁定變量類型是number類型，這將造成字段上的索引不能使用，如果是頻繁執行的SQL，將會給系統帶來嚴重的性能問題。

下面是一個比較讓人迷惑的案例：

在對某客戶一個重要系統進行優化時，在AWR的TOP SQL中遇到了這個SQL：

SQL代碼非常簡單：

SELECT NVL(MAX(DCC_PROC_ID),0)

FROM P_SERVA

WHERE ACC_NBR = :B2 AND SERV_ID <> :B1;

其中ACC_NBR(varchar2類型)和SERV_ID都是選擇性很好的字段，兩個字段上都有索引，因為SERV_ID是不等于條件，不能使用索引，那么這個SQL正常情況應該是使用ACC_NBR字段上的索引才是最佳路徑。而且平均執行時間不應該超過1毫秒，實際顯示平均執行時間達到了540毫秒，肯定有問題。

通過sqlhc工具收集了該SQL相關信息，發現SQL的執行計劃有3個：

其中1最好，2和3都與1差了幾百倍的性能。而根據常理，只有執行計劃1才是比較正常的性能表現。

再看SQL執行情況，1和2兩個計劃同時存在，效率差別非常明顯：

再來分別看3個執行計劃的具體內容：

執行計劃1，plan_hash_value=1228755719，使用了ACC_NBR字段上的索引，沒有問題。

執行計劃2，plan_hash_value=164228054，使用全表掃描的執行計劃，在執行計劃顯示的最后部分，Predicate Information里，有TO_NUMBER(“ACC_NBR”)=:B2 字樣，再結合上面部分的Peeked Binds信息，ACC_NBR字段使用的綁定變量是NUMBER類型，由此可以判斷，這是因為綁定變量使用的數據類型與字段本身的類型不匹配，優化器自動對字段做了隱式類型轉換，相當于在字段上使用了TO_NUMBER函數，這種情況就不能使用ACC_NBR字段上的索引:

再來看第3個執行計劃，plan_hash_value=1669185283，這個執行計劃因為是歷史執行計劃，看不到Predicate Information信息(只有sql執行計劃存在于V$SQL_PLAN視圖時，才會有Predicate Information信息，DBA_HIST_SQL_PLAN不保存這個信息)，但是綁定變量使用的數據類型能夠看到，是NUMBER類型。這種情況下，優化器還是因為隱式類型轉換不能使用ACC_NBR字段上的索引，轉而使用兩個索引全掃描再做index join的執行計劃，效率更差。

經過開發人員對代碼的核對，發現這段SQL在兩個不同的代碼段中被調用，一段代碼使用了正確的綁定變量類型(varchar2)，而另一段則使用了number類型的綁定變量，這就造成了同一個sql_id， 同時存在多個不同執行計劃的情況。這種情況應該屬于應用代碼不規范。

研發人員將使用number類型的綁定變量更換成varchar2類型后，SQL就都使用執行計劃1了，執行效率大幅提高，CPU使用率也下降很多。

有的DBA在遇到這種多個執行計劃同時存在的情況(這個情況比較特殊)，可能會考慮使用SQL profile來固定執行計劃，但是固定的執行計劃只對正確使用綁定變量類型的SQL生效，對于不正確綁定變量類型，SQL profile也不起作用。

總結：

研發人員在使用綁定變量時，應該密切關注表字段定義的數據類型，尤其是當電話號碼、賬號信息等全數字的字段定義成varchar2時，千萬不要使用number類型的綁定變量，否則會嚴重消耗系統資源，SQL本身性能也會非常差。

另一種情況是number數據類型的字段，在綁定變量是varchar2時，也會發生隱式類型轉換。但是，這個隱式類型轉換是發生在綁定變量上，因此不會對執行計劃有影響，是無害的。

如何檢查和發現系統中存在的隱式類型轉換？ 這里簡單列出最常見的一種檢查方法：

select sql_id,object_owner,operation,options,object_name,

filter_predicates,projection

from v$sql_plan wherefilter_predicates like 'TO_NUMBER%'

此外還有一些其他的隱式類型轉換，如date類型的字段，使用了timestamp的綁定變量等，也需要注意。

關注“老虎劉談SQL優化”，分享老虎劉那些年的SQL優化案例！

腳本分享在QQ群：16778072

歡迎轉發分享給更多的朋友

為了方便交流，有興趣的朋友可以加入同名微信群：

總結

以上是生活随笔為你收集整理的oracle隐式转换函数,隐式数据类型转换案例一则的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。