ORACLE性能優(yōu)化

1.ORACLE的優(yōu)化器共有3種
     A、RULE (基于規(guī)則) b、COST (基于成本) c、CHOOSE (選擇性) 
設置缺省的優(yōu)化器，可以通過對init.ora文件中OPTIMIZER_MODE參數(shù)的各種聲明，如RULE，COST，CHOOSE，ALL_ROWS，F(xiàn)IRST_ROWS 。 你當然也在SQL句級或是會話(session)級對其進行覆蓋。 
為了使用基于成本的優(yōu)化器(CBO， Cost-Based Optimizer) ， 你必須經常運行analyze 命令，以增加數(shù)據庫中的對象統(tǒng)計信息(object statistics)的準確性。 
如果數(shù)據庫的優(yōu)化器模式設置為選擇性(CHOOSE)，那么實際的優(yōu)化器模式將和是否運行過analyze命令有關。 如果table已經被analyze過， 優(yōu)化器模式將自動成為CBO ， 反之，數(shù)據庫將采用RULE形式的優(yōu)化器。 
在缺省情況下，ORACLE采用CHOOSE優(yōu)化器， 為了避免那些不必要的全表掃描(full table scan) ， 你必須盡量避免使用CHOOSE優(yōu)化器，而直接采用基于規(guī)則或者基于成本的優(yōu)化器。 
2.訪問Table的方式
     ORACLE 采用兩種訪問表中記錄的方式： 
     A、 全表掃描 
          全表掃描就是順序地訪問表中每條記錄。ORACLE采用一次讀入多個數(shù)據塊(database block)的方式優(yōu)化全表掃描。 
     B、 通過ROWID訪問表 
          你可以采用基于ROWID的訪問方式情況，提高訪問表的效率， ROWID包含了表中記錄的物理位置信息。ORACLE采用索引(INDEX)實現(xiàn)了數(shù)據和存放數(shù)據的物理位置(ROWID)之間的聯(lián)系。通常索引提供了快速訪問ROWID的方法，因此那些基于索引列的查詢就可以得到性能上的提高。 
3.共享SQL語句
    為了不重復解析相同的SQL語句，在第一次解析之后，ORACLE將SQL語句存放在內存中。這塊位于系統(tǒng)全局區(qū)域SGA(system global area)的共享池(shared buffer pool)中的內存可以被所有的數(shù)據庫用戶共享。 因此，當你執(zhí)行一個SQL語句(有時被稱為一個游標)時，如果它和之前的執(zhí)行過的語句完全相同， ORACLE就能很快獲得已經被解析的語句以及最好的執(zhí)行路徑。ORACLE的這個功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內存的使用。 
可惜的是ORACLE只對簡單的表提供高速緩沖(cache buffering)，這個功能并不適用于多表連接查詢。 
數(shù)據庫管理員必須在init.ora中為這個區(qū)域設置合適的參數(shù)，當這個內存區(qū)域越大，就可以保留更多的語句，當然被共享的可能性也就越大了。 
當你向ORACLE提交一個SQL語句，ORACLE會首先在這塊內存中查找相同的語句。這里需要注明的是，ORACLE對兩者采取的是一種嚴格匹配，要達成共享，SQL語句必須完全相同(包括空格，換行等)。 
數(shù)據庫管理員必須在init.ora中為這個區(qū)域設置合適的參數(shù)，當這個內存區(qū)域越大，就可以保留更多的語句，當然被共享的可能性也就越大了。 
共享的語句必須滿足三個條件： 
     A、 字符級的比較： 當前被執(zhí)行的語句和共享池中的語句必須完全相同。 
     B、 兩個語句所指的對象必須完全相同： 
     C、 兩個SQL語句中必須使用相同的名字的綁定變量(bind variables)。 
4.選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)
    ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，因此FROM子句中寫在最后的表(基礎表 driving table)將被最先處理。在FROM子句中包含多個表的情況下，你必須選擇記錄條數(shù)最少的表作為基礎表。當ORACLE處理多個表時， 會運用排序及合并的方式連接它們。首先，掃描第一個表(FROM子句中最后的那個表)并對記錄進行派序，然后掃描第二個表(FROM子句中最后第二個表)，最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并。 
如果有3個以上的表連接查詢， 那就需要選擇交叉表(intersection table)作為基礎表， 交叉表是指那個被其他表所引用的表。 
5.WHERE子句中的連接順序
    ORACLE采用自下而上的順序解析WHERE子句，根據這個原理，表之間的連接必須寫在其他WHERE條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾。 
6.SELECT子句中避免使用 ' * '
    當你想在SELECT子句中列出所有的COLUMN時，使用動態(tài)SQL列引用 '*' 是一個方便的方法。不幸的是，這是一個非常低效的方法。實際上，ORACLE在解析的過程中， 會將'*' 依次轉換成所有的列名， 這個工作是通過查詢數(shù)據字典完成的， 這意味著將耗費更多的時間。 
7.減少訪問數(shù)據庫的次數(shù)
    當執(zhí)行每條SQL語句時，ORACLE在內部執(zhí)行了許多工作：解析SQL語句，估算索引的利用率，綁定變量，讀數(shù)據塊等等。由此可見，減少訪問數(shù)據庫的次數(shù)，就能實際上減少ORACLE的工作量。 
8.使用DECODE函數(shù)來減少處理時間
    使用DECODE函數(shù)可以避免重復掃描相同記錄或重復連接相同的表。 
9.整合簡單，無關聯(lián)的數(shù)據庫訪問
    如果你有幾個簡單的數(shù)據庫查詢語句，你可以把它們整合到一個查詢中(即使它們之間沒有關系) 
10.刪除重復記錄
11.用TRUNCATE替代DELETE
     當刪除表中的記錄時，在通常情況下， 回滾段(rollback segments ) 用來存放可以被恢復的信息。 如果你沒有COMMIT事務，ORACLE會將數(shù)據恢復到刪除之前的狀態(tài)(準確地說是恢復到執(zhí)行刪除命令之前的狀況)。 
     而當運用TRUNCATE時， 回滾段不再存放任何可被恢復的信息。當命令運行后，數(shù)據不能被恢復。因此很少的資源被調用，執(zhí)行時間也會很短。 
12.盡量多使用COMMIT
     只要有可能，在程序中盡量多使用COMMIT，這樣程序的性能得到提高，需求也會因為COMMIT所釋放的資源而減少 
     COMMIT所釋放的資源： 
     A、 回滾段上用于恢復數(shù)據的信息。 
     B、被程序語句獲得的鎖。 
     C、 redo log buffer 中的空間。 
     D、ORACLE為管理上述3種資源中的內部花費。 
13.計算記錄條數(shù)
     和一般的觀點相反，count(*) 比count(1)稍快，當然如果可以通過索引檢索，對索引列的計數(shù)仍舊是最快的。例如 COUNT(EMPNO) 
14.用Where子句替換HAVING子句
     避免使用HAVING子句，HAVING 只會在檢索出所有記錄之后才對結果集進行過濾。 這個處理需要排序，總計等操作。如果能通過WHERE子句限制記錄的數(shù)目，那就能減少這方面的開銷。 
15.減少對表的查詢
     在含有子查詢的SQL語句中，要特別注意減少對表的查詢。
16.通過內部函數(shù)提高SQL效率
17.使用表的別名(Alias)
     當在SQL語句中連接多個表時， 請使用表的別名并把別名前綴于每個Column上。這樣一來，就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤。 
18.用EXISTS替代IN
     在許多基于基礎表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯(lián)接。在這種情況下，使用EXISTS(或NOT EXISTS)通常將提高查詢的效率。 
19.用NOT EXISTS替代NOT IN
     在子查詢中，NOT IN子句將執(zhí)行一個內部的排序和合并。無論在哪種情況下，NOT IN都是最低效的 (因為它對子查詢中的表執(zhí)行了一個全表遍歷)。為了避免使用NOT IN ，我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS。 
20.用表連接替換EXISTS
     通常來說 ， 采用表連接的方式比EXISTS更有效率 
21.用EXISTS替換DISTINCT
     當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時，避免在SELECT子句中使用DISTINCT。 一般可以考慮用EXIST替換 
22.用索引提高效率：
索引是表的一個概念部分,用來提高檢索數(shù)據的效率，ORACLE使用了一個復雜的自平衡B-tree結構. 通常,通過索引查詢數(shù)據比全表掃描要快. 當ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時, ORACLE優(yōu)化器將使用索引. 同樣在聯(lián)結多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證.。那些LONG或LONG RAW數(shù)據類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當然,你也會發(fā)現(xiàn), 在掃描小表時,使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來存儲,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁盤I/O . 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢.。定期的重構索引是有必要的.： 
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
23.sql語句用大寫的
因為oracle總是先解析sql語句，把小寫的字母轉換成大寫的再執(zhí)行
24.在java代碼中盡量少用連接符“＋”連接字符串！
25 .用>=替代> 
高效: SELECT * FROM EMP WHERE DEPTNO >=4 
低效: SELECT * FROM EMP WHERE DEPTNO >3
兩者的區(qū)別在于, 前者DBMS將直接跳到第一個DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個DEPT大于3的記錄.
26.用UNION替換OR (適用于索引列) 
通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果. 對索引列使用OR將造成全表掃描. 注意, 以上規(guī)則只針對多個索引列有效. 如果有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, 那就需要返回記錄最少的索引列寫在最前面. 
27.避免在索引列上使用IS NULL和IS NOT NULL 
避免在索引中使用任何可以為空的列，ORACLE將無法使用該索引．對于單列索引，如果列包含空值，索引中將不存在此記錄. 對于復合索引，如果每個列都為空，索引中同樣不存在此記錄. 如果至少有一個列不為空，則記錄存在于索引中．舉例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一條記錄的A,B值為(123,null) , ORACLE將不接受下一條具有相同A,B值（123,null）的記錄(插入). 然而如果所有的索引列都為空，ORACLE將認為整個鍵值為空而空不等于空. 因此你可以插入1000 條具有相同鍵值的記錄,當然它們都是空! 因為空值不存在于索引列中,所以WHERE子句中對索引列進行空值比較將使ORACLE停用該索引. 
低效: (索引失效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; 高效: (索引有效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;
28.用UNION-ALL 替換UNION ( 如果有可能的話)： 
    當SQL語句需要UNION兩個查詢結果集合時,這兩個結果集合會以UNION-ALL的方式被合并, 然后在輸出最終結果前進行排序. 如果用UNION ALL替代UNION, 這樣排序就不是必要了. 效率就會因此得到提高. 需要注意的是，UNION ALL 將重復輸出兩個結果集合中相同記錄. 因此各位還是要從業(yè)務需求分析使用UNION ALL的可行性. UNION 將對結果集合排序,這個操作會使用到SORT_AREA_SIZE這塊內存. 對于這塊內存的優(yōu)化也是相當重要的. 下面的SQL可以用來查詢排序的消耗量
29.需要當心的WHERE子句: 
    某些SELECT 語句中的WHERE子句不使用索引. 這里有一些例子. 在下面的例子里, (1)‘!=' 將不使用索引. 記住, 索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中. (2) ‘||'是字符連接函數(shù). 就象其他函數(shù)那樣, 停用了索引. (3) ‘+'是數(shù)學函數(shù). 就象其他數(shù)學函數(shù)那樣, 停用了索引. (4)相同的索引列不能互相比較,這將會啟用全表掃描.
30.帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎 執(zhí)行耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次排序. 通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫. 如果你的數(shù)據庫的SORT_AREA_SIZE調配得好, 使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強
31.優(yōu)化GROUP BY: 
提高GROUP BY 語句的效率, 可以通過將不需要的記錄在GROUP BY 之前過濾掉.下面兩個查詢返回相同結果但第二個明顯就快了許多。