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中應該儘量避免硬解析，下面為使用非繫結變數和繫結變數的情況：

--建立測試表

create table test
(
   id1 number,
   id2 number
);

--查詢存在的 parse count (解析數)

select name, value from v$mystat a , v$statname b
where a.STATISTIC#=b.STATISTIC#
and name like 'parse count%';

    NAME                VALUE
1 parse count (total) 56
2 parse count (hard) 7
3 parse count (failures) 0

--(硬解析)
begin
  for i in 1..10000 loop
    execute immediate 'insert into test values (' ||i||','||i|| ') ' ;
  end loop;
  commit;
end;

--已用時間:  00: 00: 05.08

--查詢執行sql後的 parse count (解析數)

select name, value from v$mystat a, v$statname b
where a.STATISTIC#=b.STATISTIC#
and name like 'parse count%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                   10064
parse count (hard)                                                    10011   硬解析次數為 10011-7
parse count (failures)                                                    0

-- 查詢shared_pool的關於執行insert into test語句的cache
select s.SQL_TEXT,s.VERSION_COUNT,s.PARSE_CALLS, s.EXECUTIONS  
from v$sqlarea s where s.SQL_TEXT like '%test%';

--alter system flush shared_pool; 清空共享池
********************************************************************
--使用繫結變數的情況
--查詢存在的 parse count (解析數)
select name, value from v$mystat a , v$statname b
where a.STATISTIC#=b.STATISTIC#
and name like 'parse count%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                     171
parse count (hard)                                                       36
parse count (failures)                                                    0

--(軟解析)
begin
  for i in 1..10000 loop
    execute immediate 'insert into test values (:v1,:v2) ' using i, i ;
  end loop;
  commit;
end;
--已用時間:  00: 00: 01.01 (硬解析為5秒多)

-查詢執行迴圈插入所生成的parse count (解析數)
select name, value from v$mystat a, v$statname b
where a.STATISTIC#=b.STATISTIC#
and name like 'parse count%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                   10206  
parse count (hard)                                                       46  (硬解析46次)
parse count (failures)                                                    0

-- 查詢shared_pool的關於執行insert into test語句的cache
select s.SQL_TEXT,s.VERSION_COUNT,s.PARSE_CALLS, s.EXECUTIONS  
from v$sqlarea s where s.SQL_TEXT like '%test%';
--查詢結果
insert into test values (:v1,:v2)共執行了10000次軟解析。
共執行10206-171 >1000次，原因是還包括其他一些系統表的讀寫。

http://jj2001hh.blog.163.com/blog/static/615781482008017104824467/