檢視oracle硬解析,oracle硬解析和軟解析

提到軟解析(soft parse)和硬解析(hard parse)，就不能不說一下Oracle對sql的處理過程。當你發出一條sql語句傳遞Oracle，在執行和擷取結果前，Oracle對此sql将進行幾個步驟的處理過程：

1、文法檢查(syntax check)

檢查此sql的拼寫是否文法。

2、語義檢查(semantic check)

諸如檢查sql語句中的通路對象是否存在及該使用者是否具備相應的權限。

3、對sql語句進行解析(parse)

利用内部算法對sql進行解析，生成解析樹(parse tree)及執行計劃(execution plan)。

4、執行sql，傳回結果(execute and return)

其中，軟、硬解析就發生在第三個過程裡。

Oracle利用内部的hash算法來取得該sql的hash值，然後在library cache裡查找是否存在該hash值；

假設存在，則将此sql與cache中的進行比較；

假設“相同”，就将利用已有的解析樹與執行計劃，而省略了優化器的相關工作。這也就是軟解析的過程。

誠然，如果上面的2個假設中任有一個不成立，那麼優化器都将進行建立解析樹、生成執行計劃的動作。這個過程就叫硬解析。

建立解析樹、生成執行計劃對于sql的執行來說是開銷昂貴的動作，是以，應當極力避免硬解析，盡量使用軟解析。

這就是在很多項目中，倡導開發設計人員對功能相同的代碼要努力保持代碼的一緻性，以及要在程式中多使用綁定變量的原因。

Oracle中的SQL在執行之前進行解析，一個硬解析包括下面的步驟：

1. 加載到共享池中 - SQL源代碼被加載到記憶體中。

2. 文法解析 - Oracle檢查文法拼寫錯誤。

3. 語義解析 - Oracle驗證來自資料字典的所有表名和列名并且驗證你是否有權通路這些資料。

4. 查詢轉換 - 如果允許(query_rewrite=true)，oracle将把複雜的SQL轉換為等價的簡單形式。

5. 優化 - 根據模式的統計資訊建立執行計劃(在10g中或許會使用動态的樣本統計資訊)。

6. 建立可執行檔案 - Oracle建立一個服務于SQL查詢的、調用本地檔案的可執行檔案。

Oracle提供了 shared_pool_size參數來緩存SQL進而使我們不需要重複解析SQL。但是，如果shared_pool_size設定太小或者在代碼中使用了非重用的SQL(例如包含直接量where name="fred")，SQL語句可能會過期。

在Oracle中軟解析和硬解析的差別是什麼？僅僅是上面用紅色标明的第一步。也就是說，軟解析不需要重新裝載到共享池(以及相關的記憶體配置設定)。

通常很高的解析調用次數(>10/秒)表明你的系統有大量不同的SQL語句，或者你的SQL語句沒有被重用(例如，沒有使用綁定變量)。

硬解析需要把 SQL語句加載到共享池中。硬解析比軟解析差很多，因為它牽涉到共享池中記憶體的配置設定和管理。一旦被加載，SQL必須完全重新檢查文法和語義以及生成可執行目标。

如果shared_pool_size設定的太小或者SQL語句沒有重用，則會發生大量的硬解析。

是以要适當的設定共享池大小并且通過主機變量重用SQL。

軟解析、硬解析的一個小測試：

SQL> create global temporary table sess_event on commit preserve rows as select * from v$session_event where 1=0;

Table created

SQL> insert into sess_event

2 select * from v$session_event

3 where sid=(select sid from v$mystat where rownum=1);

9 rows inserted

SQL>

SQL> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#

2 AND NAME LIKE 'parse count%'

3 ;

NAME VALUE

---------------------------------------------------------------- ----------

parse count (total) 32127

parse count (hard) 30365

parse count (failures) 1

SQL>

SQL> begin

2 for i in 1..10000 loop

3 execute immediate 'insert into test values('||i||','||i||')';

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed

SQL>

SQL> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#

2 AND NAME LIKE 'parse count%';

NAME VALUE

---------------------------------------------------------------- ----------

parse count (total) 42201

parse count (hard) 40379

parse count (failures) 1

沒有使用綁定變量(這種寫法往往有人誤認為使用了綁定,這裡就不多解釋了)總解析次數：10074

硬解析次數：10014

上邊2個值除了我們的循環10000次外，還包括其他一些系統表的讀寫，是以解析次數大于10000，但是我們要注意下面的結果，對于每個SQL

都有一個版本，也就是ORACLE對于每個不同的SQL分别執行了硬解析，下面是共享池最後緩存的資料(部分已經被覆寫)

SQL> select SQL_TEXT,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 1

insert into test values(9950,9950) 1 1 1

insert into test values(9625,9625) 1 1 1

insert into test values(9592,9592) 1 1 1

insert into test values(9940,9940) 1 1 1

insert into test values(9897,9897) 1 1 1

insert into test values(9679,9679) 1 1 1

insert into test values(9850,9850) 1 1 1

insert into test values(9744,9744) 1 1 1

insert into test values(9938,9938) 1 1 1

insert into test values(9977,9977) 1 1 1

insert into test values(9907,9907) 1 1 1

insert into test values(9809,9809) 1 1 1

insert into test values(9800,9800) 1 1 1

insert into test values(9645,9645) 1 1 1

insert into test values(9724,9724) 1 1 1

insert into test values(9799,9799) 1 1 1

insert into test values(9818,9818) 1 1 1

insert into test values(9642,9642) 1 1 1

insert into test values(9624,9624) 1 1 1

中間内容略

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS

-------------------------------------------------------------------------------- ------------- ----------- ----------

insert into test values(9838,9838) 1 1 1

insert into test values(9716,9716) 1 1 1

insert into test values(9691,9691) 1 1 1

insert into test values(9831,9831) 1 1 1

insert into test values(×××,×××) 1 1 1

insert into test values(9883,9883) 1 1 1

insert into test values(9865,9865) 1 1 1

insert into test values(9901,9901) 1 1 1

insert into test values(9657,9657) 1 1 1

insert into test values(10000,10000) 1 1 1

insert into test values(9659,9659) 1 1 1

insert into test values(9746,9746) 1 1 1

insert into test values(9695,9695) 1 1 1

insert into test values(9869,9869) 1 1 1

insert into test values(9804,9804) 1 1 1

insert into test values(9843,9843) 1 1 1

435 rows selected

SQL> truncate talbe sess_event;

truncate talbe sess_event

ORA-03290: 無效的截斷指令 - 缺失 CLUSTER 或 TABLE 關鍵字

SQL> truncate table sess_event;

Table truncated

SQL> alter system flush shared_pool;

System altered

SQL>

SQL> insert into sess_event

2 select * from v$session_event

3 where sid=(select sid from v$mystat where rownum=1);

10 rows inserted

SQL>

SQL> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#

2 AND NAME LIKE 'parse count%';

NAME VALUE

---------------------------------------------------------------- ----------

parse count (total) 42643

parse count (hard) 40456

parse count (failures) 2

SQL>

SQL> begin

2 for i in 1..10000 loop

3 execute immediate 'insert into test values(:v1,:v2)' using i,i;

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed

SQL>

SQL> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#

2 AND NAME LIKE 'parse count%';

NAME VALUE

---------------------------------------------------------------- ----------

parse count (total) 42688

parse count (hard) 40466

parse count (failures) 2

下面看下使用綁定變量的情況(真正使用了綁定)總解析次數：45

硬解析次數：10

我們可以看出差異是多麼大了，呵呵，對于SQL本身隻有一次軟解析，執行次數為10000

SQL> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS 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 1

update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat 1 1 1

insert into test values(:v1,:v2) 1 1 10000

select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1

select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1

SQL> alter system flush shared_pool;

System altered

但是我們要注意一個情況，即時同樣的SQL如果沒有使用綁定變量，ORACLE也會對其執行一次軟解析的，但是沒有硬解析，如下：

每執行一次SQL，也會同時産生其他寫系統表等很多相關的軟解析包括查詢SQL本身。硬解析不變。

SQL> insert into test values('1','1111111111');

1 row inserted

SQL> commit;

Commit complete

SQL> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS 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 1

insert into test values('1','1111111111') 1 1 1

select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1

select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1

SQL> insert into test values('1','1111111111');

1 row inserted

SQL> commit;

Commit complete

SQL> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS 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 1

insert into test values('1','1111111111') 1 2 2

select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1

select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 2 2

SQL>

SQL> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#

2 AND NAME LIKE 'parse count%';

NAME VALUE

---------------------------------------------------------------- ----------

parse count (total) 42906

parse count (hard) 40520

parse count (failures) 2

SQL> insert into test values('1','1111111111');

1 row inserted

SQL> commit;

Commit complete

SQL> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper(sql_text) like '%TEST%';

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS

-------------------------------------------------------------------------------- ------------- ----------- ----------

begin dbsnmp.mgmt_response.get_latest_curs(:a); end; 1 1 1

 BEGIN IF (:1 = 'READ WRITE') THEN ---- -- For a read- 1 1 1

 DECLARE instance_number NUMBER; latest_task_id NUMBER; 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 3

select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1

select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 3 3

8 rows selected

SQL>

SQL> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#

2 AND NAME LIKE 'parse count%';

NAME VALUE

---------------------------------------------------------------- ----------

parse count (total) 42922

parse count (hard) 40520

parse count (failures) 2

SQL> insert into test values('1','1111111111');

1 row inserted

SQL> commit;

Commit complete

SQL> insert into test values('1','1111111111');

1 row inserted

SQL> commit;

Commit complete

SQL> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper(sql_text) like '%TEST%';

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS

-------------------------------------------------------------------------------- ------------- ----------- ----------

begin dbsnmp.mgmt_response.get_latest_curs(:a); end; 1 1 1

 BEGIN IF (:1 = 'READ WRITE') THEN ---- -- For a read- 1 1 1

 DECLARE instance_number NUMBER; latest_task_id NUMBER; 1 1 1

update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat 1 5 5

insert into test values('1','1111111111') 1 5 5

DELETE FROM MGMT_DB_LATEST_HDM_FINDINGS WHERE TARGET_GUID = :B2 AND COLLECTION_T 2 3 3

select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1

select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 4 4

8 rows selected

SQL>

SQL> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#

2 AND NAME LIKE 'parse count%';

NAME VALUE

---------------------------------------------------------------- ----------

parse count (total) 42946

parse count (hard) 40520

parse count (failures) 2

SQL>

通過這個例子我們也就知道V$SQLAREA中的解析包含什麼内容了。