Oracle體系結構之SQL語句的執行過程

一般來說,資料庫處理SQL都會經過三個過程:解析(parse)、執行(exec)、傳回結果(fetch)

1.解析

當使用者發起一個SQL語句時，Oracle通過Server Process 接收SQL語句到達oracle執行個體，并在Shared pool 中的 Library Cache 查找是否存在該語句對應執行計劃的緩存。

如果不存在則将該SQL進行硬解析(Hard parse)，生成最優化的執行計劃(plan)，并将該執行計劃等資訊載入Library Cache。

如果存在則不經過硬解析，而是直接進行軟解析(Soft parse)，進而減少資料庫的分析時間。

2.執行

server process首先在buffer cache中查找是否存在該執行計劃所對應的資料塊，如果存在，就直接進行DML操作(邏輯IO)，否則應從資料檔案中将資料塊讀取到buffer cache中，再進行DML操作(實體IO)。

3.傳回結果

對于SELECT語句需要傳回結果，首先看是否需要排序，如果需要則排序後傳回給使用者。

對于其他DML語句(insert/delete/update)，則無需傳回結果。當buffer cache中的資料塊被修改時，server process将自動記錄buffer的改變過程到SGA中的redo log buffer，最終分别由DBWR和LGWR程序負責将buffer cache中的髒資料塊和redo log buffer中的日志寫到磁盤中的data file和redo log file。

解析類型：硬解析、軟解析、軟軟解析

對SQL的解析都需要頻繁地通路資料字典

硬解析：

判斷SQL語句是否存在文法、語義的問題

判斷SQL語句所涉及的對象(表、視圖)是否存在

判斷執行SQL語句的使用者對涉及的對象是否有權限

選擇最優的執行方案,生成執行計劃

其中生成執行計劃最消耗系統資源(CPU、I/O、Memory)，尤其是CPU和I/O資源

軟解析：隻判斷SQL語句的文法、語義、對象權限，而不生成執行計劃

軟軟解析：不解析

1

2

3

4

5

6

7

8

9

<code>SQL&gt; </code><code>select</code> <code>name</code><code>,value </code><code>from</code> <code>v$sysstat </code><code>where</code> <code>name</code> <code>like</code> <code>'parse%'</code> <code>;</code>

<code>NAME</code>                                <code>VALUE</code>

<code>------------------------------ ----------</code>

<code>parse </code><code>time</code> <code>cpu                       1339</code>

<code>parse </code><code>time</code> <code>elapsed                  17374</code>

<code>parse </code><code>count</code> <code>(total)                 23639</code>

<code>parse </code><code>count</code> <code>(hard)                   3060</code>

<code>parse </code><code>count</code> <code>(failures)                149</code>

<code>parse </code><code>count</code> <code>(describe)                  9</code>

命中率：

命中率在Oracle資料庫的多個地方都會被提起

例如當一個程序需要通路資料時，首先确定資料是否存在Buffer Cache中，如果存在(稱為高速緩存命中)，則直接讀取資料(邏輯IO/記憶體讀)；如果不存在(稱為高速緩存未命中)，則需要在Buffer Cache中尋找足夠的空間将磁盤上需要的資料塊複制到Buffer Cache中(實體IO/硬碟讀)。

命中率=邏輯IO/（邏輯IO+實體IO）*100%

注：如果命中率低肯定有問題，但命中率高不一定沒問題(邏輯IO遠高于實體IO，但實體IO仍舊很高的情況下）

可以通過作業系統指令vmstat、iostat檢視目前系統的IO情況

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

<code>[oracle@ora11g ~]$ vmstat 1 5</code>

<code>procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------</code>

<code> </code><code>r  b   swpd   </code><code>free</code>   <code>buff  cache   si   so    bi    bo   </code><code>in</code>   <code>cs us sy </code><code>id</code> <code>wa st</code>

<code> </code><code>1  0      0 374572  25260 788168    0    0   262    34  239  253  1  4 80 16  0</code>

<code> </code><code>2  0      0 374556  25268 788172    0    0     0    36  909 1341  0  1 99  0  0</code>

<code> </code><code>0  0      0 374556  25268 788172    0    0     0     0  876 1248  0  1 99  0  0</code>

<code> </code><code>0  0      0 374556  25268 788172    0    0    16    80  974 1495  0  1 99  0  0</code>

<code> </code><code>0  0      0 374556  25276 788164    0    0     0    48  966 1428  0  1 99  0  0</code>

<code>[oracle@ora11g ~]$ iostat 1 5</code>

<code>Linux 2.6.18-308.el5 (ora11g.example.com)       07</code><code>/17/2013</code>

<code>avg-cpu:  %user   %</code><code>nice</code> <code>%system %iowait  %steal   %idle</code>

<code>           </code><code>1.00    0.00    3.85   15.19    0.00   79.96</code>

<code>Device:            tps   Blk_read</code><code>/s</code>   <code>Blk_wrtn</code><code>/s</code>   <code>Blk_read   Blk_wrtn</code>

<code>sda              33.72      1551.26       202.49    1052263     137352</code>

<code>sda1              0.10         2.58         0.01       1749          4</code>

<code>sda2             22.27      1256.66       141.92     852432      96271</code>

<code>sda3              0.07         2.37         0.00       1610          0</code>

<code>sda4              0.01         0.02         0.00         11          0</code>

<code>sda5              2.17        58.06         4.54      39382       3080</code>

<code>sda6              2.40        60.32        15.53      40920      10532</code>

<code>sda7              2.85        77.55        16.47      52605      11173</code>

<code>sda8              3.01        76.16         8.77      51663       5949</code>

<code>sda9              0.80        17.03        15.25      11554      10343</code>

<code>           </code><code>0.00    0.00    0.55    0.00    0.00   99.45</code>

<code>sda               5.00        32.00       128.00         32        128</code>

<code>sda1              0.00         0.00         0.00          0          0</code>

<code>sda2              0.00         0.00         0.00          0          0</code>

<code>sda3              0.00         0.00         0.00          0          0</code>

<code>sda4              0.00         0.00         0.00          0          0</code>

<code>sda5              0.00         0.00         0.00          0          0</code>

<code>sda6              1.00         0.00        32.00          0         32</code>

<code>sda7              2.00        32.00        32.00         32         32</code>

<code>sda8              1.00         0.00        32.00          0         32</code>

<code>sda9              1.00         0.00        32.00          0         32</code>

<code>           </code><code>0.26    0.00    0.77    0.26    0.00   98.71</code>

<code>sda               2.00         0.00       104.00          0        104</code>

<code>sda2              2.00         0.00       104.00          0        104</code>

<code>sda6              0.00         0.00         0.00          0          0</code>

<code>sda7              0.00         0.00         0.00          0          0</code>

<code>sda8              0.00         0.00         0.00          0          0</code>

<code>sda9              0.00         0.00         0.00          0          0</code>

<code>           </code><code>0.26    0.00    0.26    0.00    0.00   99.48</code>

<code>sda               4.00         0.00        32.00          0         32</code>

<code>sda5              1.00         0.00         8.00          0          8</code>

<code>sda6              1.00         0.00         8.00          0          8</code>

<code>sda7              1.00         0.00         8.00          0          8</code>

<code>sda9              1.00         0.00         8.00          0          8</code>

<code>           </code><code>0.00    0.00    0.78    0.00    0.00   99.22</code>

<code>sda              13.00       160.00       208.00        160        208</code>

<code>sda2              4.00         0.00        80.00          0         80</code>

<code>sda7              3.00        64.00        32.00         64         32</code>

<code>sda8              4.00        96.00        32.00         96         32</code>

也可以通過v$buffer_pool_statistics視圖檢視命中率的情況

<code>SQL&gt; </code><code>SELECT</code> <code>NAME</code><code>, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,(1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)))*100 </code><code>"Hit Ratio"</code>      <code>FROM</code> <code>V$BUFFER_POOL_STATISTICS </code><code>WHERE</code> <code>NAME</code><code>=</code><code>'DEFAULT'</code><code>;</code>

<code>NAME</code>                 <code>PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS  Hit Ratio</code>

<code>-------------------- -------------- ------------- --------------- ----------</code>

<code>DEFAULT</code>                       <code>12786         14986          158305 92.6216595</code>

<code></code>

          本文轉自Vnimos51CTO部落格，原文連結：http://blog.51cto.com/vnimos/1251480，如需轉載請自行聯系原作者