--=======================

-- Oracle 硬解析與軟解析

--=======================

Oracle 硬解析與軟解析是我們經常遇到的問題，什麼情況會產生硬解析，什麼情況產生軟解析，又當如何避免硬解析？下面的描述將給出

軟硬解析的產生，以及硬解析的弊端和如何避免硬解析的產生。

一、SQL語句的執行過程

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

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

a.SQL程式碼的語法(語法的正確性)及語義檢查(物件的存在性與許可權)。

b.將SQL程式碼的文字進行雜湊得到雜湊值。

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

d.對於存在相同雜湊值的新命令列，其文字將與已存在的命令列的文字逐個進行比較。這些比較包括大小寫，字串是否一致，空格，註釋

等，如果一致，則對其進行軟解析，轉到步驟f。否則到d步驟。紅色字型描述有誤應該轉到步驟e(更正@20130905，謝網友指出)

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=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"

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 基於使用者管理恢復的處理(詳細描述了介質恢復及其處理)

有關RMAN的恢復與管理請參考：

有關Oracle體系結構請參考：

Oracle 例項和Oracle資料庫(Oracle體系結構)

Oracle 聯機重做日誌檔案(ONLINE LOG FILE)

Oracle 控制檔案(CONTROLFILE)

Oracle 歸檔日誌

--&gt>轉載於:http://blog.csdn.net/leshami/article/details/6195483

Oracle 硬解析與軟解析

相關文章