案例 1

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

通過生成的監(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)跟對應(yīng)表幾乎相同，那么為什么執(zhí)行時(shí)間差距這么大呢？

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

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

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

影子表的差異

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

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

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

案例 2

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

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

select * from test_a where object_id =11;

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

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

案例 3

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

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

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

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

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

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

案例 4

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

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

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

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

總結(jié)

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

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