【體系結構】sql語句解析過程小實驗 軟解析、硬解析

bitifi發表於2016-02-29
SQL
sql語句解析過程 軟解析、硬解析
解析 parse-->執行 execute -->取資料 fetch

解析：獲取sql執行計劃    
硬解析 hard parse  當前shared pool中沒有該sql執行計劃，library cache miss    
軟解析 soft parse   有現成的執行計劃，直接重用執行計劃, library cache hit

解析過程：
1語法檢查 syntax check
2語義檢查 semantic check
         資料字典data dictionary --> 共享池中資料字典緩衝區data dictionary cache
3共享池檢查 shared pool check
        庫快取library cache  檢查是否存在相同sql執行計劃 (hash value)
4結合統計資訊statistics 制定執行計劃execution plan  
5快取執行計劃      
       SGA   shared pool    library cache   shared sql area

1 分析表 檢視統計資訊
分析表，檢視錶行數、塊數、平均行長、空塊數等資訊。
SCOTT@PROD>analyze table emp compute statistics;
SCOTT@PROD>select num_rows,blocks,avg_row_len,empty_blocks
   from user_tables
   where table_name='EMP';

2 軟解析、硬解析

【1】session 1， sys使用者 startup 啟動的資料庫例項。

【2】新開會話session 2，scott使用者登入：
[oracle@oracle ~]$ sqlplus scott/tiger
SCOTT@PROD>select * from emp where empno = 7788;


【3】session1 sys使用者查詢 v$sql檢視
SYS@PROD>desc v$sql;
SYS@PROD>col sql_text for a40
SYS@PROD>select hash_value,sql_text,parse_calls,loads, executions,fetches
   from v$sql
   where sql_text like '%select * from emp%' and sql_text not like '%v$sql%';

PRASE_CALLS    解析
LOADS           硬解析
EXECUTIONS     執行
FETCHES         獲取資料


【4】新開會話session3 scott使用者再次執行相同sql，session1 檢視解析情況

[oracle@oracle ~]$ sqlplus scott/tiger
SCOTT@PROD>select * from emp where empno = 7788;


SYS@PROD>r


同一使用者，不同會話,執行完全相同sql,  hash value相同。
第一次硬解析，生成執行計劃，並快取在共享池中 library cache；
第二次軟解析，重用shared pool快取的執行計劃。


【5】sql語句中=後面多加一個空格，session1 sys使用者再次查詢解析情況
SCOTT@PROD>select * from emp where empno =  7788;


SYS@PROD>r

當前sql文字比之前語句中多了一個空格，對應hash_value不同，當作不同sql，進行硬解析。


【6】查詢其他員工資訊如7566，session 1 sys使用者再次查詢解析情況
SCOTT@PROD>select * from emp where empno = 7566;


SYS@PROD>r


【7】使用繫結變數的方法執行查詢

SCOTT@PROD>var b_empno number;
SCOTT@PROD>exec :b_empno := 7788;
SCOTT@PROD>select * from emp where empno = :b_empno;

SCOTT@PROD>exec :b_empno := 7566;
SCOTT@PROD>select * from emp where empno = :b_empno;

SCOTT@PROD>exec :b_empno := 7369;
SCOTT@PROD>select * from emp where empno = :b_empno;

SYS@PROD>r

使用繫結變數，重用執行計劃（軟解析），減少硬解析。  
解析時帶有繫結變數，執行時給繫結變數賦值。

3 檢視軟解析比率
軟解析比率可以檢視AWR報告，也可以使用sql進行查詢

SYS@PROD>col name for a30
SYS@PROD>select * from v$sysstat where name like '%parse%';


SYS@PROD>with t as (select * from v$sysstat where name = 'parse count (total)'),
   h as (select * from v$sysstat where name = 'parse count (hard)')
   select (t.value-h.value)/t.value from t,h;




呂星昊
2016.2.23

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

相關文章