331
DBA呢？我的數據庫又雙叒叕連不上了！
“工藤邦明先生似乎是個誠實可靠的人。和他結婚，你和美裏獲得幸福的幾率應該比較高。請把我完全忘記，千萬不要有罪惡感。因為如果你過得不幸福，我的行為將會完全成為徒勞。”——《嫌疑人X的獻身》
看到這裏別慌，筆者寫這篇文章的目的不是為了想要跟你探討關於這部電影裏的那些哲學命題：
-
擬一個別人無法解答的問題和解開這個問題，何者更困難？
-
自己想出的答案和判斷別人的答案，何者更容易？
隻是作為一個DBA（逗B啊）隨著對接項目的增多，難免會需要給相關的開發解答一些存在共性的數據庫問題。所以在筆者的從職生涯裏，關於故障處理有一些比較頭大的場景：
-
一類是那種你需要參考古老的DSI(Oracle的技術內部文檔但很早就停止更新了)去進行推演驗證的，抑或使用場景極端，碰上了BUG。
-
還有一類問題就比較尷尬了，比如開發找到你“我也不知道怎麼了，感覺環境有問題”。
朋友，你擬問題的時候是不是把我想像成了《嫌疑人X的獻身》裏的智商爆表的物理學家。生怕線索給多了，問題太簡單就給解決了就沒意思了，顯得我不厲害？
用人話來比喻一下：一個病人得了腎結石，他到醫院看病，醫生問診的時候他雖然不用明確的告訴醫生他是由於腎結石導致的腰部鈍痛，需要肌肉注射20mg黃體酮來擴張、鬆弛輸尿管平滑肌……
他不用清楚這個病的病理，也不用去理解黃體酮的藥理。他隻需要跟醫生描述病情的時候他能夠分得清他鈍痛的部位是腰，那裏不是腦袋，也不是手，不是胳膊不是腿。醫生隻要再問問他是不是伴有惡心，嘔吐的症狀。到這線索就明朗了，醫生再讓他去拍個B超到這基本就能確診了。當然自己的身體，我們看著他長大（這句話有點怪怪的），所以我們能夠分得清胳膊腦袋腿，這些關於生理構造的基本認知能夠很大程度的降低我們於醫生溝通是有效信息傳遞的難度。
對於開發人員，但凡你需要將數據落地存儲於一個永久介質以便你能複用這些數據，就永遠要跟數據庫打交道。因此，筆者亦希望通過這篇文章介紹一條SQL的操作執行在Oracle數據庫的曆程。能夠讓你能夠分得清數據庫的“腦袋”“胳膊”“腿”，讓你在跟DBA打交道時少走些彎路。
DBA哲學三問
我是誰，我在哪裏，我要去哪。人生三大命題，與你的人生一樣，一條SQL其實也需要搞懂這三大哲學命題。
先從連接串下手：它決定了你連接的是哪個數據庫。不論是IDE工具亦或者是sqlplus直接連接數據庫用戶名密碼之外連接串是必須填寫的。
192.xx.xx.1:1521/xxxServ 完整的Oracle連接串就由這3部分組成：
-
目標機器IP地址 192.xx.xx.1
-
監聽工作端口（默認是1521，隻有少數的Oracle數據庫環境會把監聽配置在其他端口）
-
Oracle的服務名或（SIDname） xxxServ。當然大部分會通過配置tnsname來簡化這個鏈接串的配置。
（1）客戶端使用SQL Plus或ODBC請求連接，監聽接受客戶端的TCP連接，並獲取客戶端發過來的TNS數據包。
這一步容易出的問題有兩類：
-
比如客戶端的ODBC驅動太老，或者Oracle 的Client沒有被正確安裝；
-
在網絡環境中默認的數據庫端口的1521 tcp協議被限製。
常規處理：客戶端測試，ODBC測試，Telnet網絡端口協議測試。
（2）監聽進程打開用於與子進程通信的管道，同時fork一個子進程，稱為“監聽子進程1”的子進程，然後監聽進程一直等待，直到這個“監聽子進程1”結束。
（3）監聽子進程1 Fork出子進程2。
（4）完成上麵一步，子進程1馬上退出並結束子進程1。
（5）子進程2收集本進程所在的主機名丶IP地址及進程號等信息，並把子進程2重名成server process(這裏我們也把server process叫前台進程或叫服務器進程),申請占用一小塊PGA內存。
注：第二到第五步，Oracle已經把這塊工作流封裝的很好很快了，幾乎不會出問題，但是到了第五步這裏，可能會由於Oracle的運行參數process限製以及session限製導致亦或者是pga完全耗盡（單個session占的pga內存信息可以通過v$session，v$process_memory，v$process幾個性能管理視圖查詢獲取）。當然一般碰上這類問題，會有十分明顯的報錯，並且報錯很直接。
（6）前台進程把主機名丶IP地址及進程號發送給監聽進程。
（7）監聽進程收到前台進程的信息，並返回客戶端的信息（比如用戶密碼環境變量等）給前台進程。
（8）前台進程查詢USER$丶PROFILE$等數據字典，校驗用戶名密碼是否合法，如果用戶密碼錯誤就報錯用戶名密碼無效，否則就與客戶端進行交互。
（9）客戶端收到前台進程的信息與之交互，整個連接創建完成。
以上過程，用理發這個活動來解釋一下：
我（Client程序）需要到理發店（DB 數據庫）找造型總監Tony老師（建立連接後指定給client的會話資源）理發（數據操作即事務之後以一條update為例），我要完成理發這件事，我需要找到這家理發店（連接串即地址）。
但是我不是這家店的員工（client非DBservevice的後台進程），我需要到門口找到業務員（server端的1521監聽，負責監聽遠程連接client通過他完成與數據庫建立連接）。
關於理發這件事我可能會碰上如下情況：
-
這件店地址不對（即連接串無效）
-
我自身不能進這家理發店（Client程序錯誤或者ODBC驅動太成就有問題）
-
門口接待的業務員沒了（監聽程序未啟動，或DB處於維護狀態DBA將監聽關閉
-
份驗證失效（會員卡密碼忘了）
-
這家店太忙了，不論是tony老師還是peter老師都排滿了（session或者process達到db的配置允許上限，PGA無法給client連接分配內存）
好了，客戶端跟數據庫的遠程連接已經建立，你可以開始執行SQL了。
◆ ◆ ◆ ◆ ◆
上麵我們講到了用什麼樣的姿勢更好地接入數據庫，下麵將介紹如何進行操作，上車！
連入數據庫，大家一致認為，我跟你數據庫交互，你讓我數據落盤快，請求快就好了，找到數據落盤不是順理成章嗎？為什麼要引入UNDO什麼的這些亂起八糟的東西，明顯會讓響應變慢啊。
為什麼一條簡單的Update，到Oracle裏需要經過下麵那麼多囉裏囉嗦的操作流程，這其實已經是數據庫設計人員在這麼多年以來得到的一個時間和空間，性能和冗餘的最佳“平衡點”。
其實，在計算機科學的1和0的世界裏，體係架構無非這幾件事：時間換空間，空間換時間，以及性能換冗餘，冗餘換性能。而這幾個基本問題最終都會觸及到現在的科學邊界，無法逾越。一個特性的優點，背後一定會有其對應的代價付出。
以下文即將提及的UNDO為例，提供一致性讀(Consistent Read)，回滾事務(Rollback Transaction)，實例恢複(Instance Recovery)這些特性因為其存在能夠得到很好很高效的支持。
同樣是Oracle數據庫，如果用來跑一個耗時很長的統計報表，大概需要3個小時。在這個業務場景下你也許接受了時長好像並不是太大的問題，但跟人生很多事情一樣，你等得起，不代表就一定會得到結果。UNDO機製的問題碰到了ORA-01555(快照過舊)引發了事務中斷，UNDO的機製在這種應用場景下反倒成了累贅。
這裏提一下滿足三範式的設計，對OLTP事務類型業務能夠提供很快的支持，但一些數據倉庫以及數據集市的業務，為了獲得性能反倒往往是需要反三範式的設計。一般Oracle（exedata等除外）的行數據存儲的方式，當需要進行整列的Sum統計等操作時，相比列式存儲能夠得到的性能也是天差地別。
業務場景千變萬化，所以我們就以最簡單的一條update 來感受下數據到底在幹嘛。
update t_test set name='wahaha' where id=1
當連接會話完成建立之後，你將一條SQL提交給DB Sever 接下來會發生什麼呢？他會來到Oracle的Librarychache：
（來自DSI405的Library cache 示意圖）
Oracle的內存體係結構最複雜的就是sharedpool，而shared pool中最複雜就是library cache，出於文章目的及篇幅所限，就不再繼續做詳細展開，簡單講講sql在裏麵的運作流程。
第一會將SQL（update t_test set name='wahaha' where id=1）的每個字符當然包括空格轉化成ASCII碼後，再拿這一堆ASCII碼通過HASH函數生成一個sql_hash值，Oracle拿著這個sql_hash值去描掃HASH Buckets（看上麵的圖，這個幅畫的不太好，隻畫了0號的HASH BUCKETS），假如剛好sql_hash值＝0，那麼Oracle就延著0號HASH Buckets去搜索Object Handle鏈，在這個Object Handle上存有sql的文本，如果和我們的update t_test set
name='wahaha' where id=1一模一樣對上，那就說明這條SQL已被緩存在共享池了，這個過程就是軟解析，最後執行計劃是被存放在heap 6中。
如果通過上麵的方式在Object Handle鏈沒搜索到這條SQL的文本，則說明SQL不在共享池中，這個時侯就要做硬解析（過程大要做語法，語義，權限，查詢視圖展開丶劃分小的查詢塊丶SQL等價轉換丶代價估算丶最後生成執行計劃），這個代價會有點高，如果有大量的硬解析那會消耗CPU和占用共享池。
人話版本：我的數據庫接收到你的一條SQL，每天執行的SQL那麼多，我也沒記住你是不是最近執行過了。我到SGA的library cache緩存裏找找，看看還有沒有你上一次的SQL信息。當然我們的library cache呢 使用的是lru（最近最少使用淘汰算法）。如果SQL是經常執行的，肯定能找到你的緩存的。
但是如果SQL執行的次數較少，那麼很大概率在事務量繁忙的數據庫。哪怕執行了沒多久仍然有可能被 lru淘汰掉。所以如果SQL不在，就需要消耗一定的Cpu以及內存資源來完成SQL語句的解析才生成並存放SQL的執行計劃。在SGA配置的較小但是有大量SQL解析的DB環境中，極有可能碰上library cache不足以及大量的硬解析並發導致CPU的使用率飆升的情況。
更極端一點的，在一個並發很大的Oracle線上生產係統，在業務繁忙的階段進行權限的revoke操作，也會導致這個表的相關SQL會被 aged out，引發解析風暴。同樣的，在你刪除或變更一張核心表的索引的時候，雖然最主要的風險還是執行計劃的變更導致SQL執行率下降進而導致CPU內存被占用。但是哪怕索引刪除變更後SQL執行效率沒有太大波動，但是shared_pool 中的與這個表相關的sql 都會被aged out，必須重新分析，這個帶的風險在並發壓力大的數據庫係統中也要被考慮進去。
我們再往下看的話涉及到就是Oracle數據庫是如何UNDO，和Redo在保障ACID的事務特性下，盡可能的把並發以及執行效率提升的原理（以UPDATE為例），涉及專有名詞太多，筆者說人話也不好解釋。 ╮(╯▽╰)╭
1、如果ID列上無索引
-
查詢SEG$等數據字典，找到test表段頭
-
從段頭讀出Extent Map，開始全掃描
-
找到第一個滿足條件的行，進行修改
-
查找同一塊中剩下的行，先構造一個CR塊，在CR塊中繼續查找，如果又找到滿足條件的行，在Xcur塊中修改
2、如果ID列上有索引，且版本不是11GR1（10G丶11GR2），則不需要構造CR塊
通過索引找到目標數據塊。
3、ID列無論是否有索引，在11GR1下都需要構造CR塊
4、如果NAME列上有索引，增加索引維護步驟：
-
先在原索引塊中刪除要修改的原值
-
再將新值插入
5、任何塊的修改，都有以下步驟（非IMU）
-
在PGA中生成UNDO段頭事務表的後映像（5.2）
-
在PGA中生成UNDO塊的後映像（5.1）
-
在PGA中生成DataBlock塊的後映像（11.9）
-
將前三個Redo矢量做為一條Redo Recorder寫入Log buffer
-
修改UNDO段頭的事務表，事務正式開始。
-
修改UNDO塊，寫入DataBlock的前映像。
-
修改DataBlock，將新值“wahaha”寫入Buffer cache
6、任何塊的修改，都有以下步驟（IMU）
-
在PGA中生成DataBlock塊的後映像（11.9）
-
在PGA中生成UNDO段頭事務表的後映像（5.2）
-
在PGA中生成UNDO塊的後映像（5.1）
-
將前三個Redo矢量做為一條Redo Recorder寫入Shared pool中的Private strand
-
將DataBlock中的前映像值，寫入Shared pool中的Imu pool
-
修改UNDO段頭的事務表
-
修改UNDO塊，寫入DataBlock的前映像
-
修改DataBlock，將新值“wahaha”寫入Buffer cache
commit
1、非IMU下（按最常見的快速提交）：
-
在PGA中生成Commit的Redo 信息（編號5.4），另做為一條Redo recorder，寫入Log buffer
-
修改事務表相應Slot，聲明事務已提交
-
修改DataBlock，在ITL Slot中寫入快速提交標誌和SCN。每行上的行鎖不清0
-
通知Lgwr，將Log buffer寫入Redo file
-
收到Lgwr通知，寫入完成
-
向用戶發收提交完成信息
2、IMU下（按最常見的快速提交）
-
在PGA中生成Commit的Redo 信息（編號5.4），傳入Shared pool中的Private strand，追加在事務之前的Redo recorder之後
-
修改事務表相應Slot，聲明事務已提交
-
修改DataBlock，在ITL Slot中寫入快速提交標誌和SCN。每行上的行鎖不清0
-
將Private Strand中的Redo數據寫入Log buffer
-
通知Lgwr，將Log buffer寫入Redo file
-
收到Lgwr通知，寫入完成
-
向用戶發收提交完成信息
exit
斷開連接，中止服務器進程，釋放PGA。
補充一下，任何不結合業務場景，一味鼓吹XX技術是最好的技術的人，大部分情況下並不是因為他們是傻子，他們隻是把你當傻子。而且洗腦的發生，有時候是靜悄悄的。
至此你的一條UPDATE 完成了他與數據庫交互的一生。
作者介紹 崔霄
-
目前就職於點融網infra團隊，DBA一枚。喜歡徒步、開車。
-
原文發布時間為：2016-12-28
本文來自雲棲社區合作夥伴DBAplus
最後更新：2017-05-13 08:42:44