no1：選擇合適的優化器no2：共享sql為了不重復解析相同的sql語句,在第一次解析之后, oracle將sql語句存放在內存中.這塊位于系統全局區域sga(system global area)的共享池(shared buffer pool)中的內存可以被所有的數據庫用戶共享. 因此,當你執行一個sql語句(有時被稱為一個游標)時,如果它和之前的執行過的語句完全相同, oracle就能很快獲得已經被解析的語句以及最好的執行路徑. oracle的這個功能大大地提高了sql的執行性能并節省了內存的使用. 可惜的是oracle只對簡單的表提供高速緩沖(cache buffering) ,這個功能并不適用于多表連接查詢.數據庫管理員必須在init.ora中為這個區域設置合適的參數,當這個內存區域越大,就可以保留更多的語句,當然被共享的可能性也就越大了.當你向oracle 提交一個sql語句,oracle會首先在這塊內存中查找相同的語句. 這里需要注明的是,oracle對兩者采取的是一種嚴格匹配,要達成共享,sql語句必須完全相同(包括空格,換行等).共享的語句必須滿足三個條件: a. 字符級的比較:b. 兩個語句所指的對象必須完全相同: c. 兩個sql語句中必須使用相同的名字的綁定變量(bind variables)no3. 選擇最有效率的表名順序(只在基于規則的優化器中有效)oracle的解析器按照從右到左的順序處理from子句中的表名,因此from子句中寫在最后的表(基礎表 driving table)將被最先處理. 在from子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表.當oracle處理多個表時, 會運用排序及合并的方式連接它們.首先,掃描第一個表(from子句中最后的那個表)并對記錄進行排序,然后掃描第二個表(from子句中最后第二個表),最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并.如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表. no4：where子句中的連接順序oracle采用自下而上的順序解析where子句,根據這個原理,表之間的連接必須寫在其他where條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在where子句的末尾.no5：避免使用select *當你想在select子句中列出所有的column時,使用動態sql列引用 ‘*’ 是一個方便的方法.不幸的是,這是一個非常低效的方法. 實際上,oracle在解析的過程中, 會將’*’ 依次轉換成所有的列名, 這個工作是通過查詢數據字典完成的, 這意味著將耗費更多的時間. no6：減少訪問數據庫的次數當執行每條sql語句時, oracle在內部執行了許多工作: 解析sql語句, 估算索引的利用率, 綁定變量 , 讀數據塊等等. 由此可見, 減少訪問數據庫的次數 , 就能實際上減少oracle的工作量.no7：用truncate替代delete當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復的信息. 如果你沒有commit事務,oracle會將數據恢復到刪除之前的狀態(準確地說是恢復到執行刪除命令之前的狀況)而當運用truncate時, 回滾段不再存放任何可被恢復的信息.當命令運行后,數據不能被恢復.因此很少的資源被調用,執行時間也會很短. no8：適當的使用commit這樣程序的性能得到提高,需求也會因為commit所釋放的資源而減少: commit所釋放的資源:a. 回滾段上用于恢復數據的信息.b. 被程序語句獲得的鎖c. redo log buffer 中的空間d. oracle為管理上述3種資源中的內部花費在使用commit時必須要注意到事務的完整性,現實中效率和事務完整性往往是魚和熊掌不可得兼，另外過于頻繁的使用commit效率也會降低。no9：減少對表的查詢在含有子查詢的sql語句中,要特別注意減少對表的查詢.update 多個column 例子: 低效: update emp set emp_cat = (select max(category) from emp_categories), sal_range = (select max(sal_range) from emp_categories) where emp_dept = 0020; 高效: update emp set (emp_cat, sal_range) = (select max(category) , max(sal_range) from emp_categories) where emp_dept = 0020;no10：in與exist先說in他相當對inner table執行一個個帶有distinct的子查詢語句，然后得到的查詢結果集再與outer table進行連接，當然連接的方式和索引的使用仍然同于普通的兩表連接。select * from t1 where x in (select y from t2);可以轉換成如下select * from t1,(select distinct y from t2) t2where t1.x=t2.y;再說exists實際上exists相當于對outer table進行全表掃描，用從中檢索到的每一行與inner table做循環匹配輸出相應的符合條件的結果，其主要開銷是對outer table的全表掃描（full scan），而連接方式是nested loop方式。可以寫成select * from t1 where exists (select null from t2 where t2.y=t1.x);轉換成for cursor1 in (select * from t1)loopif (exists (select null from t2 where t2.y=cursor1.x))then 返回匹配的記錄;end if;end loop;通過上面的解釋，現在很容易明白當t2數據量巨大且索引情況不好(大量重復值等)，則不宜使用產生對t2的distinct檢索而導致系統開支巨大的in操作，反之當t1表數據量巨大(不受索引影響)而t2表數據較少且索引良好則不宜使用引起t1全表掃描的exists操作no11：not in, not exist在子查詢中,not in子句將執行一個內部的排序和合并. 無論在哪種情況下,not in都是最低效的 (因為它對子查詢中的表執行了一個全表遍歷). 為了避免使用not in ,我們可以把它改寫成外連接(outer joins)或not exists. no12：使用表連接連替換exist通常來說 , 采用表連接的方式比exists更有效率select ename from emp e where exists (select ‘x’ from dept where dept_no = e.dept_no and dept_cat = ‘a’); (更高效) select ename from dept d,emp e where e.dept_no = d.dept_no and dept_cat = ‘a’ ;no13：用索引提高效率1．索引基礎索引是表的一個概念部分,用來提高檢索數據的效率. 實際上,oracle使用了一個復雜的自平衡b-tree結構. 通常,通過索引查詢數據比全表掃描要快. 當oracle找出執行查詢和update語句的最佳路徑時, oracle優化器將使用索引. 同樣在聯結多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證.除了那些long或long raw數據類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當然,你也會發現, 在掃描小表時,使用索引同樣能提高效率.雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來存儲,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的insert , delete , update將為此多付出4 , 5 次的磁盤i/o . 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢.oracle對索引有兩種訪問模式. 索引唯一掃描 ( index unique scan) 大多數情況下, 優化器通過where子句訪問index. 例如:表lodging有兩個索引 : 建立在lodging列上的唯一性索引lodging_pk和建立在manager列上的非唯一性索引lodging$manager. select * from lodgingwhere lodging = ‘rose hill’; 在內部 , 上述sql將被分成兩步執行, 首先 , lodging_pk 索引將通過索引唯一掃描的方式被訪問 , 獲得相對應的rowid, 通過rowid訪問表的方式 執行下一步檢索. 如果被檢索返回的列包括在index列中,oracle將不執行第二步的處理(通過rowid訪問表). 因為檢索數據保存在索引中, 單單訪問索引就可以完全滿足查詢結果. 下面sql只需要index unique scan 操作. select lodging from lodgingwhere lodging = ‘rose hill’; 索引范圍查詢(index range scan) 適用于兩種情況:1. 基于一個范圍的檢索2. 基于非唯一性索引的檢索 例1: select lodging from lodgingwhere lodging like ‘m%’; where子句條件包括一系列值, oracle將通過索引范圍查詢的方式查詢lodging_pk . 由于索引范圍查詢將返回一組值, 它的效率就要比索引唯一掃描低一些. 例2: select lodging from lodgingwhere manager = ‘bill gates’; 這個sql的執行分兩步, lodging$manager的索引范圍查詢(得到所有符合條件記錄的rowid) 和下一步同過rowid訪問表得到lodging列的值. 由于lodging$manager是一個非唯一性的索引,數據庫不能對它執行索引唯一掃描. 由于sql返回lodging列,而它并不存在于lodging$manager索引中, 所以在索引范圍查詢后會執行一個通過rowid訪問表的操作. 2．存在下面情況的sql，不會用到索引 存在數據類型隱形轉換的,如： select * from staff_member where staff_id=’123’； 列上有數學運算的，如： select * from staff_member where salary*2<10000; 使用不等于(<> )運算的，如： select * from staff_member where dept_no<>2001;記住, 索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中. 使用substr字符串函數的，如： select * from staff_member where substr(last_name,1,4)=’fred’; ‘%’通配符在第一個字符的，如: select * from staff_member where first_name like ‘%don’; 字符串連接(||)的，如： select * from staff_member where first_name||’’=’donald’ 避免在索引中使用任何可以為空的列，oracle將無法使用該索引 ．對于單列索引，如果列包含空值，索引中將不存在此記錄. 對于復合索引，如果每個列都為空，索引中同樣不存在此記錄. 如果至少有一個列不為空，則記錄存在于索引中． 通常， 我們要避免在索引列上使用not, not會產生在和在索引列上使用函數相同的影響. 當oracle”遇到”not,他就會停止使用索引轉而執行全表掃描. 如果一定要對使用函數的列啟用索引, oracle新的功能: 基于函數的索引(function-based index) 也許是一個較好的方案. create index emp_i on emp (upper(ename)); /*建立基于函數的索引*/ select * from emp where upper(ename) = ‘blacksnail’; /*將使用索引*/3．多個索引情況下的選擇當sql語句的執行路徑可以使用分布在多個表上的多個索引時, oracle會同時使用多個索引并在運行時對它們的記錄進行合并, 檢索出僅對全部索引有效的記錄.在oracle選擇執行路徑時,唯一性索引的等級高于非唯一性索引. 然而這個規則只有當where子句中索引列和常量比較才有效.如果索引列和其他表的索引類相比較. 這種子句在優化器中的等級是非常低的.如果不同表中兩個相同等級的索引將被引用, from子句中表的順序將決定哪個會被率先使用. from子句中最后的表的索引將有最高的優先級.如果相同表中兩個想同等級的索引將被引用, where子句中最先被引用的索引將有最高的優先級.舉例: deptno上有一個非唯一性索引,emp_cat也有一個非唯一性索引. select ename, from emp where dept_no = 20 and emp_cat = ‘a’;這里,deptno索引將被最先檢索,然后同emp_cat索引檢索出的記錄進行合并. 執行路徑如下: table access by rowid on emp and-equal index range scan on dept_idx index range scan on cat_idx當oracle無法判斷索引的等級高低差別,優化器將只使用一個索引,它就是在where子句中被列在最前面的. 4．自動選擇索引如果表中有兩個以上（包括兩個）索引，其中有一個唯一性索引，而其他是非唯一性．在這種情況下，oracle將使用唯一性索引而完全忽略非唯一性索引． 舉例:select enamefrom empwhere empno = 2326 and deptno = 20 ; 這里，只有empno上的索引是唯一性的，所以empno索引將用來檢索記錄．table access by rowid on emp index unique scan on emp_no_idx5．等式比較和范圍比較 當where子句中有索引列, oracle不能合并它們,oracle將用范圍比較. 舉例: deptno上有一個非唯一性索引,emp_cat也有一個非唯一性索引. select ename from emp where deptno > 20 and emp_cat = ‘a’; 這里只有emp_cat索引被用到,然后所有的記錄將逐條與deptno條件進行比較. 執行路徑如下: table access by rowid on emp index range scan on cat_idx 3．組合索引總是使用索引的第一個列如果索引是建立在多個列上, 只有在它的第一個列(leading column)被where子句引用時,優化器才會選擇使用該索引.6．cbo下使用更具選擇性的索引基于成本的優化器(cbo, cost-based optimizer)對索引的選擇性進行判斷來決定索引的使用是否能提高效率.如果索引有很高的選擇性, 那就是說對于每個不重復的索引鍵值,只對應數量很少的記錄.比如, 表中共有100條記錄而其中有80個不重復的索引鍵值. 這個索引的選擇性就是80/100 = 0.8 . 選擇性越高, 通過索引鍵值檢索出的記錄就越少. 如果索引的選擇性很低, 檢索數據就需要大量的索引范圍查詢操作和rowid 訪問表的操作. 也許會比全表掃描的效率更低.下列經驗請參閱:a. 如果檢索數據量超過30%的表中記錄數.使用索引將沒有顯著的效率提高. b. 在特定情況下, 使用索引也許會比全表掃描慢, 但這是同一個數量級上的區別. 而通常情況下,使用索引比全表掃描要塊幾倍乃至幾千倍! 7．用union (all)替換or (適用于索引列)通常情況下, 用union替換where子句中的or將會起到較好的效果. 對索引列使用or將造成全表掃描. 注意, 以上規則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇or而降低. 在下面的例子中, loc_id 和region上都建有索引.高效: select loc_id , loc_desc , region from location where loc_id = 10 union select loc_id , loc_desc , region from location where region = “melbourne” 低效: select loc_id , loc_desc , region from location where loc_id = 10 or region = “melbourne” 如果你堅持要用or, 那就需要返回記錄最少的索引列寫在最前面. 注意: where key1 = 10 (返回最少記錄)or key2 = 20 (返回最多記錄) oracle 內部將以上轉換為where key1 = 10 and((not key1 = 10) and key2 = 20)8．用>=替代>dept>3和dept>=4兩者的區別在于, 前者dbms將直接跳到第一個dept等于4的記錄而后者將首先定位到deptno=3的記錄并且向前掃描到第一個dept大于3的記錄. no14：用union all 替換union ( 如果有可能的話) 當sql語句需要union兩個查詢結果集合時,這兩個結果集合會以union-all的方式被合并, 然后在輸出最終結果前進行排序.如果用union all替代union, 這樣排序就不是必要了. 效率就會因此得到提高.no15：使用提示(hints)對于表的訪問,可以使用兩種hints.full 和 rowidfull hint 告訴oracle使用全表掃描的方式訪問指定表.例如: select /*+ full(emp) */ * from emp where empno = 7893; rowid hint 告訴oracle使用table access by rowid的操作訪問表. 通常, 你需要采用table access by rowid的方式特別是當訪問大表的時候, 使用這種方式, 你需要知道roiwd的值或者使用索引. 如果一個大表沒有被設定為緩存(cached)表而你希望它的數據在查詢結束是仍然停留在sga中,你就可以使用cache hint 來告訴優化器把數據保留在sga中. 通常cache hint 和 full hint 一起使用.例如:select /*+ full(worker) cache(worker)*/ *from work; 索引hint 告訴oracle使用基于索引的掃描方式. 你不必說明具體的索引名稱例如: select /*+ index(lodging) */ lodging from lodging where manager = ‘bill gates’; 在不使用hint的情況下, 以上的查詢應該也會使用索引,然而,如果該索引的重復值過多而你的優化器是cbo, 優化器就可能忽略索引. 在這種情況下, 你可以用index hint強制oracle使用該索引. oracle hints 還包括all_rows, first_rows, rule,use_nl, use_merge, use_hash 等等. 使用hint , 表示我們對oracle優化器缺省的執行路徑不滿意,需要手工修改.這是一個很有技巧性的工作. 我建議只針對特定的,少數的sql進行hint的優化.對oracle的優化器還是要有信心(特別是cbo)no16：避免使用耗費資源的操作帶有distinct,union,minus,intersect,order by的sql語句會啟動sql引擎執行耗費資源的排序(sort)功能. distinct需要一次排序操作, 而其他的至少需要執行兩次排序.例如,一個union查詢,其中每個查詢都帶有group by子句, group by會觸發嵌入排序(nested sort) ; 這樣, 每個查詢需要執行一次排序, 然后在執行union時, 又一個唯一排序(sort unique)操作被執行而且它只能在前面的嵌入排序結束后才能開始執行. 嵌入的排序的深度會大大影響查詢的效率.通常, 帶有union, minus , intersect的sql語句都可以用其他方式重寫. 如果你的數據庫的sort_area_size調配得好, 使用union , minus, intersect也是可以考慮的, 畢竟它們的可讀性很強no17：分離表和索引總是將你的表和索引建立在不同的表空間內(tablespaces). 決不要將不屬于oracle內部系統的對象存放到system表空間里. 同時,確保數據表空間和索引表空間置與不同的硬盤控制卡控制的硬盤上.no18：排序發生的情況 sql中包含group by 子句 sql 中包含order by 子句 sql 中包含 distinct 子句 sql 中包含 minus 或 union操作 創建索引時這些情況慢。no19：execute immediate, dbms_sql動態sql中，盡量多用execute immediate,而少用dbms_sql,前者綜合效率優于后者no20：用like替換和substr對于‘like’和‘substr’，其效率并沒有多大分別。但是，當所搜索的值不存在時，使用‘like’的速度明顯大于‘substr’。所以：select * from a where substr(a1,1,4) = '5378' 可以用like替代select * from a where a1 like ‘5378%’no20：dml語句優化 1．如果有可能的話truncate 替代delete 2．大表的刪除轉化為對剩余部分建表，truncate原表然后將新建的表改名為原表. 3．update多列的時候，盡量不要用多個set；如：update emp set (emp_cat, sal_range) = (select max(category) , max(sal_range) from emp_categories) where emp_dept = 0020; 4．如果有索引，刪除索引后執行操作，操作完成后重建索引。 5．環境允許的話使用并行no21：使用并行hint /*+ parallel(tablename,parallel-degree)*/調整并行執行的目的是：最大地發揮硬件的能力。如果你有一個高性能的系統，有高優先的sql語句在運行，則并行語句就可以使用所有有效的資源。oracle可以執行的下面的并行： l 并行查詢；l 并行dml(包括 insert, update, delete; append提示，并行索引掃描)；l 并行 ddl；如果你的系統缺少以下這些特點，則并行可能不會有多大改善。l 對稱多處理器（smp), 集群或強大的并行系統；l 有效的i/o帶寬；l 低利用的或閑置的cpu（如cpu使用小于30%）；l 對附加的內存無效，如分類、哈西索引及i/o緩沖區等。如果指定的并行度大于實際可用的資源(硬件資源>parallel_max_server>你指定的并行度)，將會使用最大的可用資源的并行度來處理。如果多人同時使用并行, sum(parallel_degree)>parallel_max_server ；可能產生等待使效率下降。
