shared pool是效能調優的重要物件。

Shared Pool中包含兩大核心pool，分別是Data dictionary cache和Library cache。

Data dictionary : 資料字典多數為靜態的、固定的。

Library cache：經過編譯、解析的SQL語句放在Library cache中。隨著使用者執行SQL語句的增多，執行PL/SQL程式碼的增多，Library cache會有被裝滿的時候。有效的管理其空間大小就顯得格外重要。Oracle採用LRU演算法來維持那些語句保留在Library cache中，哪些語句被清除。最近最少使用的SQL就是被清除物件的首選。

SQL語句在Library cache保留的是該語句的原始碼麼？比如select * from employees;答案是否定的，它儲存的是該SQL語句的雜湊值。

對SQL語句的解析分為兩種，一種是硬解析另一種是軟解析。硬解析是遇到從未執行過的SQL第一次為其解析的過程。軟解析是再次遇到該SQL時查詢已經解析過的解析結果。硬解析代價大於軟解析。對比兩個SQL是否相同採取的辦法是對比其雜湊值。如果雜湊值相同就認為SQL語句相同。

在SQL語句中的字母大小寫和縮排、空格等細微的差別都會導致看起來相同的SQL語句其對應的雜湊值不同。下面的3條SQL語句在Library cache中就會被判斷成2個不同的SQL語句，不能被重用。每次執行都需要一次硬解析。

select * from snow.t1;
SELECT * FROM SNOW.T1;

Library cache中最可怕的效能殺手要數“高併發下的非繫結變數SQL”。如果一個簡單的查詢語句，只是在where條件中的引數不同，會被當做不同的SQL，每次執行前進行一次硬解析。比如去營業廳查詢話費：
select * from t where phone_number=‘18611010001’;
select * from t where phone_number=‘18611019527’;

這兩個SQL語句只有phone_number的值不同導致SQL的雜湊不同。如果併發量高，查詢次數多，library cache的快取作用基本被閒置了，必然導致執行效率低下。

這裡引用TOM的經典實驗來驗證繫結變數的重要性

實驗步驟：
1，建立儲存過程proc1，使用繫結變數的方式插入10萬條記錄

SYS@PROD2 > create or replace procedure proc1
2 as
3   begin
4      for i in 1..100000
5      loop
6             execute immediate
7              'insert into snow.t2 values(:i)' using i;
8     end loop;
9 end;
10 /

Procedure created.

2，建立儲存過程proc2，不適用繫結變數的方式插入10萬條記錄

SYS@PROD2 > create or replace procedure proc2
2 as
3 begin
4      for i in 1..100000
5      loop
6             execute immediate
7              'insert into snow.t2 values('||i||')' ;
8     end loop;
9 end;
10 /

Procedure created.

3，設定計時器

SYS@PROD2 > set timing on

4，不使用繫結變數的儲存過程執行時間為32秒84

SYS@PROD2 > exec proc2

PL/SQL procedure successfully completed.

Elapsed: 00:00:32.84

5，清空snow.t2表中的資料，truncate使用0秒19

SYS@PROD2 > truncate table snow.t2;

Table truncated.

Elapsed: 00:00:00.19

6，使用繫結變數的儲存過程執行時間為2秒20

SYS@PROD2 > exec proc1

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.20

7，刪除snow.t2表中的資料，delete使用0秒84
SYS@PROD2 > delete from snow.t2;

100000 rows deleted.

Elapsed: 00:00:00.84

對比一下執行效果，不使用繫結變數的儲存過程執行時間32.84秒，使用繫結變數的儲存過程的時間為2.30，差距巨大。TOM的試驗中插入1萬條資料，我的實驗中插入了10萬條資料。如果插入的資料更多，併發更多，這個執行效率會更加懸殊。

Oracle提供了一個解決方案就是修改cursor_sharing的引數。透過對該引數的修改可以改進其“辨識度”。如果是類似於上面的例子中where字句中的變數不一樣，應用程式中又沒有采取繫結變數的做法，修改cursor sharing引數為similar活著force都會使解析器放寬要求，將他們視為相同的語句，從而減少硬解析的次數。

SYS@PROD1 > show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination      string      memory+disk
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     50

alter system set cursor_sharing=similar;

這種做法只有那些已經上線的系統，並且不能更改應用程式中的SQL語句時才建議使用。

PerformanceTuning 筆記2 繫結變數

相關文章