oracle實驗記錄（分析oracle硬解析&軟解析&fast soft parse）

解析步驟:
1.檢查語法
2.檢查sql語句涉及object是否存在,不存在則推出，檢查需要資料字典載入row cache
3.轉換物件名,（例如翻譯synonym 或實際名 test=xh.test），有問題退出parse
4.檢查發出sql的 user是否有對應許可權，許可權不足退出parse
5.根據object的統計資訊optimizer會建立一個執行計劃
6.將產生的執行計劃裝入shared pool 中library cache 中的heap
這六步完全執行就是hard parse

使用者發一條語句 (例如select * from test)oracle將這條語句轉為acsII數值，並進行利用 hash 函式進行 hash運算需要傳入hash函式 2個引數（name,namespace）
name 就是sql語句,namespace就是 'sqlarea' 對於SQL語句，計算出hash value（表示該語句被分派到library cache中此hash bucket中）然後到library cache 中對應的hash

bucket中比較下該bucket裡是否存在該語句（是否是第一次執行）這個計算hash value及其搜尋library cache找是否以前執行過存在該語句（即可共享用）需要持有library

cache latch（閂latch是輕量級的序列化裝置，用於協調對共享資料結構、物件和檔案的多使用者訪問），找到了為soft parse，執行SQL語句的server process利用找到以前執行過

的sql語句跳過解析步驟中的幾步並獲取該SQL的執行計劃釋放library cache latch 然後開始執行sql，沒找到則需要hard parse 釋放library cache latch 獲得shared pool

latch(需要將SQL語句，執行計劃寫入 shared pool中library cache) 查詢鎖定 shared pool 中free space ，釋放shared pool latch(主要控制shard pool中空間分配和回收)獲

得library cache latch（保護cache在記憶體中的sql及其執行計劃，要向library cache插入新sql及其執行計劃時需要library cache latch）開始解析　然後將sql及執行計劃插

入library cache，釋放library cache latch
執行sql　整個硬解析過程oracle會持有library cache(TOM:硬解析一個查詢時，資料庫會更長時間地佔用一種低階序列化裝置 latch)　所以hard parse非常影響latch 會造成長

時間持有造成latch 爭用

soft parse:跳過解析步驟中的幾步，但最後必須要使用共享的sql執行計劃，如果產生新執行計劃那麼就是hard parse

SQL> show parameter session_cache

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 30

10G 這個引數default 為20 ,9I 為0
多了這個後oracle 執行不步驟就變了

在算出hash value後 oracle 會查詢該session的PGA有的話直接執行避開所有parse

SQL語句的處理過程修正(參考http://www.itpub.net/thread-877110-1-5.html討論帖)

對照metalink給出的這個示意圖，我們可以對SQL的處理過程作如下的描述：
1、檢查是否有開啟的遊標，如果有，則直接通過遊標link到位於PGA的private SQL AREA（ private SQL area），轉步驟11。否則，執行步驟2。
2、檢查初始化引數SESSION_CACHED_CURSORS是否被設定，如果被設定，則同樣可以通過遊標指向到位於PGA的私有SQL AREA，轉步驟11。否則執行步驟3。
3、檢查HOLD_CURSOR以及RELEASE_CURSOR的設定。如果RELEASE_CURSOR=no（預設no），HOLD_CURSOR=yes（預設為no），當ORACLE執行完SQL語句，為private SQL AREA分配的記憶體

空間被保留，cursor和private SQL AREA之間的link也被保留，預編譯程式不再使用它，同樣可以通過這個指標直接在private SQL AREA獲得語句，轉步驟11。
這上面的三種情況，實際上都沒有作任何parse，都是直接從位於PGA中的private SQL AREA獲得語句並直接執行。此為fast parse。
這三種情況都不存在的情況下，oracle轉到步驟4執行。
4、建立一個遊標。
5、語法檢查Syntax Check：檢查語法書寫是否正確，是否符合SQL Reference Manual中給出的SQL語法。
6、語義分析Semantic Analysis：查詢資料字典，檢查表、列是否正確，在所要求的物件上獲取語法分析鎖，使得在語句的語法分析過程中不改變這些物件的定義，驗證為存取所

涉及的模式物件所需的許可權是否滿足。
7、將語句轉化成ASCII等效數字碼，再通過雜湊演算法得到雜湊值。
8、檢查庫快取中是否存在同樣hash值的語句。如果存在，轉步驟11。否則，執行步驟9。這就是soft parse。
9、選擇執行計劃。從可用的執行計劃中選擇一個最優的執行計劃，其中包括儲存大綱（srored outline）或物化檢視（materialized view）相關的決定。
10、生成該語句的一個編譯程式碼（p-code）。
11、執行語句。

SQL> show user
USER 為 "TR"

SQL> select distinct sid from v$mystat;

SID
----------
159

SQL> create table t1 (a int, b int);

表已建立。

SQL> declare
2 begin
3 for i in 1..1000 loop
4 insert into t1 values(i,i+1);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL 過程已成功完成。

SQL> execute sys.dbms_stats.gather_table_stats('tr','t1');

PL/SQL 過程已成功完成。

SQL> conn xh/a123
已連線。
SQL> show user
USER 為 "XH"
SQL> select distinct sid from v$mystat;

SID
----------
141

SQL> create table t1 (a int, b int);

表已建立。

SQL> ed
已寫入 file afiedt.buf

1 declare
2    begin
3    for i in 1..1000 loop
4   insert into t1 values(i,i+1);
5    end loop;
6    commit;
7* end;
SQL> /

PL/SQL 過程已成功完成。

SQL> execute sys.dbms_stats.gather_table_stats('tr','t1');

PL/SQL 過程已成功完成。

SQL> select table_name,owner from all_tables where table_name='T1';

TABLE_NAME                     OWNER
------------------------------ ------------------------------
T1                             XH
T1                             TR

SQL> alter system flush shared_pool;

系統已更改。

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1197
session cursor cache count                                               29
parse count (total)                                                     700
parse count (hard)                                                      115

SQL> select serial# from v$session where sid=159;

SERIAL#
----------
5

SQL> select serial# from v$session where sid=141;

SERIAL#
----------
190

SQL> execute dbms_system.set_sql_trace_in_session(159,5,true);

PL/SQL 過程已成功完成。

SQL> select * from t1 where a=1;(user tr)

A B
---------- ----------
1 2

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1198
session cursor cache count                                               29~~~~~~~cache了29個cursor
parse count (total)                                                     713
parse count (hard)                                                      140~~~~~~~~產生hard parse

SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=159;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
1489680637 0
SQL> col sql_text format a30
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a=1 1 1 1~~~~~~~~~執行1次

SQL> select * from t1 where a=1;(user tr)

A B
---------- ----------
1 2

SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a=1 1 2 2~~~~~~~~~~~~執行2次

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1198
session cursor cache count                                               29
parse count (total)                                                     714~~~~~~~~總解析次數多1次這次是soft parse
parse count (hard)                                                      140

SQL> select * from t1 where a=1;(user tr)

A B
---------- ----------
1 2
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a=1 1 3 3~~~~~~~~~執行3次

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1198
session cursor cache count                                               30~~~~~~~~ cache 了執行3次的 cursor
parse count (total)                                                     715~~~~~~~~~~~總解析次數多1次這次是soft parse
parse count (hard)                                                      140

當某個session對相同cursor 進行3次訪問會在該session的PGA中建立一個標記,當遊標關閉也不會換出library cache,此SESSION再執行相同的語句會跳過所有hard parse
也不用soft parse ,這個為sofer soft parse or fast soft parse 真正得以共享

SQL> select * from t1 where a=1;(user tr)

A B
---------- ----------
1 2

SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a=1 1 4 4~~~~~~~~~~~執行4次了這次用的fast soft parse

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1199~~~~~~~~多了一次
session cursor cache count                                               30
parse count (total)                                                     716~~~~~~~~總解析多一次 ,這次是fast soft parse
parse count (hard)                                                      140

所以可以看出fast soft parse 包含在 parse count(total)中

SQL> select * from t1 where a=1;(user tr)

A B
---------- ----------
1 2

SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a=1 1 5 5

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1200 ~多了一次
session cursor cache count                                               30
parse count (total)                                                     717~~~~~~~~總解析多一次 ,這次是fast soft parse
parse count (hard)                                                      140

SQL> host tkprof D:\oracle\product\10.2.0\admin\xh\udump\xh_ora_37116.trc d:\t1par
se.txt

TKPROF: Release 10.2.0.1.0 - Production on 星期二 9月 29 15:01:07 2009

select *
from
t1 where a=1

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        5      0.29       0.36          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch       10      0.00       0.00          0         40          0           5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total       20      0.29       0.36          0         40          0           5

Misses in library cache during parse: 1 可以看到執行5次一次為 hard parse ,fast soft parse 被算在 parse count中了
Optimizer mode: CHOOSE
Parsing user id: 67

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL T1 (cr=8 pr=0 pw=0 time=92 us)

由於 fast soft parse可以連soft parse也不用 ,所以它可以一定程度的提高DB效能

小結:
soft parse 2種情況 1是一個session執行後,另一個session又執行(USER 是一樣的但SID 不一樣)此時不在產生執行計劃將共享執行計劃,但還需要, 檢查語句物件是否存

在,USER 是否有許可權,同譯詞轉換
2.是同一個SESSION 再次執行相同SQL 會跳過幾乎全部解析步驟,但還需要檢查許可權因為有可能USER 許可權已經改變所以要檢查

這2種只是跳過hard parse中部分步驟(最重要是跳過產生執行計劃) 叫soft parse

fast soft parse:PGA中找, 找到後直接共享執行計劃真正避開hard parse

CURSOR CACHE HIT 計算 ~稍高點比較好

session cursor cache hits / (parse count (total) - parse count (hard))

SQL> alter system flush shared_pool;

SQL> execute dbms_system.set_sql_trace_in_session(159,5,false);

PL/SQL 過程已成功完成。

SQL>

可以看到解析 5次,執行5次, 其中一次為hard parse (Misses in library cache during parse: 1)

以上就是 hard parse, soft parse ,fast soft parse

看看子游標與parse (與 shared pool library cache有關會詳細實驗shared pool library cache)

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1202
session cursor cache count                                               29
parse count (total)                                                     730
parse count (hard)                                                      165

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=141 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   15
session cursor cache hits                                                44
session cursor cache count                                               30
parse count (total)                                                      81
parse count (hard)                                                       10

SQL> alter session set sql_trace=true;

會話已更改。

SQL> select * from t1 where a=1;(user tr SID 159)

A B
---------- ----------
1 2

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1203
session cursor cache count                                               29
parse count (total)                                                     743
parse count (hard)                                                      166~~~~~~~~~多了一次hard parse

SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=159;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
1489680637 0

SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a=1 1 1 1~~~~~~~~~~~執行一次解析一次

SQL> select * from t1 where a=1;(SID 159)

A B
---------- ----------
1 2

SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a=1 1 2 2~~~~~~~~~~執行2次

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1203
session cursor cache count                                               29
parse count (total)                                                     744~~總解析多了一次是soft parse
parse count (hard)                                                      166

SQL> select * from t1 where a=1;(user xh sid141)

A B
---------- ----------
1 2

SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=141;~~~由於SQL語句一樣算出來hash value一樣

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
1489680637 0

SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a=1 2 3 3~~~~~~~~~~~~~~~執行3次解析3次 ,注意 2個子游標

SQL> select sql_text,to_char(child_number,'xxxx') childnum,to_char(parse_calls,'xxx
')parses,executions,PARSING_SCHEMA_NAME from v$sql where hash_value=1489680637;

SQL_TEXT                       CHILD PARS EXECUTIONS PARSING_SC
------------------------------ ----- ---- ---------- ----------
select * from t1 where a=1         0    2          2 TR~~~~~~~~~~~可以看到tr 執行2次解析2次(1次soft parse,1 次hard parse)
select * from t1 where a=1         1    1          1 XH~~~~~~~~~~~xh 執行1次解析1次 (1次hard parse )
oracle算出 hash value一樣可以放入同一個 bucket(LIBRATY CACHE中bucket)但最後發現引用了不同的表tr.t1,xh.t1 雖然語句一樣但執行計劃可能不一樣所以oracle產生了

新的執行計劃放入這個bucket中也就產生了一個子遊標,由於產生了一個新的執行計劃肯定是一次hard parse(共享執行計劃才是soft parse)

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=141 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   15
session cursor cache hits                                                45
session cursor cache count                                               30
parse count (total)                                                      93
parse count (hard)                                                       11~~~~~~~~~~~~~~多了一次hard parse

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati SID 159的沒變(與他沒關係)
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1203
session cursor cache count                                               29
parse count (total)                                                     744
parse count (hard)                                                      166

SQL> select * from t1 where a=1;(user xh sid141)

A B
---------- ----------
1 2

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=141 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   15
session cursor cache hits                                                45~~未變
session cursor cache count                                               30
parse count (total)                                                      94~~~~~~~~~~~~總解析次數加1 是一次soft parse
parse count (hard)                                                       11~~未變~

SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS~~~~~~~~~~~~~~該buckt中SQL執行4次解析4次
------------------------------ ------------- ---------- -----------
select * from t1 where a=1 2 4 4

SQL> select sql_text,to_char(child_number,'xxxx') childnum,to_char(parse_calls,'xxx
')parses,executions,PARSING_SCHEMA_NAME from v$sql where hash_value=1489680637;

SQL_TEXT                       CHILD PARS EXECUTIONS PARSING_SC
------------------------------ ----- ---- ---------- ----------
select * from t1 where a=1         0    2          2 TR
select * from t1 where a=1         1    2          2 XH~~~~~~~~~~~~~~~~~~~~~~~~~XH 執行 2次解吸2次 1次soft 1次hard

存在子游標後再有SESSION 執行物件 XH.T1的都會到library cache中存放SQL語句執行計劃的bucket中找到xh.t1的子游標使用它的執行計劃產生soft parse

執行計劃的改變對於SQL的影響

SQL> alter system flush shared_pool
2 ;

系統已更改。
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1203
session cursor cache count                                               29
parse count (total)                                                     744
parse count (hard)                                                      166

SQL> execute dbms_system.set_sql_trace_in_session(159,5,true);

PL/SQL 過程已成功完成。
SQL> set autotrace trace exp
SQL> select * from xh.t1 where a>999;~~~~~~~~~~另一個SESSION 看下執行計劃FTS
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     7 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1   |     1 |     7 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> select * from t1 where a>999; (SID 159 USER TR)

A B
---------- ----------
1000 1001

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1204
session cursor cache count                                               29
parse count (total)                                                     757
parse count (hard)                                                      191~~~~~~多了hard parse

SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=159;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
3166732479 0

SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=3166732479;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a>999 1 1 1

SQL> select * from t1 where a>999;(SID 159 USER TR)

A B
---------- ----------
1000 1001

SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=3166732479;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a>999 1 2 2~~~~執行2次解析2次

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1204~~~cache hint沒變
session cursor cache count                                               29
parse count (total)                                                     758~~總解析次數加1 那麼是 soft parse
parse count (hard)                                                      191~~~~~~~~~~hard parse沒變

SQL> select * from t1 where a>999;(sid 159 user tr)

A B
---------- ----------
1000 1001

SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=3166732479;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a>999 1 3 3~~~~~~~~~~~執行3次

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1204
session cursor cache count                                               30~~~~~~~cache住了
parse count (total)                                                     759~~~~~~~~多了一次soft parse
parse count (hard)                                                      191

SQL> select * from t1 where a>999;

A B
---------- ----------
1000 1001

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1205~~~現在已經用上fast soft parse了
session cursor cache count                                               30
parse count (total)                                                     760
parse count (hard)                                                      191~~沒變

SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=3166732479;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a>999 1 4 4~~~~~~~~~~~~執行4次

SQL> create index trt1_ind on tr.t1(a);

索引已建立。

SQL> select * from tr.t1 where a>999;~~~~~~~~~~~使用 index

執行計劃
----------------------------------------------------------
Plan hash value: 1183216686

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00
:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 3 (0)| 00
:00:01 |

|* 2 | INDEX RANGE SCAN | TRT1_IND | 1 | | 2 (0)| 00
:00:01 |

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

SQL> select * from t1 where a>999;(SID 156 USER TR)

A B
---------- ----------
1000 1001

SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=3166732479;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a>999 1 1 1~~~~執行計劃發生了變化替換了該子游標的執行計劃新執行計劃裝入了library cahce heap中沒有

共享原有的執行計劃為HARD PARSE, 解析

1次( 為hard parse),執行1次

SQL> select sql_text,to_char(child_number,'xxxx') childnum,to_char(parse_calls,'xxx
')parses,executions,PARSING_SCHEMA_NAME from v$sql where hash_value=3166732479;

SQL_TEXT CHILD PARS EXECUTIONS PARSING_SC
------------------------------ ----- ---- ---------- ----------
select * from t1 where a>999 0 1 1 TR ~~~
對應著這個子游標將變回 hard parse

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1206
session cursor cache count                                               30
parse count (total)                                                     761
parse count (hard)                                                      192~~~~~hard parse 1次

SQL> host tkprof D:\oracle\product\10.2.0\admin\xh\udump\xh_ora_37116.trc d:\t1par
se.txt

TKPROF: Release 10.2.0.1.0 - Production on 星期二 9月 29 16:24:38 2009

select *
from
t1 where a>999

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        5      0.03       0.02          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch       10      0.00       0.01          1         31          0           5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total       20      0.03       0.04          1         31          0           5

Misses in library cache during parse: 2
Optimizer mode: CHOOSE
Parsing user id: 67

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL T1 (cr=7 pr=0 pw=0 time=85 us)

可以看到執行5次,解析 5次 2次為 hard parse (其中一次為執行計劃發生了變化重新裝入library cache heap中造成 hard parse)
********** 所以可以看出hard parse與執行計劃有關係 SQL語句 PLAN 改變了會造成HARD PARSE************

~~看下加INDEX 但執行計劃不變的情況
SQL> show user;
USER 為 "TR"
SQL> create table t2(a int, b int);

1 declare
2 begin
3 for i in 1..1000 loop
4 insert into t2 values (i,i+1);
5 end loop;
6 commit;
7* end;
8 /

PL/SQL 過程已成功完成。

SQL> execute dbms_stats.gather_table_stats('tr','T2');

PL/SQL 過程已成功完成。

SQL> execute dbms_system.set_sql_trace_in_session(159,5,true);

PL/SQL 過程已成功完成。

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1549
session cursor cache count                                               30
parse count (total)                                                    1007
parse count (hard)                                                      320

SQL> select * from t2 where a>100;(sid 159 user tr)

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1549~~~沒變
session cursor cache count                                               30
parse count (total)                                                    1015
parse count (hard)                                                      321~~~一次hard parse

SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=159;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
1871633534 0

SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1871633534;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t2 where a>100 1 1 1~~~~~~~~~~~解析一次執行一次

SQL> select * from t2 where a>100;

SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1871633534;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t2 where a>100 1 2 2

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1549~~~沒變
session cursor cache count                                               30
parse count (total)                                                    1016~~~總解析次數變了是一次soft parse
parse count (hard)                                                      321~~~沒變

SQL> select * from tr.t2 where a>100;

執行計劃
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   901 | 6307 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2   |   901 | 6307 |     3   (0)| 00:00:01 |~~~~~~~~~~~~~此時計劃是FTS
--------------------------------------------------------------------------

SQL> create index ind_t2 on tr.t2(a);~~加了一個INDEX

索引已建立。

SQL> select * from tr.t2 where a>100;

執行計劃
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   901 | 6307 |     3   (0)| 00:00:01 |~~~~~~~~~PLAN 沒變
|* 1 | TABLE ACCESS FULL| T2   |   901 | 6307 |     3   (0)| 00:00:01 |

SQL> select * from t2 where a>100;

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1549
session cursor cache count                                               30
parse count (total)                                                    1017
parse count (hard)                                                      322~~~~~~~~多了一次hard parse

SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1871633534;

SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t2 where a>100 1 1 1~~~執行1次 1次hard parse

SQL> select sql_text,to_char(child_number,'xxxx') childnum,to_char(parse_calls,'xxx
')parses,executions,PARSING_SCHEMA_NAME from v$sql where hash_value=1871633534;

SQL_TEXT CHILD PARS EXECUTIONS PARSING_SC
------------------------------ ----- ---- ---------- ----------
select * from t2 where a>100 0 1 1 TR

SQL> host tkprof D:\oracle\product\10.2.0\admin\xh\udump\xh_ora_37116.trc d:\t1par
se.txt

TKPROF: Release 10.2.0.1.0 - Production on 星期二 9月 29 17:14:28 2009

select *
from
t2 where a>100

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        3      0.01       0.01          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch      183      0.00       0.00          0        201          0        2700
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total      189      0.01       0.02          0        201          0        2700

Misses in library cache during parse: 2
Optimizer mode: CHOOSE
Parsing user id: 67

Rows Row Source Operation 一共解析3次,2次hard parse
------- ---------------------------------------------------
900 TABLE ACCESS FULL T2 (cr=67 pr=0 pw=0 time=2740 us)

分析:與上例加入INDEX執行計劃改變一樣,當加入一個INDEX 時,oracle 會根據統計資訊嘗試進行計算INDX SCNA 的COST,計算FTS 的COST,將其結果比較選擇最優此例中為FTS 然

後將最優 PLAN 裝入 LIBRARY CACHE HEAP中,雖然這個PLAN 還是FTS 不過是最新經過比較後產生的PLAN 並將其重新裝入了 LIBRARY CACHE HEAP中(相當於執行計劃發生了變化),

替換了原有的執行計劃沒有共享原有的執行計劃為 HARD PARSE

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-615870/，如需轉載，請註明出處，否則將追究法律責任。

oracle實驗記錄 （分析oracle硬解析&軟解析&fast soft parse）

相關文章

oracle實驗記錄（分析oracle硬解析&軟解析&fast soft parse）