198      阿裏雲  技術社區[雲棲]

【雲和恩墨大講堂】從執行計劃洞察ORACLE優化器的“小聰明”

黃浩  惠普

十年一劍，十年磨礪。3年通信行業，寫就近3萬條SQL；5年製造行業，遨遊在ETL的浪潮；2年性能優化，厚積薄發自成一家

主題介紹：

Oracle執行計劃的另類解讀：調皮的執行計劃 | 誠實的執行計劃 | 樸實的執行計劃

說到執行計劃，oracle的擁躉們自然而然會興奮起來。在ORACLE的世界裏，執行計劃有著其特殊的地位，如果我們將SQL性能優化看成一個生物，那某種程度上，執行計劃就是DNA。在某搜索網站中，“oracle 執行計劃”關鍵字的搜索結果與“oracle”關鍵字的搜索結果占比為1.7%。足可見執行計劃在ORACLE中舉足輕重的地位：

而當我們輸入“oracle執行計劃”時，推薦關鍵字排第一的就是“ORACLE執行計劃怎麼看”

一個標準的執行計劃大致可以分為三個部分：訪問方式（表訪問、索引訪問等）、連接方式（NESTED LOOP、HASH JOIN等）及訪問順序（驅動表等）

我們對上述SQL稍加改動，再看執行計劃：

什麼情況？DEPT表不見了，執行計劃居然“殘缺”了：

1、這是ORACLE的BUG嗎？

2、少了一張表，結果正確嗎？

3、ORACLE優化器如此大膽，其背後是誰在給他撐腰？

4、ORACLE憑什麼擅作主張？

為了回答上述問題，我們就進入今天的第一個主題：殘缺的執行計劃。

殘缺的執行計劃

在展開之前，我們先做數據準備，分別創建兩張表EMP及DEPT，腳本如下：

CREATE TABLE DEPT(

        DEPTNO NUMBER(2), 

        DNAME VARCHAR2(14), 

        LOC VARCHAR2(13)); 

 

CREATE TABLE EMP(

        EMPNO NUMBER(4)CONSTRAINT PK_EMP PRIMARYKEY, 

        ENAME VARCHAR2(10), 

        JOB VARCHAR2(9), 

        MGR NUMBER(4), 

        HIREDATE DATE, 

        SAL NUMBER(7,2), 

        COMM NUMBER(7,2), 

        DEPTNO NUMBER(2) ); 

現在我們有如下一條語句：

SELECT COUNT(1)

  FROM EMP E

  LEFTJOIN DEPT D

    ON E.DEPTNO = D.DEPTNO

這條語句非常簡單，就是獲取EMP表與DEPT表內關聯後的數據量。在看具體的執行計劃之前，我們解讀下在常規情況下，DB是如何處理這樣的數據的

1、分別讀取emp表和DEPT表的數據；

2、對EMP中的DEPTNO與DEPT表中的DEPTNO進行內關聯；

3、對內關聯後的數據進行匯總計算；

4、返回匯總計算結果。

也就是說會存在EMP與DEPT表的內關聯，因為SQL就是這樣寫的。那我們看下該語句的執行計劃，如下：

ORACLE優化器果真是按照我們的預想製定了執行計劃。

1唯一性字段對執行計劃的影響

由於在模型分析時，我們發現DEPT表的DEPTNO字段是唯一的。於是我們需要通過如下語句為該字段創建主鍵：

ALTERTABLE DEPT ADDCONSTRAINT PK_DEPT PRIMARYKEY(DEPTNO);

我們再回過頭來看執行計劃，會發生變化嗎？

如果此時的你還不能看出問題，那麼我們就對比下DEPT表的主鍵創建先後執行計劃的變化：

俗話說：不比不知道，一比嚇一跳。DEPT莫名其妙的被ORACLE優化器弄“丟”了。這不禁讓人懷疑：這樣的裁剪是否是不負責任的？也就是說，裁剪後的結果是否會因為裁剪而發生變化？在深入了解到LEFT JOIN的原理及模型結構後，你就會明白為何ORACLE優化器在DEPT表創建了基於DEPTNO字段的主鍵後，會做這樣的裁剪。

支持ORACLE做如此大膽裁剪的理由是：

1、 LEFTJOIN在沒有where條件過濾的時候，是不會減少結果數據量的；

2、 如果被關聯的字段是被關聯表的主鍵（或者唯一性字段），那麼是不會使結果數據量增多的。

既然結果集的數據量不增加也不減少，那為何還要多訪問一個表，多做一次關聯呢？這就是ORACLE的精明之處：簡單的就是高效的。

接下來，我們繼續上麵的實驗（當然是基於上麵的模型基礎，即在DEPT表上創建了基於DEPTNO字段的主鍵）。這次，我們將LEFT JOIN改成INNER JOIN，看看執行計劃是怎麼樣的：

表結構和約束關係沒有發生變化，消失的DEPT又回來了。

神馬原因呢？LEFT JOIN是不會有數據過濾的作用的，但是INNER JOIN則有過濾的功用。

為了驗證，我們準備如下數據：

INSERTINTO dept(deptno,dname)VALUES(14,'財務');

INSERTINTO dept(deptno,dname)VALUES(31,'行政');

INSERTINTO EMP(EMPNO, ENAME, DEPTNO)VALUES('001','張三',14);

INSERTINTO EMP(EMPNO, ENAME, DEPTNO)VALUES('002','李四',31);

INSERTINTO EMP(EMPNO, ENAME, DEPTNO)VALUES('003','王五',21);

INSERTINTO EMP(EMPNO, ENAME, DEPTNO)VALUES('004','麻六',14);

現在來看看LEFT JOIN和INNER JOIN的不同結果：

也就是說，LEFT JOIN和INNER JOIN還是有差異的，那麼在什麼情況下才能在執行計劃中將DEPT“槍斃”掉呢？

2主外鍵約束對執行計劃的影響

我們對EMP和DEPT創建一個主外鍵約束（在創建主外鍵約束前，我需要刪除掉empno=’003’的記錄）：

ALTERTABLE EMP ADDCONSTRAINT FK_DEPTNO FOREIGNKEY(DEPTNO)REFERENCES DEPT(DEPTNO);

看看效果如何：

這樣是不是已經非常明確了DEPT再度消失的原因了？因為創建了主外鍵，也就是等於說EMP所有的DEPTNO必須要存在DEPT表中，既然有這樣的約束，那自然就不需要多此一舉的關聯DEPT表了。

3字段屬性對執行計劃的影響

現在我們往EMP表裏麵再添加一條數據：

INSERTINTO EMP (EMPNO, ENAME, DEPTNO)VALUES('005','趙七',NULL);

再看看INNER JOIN的結果：

結果隻有3條數據，明顯剛才新增的數據是被過濾掉了，因為他的DEPTNO為null，其null並沒有存在於dept表中。

而在執行計劃裏麵，是沒有DEPT表的：

也就是說該SQL就應該等價於如下SQL:

SELECT E.EMPNO, E.ENAME

 FROM EMP E

我們再看結果：

不對呀，說好的等價呢？難道是執行計劃出了問題？還是我們對執行計劃的理解錯了？也或許是執行計劃對我們隱藏了什麼？

以上，我們都是在ORACLE的第三方開發工具PL SQL DEVELOPER裏麵查看的執行計劃。現在我們換種方式，在SQL PLUS裏麵通過explain plan這種最原始的方式來查看執行計劃，如下：

原來，在這個執行計劃的內容中，明顯的多出了“Predicate Information”，而在這部分信息裏麵，filter是：E.DEPTNO IS NOT NULL。

好吧，我們先把這個謂詞放進SQL中，看看效果：

果然，我們發現，增加了這個謂詞後，兩個SQL又等價了。此時，我們會想：天哪，如果再遇到其他場景，會不會又不等價了？

在關聯條件存在主外鍵關係約束的前提下，如下兩個SQL是等價的：

不管你信不信，反正我信了

而此時，我們來看看EMP.DEPTNO的字段屬性：

我們發現其Nullable屬性為true，即可為null值。而如果我們將該屬性值修改為false呢？

DELETEFROM emp WHERE empno ='005';

COMMIT;

ALTERTABLE EMP MODIFY DEPTNO NOTNULL;

再看執行計劃：

我們發現原來的“Predicate Information”不見了，也就沒有了E.DEPTNO IS NOT NULL的謂詞約束。

4程序員與ORACLE的較量

在上麵，我們在極力“宣傳”著oracle是多麼多麼的智能化，而事實上，她的智能程度也是存在局限性的，比如她對SQL語句的取舍絕對的依賴於物理模型結構及約束，而一旦這種物理模型結構及約束不存在，那麼ORACLE這位“巧婦”顯然也隻能“難為無米之炊”了。即便我們在SQL中進行了（唯一性）約束，ORACLE也會選擇視而不見，比如如下SQL：

SELECTCOUNT(1)

FROM EMP E

LEFT JOIN (SELECTDISTINCT DEPTNO FROM DEPT) D

ON E.DEPTNO =D.DEPTNO;

按照我們在上麵的理解，由於在子查詢D中，已經對DEPTNO進行了distinct處理，也就意味著在子查詢D中，DEPTNO絕對是唯一性的，即子查詢D對整個SQL返回的結果是沒有任何影響的，該SQL完全等價於如下SQL：

SELECT COUNT(1) FROM EMP E

而事實上呢，我們看看ORACLE的執行計劃：

這一次很讓我們意外，ORACLE居然沒有識別出子查詢D的作用。由此看來，在某些時候，尤其是在錯綜複雜的業務邏輯麵前，oracle往往束手無措，遠沒有程序員聰明，所以在ORACLE這位巨無霸麵前，我們也大可不必妄自菲薄。

5總結

至此，我們可以為第一個主題做出如下總結：

1、ORACLE優化器為達性能之目的，會不擇手段的簡化Operation；

2、ORACLE優化器的手段之一就是充分利用數據庫約束，包括但不局限於：唯一性約束、主外鍵參照性約束、Nullable約束；

3、在約束條件內，ORACLE會簡化SQL，在Operation時不再重複約束；

4、因此，在日常模型設計時，應盡可能的建立約束，最大程度上減少重複約束帶來的“非戰鬥性減員”，從而提升SQL性能

完整的執行計劃

在上一節的最後示例中，為了更全麵闡述問題，我們“拋棄”了在PL SQL DEVELOPER通過F5得到的執行計劃，轉而選擇了最原始最古老的explain。因為我們發現：

這幾列還不足夠支撐我們了解ORACLE優化器的意圖，或者說還不夠讓我們拚湊出ORACLE優化器對SQL改寫後的全貌。至少我們還需要謂詞（Predicate）。所以，一個完整的執行計劃除了：訪問方式（表訪問、索引訪問等）、連接方式（NESTED LOOP、HASH JOIN等）及訪問順序外，還應包括謂詞（Predicate），通過結合謂詞，我們更能還原ORACLE優化器對SQL做了哪些改動？

為了直觀期間，我們還是繼續在PL SQL DEVELOPER中演示，隻是執行計劃的正確打開方式是這樣的：

那麼我們能從謂詞中發現什麼呢？

我們都知道，在表的統計信息采集及時的場景下，如果某個索引字段存在條件過濾，而執行計劃中沒有通過索引訪問，而是table access full。那麼原因無非就是：該過濾條件值的數據量太大（比如超過全表數據量的20%），或者是SQL的寫法不當（該字段上應用了函數、表達式等）。

其實，除了上述兩種場景外，還有一種場景也會導致table access full。我們先來看一個非常簡單的案例，我們在EMP.DEPTNO上創建一個索引，因為經常會遇到查詢某個特定部門的員工信息。

CREATEINDEX EMP_I1 ON EMP(DEPTNO);

因為在DEPT表中，DEPTNO的數據類型為NUMBER(2)，在查deptno為14的員工信息時，我們會習慣性的寫成：

SELECT*FROM emp WHERE deptno =14;

我們的如意算盤是通過索引EMP_I1來訪問EMP表。而事實上，從執行計劃看，卻是table access full的訪問方式：

盡管deptno=14的數據量為0，並且也沒有在deptno上有任何的函數或者表達式。那麼問題出在哪裏呢？

我再來看看謂詞：

很明顯，在實際的執行過程中，DEPTNO是被TO_NUMBER函數包了一層，自然就走不了索引。那麼是什麼讓ORACLE如此“昏庸”，以致“無事生非”的添加一個函數呢？

我們再看看EMP.DEPTNO的數據類型：

原來，EMP.DEPTNO的數據類型並沒有同DEPT.DEPTNO保持一致，被設計成了VARCHAR2。因此要想走索引，就有三種辦法：將DEPTNO的數據類型修改為NUMBER(2)、創建TO_NUMBER(DEPTNO)的函數索引、將過濾條件有之前的DEPTNO=14修改成DEPTNO=’14’。

我們就看下第三種方案的執行計劃：

這才是我們想要的執行計劃，卻不是我們想要的表模型。這三種方案孰優孰劣不在本次分享主題範圍內，如有機會，再行討論。

沒錯，這就是隱式強行轉換的風險，而所有的字段隱式轉換在執行計劃中都會被“曝光”

隱式轉換都是“無意為之”，有兩種場景：其一是對過濾字段的數據類型“想當然”的認為；其二是對過濾值類型的錯誤判斷。剛才的案例屬於第一種，那麼第二種又是怎麼回事呢？

以下是一個真實的案例：

係統中存在一個日誌表，數據量非常大，我們對日誌表按照日誌時間（log_date）做了分區。在頁麵，要求強製按照log_date過濾，以命中分區而提高效率。但是分區+強製過濾並沒有收到預期的性能效果，但是將同樣的查詢條件直接在DB中執行卻非常快。通過對比執行計劃發現，通過頁麵調用執行時，並沒有命中分區，而在訪問謂詞中，log_date字段過濾時，多出了函數INTERNAL_FUNCTION。也就是將log_date字段隱式強製轉換成了timestamp。而導致這種問題的原因是JAVA數據類型與ORACLE數據類型之間的轉換出現了問題。最後通過JAVA傳STRING到ORACLE，然後在SQL中將變量值TO_DATE成DATE類型解決。

我們也可以簡單模擬下。比如我們在EMP中創建基於HIREDATE的索引：

CREATEINDEX EMP_I2 ON EMP(HIREDATE);

我們現在要查找今年以來入職的員工信息，SQL如下：

SELECT*FROM EMP WHERE HIREDATE >SYSDATE

其執行計劃如下：

而如果我們將date’2016-01-01’轉換成timestamp，則執行計劃如下：

在這個案例中，如果不查看謂詞，就很難找到性能的根源。

樸實的執行計劃

我們繼續執行計劃中的“謂詞”，看看還能給我們哪些意外之喜？

在上個章節中，我們注意到，在查詢今年入職的員工信息是，我們用了DATE’2016-01-01’。這種寫法很少見諸於正式書籍中，因為這是非標準寫法。那麼將VARCHAR2轉換成DATE的標準寫法是什麼呢？

執行計劃會告訴你：

原來DATE’2016-01-01’被轉換成了TO_DATE(‘2016-01-01’, ‘SYYYY-MM-DDHH24:MI:SS’)，這樣是為什麼DATE隻能支持YYYY-MM-DD格式的字符串的原因：

可見，ORACLE優化器會將SQL中一些非標準的寫法轉換成標準的樸實的寫法。有的時候，最樸實的寫法，最容易讓人理解。

比如，當你拿到如下SQL時：

SELECT ENAME, SAL

  FROM EMP

 WHERE SAL >SOME(SELECT SAL FROM EMP WHERE DEPTNO =10);

你會不會很懵菜？會不會去查資料，研究SOME的作用和用法？或許大半天後，你仍然被SOME\ANY\ALL弄得雲山霧罩的。

現在，我們試著從執行計劃去探究>SOME的含義。

我們將子查詢替換成具體的LIST(100,200,300)，發現在執行計劃中，謂詞變成了SAL > 100，意思就是大於最小值。換言之，原來的SQL就是要查詢大於DEPTNO=10部門最低工資的員工信息。

文章轉自數據和雲公眾號，原文鏈接

最後更新：2017-07-18 20:36:37

  上一篇：  SQL為王：oracle標量子查詢和表連接改寫

  下一篇：  VMware加強自身安全性，與IBM和Intel展開合作