SQL語句的解析有軟解析soft parse與硬解析hard parse之說：
以下是5個步驟：
1：語法是否合法(sql寫法)
2：語義是否合法(權(quán)限，對象是否存在)
3:檢查該sql是否在公享池中存在
--如果存在,直接跳過4和5,運行sql. 此時算soft parse
4：選擇執(zhí)行計劃
5：產(chǎn)生執(zhí)行計劃
--如果5個步驟全做,這就叫hard parse.
提到軟解析(soft parse)和硬解析(hard parse)，就不能不說一下Oracle對sql的處理過程。當(dāng)你發(fā)出一條sql語句交付Oracle，在執(zhí)行和獲取結(jié)果前，Oracle對此sql將進行幾個步驟的處理過程：
1、語法檢查(syntax check)
檢查此sql的拼寫是否語法。
2、語義檢查(semantic check)
諸如檢查sql語句中的訪問對象是否存在及該用戶是否具備相應(yīng)的權(quán)限。
3、對sql語句進行解析(parse)
利用內(nèi)部算法對sql進行解析，生成解析樹(parse tree)及執(zhí)行計劃(execution plan)。
4、執(zhí)行sql，返回結(jié)果(execute and return)
其中，軟、硬解析就發(fā)生在第三個過程里。
Oracle利用內(nèi)部的hash算法來取得該sql的hash值，然后在library cache里查找是否存在該hash值；
假設(shè)存在，則將此sql與cache中的進行比較；
假設(shè)“相同”，就將利用已有的解析樹與執(zhí)行計劃，而省略了優(yōu)化器的相關(guān)工作。這也就是軟解析的過程。
誠然，如果上面的2個假設(shè)中任有一個不成立，那么優(yōu)化器都將進行創(chuàng)建解析樹、生成執(zhí)行計劃的動作。這個過程就叫硬解析。
創(chuàng)建解析樹、生成執(zhí)行計劃對于sql的執(zhí)行來說是開銷昂貴的動作，所以，應(yīng)當(dāng)極力避免硬解析，盡量使用軟解析。
這就是在很多項目中，倡導(dǎo)開發(fā)設(shè)計人員對功能相同的代碼要努力保持代碼的一致性，以及要在程序中多使用綁定變量的原因。
軟解析、硬解析的一個小測試：
軟解析、硬解析的一個小測試：
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
沒有使用綁定變量(這種寫法往往有人誤認(rèn)為使用了綁定,這里就不多解釋了)總解析次數(shù)：10074
硬解析次數(shù)：10014
上邊2個值除了我們的循環(huán)10000次外，還包括其他一些系統(tǒng)表的讀寫，所以解析次數(shù)大于10000，但是我們要注意下面的結(jié)果，對于每個SQL
都有一個版本，也就是ORACLE對于每個不同的SQL分別執(zhí)行了硬解析，下面是共享池最后緩存的數(shù)據(jù)(部分已經(jīng)被覆蓋)
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
中間內(nèi)容略
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(9992,9992) 1 1 1
insert into test values(9883,9883) 1 1 1
insert into test values(9865,9865) 1 1 1
435 rows selected
SQL> truncate talbe sess_event;
truncate talbe sess_event
ORA-03290: 無效的截斷命令 - 缺失 CLUSTER 或 TABLE 關(guān)鍵字
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
下面看下使用綁定變量的情況(真正使用了綁定)總解析次數(shù)：45
硬解析次數(shù)：10
我們可以看出差異是多么大了，呵呵，對于SQL本身只有一次軟解析，執(zhí)行次數(shù)為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也會對其執(zhí)行一次軟解析的，但是沒有硬解析，如下：
每執(zhí)行一次SQL，也會同時產(chǎn)生其他寫系統(tǒng)表等很多相關(guān)的軟解析包括查詢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
/**//* OracleOEM */ BEGIN IF (:1 = 'READ WRITE') THEN ---- -- For a read- 1 1 1
/**//* OracleOEM */ 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
/**//* OracleOEM */ BEGIN IF (:1 = 'READ WRITE') THEN ---- -- For a read- 1 1 1
/**//* OracleOEM */ 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中的解析包含什么內(nèi)容了。
soft parse and hard parse
說到軟解析(soft prase)和硬解析(hard prase)，就不能不說一下Oracle對sql的處理過程。當(dāng)你發(fā)出一條sql語句交付Oracle，在執(zhí)行和獲取結(jié)果前，Oracle對此sql將進行幾個步驟的處理過程：
1、語法檢查(syntax check)
檢查此sql的拼寫是否語法。
2、語義檢查(semantic check)
諸如檢查sql語句中的訪問對象是否存在及該用戶是否具備相應(yīng)的權(quán)限。
3、對sql語句進行解析(prase)
利用內(nèi)部算法對sql進行解析，生成解析樹(parse tree)及執(zhí)行計劃(execution plan)。
4、執(zhí)行sql，返回結(jié)果(execute and return)
其中，軟、硬解析就發(fā)生在第三個過程里。
Oracle利用內(nèi)部的hash算法來取得該sql的hash值，然后在library cache里查找是否存在該hash值；
假設(shè)存在，則將此sql與cache中的進行比較；
假設(shè)“相同”，就將利用已有的解析樹與執(zhí)行計劃，而省略了優(yōu)化器的相關(guān)工作。這也就是軟解析的過程。
誠然，如果上面的2個假設(shè)中任有一個不成立，那么優(yōu)化器都將進行創(chuàng)建解析樹、生成執(zhí)行計劃的動作。這個過程就叫硬解析。
創(chuàng)建解析樹、生成執(zhí)行計劃對于sql的執(zhí)行來說是開銷昂貴的動作，所以，應(yīng)當(dāng)極力避免硬解析，盡量使用軟解析。
這就是在很多項目中，倡導(dǎo)開發(fā)設(shè)計人員對功能相同的代碼要努力保持代碼的一致性，以及要在程序中多使用綁定變量的原因。
-----------------------------------------ask TOM-------------------------------------------------------------
今天跟一個網(wǎng)友討論了這個話題，加上以前的筆記大概整理出了一個脈絡(luò)，其間參考了不少資料，看了半天的外文，這里跟大家分享，省得大家看E文頭暈了 呵呵
嗯，嗯 今天沒有白忙啊……
什么叫soft parse hard parse
You Asked (Jump to Tom's latest followup)
Hi Tom
Can you explain briefly the difference between soft and hard parse
cheers
and we said...
Here is a long winded answer, it is extracted in part from a new book coming 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.
The parsing process performs two main functions:（parse 包含語義，語法分析）
o 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.
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.
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:
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:
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!
The next step
Session_Cached_Cursors和UGA關(guān)系2007-09-09 17:11UGA memory consumed by Session_Cached_Cursors
通過設(shè)置session_cached_cursors實現(xiàn)Soft Soft Parse，Session會將多次（大于3次）執(zhí)行的SQL Cursor緩存在UGA中，這需要占用一定量的UGA內(nèi)存。
如下測試代碼測試，因緩存游標(biāo)而具體消耗了多少UGA.
#
#測試使用MTS模式連接，版本Oracle9.2.0.5
#
#主要參考UGA的統(tǒng)計數(shù)據(jù)，MTS下PGA的統(tǒng)計與該參數(shù)設(shè)置無關(guān)
#
SQL> select * from v$statname where name like '%uga%';
STATISTIC# NAME
---------- --------------------------------------------
15 session uga memory
16 session uga memory max
SQL> select * from v$statname where name like '%pga%';
STATISTIC# NAME
---------- --------------------------------------------
20 session pga memory
21 session pga memory max
#
#數(shù)據(jù)庫默認(rèn)session_cached_cursors=0
#
SQL> conn binzhang/binzhang@mts9205
Connected.
查詢當(dāng)前會話ID
select userenv('sid') from dual;
SQL> select * from v$sesstat where STATISTIC# in (15,16,20,21) and sid=10;
SID STATISTIC# VALUE
---------- ---------- ----------
10 15 77336
10 16 77336
10 20 274536
10 21 274536
SQL> declare
2 a number;
3 begin
4 for j in 1..4 loop
5 for i in 1..1000 loop
6 execute immediate 'select count(*) from t where id='||i into a;
7 end loop;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> /
SID STATISTIC# VALUE
---------- ---------- ----------
10 15 77336
10 16 93400
10 20 274536
10 21 274536
#
# session uga memory保持不變，Session并沒有緩存任何closed cursor
#
SQL> alter session set session_cached_cursors=1000;
Session altered.
SQL> declare
2 a number;
3 begin
4 for j in 1..4 loop
5 for i in 1..1000 loop
6 execute immediate 'select count(*) from t where id='||i into a;
7 end loop;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> /
SID STATISTIC# VALUE
---------- ---------- ----------
10 15 1025112
10 16 1025112
10 20 274536
10 21 274536
#
#session uga memory有明顯增加，是因為緩存了1000個closed cursor (session cursor cache count)
#
經(jīng)過測試，uga memory的增加和SQL Text的長度沒有關(guān)系。UGA中只是緩存著必要的游標(biāo)指針和SQL HASH VALUE等信息
可以類似測試session_cached_cursor為20，50，100等常用數(shù)值下,uga memory的增加，計算需要的free memory的大小,防止因為內(nèi)存短缺而引起ORA-4031和ORA-4030錯誤的發(fā)生
SESSION_CACHED_CURSORS參數(shù)對性能的意義
SESSION_CACHED_CURSORS,就是說的是一個session可以緩存多少個cursor，讓后續(xù)相同的SQL語句不再打開游標(biāo),從而避免軟解析的過程來提高性能。(綁定變量是解決硬解析的問題)
軟解析同硬解析一樣,比較消耗資源.所以這個參數(shù)非常重要.
Oracle 9i及以前,該參數(shù)缺省是0,10G上缺省是20
session_cached_cursors,cursor_space_for_time,gets,pin
上周末看到asktom上的一個帖子，其中有人提到session_cached_cursors和cursor_space_for_time對library cache gets&pin的影響，帖子的url為
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:465420331879
其中tom還為我們展示了session_cached_cursors和cursor_space_for_time對library cache gets&pin在9i和10g中的影響是不同的。
先讓我們來了解一下session_cached_cursors，cursor_space_for_time，library cache gets，pin
session_cached_cursors:
設(shè)置pga端的cache list的長度，當(dāng)session_cached_cursors設(shè)置為0時，pga的cache list長度為0，這時候當(dāng)sga中的cursor關(guān)閉的時候它相關(guān)的library cache handle的lock位被清0，從v$open_cursor里看不到這個被關(guān)閉的cursor，它服從于shared pool的lru機制，當(dāng)shared pool需要新的buffer空間時，它將會被flush出shared pool。當(dāng)session_cached_cursors設(shè)置為非0值時，pga的cache list長度為session_cached_cursors值的大小，同時pga cache list將會保留一份拷貝，這時候即使sga中的cursor關(guān)閉的時候它相關(guān)的library cache handle始終被加了null mode lock，當(dāng)shared pool空間緊張時library cache handle始終將會被保留在shared pool中.而新的應(yīng)用訪問這個cursor的時候會直接去自己的pga cache list里面搜索。
cursor_space_for_time：
當(dāng)設(shè)置了session_cached_cursors為非0值后，如果cursor_space_for_time值被設(shè)為false，那么當(dāng)shared pool空間緊張時，雖然library cache handle不會被flush出去，但是它指向的library cached object(lco,其中包含了handle和children handle的地址，權(quán)限，類型，狀態(tài)，指向kgl block的指針，其中kgl block包含了真正的代碼段和執(zhí)行計劃等內(nèi)容）將會被flush出去而不管它相關(guān)的cursor有沒關(guān)閉，如果需要lco的時候?qū)⒁猺eloads。
如果cursor_space_for_time值被設(shè)為true，那么當(dāng)cursor在打開狀態(tài)下，handle指向的lco將不會被flush出shared pool，這樣就可以降低reloads出現(xiàn)的頻率。不過對于sql共享做的不好的數(shù)據(jù)庫，設(shè)置
cursor_space_for_time將會帶來一些問題，share pool可能會出現(xiàn)04031的錯誤。
gets:
當(dāng)試圖parse一句sql時，oracle要先獲得一個handle，在handle上加載一個lock，gets表示handle request times。
pin：
當(dāng)獲得handle后，定位到lco，然后pin住lco使它在被執(zhí)行的時候不被flush出去。
既然理解了以上一些概念，那么我們可以通過一些代碼演示session_cached_cursors,cursor_space_for_time對pin，gets的影響
在9i和10g中兩個參數(shù)對pin,gets的影響也不一樣
9i：
vi 1.sql
set wrap off
set linesize 100
set pagesize 0
set verify off
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA' ;
執(zhí)行10次sql
SQL 9I>show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_space_for_time boolean FALSE
session_cached_cursors integer 0
SQL 9I>@$HOME/1.sql
NAMESPACE GETS GETHITS PINS PINHITS
--------------- ---------- ---------- ---------- ----------
SQL AREA 2942908 2939105 75218597 75211231
SQL AREA 2942909 2939106 75218600 75211234
SQL AREA 2942910 2939107 75218603 75211237
SQL AREA 2942911 2939108 75218606 75211240
SQL AREA 2942912 2939109 75218609 75211243
SQL AREA
delphi變量綁定
procedure TForm1.Button1Click(Sender: TObject);
var i:String;
begin
i:='mal1000';
adoquery1.Close;
adoquery1.SQL.Text:= 'select * from sys.test where sex=:v1 ' ;
adoquery1.Parameters[0].Value:=i;
adoquery1.Open;
end;
Hi Tom
Can you explain briefly the difference between soft and hard parse?
你能不能給我詳細(xì)介紹一下軟解析與硬解析的區(qū)別啊?
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:
這是一個很長的解釋,是從我與幾個同事一起合著的新書<<開始學(xué)習(xí)Oracle編程>>中提取出來的.
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處理所有語句的第一步,解析就是將用戶提交的語句(根據(jù)一定的規(guī)則)分成不同的組件,
來確定這條語句是什么類型的語句(query,查詢,DML,數(shù)據(jù)操縱語言,DDL還是數(shù)據(jù)定義語言),以對這條語句執(zhí)行各種不同的語法檢測.
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語法文檔<>的相關(guān)語法,是不是符合SQL語句的所有規(guī)則.
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.語義分析: 已經(jīng)處理完語法檢查了嗎? 這條語句是否正確的引用了數(shù)據(jù)庫中對象
(是否語句中所有的表和字段都存在). 是否有訪問這些對象的權(quán)限? 是不是對應(yīng)的權(quán)限都有效(主要指role對應(yīng)的權(quán)限吧,)?
比如是否有如下的兩個表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:
因此,你可以認(rèn)為解析基本上是由這兩個步驟組成的: 檢查語句有效性的語法檢查和確保語句能夠正常運行的語義檢查.
這兩種檢查的區(qū)別就是你所要說的硬解析了.Oracle不會特別的指出這條語句沒有通過語法檢查,它給你返回這條語句對應(yīng)的錯誤代碼和錯誤信息.
比如: 下面這條語句沒有通過語法檢查.
Code: [Copy to clipboard]
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存在,并且我們具有訪問這個表的權(quán)限,這條語句就會正確執(zhí)行了.
Code: [Copy to clipboard]
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!
只是我能用來解釋語法錯誤和語義錯誤的唯一的辦法了.如果在賦予合適的對象和權(quán)限的情況下,這條語句確實能夠執(zhí)行的話,
我們稱之為語義錯誤,否則,如果這條語句在任何條件下都不能執(zhí)行的話,我們就稱之為語法錯誤.無論如何,Oracle都不會為你運行
