作者為:? SHOUG成員 – ORACLE ACS高級顧問羅敏 問題和需求 “你們Oracle公司有這樣的自動掃描SQL語句工具嗎？通過這個工具，把我們的應用軟件輸進去，就能掃出SQL語句的大部分問題。這樣就可以減少我們測試和性能優化工作量，更能避免投產之后才暴露性能問

作者為:?

SHOUG成員 – ORACLE ACS高級顧問羅敏

1. 問題和需求

“你們Oracle公司有這樣的自動掃描SQL語句工具嗎？通過這個工具，把我們的應用軟件輸進去，就能掃出SQL語句的大部分問題。這樣就可以減少我們測試和性能優化工作量，更能避免投產之后才暴露性能問題。” — 來自某移動客戶的需求。

“老羅，XX移動公司希望我們Oracle公司提供自動掃描SQL工具，我們有嗎？聽說第三方公司有這樣的產品，已經在客戶那兒試用了。” — 來自Oracle服務銷售同事的擔憂。

是啊，客戶的需求再合理不過。但據我所知，Oracle公司好像沒有這樣包治百病的神奇工具。第三方公司居然有這樣的工具，太吸引客戶眼球了，一方面讓人感到質疑，另一方面也令人感到一種競爭壓力。

2. 初識廬山真面目

于是，我和銷售同事趁去該客戶現場拜訪、調研的機會，對該客戶的上述需求和第三方公司的自動化工具一探究竟了。客戶的需求不必多言了，我們關鍵是對所謂自動化工具充滿好奇。因商務因素，客戶并沒有給我們直接展示該工具的使用過程和界面，但告訴我們大致原理：原來該工具首先通過定義一組評分規則，例如：SQL語句是否使用綁定變量；條件字段前是否有函數；多表連接是否超過4個表… …，然后將輸入的SQL語句進行評判，若違反這些規則，扣分！最后給該SQL語句和整個應用模塊打分。

原來如此！這些規則在大部分情況下不無道理，例如，條件字段加函數，特別是在日期字段前加to_char函數：

to_char(DJ_SZ.JDRQ, ‘YYYY.MM.DD’) BETWEEN ‘2014.04.01’ AND ‘2014.04.17’

就是一種非常初級、業余、錯誤的編程方式。正確方式應該是：

DJ_SZ.JDRQ BETWEEN to_date(‘2014.04.01’,’YYYY.MM.DD’) AND to_date(‘2014.04.17’,’YYYY.MM.DD’)

但是，更多的規則值得商榷。例如，在Oracle公司推薦的編程規范中，并不是所有SQL語句都應該使用綁定變量的，而只是針對并發量大的小事務SQL語句才應該使用綁定變量，而針對并發量小的大事務SQL語句，特別是非常復雜SQL語句，Oracle公司建議是不要使用綁定變量。第三方的自動工具能分析出SQL語句是高并發量還是低并發量訪問，以及大事務和小事務嗎？值得懷疑。

更為典型的例子是，其實Oracle公司從來沒有官方正式建議：一個SQL語句不能超過4個表的連接。的確，多表連接可能導致性能不佳，但問題不在于連接表的多和少，而在于編程人員是否理解了Oracle的Nested Loop、Hash Join等多種表連接技術原理和適應場景，以及在表連接中索引的設計原理。以下就是一個國內著名財務軟件的典型SQL語句：

select *

from (select rownum num, temp.*

from (select a.fid,

… …

a.playdeptname as playdeptNameCode

from t_claim_remittancerecord a

left join t_pay_remittype b on a.remittype = b.fid

left join t_pay_fundtype c on c.fid = a.amountscategory

left join t_org_department d on a.remitdepart =

d.finasyscode

left join t_org_department y on a.playdeptname =

y.finasyscode

and y.status = 1

left join t_org_employee f on f.empcode = a.addperson

left join t_org_department k on f.deptid = k.id

left join t_org_employee g on g.empcode = a.updateperson

left join t_org_employee h on h.empcode = a.claimman

left join t_bd_customer cus on cus.fnumber = a.customer

left join V_LMS_SUPPLIER s on s.snumber = a.supplier

left join t_deposit_printer i on i.codenum = a.codenum

left join t_org_employee j on i.createuser = j.empcode

WHERE 1 = 1

and a.accountName like ‘%’ || :1 || ‘%’

and a.claimState like ‘%’ || :2 || ‘%’

and a.writeOffState like ‘%’ || :3 || ‘%’

and a.reachAmountDate between :4 and :5

and a.repealstate != 1

order by addTime desc, codeNum) temp) t

WHERE t.num <= :6

and t.num > :7

哇！該語句好復雜哦，連接的表多達10多個。若采用第三方公司的SQL自動掃描工具。該語句一定被扣分甚至徹底槍斃了。可是，該語句實際運行情況如何呢？以下就是該語句的執行計劃：

———————————————————————————————| Id? | Operation???????????????????????????????????? | Name????????????????????? | Cost (%CPU)|

———————————————————————————————|?? 0 | SELECT STATEMENT????????????????????????????? |?????????????????????????? |??? 14 (100)|

|?? 1 |? FILTER?????????????????????????????????????? |?????????? ????????????????|??????????? |

|?? 2 |?? VIEW??????????????????????????????????????? |?????????????????????????? |??? 14?? (8)|

|?? 3 |??? COUNT????????????????????????????????????? |?????????????????????????? |??????????? |

|?? 4 |???? VIEW?????????????? ???????????????????????|?????????????????????????? |??? 14?? (8)|

|?? 5 |????? SORT ORDER BY??????????????????????????? |?????????????????????????? |??? 14?? (8)|

|?? 6 |?????? FILTER????????????????????????????????? |?????????????????????????? |????????? ??|

|?? 7 |??????? NESTED LOOPS OUTER???????????????????? |?????????????????????????? |??? 13?? (0)|

|?? 8 |???????? NESTED LOOPS OUTER??????????????????? |?????????????????????????? |??? 12?? (0)|

|?? 9 |????????? NESTED LOOPS OUTER?????????????????? |?? ????????????????????????|??? 11?? (0)|

|? 10 |?????????? NESTED LOOPS OUTER????????????????? |?????????????????????????? |??? 10?? (0)|

|? 11 |??????????? NESTED LOOPS OUTER???????????????? |?????????????????????????? |???? 9?? (0)|

|? 12 |???????????? NESTED LOOPS OUTER??????????????? |?????????????????????????? |???? 8?? (0)|

|? 13 |????????????? NESTED LOOPS OUTER?????????????? |?????????????????????????? |???? 7?? (0)|

|? 14 |?????????????? NESTED LOOPS OUTER????????????? |?????????????????????????? |? ???6?? (0)|

|? 15 |??????????????? NESTED LOOPS OUTER???????????? |?????????????????????????? |???? 5?? (0)|

|? 16 |???????????????? NESTED LOOPS OUTER??????????? |?????????????????????????? |???? 4?? (0)|

|? 17 |????????????????? NESTED LOOPS OUTER?????? ????|?????????????????????????? |???? 3?? (0)|

|? 18 |?????????????????? NESTED LOOPS OUTER????????? |?????????????????????????? |???? 2?? (0)|

|? 19 |??????????????????? TABLE ACCESS BY INDEX ROWID| T_CLAIM_REMITTANCERECORD? |???? 1?? (0)|

|? 20 |??????? ?????????????INDEX RANGE SCAN????????? | IDX_TCR?????????????????? |???? 1?? (0)|

|? 21 |??????????????????? TABLE ACCESS BY INDEX ROWID| T_PAY_REMITTYPE?????????? |???? 1?? (0)|

|? 22 |???????????????????? INDEX UNIQUE SCAN???????? | PK_REMITTYPE_FID???? ?????|???? 1?? (0)|

|? 23 |?????????????????? TABLE ACCESS BY INDEX ROWID | T_PAY_FUNDTYPE??????????? |???? 1?? (0)|

|? 24 |??????????????????? INDEX UNIQUE SCAN????????? | PK_FUNDTYPE_FID?????????? |???? 1?? (0)|

|? 25 |????????????????? TABLE ACCESS BY INDEX ROWID? | T_DEPOSIT_PRINTER???????? |???? 1?? (0)|

|? 26 |?????????????????? INDEX UNIQUE SCAN?????????? | PK_T_DEPOSIT_PRINTER????? |???? 1?? (0)|

|? 27 |???????????????? TABLE ACCESS BY INDEX ROWID?? | T_BD_SUPPLIER???????????? |???? 1?? (0)|

|? 28 |????????????????? INDEX RANGE SCAN???????????? | IDX_BD_SUPPLIER_NUM?????? |???? 1?? (0)|

|? 29 |??????????????? TABLE ACCESS BY INDEX ROWID??? | T_ORG_DEPARTMENT????????? |???? 1?? (0)|

|? 30 |???????????????? INDEX RANGE SCAN????????????? | IDX_T_ORG_DPT_FINASYSCODE |???? 1?? (0)|

|? 31 |?????????????? TABLE ACCESS BY INDEX ROWID???? | T_ORG_DEPARTMENT????????? |???? 1?? (0)|

|? 32 |??????????????? INDEX RANGE SCAN?????????????? | IDX_T_ORG_DPT_FINASYSCODE |???? 1?? (0)|

|? 33 |????????????? TABLE ACCESS BY INDEX ROWID????? | T_BD_CUSTOMER???????????? |???? 1?? (0)|

|? 34 |?????????????? INDEX RANGE SCAN??????????????? | IDX_BD_CUSTOMER_NUM?????? |???? 1?? (0)|

|? 35 |???????????? TABLE ACCESS BY INDEX ROWID?????? | T_ORG_EMPLOYEE??????????? |???? 1?? (0)|

|? 36 |????????????? INDEX UNIQUE SCAN??????????????? | UK_EMPLOYEE_EMPCODE?????? |???? 1?? (0)|

|? 37 |??????????? TABLE ACCESS BY INDEX ROWID??????? | T_ORG_DEPARTMENT????????? |???? 1?? (0)|

|? 38 |???????????? INDEX UNIQUE SCAN???????????????? | SYS_C00797036???????????? |???? 1?? (0)|

|? 39 |?????????? TABLE ACCESS BY INDEX ROWID???????? | T_ORG_EMPLOYEE??????????? |???? 1?? (0)|

|? 40 |??????????? INDEX UNIQUE SCAN????????????????? | UK_EMPLOYEE_EMPCODE?????? |?? ??1?? (0)|

|? 41 |????????? TABLE ACCESS BY INDEX ROWID????????? | T_ORG_EMPLOYEE??????????? |???? 1?? (0)|

|? 42 |?????????? INDEX UNIQUE SCAN?????????????????? | UK_EMPLOYEE_EMPCODE?????? |???? 1?? (0)|

|? 43 |???????? TABLE ACCESS BY INDEX ROWID??????? ???| T_ORG_EMPLOYEE??????????? |???? 1?? (0)|

|? 44 |????????? INDEX UNIQUE SCAN??????????????????? | UK_EMPLOYEE_EMPCODE?????? |???? 1?? (0)|

大家看到上述執行計劃，首先不應感到畏懼，而應該從外觀上感慨一下，那就是數據庫的美感！大家看這個執行計劃的形狀多么對稱和富有韻律感，也多像一把打開的美麗扇子。其次，大家一定要相信，外觀充滿美感的東西，本質上也應該不錯，呵呵。的確，回到技術本質，我們發現雖然該語句涉及10多張表的連接，但實際運行效率效果非常高，例如Cost才14，當然Cost有不準確的時候。更重要的是，該語句每次表連接都非常漂亮地采用了Nested Loop連接技術，并且都合理地采用了被連接字段的索引。正是因為設計開發人員非常了解Oracle表連接原理以及索引設計規范，所以才設計出了這樣“又好吃、又好看”的SQL語句。

可是，第三方公司的SQL自動掃描工具卻很可能濫殺無辜了。大家一定能相信一個原理：世界上一件事物的好壞不在于多和少，而在于其本身的對和錯。若將此原理運用在多表連接技術方面，那就是：多表連接的好壞不在于連接表的多和少，而在于每次表連接的對和錯。因為Oracle表連接每次都是兩個表進行連接，然后再進行第三個、第四個表的連接。若充分理解了Oracle各種表連接技術、索引設計規范等，每次表連接都是高效的，再多的表連接也是合理的。反過來，若不了解Oracle表連接技術和適應場景，即便是兩個表的連接都會出問題。

3. 少一點噱頭，多一點務實

此標題有點刺耳，甚至刻薄，但的確是本人有感而發。國內IT市場也的確存在這種不太正常現象：面對客戶某些看似合理，實則很難實現的需求，某些公司不是去合理引導客戶，反而是一味迎合客戶，甚至是推波助瀾，更實質的目的還是出于商業考慮。但是，大家不知這是一種非常短視的行為嗎？難道客戶不會很快就驗證出這種所謂SQL自動掃描工具的有效性，甚至真偽性嗎？既然如此，大家何必去費盡心機，去討客戶這種“好”？實際上很可能是既讓客戶失望，也毀自己聲譽的事情。

性能分析和優化，特別是SQL語句性能分析和優化，怎么可能只做靜態的形式分析？而不做與實際系統和數據相關聯的動態神式分析？記得有一年參加一個數據庫技術大會，一位國外性能優化大師的演講曾經讓我非常震撼，他的演講主題是性能優化與應用數據的關聯性，整個演講中，他未展現一個SQL語句優化技術，而是大談數據分布對SQL語句訪問性能的重要性，諸如按字段分析最大值、最小值、分組統計等，以及何時需要按Bucket方式收集統計信息等。所謂的SQL語句自動掃描工具，可能連客戶實際系統都不連接，執行計劃也不分析，客戶數據更不了解，就能掃描出SQL語句質量？的確有點像個烏托邦的東西。

再回到本文開頭一個問題：“你們Oracle公司有這樣的自動掃描SQL語句工具嗎？”準確地回答是：Oracle的確沒有這種不看數據、不看執行計劃的所謂自動掃描SQL語句工具，但Oracle公司自10g開始就提供了大量內置的SQL優化工具，例如：ADDM、SQL Access Advisor、SQL Tuning Advisor、Automatic SQL Tuning、SQL Profile、SPA（SQL Performance Analyzer）、SPM（SQL Plan Management）… …這些工具一個共同特點是不僅分析SQL語句執行計劃，而且分析統計信息，分析數據分布情況，分析索引設計情況等，綜合各方面情況，給出一些更合理的SQL語句優化建議。例如，11g的Automatic SQL Tuning就是分析SQL語句所訪問表的統計信息是否過期、是否缺乏索引、是否可產生有效的SQL Profile信息、語句編寫是否合理等。

再者，雖然Oracle自動優化工具能有效分析和解決很多SQL性能問題，但更多基礎性，特別是與應用數據緊密相關的問題，還是需要應用設計開發人員從數據庫模型規范化設計、 基礎技術掌握、SQL設計開發規范、應用軟件質量控制、加強設計開發管理等層面和角度去加以解決。

總之，性能優化工作，特別是應用性能分析和優化工作，還是需要大家踏踏實實、一點一滴地做起，即便需要所謂自動化的工具，也建議大家優先考慮Oracle公司本身自帶的工具，畢竟這些工具是Oracle產品的一部分，經過了嚴格測試，也為全球廣大客戶的大量實踐所驗證，是具有普遍適用性的東西。

還是以本節標題作為本文結尾：

少一點噱頭，多一點務實！

Related posts:

1. Oracle Acs資深顧問羅敏 老羅技術核心感悟：分表還是分區？
2. Oracle Acs資深顧問羅敏 老羅技術核心感悟：牛! 11g的自動調優和SQL Profile
3. 【Oracle CBO優化器】視圖合并View Merging技術 _complex_view_merging & _simple_view_merging
4. Gather more plan statistics by gather_plan_statistics hint
5. latch free:cache buffer handles造成的SQL性能問題
6. DBA_HIST_PLAN_OPERATION_NAME
7. ORA-00600:[15570]內部錯誤一例
8. Oracle中可被并行化執行的SQL操作
9. unique index vs non-unique index
10. UNION ALL returning wrong results?

原文地址：Oracle Acs資深顧問羅敏 老羅技術核心感悟：自動掃描SQL語句工具？, 感謝原作者分享。

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com