標籤：
oracle學習筆記
SQL語句執行過程剖析講課
這節課通過講述一條SQL語句進入資料庫
和其在資料庫中的整個的執行過程
把資料庫裡面的體繫結構串一下。
讓大家再進一步瞭解oracle資料庫裡面的各個進程、儲存結構以及記憶體結構的關聯關係。
首先來講整個體系中有用戶端、執行個體和資料庫
資料庫裡有三類檔案
控制檔案ctl、資料檔案dbf、記錄檔log
執行個體中SGA有六大池子
第一大記憶體區shared pool即共用池
第二大記憶體區buffer cache
第三塊是redo log
我們主要講上面的三大池它們容易出問題，
特別是shared pool和buffer cache。
另外三大池不會出問題。
接下來的幾個章節主要講shared pool ，buffer cache。
一）shared pool
使用者串連上執行個體，
執行個體會專門針對這個串連開一個進程，
這個進程叫前台進程也叫伺服器處理序，翻譯成英文叫server process。
它是執行個體的進程，
oracle會單獨的給它分配一個PGA空間。
在用戶端錄入一條sql，
輸入後斷行符號，
如：
SQL> select * from dba_data_files;
執行後獲得一堆資料。
這條語句在用戶端輸入，
然後通過用戶端到執行個體的連結送給了server process。
在服務端，
sql語句通過網路送給oracle，被oracle的server process接受和接待。
server process接受到這個sql語句
然後做的事情
1、這條語句oracle認識，但不能直接運行
oracle需要將sql語句解析成執行計畫然後才能執行。
2、解析後oracle拿著執行計畫去執行。
解析的過程應該說是比較複雜的，
包括很多步驟
server process首先會判斷sql語句文法有沒有問題，如果有問題不會執行後面的操作。
還看一下，sql語句所涉及的表、視圖在資料庫裡到底有沒有。
接下來看一下輸入sql語句的使用者對sql語句所涉及的表和視圖有沒有許可權。
還有一個重要的工作，
判斷sql語句到底該怎麼執行。
一條sql語句可以有N種執行方案，
這n中執行方案中有的執行方案是好的，有的是不好的。
這時候serverprocess要從這條sql語句的n個執行方案中找一個最優的執行方案。
然後產生執行計畫。
在選擇最優執行方案的過程中，它要訪問很多個物件，訪問很多資料，
否則它不能憑空的判斷哪個方案最優秀。
這時需要消耗很多的電腦資源
最主要消耗cpu資源，其次是IO資源，再者是記憶體資源。
既然解析會消耗資源，這時候會想到另外一個問題
A使用者執行一條sql語句
這條sql語句需要解析，解析完產生執行計畫。
A使用者執行了。
B使用者連上以後和A使用者做相同的業務，
比如兩個營業員都做的是取款，業務相同。
有可能執行相同的sql語句。
A使用者執行時產生的執行計畫，如果能夠緩衝起來的話，
B使用者上來後，如果能夠找到這個緩衝的執行計畫，
它就不需要經過解析了，可以直接拿著執行計畫去執行。
在資料庫裡面就提到一個問題，
我們有沒有必要對sql語句和sql語句所對應的執行計畫進行緩衝？
現在看有必要。
sql語句和sql語句所對應的執行計畫我們緩衝在shared pool中。
回顧一下：
一條sql語句在用戶端輸入，
通過網路到達oracle執行個體，
執行個體中server process來接收這條語句，
server process接收到後會拿著sql語句
首先進到shared pool裡面，
要看一下這條sql語句在shared pool中有沒有緩衝。
如果有緩衝，
這時server process會在shared pool中找到這條sql語句以及所對應的執行計畫，
然後再去執行。
減少瞭解析的步驟。
如果server process在sharedpool中沒有找到這條sql語句以及對應的執行計畫，
這時server process 就會將sql進行解析，最終產生執行計畫。
然後接著下一步去執行。
server process拿到sql語句要執行的第一件事情是：
找，
如果找不找它會去解析。
這兩個過程都是server process所做的。
shared pool最主要的一個事情就是緩衝sql語句和sql語句所對應的執行計畫。
對shared pool的訪問以及對sharedpool的修改這些操作都是serverprocess進程進行的。
二）buffer cache
SQL語句解析完，產生執行計畫，接著要執行。
sql語句執行時，要去取資料
如語句：
select * from dba_data_files
要從dba_data_files表中取資料。
表的資料都在磁碟dbf檔案裡面，
這時還是server process要根據這個sql語句的執行計畫去執行。
這個sql語句的執行計畫從dbf裡面取出資料，然後返給使用者。
buffer cache 就是用來緩衝dbf的資料。
非常有必要緩衝。
因為A使用者訪問了某個表，B使用者訪問這個表的可能性也比較大。
A使用者再來訪問這個表的機會也比較大。
如果沒有buffer cache的話，
每次訪問dbf時，都要發生物理IO，資料庫的效能會非常低。
當一個進程訪問dbf訪問我們的資料的時候，
如要訪問一個表的資料，
因為server process知道有緩衝buffer cache這個概念，
首先到buffer cache裡面找這個表所對應的資料在buffer cache裡面有沒有。
如果有，server process直接存取buffer cache，
把所有的資料取出來，通過網路再返給用戶端。
如果buffercache中沒有它要找的資料，這個時候它就到磁碟dbf去找。
從dbf中把資料取出來，取出來不是直接返回給我們的使用者，
取出來以後放到buffer cache裡面去，
然後再從buffercache中返給我們的使用者。
對oracle來講，從dbf裡面取資料，取到buffercache裡面，為物理讀，
這個操作是serverprocess來完成的。
講兩個概念：
邏輯IO
logic IO
物理IO
Physical IO
邏輯IO，
serverprocess執行sql語句的時候
從buffercache裡面讀，叫邏輯讀，也叫記憶體讀。
物理IO，
buffercache中找不到資料，
它就到磁碟上去讀，這個讀叫物理讀，也叫磁碟讀。
我們希望邏輯讀多一些，物理讀少一些。
這樣會大大減少物理讀的數量，進而提高資料庫的訪問速度。
命中率的概念
命中率在緩衝裡面時刻被提出來，
在buffercache裡面有命中率，計算方法L/L+P，就是：邏輯讀/邏輯讀+物理讀
命中率越高，說明邏輯讀相對比較大，物理讀相對比較小。
這是相對。
我們希望命中率高相對好一些。
命中率高意味著物理讀少，邏輯讀相對多一些。
物理讀高IO就會很繁忙，資料庫的速度就會比較慢。
比如：
oracle發生100次讀，其中只有一次物理讀99次邏輯讀。
演算法 99/99+1
命中率為99%。
這個時候我們就說，buffercache的命中率為99%。
oracle讀資料100次 只有一次物理讀，99次邏輯讀。
大幅的提高了資料庫的讀的效能。這是好事。
對oracle資料庫來講，
命中率低肯定有問題。
命中率高不一定沒有問題。
舉例：
邏輯讀10萬，物理讀1萬
在一個時間段裡面物理讀也挺高。
計算命中率的時候我們發現，
因為邏輯讀非常的大，命中率也很高，
但是資料庫整體的服務速度也很慢。
所以說命中率低肯定有問題，命中率高不一定沒有問題
有可能是因為邏輯讀非常高，即使是物理讀比較高，計算命中率也會很大。
命中率大我們就認為沒問題，但不見得，我們要關心每秒的物理讀。
其實有linux命令可以監控系統裝置效能
1、vmstat
vmstat是一個很全面的效能分析工具，
可以觀察到系統的進程狀態、記憶體使用量、虛擬記憶體使用、磁碟的IO、
系統(中斷、環境切換)、CPU使用等
[[email protected] ~]$ vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
3 0 204 61596 39456 669880 0 0 13 35 303 174 48 22 30 0
2、iostat
iostat主要用於監控系統裝置的IO負載情況,運行時顯示系統的各項統計資訊
[[email protected] ~]$ iostat
Linux 2.6.9-78.ELsmp (redhat4) 2016年07月14日
avg-cpu: %user %nice %sys %iowait %idle
47.79 0.05 22.11 0.43 29.61
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 6.37 51.81 142.53 3267484 8989120
sda1 6.17 51.01 137.64 3216970 8680600
sda2 0.18 0.44 3.86 27814 243424
sda3 0.02 0.34 1.03 21716 65096
從這些資料可以知道 伺服器整體是否繁忙。
不僅看命中率反饋問題，還要看物理讀量的問題。
serverprocess在執行sql語句的時候，
需要從磁碟上dbf讀資料的時候，
這個資料serverprocess從dbf裡面讀，
然後讀到buffercache，然後從buffercache再返給使用者。
三）DBWn LGWR進程
sql語句目前是對某個表進行讀，
也有可能對某個表進行修改，進行刪除，
sql分增刪改查
我們以改為例，假設我們要修改某個表的資料
serverprocess還跟以前一樣，把這個表讀到記憶體裡面去。
修改也要讀到記憶體裡面去，
serverprocess在記憶體裡面對錶進行修改。
修改的時候，oracle資料庫對dbf資料庫所有的修改都會產生日誌。
日誌也是serverprocess產生的。
產生日誌以後會寫到redologbuffer裡面去。
修改完了要提交，
因為修改的是記憶體裡面的資料，
記憶體的資料和dbf裡面的資料就不一樣了，
不一樣就需要寫回dbf，
是誰負責資料從記憶體寫到硬碟，
不是serverporcess，是另外一個進程，叫dbwrite即DBWn進程
這個進程負責把serverprocess修改的資料寫回磁碟，
是誰負責將redlog buffer裡面的日誌 寫到redlog記錄檔裡面去
也不是serverprocess，是logwrite即LGWR進程
DBWn和LGWR都叫後台進程，
serverprocess叫前台進程。
serverprocess只是負責讀出來資料在記憶體buffercache裡面修改，
然後產生日誌寫到redologbuffer裡面去，
它只負責這些事情。
它不負責把修改後的資料，寫回磁碟，把日誌寫回磁碟，
由DBWn，LGWR負責。
為什麼serverporcess只負責讀不負責寫入磁碟呢，
這其實是oralce設計的時候一個技巧所在。
serverprocess是直接為使用者服務的，
使用者把sql語句給了serverprocess，
serverprocess做幾件事情，
對sql語句進行解析，然後執行，然後擷取資料，擷取資料後把結果返給使用者。
serverprocess始終在和使用者打交道，
如果serverprocess慢了，這時候使用者會感到資料庫慢。
我們就希望讓serverprocess去做最有意義的工作，
對一些serverprocess可以不做的事情，交給別人去做。
這樣使用者會得到最好的使用者體驗。
serverprocess負責把資料讀出來。
然後修改完了，把修改完的結果返給使用者。
它對修改的結果，什麼時候寫回磁碟，它不去管，讓DBWn去做。
因為什麼時候寫回磁碟，使用者不關心，
使用者只關心，我修改某個資料，然後告訴我資料修改好了，它只關心這個問題。
使用者所關心的就是serverprocess所關心的。
所以我們把一些後台能夠做的事情，
盡量的不給serverprocess去做，
讓後台進程專門去做。
所以oracle資料庫裡面就有了後台進程的概念。
後台進程的特點是使用者不知道，它在後面悄悄的做，
使用者只關心我輸入一個sql語句然後快速的得到結果。
serverprocess不關心一些寫磁碟操作，
是後台進程去做。
所以產生了DBWn、LGWR這些進程。
所以有前台和後台進程。
我們在資料庫最佳化，和資料庫效能監控的時候，
我們主要關心的是serverprocess。
我們並不過多的關心DBWn和LGWR這些。
serverprocess的快慢直接反饋到資料庫的快慢。
直接反饋到使用者對資料庫的一個感受。
不管後台進程有多忙，只要serverprocess快速輕鬆
使用者就會感到快速輕鬆。
使用者就會感到資料庫很快。
可能這時後台進程非常的繁忙，這是我們理想的一個結果。
後台進程很輕鬆，但是serverprocess很忙
使用者感到資料庫很慢，
你探索資料庫的cpu佔用率不高。
這是一個比較不好的結果。
在oracle資料庫裡面我們非常關心的一個進程是serverprocess。
以後最佳化的時候，我們經常去看那個serverprocess目前很忙。
哪個serverprocess目前很慢。
這是我們的一個下手點。
四）CKPT進程、SMON、PMON
還有一個檢查點進程，也是oracle的一個進程。
五大後台進程之一。
這個進程是周期性啟動並執行，
DBWn和LGWR也是周期運行，但頻率比較高，負載相對比較大，都比較忙。
特別是DBWn很忙。
但是CKPT（檢查點進程）這個進程它比較輕鬆，
只是周期性運行，
把資料庫當前的一些狀態資訊寫到控制檔案和資料檔案的頭部。
每個資料檔案的頭部都記錄著一些這個資料檔案的狀態資訊。
checkpoint這個進程，它負責更新控制檔案和資料檔案的頭部。
它的負載是比較輕的。
幾乎沒有什麼事可幹。
還有兩個進程
SMON（系統監視器）
這個進程是對資料庫執行個體進行維護的
舉一個例子
共用池裡面放的sql語句和執行計畫
用的時間長了，裡面可能出現很多片段。
這時SMonitor會對這些片段進行整合。
也就是說SMonitor負責對資料庫執行個體內部進行清理和維護的。
它主內。
PMON（進程監視器）
它主外
主內是指對SGA內部進行一些維護。
主外是對serverprocess進行維護。
例子
客戶網路突然斷了，serverprocess還一直為使用者啟著。
PMonitor會周期性的啟動，
啟動後發現某個serverprocess它所對應的客戶已經死掉了，
他會把這個serverprocess進行清理。
包括把這個serverprocess進程關掉，
把這個serverprocess所對應的PGA記憶體空間，給它清理。
PMonitor主外 SMonitor主內
現在講了五大進程。
五）ARCn進程
資料庫檔案還有一種歸檔記錄檔。
oracle資料庫裡面有很多日誌
[[email protected] jiagulun]$ ls
control01.ctl example01.dbf redo03.log temp01.dbf
control02.ctl redo01.log sysaux01.dbf undotbs01.dbf
control03.ctl redo02.log system01.dbf users01.dbf
可看到資料庫裡面有很多.log日誌。
oracle有redolog，它又分成多組，預設分成三組
首先oracle使用第一組日誌，往裡記日誌，時間長了第一個記錄檔滿了。
它開始用第二組，第二組滿了用第三組，
第三組滿了它反過來用第一組。
就把第一組覆蓋了。
oracle最多隻能保持三組日誌。
想保留更多的日誌，
因為日誌的作用有很多，
oracle就產生了一個歸檔的一個工作模式。
先寫第一組日誌，寫滿了這時候
oracle會啟動一個進程叫ARCn。
這個進程會把日誌歸檔到另外一個目錄底下去，另外起一個名字，
大小和它一樣。
然後開始寫第二個日誌log檔案，第二個寫滿切到第三個的時候，
這個進程把第二個log日誌寫到歸檔到的那個目錄下。
然後寫第三個、第四個。
在歸檔這個位置，
保留了oracle所有的日誌
我們要找較早的資料較早的日誌會從歸檔中找。
我們通過講一條sql語句，
講了從請求進來到擷取資料到返給使用者的一個工作流程。
進一步的oracle執行個體結構，給大家粗略的講了一下，後面還會詳細的講。
六）buffercache中的資料區塊
伺服器處理序它首先會從資料庫資料檔案裡面讀資料出來，
同時伺服器處理序找資料的時候首先在記憶體裡面找，在buffercache裡面找。
伺服器處理序還會修改裡面的資料。
修改完的資料是DBWn負責寫；
伺服器處理序它會對buffercache進行讀進行寫，對磁碟資料檔案只進行讀。
buffercache寫回資料檔案需要DBWn。
緩衝區裡面的資料，有幾種情況。
已串連
乾淨
空閑或未使用
髒
dbf中的資料讀到記憶體SGA，
buffercache中的資料和磁碟dbf檔案中的資料是一致的，
叫這個資料是乾淨的。
裡面還有一些記憶體空間沒有使用
這是空閑或未使用的。
還有對這個資料區塊，
serverprocess在記憶體裡面把它的資料修改了，
記憶體裡面的資料，和磁碟中的資料就不一致了。
這時的記憶體裡面的資料叫髒資料。
髒資料就需要寫回dbf檔案。
寫回來以後它倆又一致了，又成乾淨資料了。
還有在記憶體裡面的資料
目前serverprocess正在對它讀或者正在對它進行寫，
那個瞬間，這個資料區塊叫pin住了。
翻譯叫串連了。
寫完以後馬上成了髒資料了。
PIN是讀寫一瞬間，
對記憶體的讀寫瞬間速度非常快。
已串連就是pin住。
隨著資料庫的運行，
buffercache慢慢在被使用，
其內資料區塊有髒的、乾淨的、閒置
再次把dbf中的一個資料調到記憶體的時候，它優先使用閒置沒使用過的。
如果buffercache中所有的資料都使用過了，沒有閒置，
它接著使用乾淨的。
因為乾淨的資料意味著磁碟上有一個和它一樣的資料。
這樣新資料可以覆蓋掉乾淨的塊。
再需要原來乾淨資料區塊時，可以再把原乾淨資料調入記憶體。
也就是說對buffercache來講
乾淨的或者閒置這些資料，記憶體資料是可以被重用的。
對髒的能不能不直接覆蓋？
不能！
我們為了使用這個髒的資料區塊佔用的記憶體空間，
比如說裡面全是髒的，
我還需要再使用記憶體的時候，
就會觸發DBWn，將髒塊寫回磁碟。
寫回磁碟後原塊就成乾淨的了，
乾淨的就可以被重用了。
這是buffercache中資料區塊的幾個狀態。
七）總結
前面給大家講了一條sql語句進入oracle的一個整體的執行流程。
進一步將資料庫的記憶體和進程結構，
以及一些物理結構的一些資訊給大家講了。
這就是oracle的一個整體的體繫結構。
以後的課我們一塊塊的去講。
包括物理結構、記憶體結構、進程結構等。
記憶體結構裡面還有一塊塊內容，我們也會分開去講。
oracle本身是電腦的一個軟體，
和其它軟體的結構其實是一樣的，儘管某些部分各有各的特點。
電腦程式的結構，
只包括兩部分：
資料部分 和 處理資料的代碼。
這兩個部分再根據需要和程式的目的，發生不同的變化。
程式最終的目的都是為了處理資料。
資料庫的資料部分所佔比例較大，
一般程式資料部分所佔比例較小，
但都是為了處理它。
程式員把絕大部分精力都放在了代碼上，但都是為了呈獻最終的資料。
2016年7月17日
文字：韻箏
oracle學習筆記 SQL語句執行過程剖析講課
