SQL語句的解析有軟解析softparse與硬解析hardparse之說

1、SQL語句的解析有軟解析 soft parse與硬解析hard parse之說SQL語句的解析有軟解析 soft parse與硬解析hard parse之說：以下是 5 個步驟：1：語法是否合法 (sql 寫法 )2：語義是否合法 (權(quán)限，對象是否存在 )3:檢查該 sql 是否在公享池中存在-如果存在 ,直接跳過 4 和 5,運行 sql. 此時算 soft parse4：選擇執(zhí)行計劃5：產(chǎn)生執(zhí)行計劃-如果 5 個步驟全做 ,這就叫 hard parse.提到軟解析(soft parse)和硬解析(hard parse),就不能不說一下 Oracle對sql的處理過程。當(dāng)你發(fā)出一條sql語句

2、交付 Oracle, 在執(zhí)行和獲取結(jié)果前， Oracle 對此 sql 將進(jìn)行幾個步驟的處 理過程：1、語法檢查 (syntax check)檢查此 sql 的拼寫是否語法。2、語義檢查 (semantic check)諸如檢查 sql 語句中的訪問對象是否存在及該用戶是否具備 相應(yīng)的權(quán)限。3、對 sql 語句進(jìn)行解析 (parse) 利用內(nèi)部算法對sql進(jìn)行解析，生成解析樹(parse tree)及執(zhí)行 計劃 (execution plan) 。4、執(zhí)行 sql，返回結(jié)果(execute and return) 其中，軟、硬解析就發(fā)生在第三個過程里。Oracle利用內(nèi)部的hash算法來取得該

3、sql的hash值，然后在 library cache 里查找是否存在該 hash 值； 假設(shè)存在，則將此 sql 與 cache 中的進(jìn)行比較； 假設(shè)“ 相同” ，就將利用已有的解析樹與執(zhí)行計 劃，而省略了優(yōu)化器的相關(guān)工作。這也就是軟解析的過程。 誠然，如果上面的 2 個假設(shè)中任有一個不成立，那么優(yōu)化器 都將進(jìn)行創(chuàng)建解析樹、生成執(zhí)行計劃的動作。這個過程就叫 硬解析。創(chuàng)建解析樹、生成執(zhí)行計劃對于 sql 的執(zhí)行來說是開銷昂貴 的動作，所以，應(yīng)當(dāng)極力避免硬解析，盡量使用軟解析。 這就是在很多項目中，倡導(dǎo)開發(fā)設(shè)計人員對功能相同的代碼 要努力保持代碼的一致性，

4、以及要在程序中多使用綁定變量 的原因。軟解析、硬解析的一個小測試：軟解析、硬解析的一個小測試：SQL> create global temporary table sess_event on commit preserve rows as select * from v$session_event where 1=0; Table createdSQL> insert into sess_event2 select * from v$session_event3 where sid=(select sid from v$mystat where rownum=1);

5、9 rows insertedSQL>SQL> SELECT NAME,V ALUE FROM V$MYSTATA,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#2 AND NAME LIKE 'parse count%'3 ;NAME V ALUE parse count (total) 32127parse count (hard) 30365parse count (failures) 1 SQL>SQL> begin2 for i in 1.10000 loop3 exec

6、ute immediate 'insert into test values('|i|','|i|')'4 end loop;5 commit;6 end;7 /PL/SQL procedure successfully completed SQL>SQL> SELECT NAME,V ALUE FROM V$MYSTATA,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#2 AND NAME LIKE 'parse count%'NAME V ALUE pars

7、e count (total) 42201parse count (hard) 40379parse count (failures) 1 沒有使用綁定變量 (這種寫法往往有人 誤認(rèn)為使用了綁定 ,這里就不多解釋了 )總解析次數(shù)： 10074 硬解析次數(shù)： 10014上邊 2個值除了我們的循環(huán) 10000 次外，還包括其他一些系 統(tǒng)表的讀寫，所以解析次數(shù)大于10000，但是我們要注意下面的結(jié)果，對于每個 SQL都有一個版本，也就是 ORACLE 對于每個不同的 SQL 分別 執(zhí)行了硬解析， 下面是共享池最后緩存的數(shù)據(jù) (部分已經(jīng)被覆 蓋 )SQL> selectSQL_TEXT,

8、VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper(sql_text) like '%TEST%'SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS insert into test values(9630,9630) 1 1 1insert into test values(9950,9950) 1 1 1 insert into test values(9625,9625) 1 1 1insert into test values(9592,9592) 1 1

9、 1insert into test values(9940,9940) 1 1 1insert into test values(9897,9897) 1 1 1中間內(nèi)容略 SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS insert into test values(9838,9838) 1 1 1insert into test values(9716,9716) 1 1 1insert into test values(9691,9691) 1 1 1insert into test values(9831,9831) 1 1 1insert

10、 into test values(9992,9992) 1 1 1insert into test values(9883,9883) 1 1 1insert into test values(9865,9865) 1 1 1435 rows selectedSQL> truncate talbe sess_event;truncate talbe sess_eventORA-03290: 無效的截斷命令 - 缺失 CLUSTER 或 TABLE 關(guān)鍵字 SQL> truncate table sess_event;Table truncatedSQL&g

11、t; alter system flush shared_pool;System alteredSQL>SQL> insert into sess_event2 select * from v$session_event3 where sid=(select sid from v$mystat where rownum=1);10 rows insertedSQL>SQL> SELECT NAME,V ALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC# 2 A

12、ND NAME LIKE 'parse count%'NAME V ALUE parse count (total) 42643parse count (hard) 40456parse count (failures) 2SQL>SQL> begin2 for i in 1.10000 loop3 execute immediate 'insert into test values(:v1,:v2)' using i,i;4 end loop;5 commit;6 end;7 /PL/SQL procedure successful

13、ly completedSQL> SQL> SELECT NAME,V ALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#2 AND NAME LIKE 'parse count%'NAME V ALUE parse count (total) 42688 parse count (hard) 40466 parse count (failures) 2 下面看下使用綁定變量的情況 (真正使 用了綁定 ) 總解析次數(shù)： 45硬解析次數(shù)： 10我們可以看出差異是多么大了，

14、呵呵， 對于 SQL 本身只有一 次軟解析，執(zhí)行次數(shù)為 10000SQL> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIO NS from v$sqlarea where upper(sql_text) like '%TEST%'SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS begin for i in 1.10000 loop execute immediate 'insert into test values(:v1,:v2 1 1 1update sy

15、s.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat 1 1 1insert into test values(:v1,:v2) 1 1 10000selectSQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1selectSQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1SQL>

16、alter system flush shared_pool;System altered 但是我們要注意一個情況，即時同樣的 SQL 如果沒有使用綁定變量， ORACLE 也會對其執(zhí)行一次軟解析 的，但是沒有硬解析，如下：每執(zhí)行一次 SQL ，也會同時產(chǎn)生其他寫系統(tǒng)表等很多相關(guān)的 軟解析包括查詢 SQL 本身。硬解析不變。SQL> insert into test values('1','1111111111');1 row insertedSQL> commit;Commit completeSQL> select

17、SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIO NS from v$sqlarea where upper(sql_text) like '%TEST%'SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat 1 1 1insert into test values('1','1111111111') 1 1 1s

18、elect SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIO NS from v$sqlarea where upper( 1 1 1 select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIO NS from v$sqlarea where upper( 1 1 1SQL> insert into test values('1','1111111111');1 row insertedSQL> commit;Commit completeSQL&

19、gt; selectSQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIO NS from v$sqlarea where upper(sql_text) like '%TEST%'SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat 1 1 1insert into test values('1','1111111111

20、9;) 1 2 2 selectSQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIO NS from v$sqlarea where upper( 1 1 1 selectSQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIO NS from v$sqlarea where upper( 1 2 2SQL> SQL> SELECT NAME,V ALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC# 2 AND NAME LI

21、KE 'parse count%'NAME V ALUEparse count (total) 42906parse count (hard) 40520parse count (failures) 2SQL> insert into test values('1','1111111111');1 row insertedSQL> commit;Commit completeSQL> selectSQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sq

22、larea where upper(sql_text) like '%TEST%'SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONSbegin dbsnmp.mgmt_response.get_latest_curs(:a); end; 1 1 1 /*/* OracleOEM */ BEGIN IF (:1 = 'READ WRITE') THEN - For a read- 1 1 1/*/* OracleOEM */ DECLARE instance_number NUMBER; latest_task_id NUM

23、BER; 1 1 1 update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat 1 4 4 insert into test values('1','1111111111') 1 3 3 DELETE FROM MGMT_DB_LATEST_HDM_FINDINGS WHERE TARGET_GUID = :B2 AND COLLECTION_T 2 3 3selectSQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIO NS

24、from v$sqlarea where upper( 1 1 1 selectSQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIO NS from v$sqlarea where upper( 1 3 38 rows selectedSQL> SQL> SELECT NAME,V ALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC# 2 AND NAME LIKE 'parse count%'NAME V ALUE parse count

25、 (total) 42922parse count (hard) 40520parse count (failures) 2SQL> insert into test values('1','1111111111');1 row insertedSQL> commit;Commit completeSQL> insert into test values('1','1111111111');1 row insertedSQL> commit;Commit completeSQL> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIO NS from v$sqlarea where upper(sql_text) like '%TEST%'SQL_TEXT VERSION_COUNT PARSE_CALLSEXECUTIONSbegin dbsnmp.mgmt_response.get_latest_curs(:a); end; 1 1 1 /*/* OracleOEM */ BEGIN IF (:1 = 'READ WRITE'