這篇文章是參考甲骨論老相老師所做的教學視頻:
http://v.youku.com/v_show/id_XMzkyMTczMTQ4.html
所做的學習筆記:

接上文:
http://nvd11.blog.163.com/blog/static/200018312201301875752730/
sql 是在shared Pool里面解析成執行計劃的.

如圖:



??????? 1.什么是SQL語句共享
??????? sql 語句傳入shared pool后, 會被先轉換成ASCII碼, 然后經過一系列運算,其中包括HASH運算,得出HASH值,然后得出一串數字,并包含1個內存鏈chain號碼.

??????? Server Process 就會拿著chain號碼和HASH號碼,到Library cache上找到對應的內存鏈chain, 然后遍歷上面的chunk(比較Hash值), 如果有1個chunk的Hash與之相同,則證明這條sql曾經被硬解析過, 就直接可以拿出對應執行計劃, 這就是軟解析.

???????? 我們也可以說這條sql與之前硬解析過的sql共享了.

??????? 如果找不到hash值相同的chunk,? 則認為這條sql未被硬解析過, 就要執行上圖的動作2,進行硬解析.

??????? 所以可以看出,那個HASH值十分重要, 而hash值是由ASCII碼運算出來的, 所以共享sql之間的ASCII碼要相同.

??????? 而ASCII是由SQL語句的字符決定的.? 所以兩條共享sql語句的每個字符要相同. 而且大小寫都要相同

??????? 舉個例子: 下面3條語句
??????? select /*comment_1*/ count(1) from dba_tables where table_name = 'EMP';
??????? select /*comment_1*/ count(1) from dba_tables where table_name = 'DEPT';
??????? select /*comment_1*/ count(1) from? dba_tables where table_name = 'DEPT';

?????? 首先,很明顯 第一條語句和第二條語句不相同, 因為EMP 和 DEPT 是兩個不同的字符
?????? 第二條和第三條呢?? 也不相同, 因為第二條 from 與 dba_tables直接只有1個空格, 而第3條有2個! 所以他們的ASCII碼不同,所以規范編寫SQL語句很重要啊.

?===============================================分割線===============================
2.判斷SQL語句有無共享
下面我分別執行上面3條語句1次
??
注意第2條和第3條語句的空格區別哦

然后可以去v$sql 去查看已執行過的sql語句:
?select sql_id,sql_text,executions from v$sql where sql_text like '%comment_1%';

可以看出這3條sql語句分別被執行了1次??? (因為調整顯示格式, 查詢v$sql的這條執行3次)
也就說進行了3次硬解析.

其實每執行1條sql語句, 都會分配1個sql_id(參見上圖),上面3條語句被分配3個sql_id, 所以oracle認為上面3條語句是不一樣的.

所以Oracle中sql語句要相同:
則所有字符要一樣, 包括空格,空行,回車...

例如, 如果要第3條語句與第一條語句共享, 則要去掉from 和 dba_tables之間的1個空格:
我們在執行一次:
?
可發現,v$sql中并沒有多出一個sql_id, 只不過第一條的執行次數變成了2,? 也就是說第4次執行的那條跟第一條的sql_id是一樣的,因為它們的字符完全一樣.

那么第一條和第二條sql能不能共享呢, 因為條件'EMP' 跟 'DEPT' 完全不同啊, 貌似是個硬傷.

方法是有的:
就是用1個相同名字的變量, 分別賦值'EMP'和'DEPT'各執行一次, 因為變量名相同.
做個例子:

寫1個 sql 腳本如下:
?

上面的腳本會插入11行數據進入表test, 雖然每一行的值都是不同, 但是我們可以用變量? i來裝載它.
然后使用 execute 來執行sql語句.

下面執行這個腳本:


在查看v$sql 視圖:
?
可以見到11次sql語句之分配了1個 sql_id , 用:1 來取代實際的值. 也就是說11條insert語句之產生1此硬解析, 其余10次是軟解析,成功共享SQL.
這個方法很重要, 可以想象一下銀行業務, 可能經常大量地使用同一條sql語句,但是參數次次都不同, 如果在寫程序時不使用綁定變量,則會產生大量的硬解析,? 而使用了綁定變量去取代參數, 就能避免這個情況!

所以總結一下共享SQL的關鍵:

1. 嚴格統一書寫風格
2. 使用綁定變量

提醒下, v$sql實在上就是緩存在shared pool里的sql語句信息.
可以用alter system flush shared pool 來清空v$sql, 不過接下來就大量硬解 你懂的.


?===============================================分割線===============================

3.如何找出未被共享(硬解析)的sql語句:

原理都好簡單, 因為如果一條sql未被共享,則這條sql在v$sql里的執行次數為1.
所以只需查找v$sql
select sql_id, sql_fulltext,executions from v$sql where sql_text like '%from t%' and executions = 1 order by sql_text ;

注意綠色高亮部分根據實際情況修改

order by 排序很重要啊, 因為有些sql的確只被用戶執行了一次, 用order by就可找出那些類似的sql,而被硬解析多次了~

下面做個例子,
修改一下上面的腳本, 令到循環中每次執行的語句都不一樣(參數不一樣)
如下圖, 注意右邊是修改后的:
執行了900幾次..
?
然后執行這個修改后的腳本:
?
接下來用
select sql_id, sql_fulltext,executions from v$sql where sql_text like '%insert%' and executions = 1 order by sql_text;
查看 未被共享的語句.
?

接下來去查看導出來的log文件, 通常就可以分析出哪些sql語句未被共享而被執行大量硬解析了..
?
這是1個很經典的查找未被共享sql語句的方法, 用于數據庫優化.

===============================================分割線===============================

4. 設置cursor_sharing參數
找到未被共享的sql語句后怎么做呢, 一般會交給程序員,讓他們修改.

但是做過碼農的都知道, 程序里的代碼有些時候不是那么好改了,有時真的牽一發而動全身啊..

那改不了怎么辦.

其實還有1個辦法是設置oracle的一個參數: cursor_sharing
我們先查看當前默認的參數值是什么:
?
可以見到當前值是EXACT, 代表什么意思呢, 我們去oracle官方文檔查下:

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

Values:

• FORCE

  Allows the creation of a new cursor if sharing an existing cursor, or if the cursor plan is not optimal.

• EXACT

  Only allows statements with identical text to share the same cursor.

可以見到, 游標共享參數決定了什么情況下可以共享SQL

EXACT : 只有當sql語句完全一樣才共享sql

關鍵是FORCE了:

首先共享SQL失敗原因有兩種:

1.是書寫不規范(大小寫, 空格)

2. 沒有用綁定變量去取代參數.

對于第一種原因的sql共享失敗, 這個cursor_sharing設置也沒辦法解決, 不過很容易交給程序員解決.

而將cursor_sharing設置為force 可以解決第2種情況,

我們測試一下:

首先將cursor_sharing 設置為force

?
跟住清空shared pool緩存:
?

然后我們再執行一次上面執行過的那個腳本:

?
執行:
?
我們再去查看v$sql..
但是由這個測試來講是無效果的... 而老相在視頻里沒提供測試..
可以見到 sql語句并沒有共享, 依然是沒一條insert 語句分配1個sql id.
?

于是我私人再做多個測試,睇下這個參數會不會影響硬解析的數量.

首先將cursor_sharing設置成默認的'EXACT', 并清空shared pool緩存.

接著查看當前硬解析和軟解析數量:
?
可以記下:
當前已經發生過的解析數量總數為: 611230
其中硬解析數量為: 35774

這時我們執行一次insert腳本:
?
再次查看解析數量:
?
其中解析數量總數增加了? 613301 - 611230 =? 2071
而硬解析數量增加了 36778 - 35774 =? 1004
可以看出硬解析的數量是相當接近 腳本中循環執行的insert 語句數量的!

這是我們將cursor_sharing 設置為Force
并且清空shared pool


再查看當前的解析數量:
?
?當前已經發生過的解析數量總數為: 616473
其中硬解析數量為: 37432


這時我們再次執行一次insert腳本:
?
再次查看解析數量:
??
其中解析數量總數增加了? 618008 - 616473 =? 1535
而硬解析數量增加了 38511 - 37432 =? 1079

尼瑪硬解析數量反而增加了

我覺得原因是可能這個參數對我腳步那種sql 無作用, 至于到底會影響哪些sql共享, 以后再研究了...

?







??????


??????
?

總結

以上是生活随笔為你收集整理的共享SQL语句减少硬解析的全部內容，希望文章能夠幫你解決所遇到的問題。

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