案例 1

用戶反饋生產(chǎn)環(huán)境有兩條SQL語(yǔ)句，可以確認(rèn)區(qū)別只有表名的不同（實(shí)際參數(shù)相同），但性能上卻有10倍以上的差距。

通過(guò)生成的監(jiān)視報(bào)告可發(fā)現(xiàn)SQL1執(zhí)行時(shí)間8s，IO 403MB，如下圖：

SQL2 執(zhí)行時(shí)間2.1m，IO則有15GB，如下圖：

根據(jù)業(yè)務(wù)反饋，SQL1中表是影子表，數(shù)據(jù)量，表結(jié)構(gòu)跟對(duì)應(yīng)表幾乎相同，那么為什么執(zhí)行時(shí)間差距這么大呢？

DBA在此之前已經(jīng)在準(zhǔn)生產(chǎn)環(huán)境多次通過(guò)DBMS_SHARED_POOL.PURGE刪除對(duì)應(yīng)的執(zhí)行計(jì)劃，換參數(shù)多次重復(fù)解析，均沒(méi)獲得正確的執(zhí)行計(jì)劃。

通過(guò)分析對(duì)比監(jiān)視報(bào)告發(fā)現(xiàn)，SQL1中with語(yǔ)句正常物化，執(zhí)行計(jì)劃中存在臨時(shí)表轉(zhuǎn)化操作，即TEMP TABLE TRANSFORMATION，而SQL2中由于沒(méi)做臨時(shí)表轉(zhuǎn)化操作，IM_HISMESSAGE表被訪問(wèn)多次，效率低下。

通過(guò)再次觀察整個(gè)SQL運(yùn)行期間的等待事件，我們可以快速發(fā)現(xiàn)，其實(shí)SQL慢了10倍的原因并非是執(zhí)行計(jì)劃引起的（主要等待事件為直接路徑讀，讀寫(xiě)臨時(shí)表比例很低，而且并沒(méi)有出現(xiàn)經(jīng)典的大表作為NL被驅(qū)動(dòng)表的情況），對(duì)比運(yùn)行數(shù)據(jù)，可以發(fā)現(xiàn)IO的增量主要來(lái)源于對(duì)IM_HISMESSAGE表的掃描。

影子表的差異

通過(guò)逐行對(duì)比號(hào)返回列的詳細(xì)信息，我們終于發(fā)現(xiàn)了謎底：

原始表是有LOB字段的，影子表沒(méi)有LOB字段，IO量小了很多。同時(shí)由于存在LOB字段，with語(yǔ)句無(wú)法進(jìn)行臨時(shí)表轉(zhuǎn)化。

而SQL文本中經(jīng)典的 select 則完美的掩蓋了這一差異，開(kāi)發(fā)人員圖方便寫(xiě)出來(lái)的 select * 查詢了根本不需要的LOB字段，導(dǎo)致了性能的急劇下降。

案例 2

客戶生產(chǎn)環(huán)境的AWR報(bào)告上有一條夸張的TOPSQL，占全天DBTIME的84%

原始SQL不展示了，SQL本身其實(shí)比較簡(jiǎn)單，模擬下來(lái)如下：

select * from test_a where object_id =11;

執(zhí)行計(jì)劃也很簡(jiǎn)單，所以很快也能發(fā)現(xiàn)問(wèn)題，TABLE ACCESS BY INDEX ROWID的COST相對(duì)異常的高，排查下表的統(tǒng)計(jì)信息時(shí)，驚奇的發(fā)現(xiàn)，這是張寬表，有400+列，當(dāng)寬表遇上select *時(shí)，性能就急劇下降了。

問(wèn)題定位雖然很快，但處理起來(lái)卻并不方便，畢竟需要找到開(kāi)發(fā)改SQL，這快不了。當(dāng)然沒(méi)什么疑問(wèn)的是，系統(tǒng)的性能問(wèn)題出在SQL代碼的質(zhì)量。

案例 3

準(zhǔn)備環(huán)境如下：從dba_objects中復(fù)制兩張表t1，t2作為測(cè)試環(huán)境表。

準(zhǔn)備了兩個(gè)查詢，相同的條件，區(qū)別主要在于一個(gè)只查單列，另外一個(gè)查詢?nèi)小?/span>

通過(guò)模擬，可以發(fā)現(xiàn)，use_merge這種表連接方式情況下，排序操作的內(nèi)存消耗有較大的差距，這種差距會(huì)在有索引情況下，且指定查詢列也能命中索引走索引快速全掃描時(shí)被大幅放大。

查詢?nèi)校?/span>SORT JOIN內(nèi)存消耗 1810K：

查詢id，SORT JOIN內(nèi)存消耗 424K：

如果是HASH JOIN的話，join操作影響相對(duì)較小，可以換hint再測(cè)試看看。

案例 4

有些場(chǎng)景，SQL查詢的表數(shù)據(jù)量較大，查詢字段也較多（無(wú)法全部走索引）的時(shí)候，這里暫時(shí)不考慮*增加的不需要使用的列在數(shù)據(jù)庫(kù)返回?cái)?shù)據(jù)到應(yīng)用時(shí)網(wǎng)絡(luò)層的消耗。

如果你的機(jī)器剛好是EXADATA，那么smart scan也會(huì)讓select *與指定列的查詢有明顯的性能差異。

這個(gè)限于篇幅推薦直接參考Oracle官方技術(shù)博客：

https://blogs.oracle.com/exadatacn/exadata-v5

總結(jié)

通過(guò)這些案例，select *這個(gè)規(guī)則，變得立體了許多。

select *寫(xiě)法方便快捷，但帶來(lái)的問(wèn)題卻藏得很深，這種問(wèn)題在上線后，隨著系統(tǒng)的維護(hù)，都將變成修復(fù)成本極高的隱患。