--=======================
-- Oracle 硬解析與軟解析
--=======================
?
??? Oracle 硬解析與軟解析是我們經常遇到的問題，什么情況會產生硬解析，什么情況產生軟解析，又當如何避免硬解析？下面的描述將給出
軟硬解析的產生，以及硬解析的弊端和如何避免硬解析的產生。
???
一、 SQL 語句的執行過程
??? 當發布一條 SQL 或 PL / SQL 命令時， Oracle 會自動尋找該命令是否存在于共享池中來決定對當前的語句使用硬解析或軟解析。
??? 通常情況下， SQL 語句的執行過程如下：
??? a . SQL 代碼的語法 ( 語法的正確性 ) 及語義檢查 ( 對象的存在性與權限 ) 。
??? b . 將 SQL 代碼的文本進行哈希得到哈希值。
??? c . 如果共享池中存在相同的哈希值，則對這個命令進一步判斷是否進行軟解析，否則到 e 步驟。
??? d . 對于存在相同哈希值的新命令行，其文本將與已存在的命令行的文本逐個進行比較。這些比較包括大小寫，字符串是否一致，空格，注釋
??????? 等，如果一致，則對其進行軟解析，轉到步驟 f 。否則到 d 步驟。
??? e . 硬解析，生成執行計劃。
??? f . 執行 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 資源。在此不
??? 得不提的是對庫緩存中閂的使用。閂是鎖的細化，可以理解為是一種輕量級的串行化設備。當進程申請到閂后，則這些閂用于保護共享內存
??? 的數在同一時刻不會被兩個以上的進程修改。在硬解析時，需要申請閂的使用，而閂的數量在有限的情況下需要等待。大量的閂的使用由此
??? 造成需要使用閂的進程排隊越頻繁，性能則逾低下。
???????
四、硬解析的演示 ???????
??? 下面對上面的兩種情形進行演示
??? 在兩個不同的 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 = 7369 ; ???
?
??????? 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"
??????????? ? 2 ? where sql_text like 'select * from dept where deptno%' ;
?
??????????? SQL_TEXT ?????????????????????????????????????????? CHILD_NUMBER
??????????? -------------------------------------------------- ------------ ??
??????????? 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 ???????????????
??????????? ? 2 ? 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
?
??????? 注意當該參數設置為 similar ，會產生不利的影響，可以參考這里： cursor_sharing 參 數 對 于expdp 的 性 能 影 響
?
??? 2. 使用綁定變量
??????? 綁定變量要求變量名稱，數據類型以及長度是一致，否則無法使用軟解析
??????? 綁定變量（ bind variable ）是指在 DML 語句中使用一個占位符，即使用冒號后面緊跟變量名的形式，如下
??????????? select * from emp where empno = 7788 ??? -- 未使用綁定變量
??????????? select * from emp where empono =: eno ?? --:eno 即為綁定變量
??????? 在第二個查詢中，變量值在查詢執行時被提供。該查詢只編譯一次，隨后會把查詢計劃存儲在一個共享池（庫緩存）中，以便以后獲取
??????????? 和重用這個查詢計劃。
???
??????? 下面使用了綁定變量，但兩個變量其實質是不相同的，對這種情形，同樣使用硬解析
??????????? select * from emp where empno =: eno ;
??????????? select * from emp where empno =: emp_no
?
??????? 使用綁定變量時要求不同的會話中使用了相同的回話環境，以及優化器的規則等。
?
??????? 使用綁定變量的例子 ( 參照了 TOM 大師的 Oracle 9i & 10g 編程藝術 )
??????????? scott@ASMDB > create table tb_test ( col int ); ???? -- 創建表 tb_test
?
??????????? scott@ASMDB > create or replace procedure proc1 ? -- 創建存儲過程 proc1 使用綁定變量來插入新記錄
??????????? ? 2 ? as
??????????? ? 3 ? begin
??????????? ? 4 ????? for i in 1..10000
??????????? ? 5 ????? loop
??????????? ? 6 ????????? execute immediate 'insert into tb_test values(:n)' using i ;
??????????? ? 7 ????? end loop ;
??????????? ? 8 ? end ;
??????????? ? 9 ? /
?
??????????? Procedure created .
?
??????????? scott@ASMDB > create or replace procedure proc2 -- 創建存儲過程 proc2 ，未使用綁定變量，因此每一個 SQL 插入語句都會硬解析
??????????? ? 2 ? as
??????????? ? 3 ? begin
??????????? ? 4 ????? for i in 1..10000
??????????? ? 5 ????? loop
??????????? ? 6 ????????? execute immediate 'insert into tb_test values(' || i|| ')' ;
??????????? ? 7 ????? end loop ;
??????????? ? 8 ? end ;
??????????? ? 9 ? /
?
??????????? 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 .
???????????
??????? 由上面的示例可知，在未使用綁定變量的情形下，不論是解析次數，閂使用的數量，隊列，分配的內存，庫緩存，行緩存遠遠高于綁定
??????? 變量的情況。因此盡可能的使用綁定變量避免硬解析產生所需的額外的系統資源。
???????
??????? 綁定變量的優點
??????????? 減少 SQL 語句的硬解析，從而減少因硬解析產生的額外開銷 ( CPU , Shared pool , latch ) 。其次提高編程效率，減少數據庫的訪問次數。
??????? 綁定變量的缺點
??????????? 優化器就會忽略直方圖的信息，在生成執行計劃的時候可能不夠優化。 SQL 優化相對比較困難
?
六、總結
??? 1. 盡可能的避免硬解析，因為硬解析需要更多的 CPU 資源，閂等。
??? 2.cursor_sharing 參數應權衡利弊，需要考慮使用 similar 與 force 帶來的影響。
??? 3. 盡可能的使用綁定變量來避免硬解析。
???
七、更多參考
有關閃回特性請參考
??????? Oracle 閃回特性(FLASHBACK DATABASE)
Oracle 閃回特性(FLASHBACK DROP & RECYCLEBIN)
Oracle 閃回特性(Flashback Query 、Flashback Table)
Oracle 閃回特性(Flashback Version 、Flashback Transaction)
?
有關基于用戶管理的備份和備份恢復的概念請參考：
??????? Oracle 冷備份
??????? Oracle 熱備份
??????? Oracle 備份恢復概念
??????? Oracle 實例恢復
??????? Oracle 基于用戶管理恢復的處理 ( 詳細描述了介質恢復及其處理 )
???????
??? 有關 RMAN 的恢復與管理請參考：
??????? RMAN 概述及其體系結構
??????? RMAN 配置、監控 與管理
??????? RMAN 備份詳解
??????? RMAN 還原與恢復
???????
??? 有關 Oracle 體系結構請參考：
??????? Oracle 實例和Oracle 數據庫(Oracle 體系結構)
??????? Oracle 表空間與數據文件
??????? Oracle 密碼文件
??????? Oracle 參數文件
??????? Oracle 聯機重做日志文件(ONLINE LOG FILE)
??????? Oracle 控制文件(CONTROLFILE)
??????? Oracle 歸檔日志
???
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
