Oracle硬解析和軟解析的區(qū)別分析

更新時(shí)間：2014年08月17日 09:23:03 投稿：hebedich

我們都知道在Oracle中每條SQL語(yǔ)句在執(zhí)行之前都需要經(jīng)過(guò)解析，這里面又分為軟解析和硬解析。在Oracle中存在兩種類(lèi)型的SQL語(yǔ)句，一類(lèi)為 DDL語(yǔ)句(數(shù)據(jù)定義語(yǔ)言)，就是每次執(zhí)行都需要進(jìn)行硬解析。還有一類(lèi)就是DML語(yǔ)句，他們會(huì)根據(jù)情況選擇要么進(jìn)行硬解析，要么進(jìn)行軟解析。

一、摘要

Oracle硬解析和軟解析是我們經(jīng)常遇到的問(wèn)題，所以需要考慮何時(shí)產(chǎn)生軟解析何時(shí)產(chǎn)生硬解析，如何判斷

SQL的執(zhí)行過(guò)程

當(dāng)發(fā)布一條SQL或PL/SQL命令時(shí)，Oracle會(huì)自動(dòng)尋找該命令是否存在于共享池中來(lái)決定對(duì)當(dāng)前的語(yǔ)句使用硬解析或軟解析。

通常情況下，SQL語(yǔ)句的執(zhí)行過(guò)程如下：

Step1. SQL代碼的語(yǔ)法(語(yǔ)法的正確性)及語(yǔ)義檢查(對(duì)象的存在性與權(quán)限)。

Step2. 將SQL代碼的文本進(jìn)行哈希得到哈希值。

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

Step4. 對(duì)于存在相同哈希值的新命令行，其文本將與已存在的命令行的文本逐個(gè)進(jìn)行比較。

這些比較包括大小寫(xiě)，字符串是否一致，空格，注釋等，如果一致，則對(duì)其進(jìn)行軟解析，轉(zhuǎn)到步驟Step6，無(wú)需再次硬解析。

否則到步驟Step5。

Step5. 硬解析，生成執(zhí)行計(jì)劃。

Step6. 執(zhí)行SQL代碼，返回結(jié)果。

二、軟解析

1.下面的三個(gè)查詢(xún)語(yǔ)句，不能使用相同的共享SQL區(qū)。盡管查詢(xún)的表對(duì)象使用了大小寫(xiě)，但Oracle為其生成了不同的執(zhí)行計(jì)劃

select * from emp;

select * from Emp;

select * from EMP;

2.類(lèi)似的情況，下面的查詢(xún)中，盡管其where子句empno的值不同，Oracle同樣為其生成了不同的執(zhí)行計(jì)劃

select * from emp where empno=7369

select * from emp where empno=7788

3.在判斷是否使用硬解析時(shí)，所參照的對(duì)象及schema應(yīng)該是相同的，如果對(duì)象相同，而schema不同，則需要使用硬解析,生成不同的執(zhí)行計(jì)劃

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

三、硬解析

硬解析即整個(gè)SQL語(yǔ)句的執(zhí)行需要完完全全的解析，生成執(zhí)行計(jì)劃。而硬解析，生成執(zhí)行計(jì)劃需要耗用CPU資源，以及SGA資源。在此不得不提的是對(duì)庫(kù)緩存中閂的使用。閂是鎖的細(xì)化，可以理解為是一種輕量級(jí)的串行化設(shè)備。當(dāng)進(jìn)程申請(qǐng)到閂后，則這些閂用于保護(hù)共享內(nèi)存的數(shù)在同一時(shí)刻不會(huì)被兩個(gè)以上的進(jìn)程修改。在硬解析時(shí)，需要申請(qǐng)閂的使用，而閂的數(shù)量在有限的情況下需要等待。大量的閂的使用由此造成需要使用閂的進(jìn)程排隊(duì)越頻繁，性能則逾低下。

1. 下面對(duì)上面的兩種情形進(jìn)行演示

在兩個(gè)不同的session中完成，一個(gè)為sys帳戶(hù)的session，一個(gè)為scott賬戶(hù)的session，不同的session，其SQL命令行以不同的帳戶(hù)名開(kāi)頭

如" sys@ASMDB> " 表示使用時(shí)sys帳戶(hù)的session，" scott@ASMDB> "表示scott帳戶(hù)的session

sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;     
NAME           CLASS   VALUE
-------------------- ---------- ----------      --當(dāng)前的硬解析值為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
    -------------------- ---------- ----------      --執(zhí)行上一個(gè)查詢(xún)后硬解析值為570，解析次數(shù)增加了一次
    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
    -------------------- ---------- ----------      --執(zhí)行上一個(gè)查詢(xún)后硬解析值為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
    -------------------- ---------- ----------      --執(zhí)行上一個(gè)查詢(xún)后硬解析值為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
    -------------------- ---------- ----------      --執(zhí)行上一個(gè)查詢(xún)后硬解析值為573
    parse count (hard)      64    573
scott@ASMDB> select * from emp where empno=7788;  --此處原來(lái)empno=7369，復(fù)制錯(cuò)誤所致，現(xiàn)已更正為7788@20130905  
    sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
    NAME           CLASS   VALUE
    -------------------- ---------- ----------     --執(zhí)行上一個(gè)查詢(xún)后硬解析值為574
    parse count (hard)      64    574

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

四、硬解析改進(jìn) - 使用動(dòng)態(tài)語(yǔ)句

1. 更改參數(shù)cursor_sharing

參數(shù)cursor_sharing決定了何種類(lèi)型的SQL能夠使用相同的SQL area

CURSOR_SHARING = { SIMILAR | EXACT | FORCE }

EXACT --只有當(dāng)發(fā)布的SQL語(yǔ)句與緩存中的語(yǔ)句完全相同時(shí)才用已有的執(zhí)行計(jì)劃。

FORCE --如果SQL語(yǔ)句是字面量，則迫使Optimizer始終使用已有的執(zhí)行計(jì)劃,無(wú)論已有的執(zhí)行計(jì)劃是不是最佳的。

SIMILAR --如果SQL語(yǔ)句是字面量，則只有當(dāng)已有的執(zhí)行計(jì)劃是最佳時(shí)才使用它，如果已有執(zhí)行計(jì)劃不是最佳則重新對(duì)這個(gè)SQL

--語(yǔ)句進(jìn)行分析來(lái)制定最佳執(zhí)行計(jì)劃。

可以基于不同的級(jí)別來(lái)設(shè)定該參數(shù)，如ALTER SESSION, ALTER SYSTEM

sys@ASMDB> show parameter cursor_shar       --查看參數(shù)cursor_sharing
      NAME                 TYPE    VALUE
      ------------------------------------ ----------- ------------------------------
      cursor_sharing            string   EXACT
sys@ASMDB> alter system set cursor_sharing='similar';  --將參數(shù)cursor_sharing的值更改為similar
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;  
      NAME           CLASS   VALUE
      -------------------- ---------- ----------    --當(dāng)前硬解析的值為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
      -------------------- ---------- ----------    --執(zhí)行上一條SQL查詢(xún)后，硬解析的值變?yōu)?66
      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
      -------------------- ---------- ----------    --執(zhí)行上一條SQL查詢(xún)后，硬解析的值沒(méi)有發(fā)生變化還是866
      parse count (hard)      64    866
sys@ASMDB> select sql_text,child_number from v$sql  -- 在下面的結(jié)果中可以看到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
      --接下來(lái)在scott的session 中執(zhí)行deptno=40 和的查詢(xún)后再查看sql_text，當(dāng)cursor_sharing改為exact后，每執(zhí)行那個(gè)一次
      --也會(huì)在v$sql中增加一條語(yǔ)句
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. 使用綁定變量的方式

綁定變量要求變量名稱(chēng)，數(shù)據(jù)類(lèi)型以及長(zhǎng)度是一致，否則無(wú)法使用軟解析

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

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

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

在第二個(gè)查詢(xún)中，變量值在查詢(xún)執(zhí)行時(shí)被提供。該查詢(xún)只編譯一次，隨后會(huì)把查詢(xún)計(jì)劃存儲(chǔ)在一個(gè)共享池（庫(kù)緩存）中，以便以后獲取和重用這個(gè)查詢(xún)計(jì)劃。

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

select * from emp where empno=:eno;

select * from emp where empno=:emp_no

使用綁定變量時(shí)要求不同的會(huì)話(huà)中使用了相同的回話(huà)環(huán)境，以及優(yōu)化器的規(guī)則等

scott@ASMDB> create table tb_test(col int);   --創(chuàng)建表tb_test
scott@ASMDB> create or replace procedure proc1 --創(chuàng)建存儲(chǔ)過(guò)程proc1使用綁定變量來(lái)插入新記錄
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 --創(chuàng)建存儲(chǔ)過(guò)程proc2，未使用綁定變量，因此每一個(gè)SQL插入語(yǔ)句都會(huì)硬解析
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運(yùn)行的時(shí)間是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  --硬解析的次數(shù)，前者只有四次
      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  --閂的隊(duì)列數(shù)比較
      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  --庫(kù)緩存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使用閂的數(shù)量也遠(yuǎn)遠(yuǎn)多于proc1，其比值是.13% 
PL/SQL procedure successfully completed.

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

由上面的示例可知，在未使用綁定變量的情形下，不論是解析次數(shù)，閂使用的數(shù)量，隊(duì)列，分配的內(nèi)存，庫(kù)緩存，行緩存遠(yuǎn)遠(yuǎn)高于綁定

變量的情況。因此盡可能的使用綁定變量避免硬解析產(chǎn)生所需的額外的系統(tǒng)資源。

綁定變量的優(yōu)點(diǎn)

減少SQL語(yǔ)句的硬解析，從而減少因硬解析產(chǎn)生的額外開(kāi)銷(xiāo)(CPU,Shared pool,latch)。其次提高編程效率，減少數(shù)據(jù)庫(kù)的訪(fǎng)問(wèn)次數(shù)。

綁定變量的缺點(diǎn)

優(yōu)化器就會(huì)忽略直方圖的信息，在生成執(zhí)行計(jì)劃的時(shí)候可能不夠優(yōu)化。SQL優(yōu)化相對(duì)比較困難

五、總結(jié)

1.盡可能的避免硬解析，因?yàn)橛步馕鲂枰嗟腃PU資源，閂等。

2.cursor_sharing參數(shù)應(yīng)權(quán)衡利弊，需要考慮使用similar與force帶來(lái)的影響。

3.盡可能的使用綁定變量來(lái)避免硬解析。