程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle硬解析和軟解析的區別分析，oracle和軟

Oracle硬解析和軟解析的區別分析，oracle和軟

編輯：Oracle教程

Oracle硬解析和軟解析的區別分析，oracle和軟

一、摘要

Oracle硬解析和軟解析是我們經常遇到的問題，所以需要考慮何時產生軟解析何時產生硬解析，如何判斷

SQL的執行過程

當發布一條SQL或PL/SQL命令時，Oracle會自動尋找該命令是否存在於共享池中來決定對當前的語句使用硬解析或軟解析。

通常情況下，SQL語句的執行過程如下：

Step1. SQL代碼的語法(語法的正確性)及語義檢查(對象的存在性與權限)。

Step2. 將SQL代碼的文本進行哈希得到哈希值。

Step3. 如果共享池中存在相同的哈希值，則對這個命令進一步判斷是否進行軟解析，否則到e步驟。

Step4. 對於存在相同哈希值的新命令行，其文本將與已存在的命令行的文本逐個進行比較。

這些比較包括大小寫，字符串是否一致，空格，注釋等，如果一致，則對其進行軟解析，轉到步驟Step6，無需再次硬解析。

否則到步驟Step5。

Step5. 硬解析，生成執行計劃。

Step6. 執行SQL代碼，返回結果。

二、軟解析

1.下面的三個查詢語句，不能使用相同的共享SQL區。盡管查詢的表對象使用了大小寫，但Oracle為其生成了不同的執行計劃

select * from emp;

select * from Emp;

select * from EMP;

2.類似的情況，下面的查詢中，盡管其where子句empno的值不同，Oracle同樣為其生成了不同的執行計劃

select * from emp where empno=7369

select * from emp where empno=7788

3.在判斷是否使用硬解析時，所參照的對象及schema應該是相同的，如果對象相同，而schema不同，則需要使用硬解析,生成不同的執行計劃

sys@ASMDB> select owner,table_name from dba_tables where table_name like 'TB_OBJ%';
    OWNER             TABLE_NAME
    ------------------------------ ------------------------------
    USR1              TB_OBJ        --兩個對象的名字相同，當所有者不同
    SCOTT             TB_OBJ
usr1@ASMDB> select * from tb_obj;
scott@ASMDB> select * from tb_obj;   --此時兩者都需要使用硬解析以及走不同的執行計劃

三、硬解析

硬解析即整個SQL語句的執行需要完完全全的解析，生成執行計劃。而硬解析，生成執行計劃需要耗用CPU資源，以及SGA資源。在此不得不提的是對庫緩存中闩的使用。闩是鎖的細化，可以理解為是一種輕量級的串行化設備。當進程申請到闩後，則這些闩用於保護共享內存的數在同一時刻不會被兩個以上的進程修改。在硬解析時，需要申請闩的使用，而闩的數量在有限的情況下需要等待。大量的闩的使用由此造成需要使用闩的進程排隊越頻繁，性能則逾低下。

1. 下面對上面的兩種情形進行演示

在兩個不同的session中完成，一個為sys帳戶的session，一個為scott賬戶的session，不同的session，其SQL命令行以不同的帳戶名開頭

如" sys@ASMDB> " 表示使用時sys帳戶的session，" scott@ASMDB> "表示scott帳戶的session

sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;     
NAME           CLASS   VALUE
-------------------- ---------- ----------      --當前的硬解析值為569
parse count (hard)      64    569
scott@ASMDB> select * from emp;  
    sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;   
    NAME           CLASS   VALUE
    -------------------- ---------- ----------      --執行上一個查詢後硬解析值為570，解析次數增加了一次
    parse count (hard)      64    570
scott@ASMDB> select * from Emp;
    sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;    
    NAME           CLASS   VALUE
    -------------------- ---------- ----------      --執行上一個查詢後硬解析值為571
    parse count (hard)      64    571
scott@ASMDB> select * from EMP;
    sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;    
    NAME           CLASS   VALUE
    -------------------- ---------- ----------      --執行上一個查詢後硬解析值為572
    parse count (hard)      64    572  
scott@ASMDB> select * from emp where empno=7369;    
    sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
    NAME           CLASS   VALUE
    -------------------- ---------- ----------      --執行上一個查詢後硬解析值為573
    parse count (hard)      64    573
scott@ASMDB> select * from emp where empno=7788;  --此處原來empno=7369，復制錯誤所致，現已更正為7788@20130905  
    sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
    NAME           CLASS   VALUE
    -------------------- ---------- ----------     --執行上一個查詢後硬解析值為574
    parse count (hard)      64    574

從上面的示例中可以看出，盡管執行的語句存在細微的差別，但Oracle還是為其進行了硬解析，生成了不同的執行計劃。即便是同樣的SQL語句，而兩條語句中空格的多少不一樣，Oracle同樣會進行硬解析。

四、硬解析改進 - 使用動態語句

1. 更改參數cursor_sharing

參數cursor_sharing決定了何種類型的SQL能夠使用相同的SQL area

CURSOR_SHARING = { SIMILAR | EXACT | FORCE }

EXACT --只有當發布的SQL語句與緩存中的語句完全相同時才用已有的執行計劃。

FORCE --如果SQL語句是字面量，則迫使Optimizer始終使用已有的執行計劃,無論已有的執行計劃是不是最佳的。

SIMILAR --如果SQL語句是字面量，則只有當已有的執行計劃是最佳時才使用它，如果已有執行計劃不是最佳則重新對這個SQL

--語句進行分析來制定最佳執行計劃。

可以基於不同的級別來設定該參數，如ALTER SESSION, ALTER SYSTEM

sys@ASMDB> show parameter cursor_shar       --查看參數cursor_sharing
      NAME                 TYPE    VALUE
      ------------------------------------ ----------- ------------------------------
      cursor_sharing            string   EXACT
sys@ASMDB> alter system set cursor_sharing='similar';  --將參數cursor_sharing的值更改為similar
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;  
      NAME           CLASS   VALUE
      -------------------- ---------- ----------    --當前硬解析的值為865
      parse count (hard)      64    865
scott@ASMDB> select * from dept where deptno=10;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331; 
      NAME           CLASS   VALUE
      -------------------- ---------- ----------    --執行上一條SQL查詢後，硬解析的值變為866
      parse count (hard)      64    866
scott@ASMDB> select * from dept where deptno=20;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
      NAME           CLASS   VALUE
      -------------------- ---------- ----------    --執行上一條SQL查詢後，硬解析的值沒有發生變化還是866
      parse count (hard)      64    866
sys@ASMDB> select sql_text,child_number from v$sql  -- 在下面的結果中可以看到SQL_TEXT列中使用了綁定變量:"SYS_B_0" 
      where sql_text like 'select * from dept where deptno%';
      SQL_TEXT                      CHILD_NUMBE
      -------------------------------------------------- ------------ 
      select * from dept where deptno=:"SYS_B_0"          0
sys@ASMDB> alter system set cursor_sharing='exact';    --將cursor_sharing改回為exact
      --接下來在scott的session 中執行deptno=40 和的查詢後再查看sql_text，當cursor_sharing改為exact後，每執行那個一次
      --也會在v$sql中增加一條語句
sys@ASMDB> select sql_text,child_number from v$sql        
      where sql_text like 'select * from dept where deptno%';
      SQL_TEXT                      CHILD_NUMBER
      -------------------------------------------------- ------------
      select * from dept where deptno=50              0   
      select * from dept where deptno=40              0
      select * from dept where deptno=:"SYS_B_0"          0

2. 使用綁定變量的方式

綁定變量要求變量名稱，數據類型以及長度是一致，否則無法使用軟解析

(1). 綁定變量（bind variable）是指在DML語句中使用一個占位符，即使用冒號後面緊跟變量名的形式，如下

select * from emp where empno=7788 --未使用綁定變量

select * from emp where empono=:eno --:eno即為綁定變量

在第二個查詢中，變量值在查詢執行時被提供。該查詢只編譯一次，隨後會把查詢計劃存儲在一個共享池（庫緩存）中，以便以後獲取和重用這個查詢計劃。

(2). 下面使用了綁定變量，但兩個變量其實質是不相同的，對這種情形，同樣使用硬解析

select * from emp where empno=:eno;

select * from emp where empno=:emp_no

使用綁定變量時要求不同的會話中使用了相同的回話環境，以及優化器的規則等

scott@ASMDB> create table tb_test(col int);   --創建表tb_test
scott@ASMDB> create or replace procedure proc1 --創建存儲過程proc1使用綁定變量來插入新記錄
as
begin
for i in 1..10000
loop
execute immediate 'insert into tb_test values(:n)' using i;
end loop;
end;
/
Procedure created.
scott@ASMDB> create or replace procedure proc2 --創建存儲過程proc2，未使用綁定變量，因此每一個SQL插入語句都會硬解析
as
begin
for i in 1..10000
loop
execute immediate 'insert into tb_test values('||i||')';
end loop;
end;
/
Procedure created.
scott@ASMDB> exec runstats_pkg.rs_start
PL/SQL procedure successfully completed.
scott@ASMDB> exec proc1;
PL/SQL procedure successfully completed.
scott@ASMDB> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
scott@ASMDB> exec proc2;
PL/SQL procedure successfully completed.
scott@ASMDB> exec runstats_pkg.rs_stop(1000);
      Run1 ran in 1769 hsecs
      Run2 ran in 12243 hsecs       --run2運行的時間是run1的/1769≈倍
      run 1 ran in 14.45% of the time  
      Name                Run1   Run2   Diff
      LATCH.SQL memory manager worka    410   2,694   2,284
      LATCH.session allocation       532   8,912   8,380
      LATCH.simulator lru latch       33   9,371   9,338
      LATCH.simulator hash latch      51   9,398   9,347
      STAT...enqueue requests        31  10,030   9,999
      STAT...enqueue releases        29  10,030  10,001
      STAT...parse count (hard)       4  10,011  10,007  --硬解析的次數，前者只有四次
      STAT...calls to get snapshot s    55  10,087  10,032
      STAT...parse count (total)      33  10,067  10,034
      STAT...consistent gets        247  10,353  10,106
      STAT...consistent gets from ca    247  10,353  10,106
      STAT...recursive calls      10,474  20,885  10,411
      STAT...db block gets from cach  10,408  30,371  19,963
      STAT...db block gets       10,408  30,371  19,963
      LATCH.enqueues            322  21,820  21,498  --闩的隊列數比較
      LATCH.enqueue hash chains      351  21,904  21,553
      STAT...session logical reads   10,655  40,724  30,069
      LATCH.library cache pin      40,348  72,410  32,062  --庫緩存pin
      LATCH.kks stats            8  40,061  40,053
      LATCH.library cache lock       318  61,294  60,976
      LATCH.cache buffers chains    51,851  118,340  66,489
      LATCH.row cache objects       351  123,512  123,161
      LATCH.library cache        40,710  234,653  193,943
      LATCH.shared pool         20,357  243,376  223,019
      Run1 latches total versus runs -- difference and pct
      Run1   Run2   Diff   Pct
      157,159  974,086  816,927 16.13%     --proc2使用闩的數量也遠遠多於proc1，其比值是.13% 
PL/SQL procedure successfully completed.

(3). 使用綁定變量的好處

由上面的示例可知，在未使用綁定變量的情形下，不論是解析次數，闩使用的數量，隊列，分配的內存，庫緩存，行緩存遠遠高於綁定

變量的情況。因此盡可能的使用綁定變量避免硬解析產生所需的額外的系統資源。

綁定變量的優點

減少SQL語句的硬解析，從而減少因硬解析產生的額外開銷(CPU,Shared pool,latch)。其次提高編程效率，減少數據庫的訪問次數。

綁定變量的缺點

優化器就會忽略直方圖的信息，在生成執行計劃的時候可能不夠優化。SQL優化相對比較困難

五、總結

1.盡可能的避免硬解析，因為硬解析需要更多的CPU資源，闩等。

2.cursor_sharing參數應權衡利弊，需要考慮使用similar與force帶來的影響。

3.盡可能的使用綁定變量來避免硬解析。

oracle中：=與=：的不同？

變量綁定是指在sql語句的條件中使用變量而不是常量。比如shared pool裡有兩條sql語句，
select * from tab1 where col1=1;
select * from tab1 where col1=2;
對oracle數據庫來說，這是兩條完全不同的SQL，對這兩條語句都需要進行hard parse。因為oracle會根據sql語句的文本去計算每個字符在內存裡的hash值，因此雖然上述兩條SQL只有一個字符不一樣，oracle根據hash算法在內存中得到的hash地址就不一樣，所以oracle就會認為這是兩條完全不同的語句。而如果將上述SQL改寫成select * from tab1 where col1=:var1;,然後通過對變量var1的賦值去查詢，那麼oracle對這條語句第一次會進行hard parse，以後就只進行soft parse。假設某條語句被重復執行了幾十萬次，那麼使用bind var帶來的好處是巨大的。一個應用程序如果bind var使用不充分，那麼幾乎一定會伴隨著嚴重的性能問題。

綁定變量是相對文本變量來講的,所謂文本變量是指在SQL直接書寫查詢條件，這樣的SQL在不同條件下需要反復解析,綁定變量是指使用變量來代替直接書寫條件，查詢bind value在運行時傳遞，然後綁定執行。優點是減少硬解析,降低CPU的爭用,節省shared_pool ;缺點是不能使用histogram,sql優化比較困難

硬解與軟解的不同

要深入了解這個問題，首先你要了解軟解和硬解。我是這麼理解的，先說好了這是我個人的理解，不是官方的，呵呵。黑莓在國外都是通訊公司定制的機器，比如沃達豐，這樣的機器拿到中國來是不能直接用的，好像是帶什麼網絡鎖，要想在國內使用，首先必須解鎖。解鎖有兩種方法，就是傳說中的軟解和硬解。軟解就是通過一些軟件刷機解鎖，這種方法對機器基本上可以說沒有影響的，可以正常使用；還有就是硬解，好像是強行拆機後修改硬件（說法不官方但是不影響理解，呵呵），就是很暴力的手段，但是傳說手藝好的師傅硬解對機器影響不大，不過我不認為國內的硬解機師傅們有這樣的手藝。硬解可能導致機器受到一切損傷，比如無辜死機等現象。所以軟解和硬解的價格上也是有差距的。這個好比你買手機的時候買到的翻新機或者垃圾水貨機，會出現很多你想不到的問題。如果你還沒買的話，我可以給你推薦個賣家，我的81就是從他那買的，用了很長時間了，還沒發現什麼問題，質量挺不錯的。
說了這麼多，可能沒什麼重點，不過希望對你購機有所幫助。祝樓主好運。