引自：
http://www.aygfsteel.com/calvin/archive/2005/11/11/19276.html從8i到10g，Oracle不斷進(jìn)化自己的SQL Tuning智能，一些秘籍級(jí)的優(yōu)化口訣已經(jīng)失效。
???但我喜歡失效，不用記口訣，操個(gè)
Toad for Oracle Xpert?，按照大方向舒舒服服的調(diào)優(yōu)才是愛(ài)做的事情。
1.Excution Plan
???? Excution Plan是最基本的調(diào)優(yōu)概念，不管你的調(diào)優(yōu)吹得如何天花亂墮，結(jié)果還是要由Excution plan來(lái)顯示Oracle 最終用什么索引、按什么順序連接各表，F(xiàn)ull Table Scan還是Access by Rowid Index，瓶頸在什么地方。如果沒(méi)有它的指導(dǎo)，一切調(diào)優(yōu)都是蒙的。
2.Toad for Oracle Xpert??? 用它來(lái)調(diào)優(yōu)在真的好舒服。
Quest 吞并了Lecco后，將它整合到了Toad 的SQL Tunning里面：最清晰的執(zhí)行計(jì)劃顯示，自動(dòng)生成N條等價(jià)SQL、給出優(yōu)化建議，不同SQL執(zhí)行計(jì)劃的對(duì)比，還有實(shí)際執(zhí)行的邏輯讀、物理讀數(shù)據(jù)等等一目了然。
3.索引
???大部分的性能問(wèn)題其實(shí)都是索引應(yīng)用的問(wèn)題，Where子句、Order By、Group?By 都要用到索引。
???一般開(kāi)發(fā)人員認(rèn)為將索引建全了就可以下班回家了，實(shí)則還有頗多的思量和陷阱。
3.1 索引列上不要進(jìn)行計(jì)算????? 這是最最普遍的失效陷阱，比如where trunc(order_date)=trunc(sysdate), i+2>4。索引失效的原因也簡(jiǎn)單，索引是針對(duì)原值建的二叉樹(shù)，你將列值*3/4+2折騰一番后，原來(lái)的二叉樹(shù)當(dāng)然就用不上了。解決的方法:
1. 換成等價(jià)語(yǔ)法，比如trunc(order_date) 換成
where order_date>trunc(sysdate)-1?and?order_date<trunc(sysdate)+1
?2.??? 特別為計(jì)算建立函數(shù)索引
create?index?Ｉ_XXXX?on?shop_order(trunc(order_date))
??? 3.??? 將計(jì)算從等號(hào)左邊移到右邊
這是針對(duì)某些無(wú)心之失的糾正，把a(bǔ)*2>4 改為a>4/2；把TO_CHAR(zip) = '94002' 改為zip = TO_NUMBER('94002');
3.2 CBO與索引選擇性
???? 建了索引也不一定會(huì)被Oracle用的，就像個(gè)挑食的孩子。基于成本的優(yōu)化器(CBO, Cost-Based Optimizer)，會(huì)先看看表的大小，還有索引的重復(fù)度，再?zèng)Q定用還是不用。表中有100 條記錄而其中有80 個(gè)不重復(fù)的索引鍵值. 這個(gè)索引的選擇性就是80/100 = 0.8，留意Toad里顯示索引的Selective和Cardinailty。實(shí)在不聽(tīng)話時(shí)，就要用hints來(lái)調(diào)教。
???? 另外，where語(yǔ)句存在多條索引可用時(shí)，只會(huì)選擇其中一條。所以索引也不是越多越好：）
3.3 索引重建
?????傳說(shuō)中數(shù)據(jù)更新頻繁導(dǎo)致有20%的碎片時(shí)，Oracle就會(huì)放棄這個(gè)索引。寧可信其有之下，應(yīng)該時(shí)常alter index <INDEXNAME> rebuild一下。
3.4 其他要注意的地方
????? 不要使用Not，如goods_no != 2，要改為
where?goods_no>2?and?goods_no<2
??????不要使用is null?, 如WHERE DEPT_CODE IS NOT NULL?要改為
WHERE?DEPT_CODE?>=0;
3.5 select 的列如果全是索引列時(shí)
?? 又如果沒(méi)有where 條件，或者where條件全部是索引列時(shí)，Oracle 將直接從索引里獲取數(shù)據(jù)而不去讀真實(shí)的數(shù)據(jù)表，這樣子理論上會(huì)快很多，比如
select?order_no,order_time?from?shop_order?where?shop_no=4
當(dāng)order_no,order_time,shop_no 這三列全為索引列時(shí)，你將看到一個(gè)和平時(shí)完全不同的執(zhí)行計(jì)劃。
3.6 位圖索引
?????傳說(shuō)中當(dāng)數(shù)據(jù)值較少，比如某些表示分類、狀態(tài)的列，應(yīng)該建位圖索引而不是普通的二叉樹(shù)索引，否則效率低下。不過(guò)看執(zhí)行計(jì)劃，這些位圖索引鮮有被Oracle臨幸的。
?
4.減少查詢往返和查詢的表
這也是很簡(jiǎn)單的大道理，程序與Oracle交互的成本極高，所以一個(gè)查詢能完成的不要分開(kāi)兩次查，如果一個(gè)循環(huán)執(zhí)行１萬(wàn)條查詢的，怎么都快不到哪里去了。
4.1 封裝PL/SQL存儲(chǔ)過(guò)程
最高級(jí)的做法是把循環(huán)的操作封裝到PL/SQL寫的存儲(chǔ)過(guò)程里，因?yàn)榇鎯?chǔ)過(guò)程都在服務(wù)端執(zhí)行，所以沒(méi)有數(shù)據(jù)往返的消耗。
4.2 封裝PL/SQL內(nèi)部函數(shù)
??有機(jī)會(huì)，將一些查詢封裝到函數(shù)里，而在普通SQL里使用這些函數(shù)，同樣是很有效的優(yōu)化。
4.3 Decode/Case
但存儲(chǔ)過(guò)程也麻煩，所以有case/decode把幾條條件基本相同的重復(fù)查詢合并為一條的用法：
SELECT
?COUNT(CASE?WHEN?price?<?13?THEN?1?ELSE?null?END)?low,
?COUNT(CASE?WHEN?price?BETWEEN?13?AND?15?THEN?1?ELSE?null?END)?med,
?COUNT(CASE?WHEN?price?>?15?THEN?1?ELSE?null?END)?high
FROM?products;
4.4 一種Where/Update語(yǔ)法
SELECT?TAB_NAME FROM?TABLES
WHERE?(TAB_NAME,DB_VER)?=?（(?SELECT?TAB_NAME,DB_VER)
FROM?TAB_COLUMNS?WHERE?VERSION?=?604)
UPDATE?EMP
SET?(EMP_CAT,?SAL_RANGE)
=?(SELECT?MAX(CATEGORY)FROM?EMP_CATEGORIES)
5.其他優(yōu)化
5.1RowID和ROWNUM
???? 連Hibernate 新版也支持ROWID了，證明它非常有用。比如號(hào)稱刪除重復(fù)數(shù)據(jù)的最快寫法：
DELETE?FROM?EMP?E
WHERE?E.ROWID?>?(SELECT?MIN(X.ROWID)
FROM?EMP?X
WHERE?X.EMP_NO?=?E.EMP_NO);
?
6.終極秘技 - Hints
?? 這是Oracle DBA的玩具，也是終極武器，比如Oracle在CBO,RBO中所做的選擇總不合自己心水時(shí)，可以用它來(lái)強(qiáng)力調(diào)教一下Oracle，結(jié)果經(jīng)常讓人喜出望外。
?? 如果開(kāi)發(fā)人員沒(méi)那么多時(shí)間來(lái)專門學(xué)習(xí)它，可以依靠Toad SQL opmitzer 來(lái)自動(dòng)生成這些提示，然后對(duì)比一下各種提示的實(shí)際效果。不過(guò)隨著10g智能的進(jìn)化，hints的驚喜少了。
7. 找出要優(yōu)化的Top SQL
??? 磨了這么久的槍，如果找不到敵人是件郁悶的事情。
??? 幸虧10g這方面做得非常好。進(jìn)入Web管理界面，就能看到當(dāng)前或者任意一天的SQL列表，按性能排序。
??? 有了它，SQL Trace和TKPROF都可以不用了。