早些年搞Oracle的時(shí)候，最讓人頭疼的事情就是和共享池相關(guān)的，而共享池里遇到問(wèn)題最多的事情大多數(shù)是和SQL編譯相關(guān)的，關(guān)于十多年前這方面的往事我最近總想寫(xiě)一篇Oracle往事來(lái)給大家介紹一下。
Oracle共享池最初是為了全局共享SQL執(zhí)行計(jì)劃而設(shè)計(jì)的，全局共享執(zhí)行計(jì)劃可以最大限度的減少SQL解析，在高并發(fā)的系統(tǒng)中提升數(shù)據(jù)庫(kù)的整體并發(fā)能力，這對(duì)于二三十年前的計(jì)算機(jī)硬件來(lái)說(shuō)至關(guān)重要。其實(shí)除了Oracle之外，那個(gè)時(shí)代發(fā)展起來(lái)的商用數(shù)據(jù)庫(kù)大多也支持全局SQL執(zhí)行計(jì)劃共享，比如SQL SERVER 的Parameterized Query Plan Caching。
全局SQL PLAN共享并不是數(shù)據(jù)庫(kù)必須具備的功能，也不是所有的數(shù)據(jù)庫(kù)都支持全局SQL PLAN共享，MySQL、Postgresql等開(kāi)源數(shù)據(jù)庫(kù)以及絕大多數(shù)國(guó)產(chǎn)數(shù)據(jù)庫(kù)都是會(huì)話(huà)級(jí)共享SQL PLAN的。會(huì)話(huà)級(jí)共享SQL PLAN可能沒(méi)有全局共享效果好，不過(guò)MySQL、PG這些數(shù)據(jù)庫(kù)在設(shè)計(jì)之初并沒(méi)有考慮在復(fù)雜業(yè)務(wù)場(chǎng)景下的超高并發(fā)執(zhí)行問(wèn)題，因此全局共享SQL PLAN并不是其涉及要點(diǎn)。采用會(huì)話(huà)級(jí)共享執(zhí)行計(jì)劃減少了數(shù)據(jù)字典相關(guān)的鎖以及字典緩沖的閂鎖爭(zhēng)用，有效的降低了SQL解析器的復(fù)雜度，有助于數(shù)據(jù)庫(kù)更穩(wěn)定的運(yùn)行，特別是在數(shù)據(jù)字典不太發(fā)生變化的場(chǎng)景中。隨著現(xiàn)代硬件的快速發(fā)展，在大多數(shù)情況下，SQL解析所消耗的系統(tǒng)資源已經(jīng)不成為主要的問(wèn)題了，因此沒(méi)有使用全局SQL PLAN CACHE的開(kāi)源數(shù)據(jù)庫(kù)在絕大多數(shù)高并發(fā)量執(zhí)行的場(chǎng)景中也都能夠勝任。在我所遇到的運(yùn)維案例中，反而是采用全局執(zhí)行計(jì)劃共享的Oracle數(shù)據(jù)庫(kù)經(jīng)常在負(fù)載不算太高的場(chǎng)景中，比如每秒幾萬(wàn)次執(zhí)行的場(chǎng)景中，因?yàn)橛步馕鲞^(guò)多而觸發(fā)了shared pool、Library cache 、cursor mutex等方面的爭(zhēng)用，引發(fā)了較為嚴(yán)重的性能問(wèn)題。
不管如何，共享執(zhí)行計(jì)劃（全局和會(huì)話(huà)級(jí)）確實(shí)有效的提高了數(shù)據(jù)庫(kù)在SQL解析方面的效率，從而可以更好的支撐高并發(fā)場(chǎng)景和一些數(shù)據(jù)字典經(jīng)常發(fā)生變更的高并發(fā)場(chǎng)景。不同的數(shù)據(jù)庫(kù)共享SQL PLAN的實(shí)現(xiàn)方式差異很大，Oracle使用家傳的共享池，隨著Oracle數(shù)據(jù)庫(kù)的發(fā)展，共享池已經(jīng)變成了一個(gè)極其極其復(fù)雜的全局共享數(shù)據(jù)結(jié)構(gòu)，不僅僅用于SQL PLAN共享，其全局內(nèi)存堆（KGH）管理的模式是統(tǒng)一的。其他數(shù)據(jù)庫(kù)一般使用相對(duì)簡(jiǎn)單一點(diǎn)的SQL PLAN CACHE來(lái)實(shí)現(xiàn)執(zhí)行計(jì)劃共享。
實(shí)現(xiàn)執(zhí)行計(jì)劃共享遇到的第一個(gè)問(wèn)題是SQL使用非綁定變量的問(wèn)題，最初的SQL共享完全是基于SQL文本的，其原理是對(duì)SQLTEXT做一個(gè)HASH函數(shù)，HASH值相同的SQL被認(rèn)為是相同的，可以共享的。如果我們?cè)赟QL中直接使用常量值而沒(méi)有使用綁定變量，那么SQL是無(wú)法共享的。Oracle在早期想要共享cursor，必須強(qiáng)制開(kāi)發(fā)人員在編程時(shí)使用綁定變量。但是開(kāi)發(fā)人員的水平參差不齊，經(jīng)常會(huì)忘記這個(gè)開(kāi)發(fā)規(guī)范。于是Oracle通過(guò)cursor_sharing參數(shù)對(duì)SQL進(jìn)行簽名處理。自動(dòng)將相似的SQL進(jìn)行歸并，從而讓SQL能夠最大限度的共享。哪怕寫(xiě)程序是沒(méi)有使用綁定變量，SQL解析器會(huì)自動(dòng)幫你完成這個(gè)工作。
通過(guò)cursor_sharing自動(dòng)處理非綁定變量的SQL或者使用綁定變量一定是件好事情嗎？也不一定是這樣的，如果某條SQL只有一個(gè)最佳的SQL PLAN，這是沒(méi)有問(wèn)題的。不過(guò)在現(xiàn)實(shí)中可能存在相同的SQL有多種最好的執(zhí)行計(jì)劃的情況存在。比如下面這個(gè)例子。
圖片
當(dāng)Object_id為20的時(shí)候，記錄數(shù)有419萬(wàn)行數(shù)據(jù)，因此全表掃描是比較合理的執(zhí)行計(jì)劃。
圖片
而當(dāng)Object_id=21的時(shí)候，返回?cái)?shù)據(jù)只有16行，因此此時(shí)使用索引范圍掃描是比較合理的執(zhí)行計(jì)劃。而如果我們使用綁定變量，select object_name from t1 where object_id=:p，這樣兩種情況如果共享執(zhí)行計(jì)劃，那就明顯是不合理的。
這種情況下如果共享執(zhí)行計(jì)劃，那么可能會(huì)出現(xiàn)十分怪異的情況。比如說(shuō)同一條SQL有時(shí)候走索引，有時(shí)候走全表掃描。有時(shí)候突然就會(huì)大量SQL不走索引全部走執(zhí)行計(jì)劃了。甚至出現(xiàn)同一條SQL在不同RAC節(jié)點(diǎn)上，一個(gè)節(jié)點(diǎn)是走索引的，另一個(gè)節(jié)點(diǎn)上死活不走索引。其實(shí)明白了SQL共享與SQL解析的原理就很清楚了。在解析帶有綁定變量或者通過(guò)cursor_sharing進(jìn)行共享的SQL的時(shí)候，會(huì)進(jìn)行綁定變量的窺探，編譯時(shí)帶入的參數(shù)就決定了執(zhí)行計(jì)劃最終走不走索引。而下一回同一條可共享的SQL執(zhí)行的時(shí)候，就不會(huì)再去窺探參數(shù)了，因此就會(huì)導(dǎo)致不必要的SQL執(zhí)行計(jì)劃錯(cuò)誤。
為了解決這個(gè)問(wèn)題，Oracle推出了Adaptive Cursor Sharing（ACS）。在ACS技術(shù)的加持下如果存在數(shù)據(jù)偏斜，ACS 能夠識(shí)別不同的綁定變量值具有不同的選擇性，從而選擇最為合理的執(zhí)行計(jì)劃。
當(dāng)帶有綁定變量的 SQL 語(yǔ)句首次被解析時(shí)，優(yōu)化器會(huì)窺探綁定變量的值，并根據(jù)謂詞的選擇性生成一個(gè)執(zhí)行計(jì)劃，同時(shí)把該游標(biāo)標(biāo)記為 bind-sensitive（綁定敏感的）。當(dāng)同樣的 SQL 語(yǔ)句再次被執(zhí)行時(shí)，優(yōu)化器會(huì)比較當(dāng)前綁定變量的值和之前的值，如果發(fā)現(xiàn)選擇性有顯著差異，優(yōu)化器可能會(huì)創(chuàng)建一個(gè)新的子游標(biāo)和執(zhí)行計(jì)劃，同時(shí)把該游標(biāo)標(biāo)記為 bind-aware（綁定感知的）。當(dāng)同樣的 SQL 語(yǔ)句再次被執(zhí)行時(shí)，優(yōu)化器會(huì)根據(jù)綁定變量的值的選擇性范圍，匹配最合適的子游標(biāo)和執(zhí)行計(jì)劃，從而避免使用不適合的執(zhí)行計(jì)劃。
目前Oracle ACS可以在絕大多數(shù)場(chǎng)合下解決cursor共享的問(wèn)題，不過(guò)ACS也存在一定的負(fù)面作用。比如會(huì)增加每次SQL執(zhí)行的開(kāi)銷(xiāo)，同時(shí)會(huì)讓一個(gè)CURSOR產(chǎn)生過(guò)多的不共享的執(zhí)行計(jì)劃，從而影響這個(gè)CURSOR的執(zhí)行效率，增加MUTEX爭(zhēng)用，嚴(yán)重時(shí)會(huì)引發(fā)系統(tǒng)性能問(wèn)題。因此在某些應(yīng)用場(chǎng)景中，用戶(hù)會(huì)選擇關(guān)閉ACS功能。
看到這里可能大多數(shù)朋友都會(huì)覺(jué)得我今天還是在炒Oracle ACS的冷飯，如果能看到這里的朋友，今天算是來(lái)對(duì)了。前面的近兩千字的鋪墊，只是為了讓人更好地理解今天我想帶給大家的一些干貨。
今天要帶給大家的第一點(diǎn)干貨是和Oracle ACS相關(guān)的。雖然很多數(shù)據(jù)庫(kù)不支持全局SQL PLAN CACHE，不過(guò)一般都支持會(huì)話(huà)級(jí)SQL PLAN CACHE。當(dāng)某條SQL執(zhí)行多次的時(shí)候，就不會(huì)再對(duì)這條SQL做解析，而直接復(fù)用緩沖中的執(zhí)行計(jì)劃了。那么與ORACLE 類(lèi)似的問(wèn)題出現(xiàn)了，如果數(shù)據(jù)是不均衡的，有些時(shí)候要走索引，有些時(shí)候需要走全表掃描怎么辦？這種情況下，就會(huì)出現(xiàn)類(lèi)似Oracle出現(xiàn)過(guò)的奇怪現(xiàn)象，某條SQL，有時(shí)候執(zhí)行效率高，有時(shí)候執(zhí)行效率低，而且我們無(wú)法控制。遇到這種情況，有時(shí)候可能就是因?yàn)镾QL PLAN CACHE緩沖的執(zhí)行計(jì)劃不一定適合某個(gè)場(chǎng)景的SQL。
有些用過(guò)Oracle的朋友可能會(huì)想到解決這個(gè)問(wèn)題的辦法，那就不使用綁定變量，讓SQL PLAN無(wú)法共享。其實(shí)這個(gè)辦法在某些開(kāi)源或者國(guó)產(chǎn)數(shù)據(jù)庫(kù)中并不一定有效。比如PG數(shù)據(jù)庫(kù)，默認(rèn)就會(huì)對(duì)SQL進(jìn)行簽名，自動(dòng)轉(zhuǎn)換成綁定變量格式，就像Oracle數(shù)據(jù)庫(kù)里設(shè)置了cursor_sharing=FORCE。而且這些數(shù)據(jù)庫(kù)往往有不支持類(lèi)似Oracle ACS的功能，因此使用非綁定變量來(lái)解決這個(gè)問(wèn)題是無(wú)效的。
我們?cè)撊绾谓鉀Q這個(gè)問(wèn)題呢？其實(shí)在Oracle占主導(dǎo)地位的時(shí)代，這個(gè)問(wèn)題就已經(jīng)有解了。如果我們關(guān)閉了ACS功能，但是確實(shí)存在某些場(chǎng)景中同樣的SQL帶入不同的參數(shù)時(shí)，需要有不同的執(zhí)行計(jì)劃，該如何處理呢。遇到這種情況，我們就只能通過(guò)在SQL語(yǔ)句上加上注釋?zhuān)?* PLAN B */）,強(qiáng)制性的讓優(yōu)化器把這條SQL區(qū)分為兩條不同的SQL。實(shí)際上，當(dāng)Oracle還沒(méi)有推出ACS功能的時(shí)候，我們就是這樣在cursor_sharing=FORCE的數(shù)據(jù)庫(kù)里糾正錯(cuò)誤的執(zhí)行計(jì)劃的，這個(gè)方法對(duì)于MySQL、PG和一些國(guó)產(chǎn)數(shù)據(jù)庫(kù)依然有效。
今天的第二點(diǎn)干貨是關(guān)于分布式數(shù)據(jù)庫(kù)的，與集中式數(shù)據(jù)庫(kù)不同的是，分布式數(shù)據(jù)庫(kù)上的 硬解析的成本要高得多，因此在分布式數(shù)據(jù)庫(kù)中，盡可能要實(shí)現(xiàn)SQL PLAN CACHE。因此在分布式數(shù)據(jù)庫(kù)上，因?yàn)镾QL PLAN CACHE引起的SQL PLAN CACHE中的執(zhí)行計(jì)劃不適配的問(wèn)題依然是存在的，并且在一些高負(fù)載的場(chǎng)景中，往往因?yàn)榇祟?lèi)問(wèn)題引發(fā)分布式集群范圍的性能問(wèn)題。這種情況下，如果你能夠很快發(fā)現(xiàn)問(wèn)題，并且將某個(gè)不合理的執(zhí)行計(jì)劃從SQL PLAN CACHE中清除掉，很快就能解決數(shù)據(jù)庫(kù)集群的性能問(wèn)題。
周五的時(shí)候，張瑞遠(yuǎn)先生就和我討論過(guò)一個(gè)在OB上遇到的SQL PLAN CACHE引發(fā)的執(zhí)行計(jì)劃問(wèn)題。經(jīng)過(guò)分析發(fā)現(xiàn)OB默認(rèn)的CURSOR_SHARING是FORCE，也就是說(shuō)默認(rèn)情況下，OB會(huì)自動(dòng)對(duì)SQL進(jìn)行簽名，將沒(méi)有使用綁定變量的SQL轉(zhuǎn)化為使用綁定變量的格式。如果訪(fǎng)問(wèn)的數(shù)據(jù)存在較為嚴(yán)重的列傾斜現(xiàn)象，那么就會(huì)遇到SQL PLAN CACHE中的執(zhí)行計(jì)劃不適用的問(wèn)題。經(jīng)過(guò)和OB的朋友一起討論，對(duì)OB中解決這個(gè)問(wèn)題初步有了一個(gè)方案。首先在Oracle上使用 的PLAN B方案依然適用。如果某條SQL根據(jù)綁定變量不同，有少量的幾種情況可以明確區(qū)分，那么PLAN B方案是可行的。如果數(shù)據(jù)傾斜問(wèn)題比較復(fù)雜，不能簡(jiǎn)單的分類(lèi)，那么在OB中可以通過(guò)HINT或者outlines，將這條SQL設(shè)置為不適用PLAN CACHE來(lái)規(guī)避這個(gè)問(wèn)題。
在Oracle數(shù)據(jù)庫(kù)中，也有類(lèi)似的HINT，在數(shù)據(jù)庫(kù)產(chǎn)品沒(méi)有ACS功能或者關(guān)閉了ACS功能的 時(shí)候，這是一種十分有效的方法。繞開(kāi)PLAN CACHE可以讓本身就無(wú)法共享執(zhí)行計(jì)劃的SQL不要去干擾PLAN CACHE，可以更好的保護(hù)全局PLAN CACHE。
沒(méi)想到今天寫(xiě)著寫(xiě)著就寫(xiě)多了，數(shù)據(jù)庫(kù)的問(wèn)題，每個(gè)小問(wèn)題其實(shí)都?jí)驈?fù)雜的，攤開(kāi)了講，三五千字根本說(shuō)不清楚，不過(guò)不要緊，花上幾年時(shí)間，一個(gè)個(gè)研究清楚，你也就成了高手了。
