-- Thanks for the question regarding "difference between soft parse and hard parse", version 8.1.7.2
originally submitted on 26-Jan-2002 19:17 Eastern US time, last updated 5-Aug-2004 13:07

Hi Tom

Can you explain briefly the difference between soft and hard parse?

你能不能給我詳細介紹一下軟解析與硬解析的區別啊?

and we said...

Here is a long winded answer, it is extracted in part from a new book oming out soon "beginning Oracle programming" that I collaborated on:
這是一個很長的解釋,是從我與幾個同事一起合著的新書<>中提取出來的.

Parsing
解析

This is the first step in the processing of any statement in Oracle. Parsing is
the act of breaking the submitted statement down into its component parts ? determining what type of statement it is (query, DML, DDL) and performing various checks on it.
這是Oracle處理所有語句的第一步,解析就是將使用者提交的語句(根據一定的規則)分成不同的元件,來確定這條語句是什麼型別的語句(query,查詢,DML,資料操縱語言,DDL還是資料定義語言),以對這條語句執行各種不同的語法檢測.

The parsing process performs two main functions:
解析過程主要有一下兩個功能.

Syntax Check: is the statement a valid one? Does it make sense given the SQL grammar documented in the SQL Reference Manual. Does it follow all of the rules for SQL.
1. 語法檢查,這條語句是否合理? 它是否符合SQL語法文件<>的相關語法,是不是符合SQL語句的所有規則.

o Semantic Analysis: Going beyond the syntax ? is the statement valid in light of the objects in the database (do the tables and columns referenced exist). Do you have access to the objects ? are the proper privileges in place? Are there ambiguities in the statement ? for example if there are two tables T1 and T2 and both have a column X, the query ?select X from T1, T2 where ?? is ambiguous, we don?t know which table to get X from. And so on.
2.語義分析: 已經處理完語法檢查了嗎? 這條語句是否正確的引用了資料庫中物件
(是否語句中所有的表和欄位都存在). 是否有訪問這些物件的許可權? 是不是對應的許可權都有效(主要指role對應的許可權吧,:D)? 比如是否有如下的兩個表T1,T2,而這兩個表有相同的欄位名column X,而查詢語句 ? select X from T1,T2 where ??,(沒有明確指定列名),我們無法知道從哪個表去取出欄位X的值,以及類似的一系列問題.

So, you can think of parsing as basically a two step process, that of a syntax
check to check the validity of the statement and that of a semantic check ? to ensure the statement can execute properly. The difference between the two types of checks are hard for you to see ? Oracle does not come back and say ?it failed the syntax check?, rather it returns the statement with a error code and message. So for example, this statement fails with a syntax error:
因此,你可以認為解析基本上是由這兩個步驟組成的: 檢查語句有效性的語法檢查和確保語句能夠正常執行的語義檢查.這兩種檢查的區別就是你所要說的硬解析了.Oracle不會特別的指出這條語句沒有透過語法檢查,它給你返回這條語句對應的錯誤程式碼和錯誤資訊. 比如: 下面這條語句沒有透過語法檢查.

CODE:

SQL> select from where 2;
select from where 2
*
ERROR at line 1:
ORA-00936: missing expression

While this statement failed with a semantic error ? if the table NOT_A_TABLE existed and we had permission to access it, this statement would have succeeded:

而這條語句沒有透過語義檢查? 如果表NOT_A_TABLE存在,並且我們具有訪問這個表的許可權,這條語句就會正確執行了.

[Copy to clipboard] [ - ]

CODE:

SQL> select * from not_a_table;
select * from not_a_table
*
ERROR at line 1:
ORA-00942: table or view does not exist

That is the only way to really tell the difference between a semantic and
syntactic error ? if the statement COULD have executed given the proper objects and privileges, you had a semantic error, otherwise if the statement could not execute under any circumstances, you have a syntax error. Regardless ? Oracle will not execute the statement for you!

只是我能用來解釋語法錯誤和語義錯誤的唯一的辦法了.如果在賦予合適的物件和許可權的情況下,這條語句確實能夠執行的話,我們稱之為語義錯誤,否則,如果這條語句在任何條件下都不能執行的話,我們就稱之為語法錯誤.無論如何,Oracle都不會為你執行這條語句.

The next step in the parse operation is to see if the statement we are currently parsing has already in fact been processed by some other session. If it has ? we may be in luck here, we can skip the next two steps in the process, that of optimization and row source generation. If we can skip these next two steps in the process, we have done what is known as a Soft Parse ? a shorter process to getting our query going. If we cannot, if we must do all of the steps, we are performing what is known as a Hard Parse ? we must parse, optimize, generate the plan for the query. This distinction is very important. When developing our applications we want a very high percentage of our queries to be Soft Parsed ? to be able to skip the optimize/generate phases ? as they are very CPU intensive as well as a point of contention (serialization). If we have to Hard Parse a large percentage of our queries, our system will function slowly and in some cases ? not at all.

解析中的下一個步驟就是檢查我們現在正在解析的語句是否已經被其他的session執行過,在這一步我們可能很幸運,我們可以跳過下面的兩個步驟: 語句的最佳化(生成執行計劃) 和生成執行編碼. 如果我們能夠跳過這兩個步驟,我們就實現了一般意義上的軟解析, 我們必須解析,最佳化,併為這條語句生成執行計劃.這個特性(distinction)非常重要,當我們開發應用程式的時候,我們會希望大部分的語句都是軟解析的,以跳過最佳化和生成編碼的步驟,因為他們和爭用(序列化,)(contention)一樣,都是非常消耗CPU的操作.如果我們必須硬解析我們語句的大部分的話,在某些情況下我們的系統就會執行的很慢,並不是任何時候(在olap,dss系統應該是個例外吧:))

The way this sharing of SQL in Oracle is accomplished is via the shared pool, a piece of memory in the SGA maintained by Oracle. We covered this topic in chapter 5 but will revisit it again in the context of processing a query. After Oracle parses the query and it passes the syntax and semantic checks ? it will look in the shared pool component of the SGA to see if that same exact query has already been processed by another session. Since it has performed the semantic check it has already figured out:

在Oracle中共享SQL的方式是透過共享池--SGA中的一塊記憶體(由Oracle系統來維護)--實現的.我們在第五章中討論了這個主題,並且會在查詢處理的章節再次討論這個主題.當Oracle已經解析了這條語句,並且已經透過語法和語義檢查的時候,Oracle就會在SGA的shared pool(共享池)元件中查詢,看看,是否已經有一個完全一樣的語句已經被另外一個session執行過.因為語句已經透過了oracle的語義檢查,並且oracle已經計算出:

o Exactly what tables are involved
具體涉及到哪些表.

o That we have access to the tables (the proper privileges are there)
我們擁有訪問對應表的個相應許可權.等等...

And so on. Now, it can look at all of the queries in the shared pool that have already been parsed/optimized and generated to see if the work has already been done.
現在,可以在所有已經經過解析和最佳化並且生成的可執行編碼的SQL語句搜尋,來看看對應的語句是否已經經過解析.
Soft Parse and session_cashed_cursor parameter January 28, 2002
Reviewer: A reader
軟解析與session_cashed_cursor引數

can you explain what means the default value (0) of session_ casched_cursor parameter? Oracle always has a cache of sqls in sga and size of this cache is determinated through init.ora parameter shared_pool_size.

tom : 你能不能給我解釋一下,session_cashed_cursor(預設值為0)引數的含義,Oracle在SGA中始終保持一個sql語句的快取.這個快取的大小是由初始化引數shared_pool_size的大小決定的.

Followup:
The default value of ZERO means that by default, ZERO cursors will be cached for your session.

預設值為0表示,在預設情況下,Oracle不會給你的session快取遊標.

They will be cached in the shared pool -- but your session will have to find
them there. Session Cached Cursors can remove the need to have to "find" them. Consider this example that shows the difference spent when soft parsing without session cached cursors vs soft parsing WITH session cached cursors:
他們會被快取在共享池(Shared Pool)中,但是你的session必須在共享池中去查詢他們,session cached cursors可以省略掉去共享池中查詢這一步.下面這個例子
給你演示了沒有快取遊標和快取了遊標之後軟解析之間的差別和系統消耗資源的情況

[Copy to clipboard] [ - ]

CODE:

ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> create table emp as select * from scott.emp;
Table created.

ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> create table run_stats ( runid varchar2(15),
name varchar2(80), value int );
Table created.

ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> create or replace view stats
2 as select 'STAT...' || a.name name, b.value
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 union all
6 select 'LATCH.' || name, gets
7 from v$latch;

View created.

ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> column name format a40
ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> declare
2 l_start number;
3 l_cnt number;
4 begin
5 execute immediate 'alter session set session_cached_cursors=0';
6 insert into run_stats select 'before', stats.* from stats;
7
8 l_start := dbms_utility.get_time;
9 for i in 1 .. 1000
10 loop
11 execute immediate 'select count(*) from emp' into l_cnt;
12 end loop;
13 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
14
15 execute immediate 'alter session set session_cached_cursors=100';
16 insert into run_stats select 'after 1', stats.* from stats;
17
18 l_start := dbms_utility.get_time;
19 for i in 1 .. 1000
20 loop
21 execute immediate 'select count(*) from emp' into l_cnt;
22 end loop;
23 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
24
25 insert into run_stats select 'after 2', stats.* from stats;
26 end;
27 /
45 hsecs
35 hsecs

PL/SQL procedure successfully completed.

so, session cached cursors RAN faster (i ran this a couple of times, there
were no hard parses going on. But the real good news is:
因此,在session中快取遊標可以執行的更快(我執行了好幾次,沒有硬解析的發生),真正可喜的訊息是:

[Copy to clipboard] [ - ]

CODE:

ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> select a.name, b.value-a.value run1,
c.value-b.value run2,
2 ( (c.value-b.value)-(b.value-a.value)) diff
3 from run_stats a, run_stats b, run_stats c
4 where a.name = b.name
5 and b.name = c.name
6 and a.runid = 'before'
7 and b.runid = 'after 1'
8 and c.runid = 'after 2'
9 and (c.value-a.value) > 0
10 and (c.value-b.value) <> (b.value-a.value)
11 order by abs( (c.value-b.value)-(b.value-a.value))
12 /

NAME RUN1 RUN2 DIFF
---------------------------------------- ---------- ---------- ----------
LATCH.checkpoint queue latch 3 4 1
LATCH.redo allocation 30 31 1
STAT...consistent gets 5088 5089 1
STAT...deferred (CURRENT) block cleanout 2 3 1
applications

STAT...calls to get snapshot scn: kcmgss 5019 5018 -1
STAT...enqueue releases 10 9 -1
STAT...execute count 1015 1014 -1
STAT...opened cursors cumulative 1015 1014 -1
STAT...parse count (total) 1015 1014 -1
STAT...session cursor cache count 0 1 1
STAT...redo entries 28 27 -1
STAT...recursive calls 1180 1179 -1
STAT...physical reads 1 0 -1
LATCH.direct msg latch 2 0 -2
LATCH.session queue latch 2 0 -2
LATCH.done queue latch 2 0 -2
STAT...free buffer requested 8 6 -2
STAT...enqueue requests 11 9 -2
LATCH.messages 3 0 -3
STAT...db block changes 47 44 -3
LATCH.redo writing 3 0 -3
LATCH.ksfv messages 4 0 -4
STAT...session logical reads 17128 17123 -5
LATCH.row cache objects 184 178 -6
STAT...db block gets 12040 12034 -6
STAT...parse time elapsed 9 3 -6
STAT...parse time cpu 13 4 -9
STAT...recursive cpu usage 51 38 -13
LATCH.cache buffers chains 34315 34335 20
STAT...redo size 23900 24000 100
STAT...session cursor cache hits 3 1002 999
LATCH.shared pool 2142 1097 -1045
LATCH.library cache 17361 2388 -14973

34 rows selected.

ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]>

see the significantly REDUCED number of LATCH counts on the library and shared pool. Since a latch is a lock, a lock is a serialization device, serialization
implies WAITS -- using the session cached cursors will increase scalability and performance as you add more and more users. Its not only FASTER, but more scalable as well...
看到庫快取和共享池中鎖存(Latch)數目的明顯的下降了嗎,因為鎖存(Latch)就是鎖(Lock),而鎖(Lock)就是隻能序列執行的裝置,序列就意味著等待--使用session快取的遊標可以提高系統的擴充套件性(scalability)和效能,當你的系統的使用者數不斷增加的時候,它不僅可以執行的更加快捷,而且可以提高了系統的擴充套件性

關於軟解析(soft parse)與硬解析(hard parse),以及session cached cursors (asktom)

相關文章