提高數據庫性能的方式有兩種

一、一種是DBA通過對數據庫的各個方面調優

   調整數據庫:共享池,java池,高速緩存,大型池,java池

   針對于window操作系統 32位,Oracle內存占用，最大為1.7G,超過則不作用,因此這幾項值之和不應超過1.7G

   目前各池參數為:

         共享池:512MB

         高速緩存:904MB

         大型池:64MB

         java池:40MB

         PGA:312MB

二、對sql語句的優化

   (1)、建立必要的索引，可以初步判斷出哪些表的哪些字段應該建立索引。  

   (2)、必須熟悉應用程序。必須了解哪些表是數據操作頻繁的表；哪些表經常與其他表進行連接；哪些表中的數據量可能很大；      對于數據量大的表，其中各個字段的數據分布情況如何；建立索引常用的規則如下：

       1、表的主鍵、外鍵必須有索引；

       2、數據量超過300的表應該有索引；

       3、經常與其他表進行連接的表，在連接字段上應該建立索引；

       4、經常出現在Where子句中的字段，特別是大表的字段，應該建立索引；

       5、索引應該建在選擇性高的字段上；

       6、索引應該建在小字段上，對于大的文本字段甚至超長字段，不要建索引；

       7、復合索引的建立需要進行仔細分析；盡量考慮用單字段索引代替：

             A、正確選擇復合索引中的主列字段，一般是選擇性較好的字段；

             B、復合索引的幾個字段是否經常同時以AND方式出現在Where子句中？單字段查詢是否極少甚至沒有？如果是，             則可以建立復合索引；否則考慮單字段索引；

            C、如果復合索引中包含的字段經常單獨出現在Where子句中，則分解為多個單字段索引；

            D、如果復合索引所包含的字段超過3個，那么仔細考慮其必要性，考慮減少復合的字段；

            E、如果既有單字段索引，又有這幾個字段上的復合索引，一般可以刪除復合索引；

     8、頻繁進行數據操作的表，不要建立太多的索引；

     9、刪除無用的索引，避免對執行計劃造成負面影響；

    在表上建立的每個索引都會增加存儲開銷，索引對于插入、刪除、更新操作也會增加處理上的開銷。 另外，過多的復合索引，    在有單字段索引的情況下，一般都是沒有存在價值的；相反，還會降低數據增加刪除時的性能，特別是對頻繁更新的表來說，負面影響更大。

 (3)、避免對列的操作   任何對列的操作都可能導致全表掃描，這里所謂的操作包括數據庫函數、計算表達式等等，查詢時要盡可能將操作移至等式的右邊，   甚至去掉函數。         例1：下列SQL條件語句中的列都建有恰當的索引，但30萬行數據情況下執行速度卻非常慢：         select * from record where  substrb(CardNo,1,4)='5378'(13秒)        select * from record where  amount/30< 1000（11秒）        select * from record where  to_char(ActionTime,'yyyymmdd')='19991201'（10秒）        由于where子句中對列的任何操作結果都是在SQL運行時逐行計算得到的，因此它不得不進行表掃描，而沒有使用該列上面的索引；   如果這些結果在查詢編譯時就能得到，那么就可以被SQL優化器優化，使用索引，避免表掃描，因此將SQL重寫如下：       select * from record where CardNo like  '5378%'（< 1秒）      select * from record where amount  < 1000*30（< 1秒）      select * from record where ActionTime= to_date ('19991201' ,'yyyymmdd')（< 1秒）      差別是很明顯的！

 (4)、避免不必要的類型轉換   需要注意的是，盡量避免潛在的數據類型轉換。如將字符型數據與數值型數據比較，ORACLE會自動將字符型用to_number()函數進行轉換，   從而導致全表掃描。

   例2：表tab1中的列col1是字符型（char)，則以下語句存在類型轉換：      select col1,col2 from tab1 where col1>10，      應該寫為： select col1,col2 from tab1 where col1>'10'。

  (5)、增加查詢的范圍限制   增加查詢的范圍限制，避免全范圍的搜索。

   例3：以下查詢表record 中時間ActionTime小于2001年3月1日的數據：

      select * from record where ActionTime < to_date ('20010301' ,'yyyymm')

   查詢計劃表明，上面的查詢對表進行全表掃描，如果我們知道表中的最早的數據為2001年1月1日，那么，可以增加一個最小時間，   使查詢在一個完整的范圍之內。修改如下： select * from record where

   ActionTime < to_date ('20010301' ,'yyyymm')      and   ActionTime > to_date ('20010101' ,'yyyymm')      后一種SQL語句將利用上ActionTime字段上的索引，從而提高查詢效率。把'20010301'換成一個變量，根據取值的機率，   可以有一半以上的機會提高效率。同理，對于大于某個值的查詢，如果知道當前可能的最大值，   也可以在Where子句中加上 “AND 列名< MAX(最大值)”。

  (6)、盡量去掉"IN"、"OR"   含有"IN"、"OR"的Where子句常會使用工作表，使索引失效；如果不產生大量重復值，可以考慮把子句拆開；拆開的子句中應該包含索引。  

   例4：     select count(*) from stuff where id_no in('0','1')（23秒）

   可以考慮將or子句分開：  

   select count(*) from stuff where id_no='0' 

   select count(*) from stuff where id_no='1'

   然后再做一個簡單的加法，與原來的SQL語句相比，查詢速度更快。

  (7)、盡量去掉 "<>"   盡量去掉 "<>"，避免全表掃描，如果數據是枚舉值，且取值范圍固定，則修改為"OR"方式。

   例5：      UPDATE SERVICEINFO SET STATE=0 WHERE STATE<>0;      以上語句由于其中包含了"<>"，執行計劃中用了全表掃描（TABLE access FULL），沒有用到state字段上的索引。實際應用中，   由于業務邏輯的限制，字段state為枚舉值，只能等于0，1或2，而且，值等于=1，2的很少，因此可以去掉"<>"，利用索引來提高效率。      修改為：UPDATE SERVICEINFO SET STATE=0  WHERE STATE = 1 OR STATE = 2 。進一步的修改可以參考第4種方法。      (8)、去掉Where子句中的IS NULL和IS NOT NULL   Where字句中的IS NULL和IS NOT NULL將不會使用索引而是進行全表搜索，因此需要通過改變查詢方式，分情況討論等方法，   去掉Where子句中的IS NULL和IS NOT NULL。

  (9)、索引提高數據分布不均勻時查詢效率   索引的選擇性低，但數據的值分布差異很大時，仍然可以利用索引提高效率。   A、數據分布不均勻的特殊情況下，選擇性不高的索引也要創建。   表ServiceInfo中數據量很大，假設有一百萬行，其中有一個字段DisposalCourseFlag，取值范圍為枚舉值：[0，1，2，3，4，5，6，7]。   按照前面說的索引建立的規則，“選擇性不高的字段不應該建立索引，該字段只有8種取值，索引值的重復率很高，索引選擇性明顯很低，   因此不建索引。然而，由于該字段上數據值的分布情況非常特殊，具體如下表：

   取值范圍    1~5    6    7       占總數據量的百分比    1%    98%    1%   而且，常用的查詢中，查詢DisposalCourseFlag<6 的情況既多又頻繁，毫無疑問，如果能夠建立索引，并且被應用，   那么將大大提高這種情況的查詢效率。因此，我們需要在該字段上建立索引。

  (10)、利用HINT強制指定索引   在ORACLE優化器無法用上合理索引的情況下，利用HINT強制指定索引。      繼續上面7的例子，ORACLE缺省認定，表中列的值是在所有數據行中均勻分布的，也就是說，在一百萬數據量下，   每種DisposalCourseFlag值各有12.5萬數據行與之對應。假設SQL搜索條件DisposalCourseFlag=2，   利用DisposalCourseFlag列上的索引進行數據搜索效率，往往不比全表掃描的高，ORACLE因此對索引“視而不見”，從而在查詢路徑的選擇中，   用其他字段上的索引甚至全表掃描。根據我們上面的分析，數據值的分布很特殊，嚴重的不均勻。為了利用索引提高效率，   此時，一方面可以單獨對該字段或該表用analyze語句進行分析，對該列搜集足夠的統計數據，使ORACLE在查詢選擇性較高的值時能用上索引；   另一方面，可以利用HINT提示，在SELECT關鍵字后面，加上“/*+ INDEX(表名稱，索引名稱）*/”的方式，強制ORACLE優化器用上該索引。      比如： select * from  serviceinfo where DisposalCourseFlag=1 ;      上面的語句，實際執行中ORACLE用了全表掃描，加上藍色提示部分后，用到索引查詢。如下：      select /*+  INDEX(SERVICEINFO,IX_S_DISPOSALCOURSEFLAG)  */  *       from  serviceinfo where DisposalCourseFlag=1;      請注意，這種方法會加大代碼維護的難度，而且該字段上索引的名稱被改變之后，必須要同步所有指定索引的HINT代碼，否則HINT提示將被ORACLE忽略掉。

   (11)、屏蔽無用索引   繼續上面8的例子，由于實際查詢中，還有涉及到DisposalCourseFlag=6的查詢，而此時如果用上該字段上的索引，將是非常不明智的，   效率也極低。因此這種情況下，我們需要用特殊的方法屏蔽該索引，以便ORACLE選擇其他字段上的索引。   比如，如果字段為數值型的就在表達式的字段名后，添加“+ 0”，為字符型的就并上空串：“||""”

   如： select * from  serviceinfo where DisposalCourseFlag+ 0 = 6 and workNo =  '36' 。

   不過，不要把該用的索引屏蔽掉了，否則同樣會產生低效率的全表掃描。   (12)、分解復雜查詢，用常量代替變量   對于復雜的Where條件組合，Where中含有多個帶索引的字段，考慮用IF語句分情況進行討論；同時，去掉不必要的外來參數條件，   減低復雜度，以便在不同情況下用不同字段上的索引。

   繼續上面9的例子，對于包含      Where (DisposalCourseFlag < v_DisPosalCourseFlag) or (v_DisPosalCourseFlag is null) and ....的查詢，(這里v_DisPosalCourseFlag為一個輸入變量，取值范圍可能為[NULL，0，1，2，3，4，5，6，7])，可以考慮分情況用IF語句進行討論，類似：      IF v_DisPosalCourseFlag =1 THEN      Where DisposalCourseFlag = 1 and ....      ELSIF v_DisPosalCourseFlag =2 THEN      Where DisposalCourseFlag = 2 and ....        。。。。。。

  (13)、 like子句盡量前端匹配   因為like參數使用的非常頻繁，因此如果能夠對like子句使用索引，將很高的提高查詢的效率。      例6：select * from city where name like ‘%S%’      以上查詢的執行計劃用了全表掃描（TABLE ACCESS FULL），如果能夠修改為：      select * from city where name like ‘S%’      那么查詢的執行計劃將會變成（INDEX RANGE SCAN），成功的利用了name字段的索引。   這意味著Oracle SQL優化器會識別出用于索引的like子句，只要該查詢的匹配端是具體值。因此我們在做like查詢時，   應該盡量使查詢的匹配端是具體值，即使用like ‘S%’。

  (14)、用Case語句合并多重掃描   我們常常必須基于多組數據表計算不同的聚集。例如下例通過三個獨立查詢：      例8：1）select count(*) from emp where sal<1000;           2）select count(*) from emp where sal between 1000 and 5000;           3）select count(*) from emp where sal>5000;      這樣我們需要進行三次全表查詢，但是如果我們使用case語句：      select       count (sale when sal <1000      then 1 else null end)              count_poor,      count (sale when between 1000 and 5000      then 1 else null end)              count_blue_collar,      count (sale when sal >5000      then 1 else null end)              count_poor      from emp;        這樣查詢的結果一樣，但是執行計劃只進行了一次全表查詢。

  (15)、使用nls_date_format   例9：      select * from record where  to_char(ActionTime,'mm')='12'      這個查詢的執行計劃將是全表查詢，如果我們改變nls_date_format，      SQL>alert session set nls_date_formate=’MM’;      現在重新修改上面的查詢：      select * from record where  ActionTime='12'      這樣就能使用actiontime上的索引了，它的執行計劃將是（INDEX RANGE SCAN）。     (16)、使用基于函數的索引   前面談到任何對列的操作都可能導致全表掃描，例如：      select * from emp where substr(ename,1,2)=’SM’;      但是這種查詢在客服系統又經常使用，我們可以創建一個帶有substr函數的基于函數的索引，      create index emp_ename_substr on eemp ( substr(ename,1,2) );      這樣在執行上面的查詢語句時，這個基于函數的索引將排上用場，執行計劃將是（INDEX RANGE SCAN）。

  (17)、基于函數的索引要求等式匹配   上面的例子中，我們創建了基于函數的索引，但是如果執行下面的查詢：      select * from emp where substr(ename,1,1)=’S’      得到的執行計劃將還是（TABLE ACCESS FULL），因為只有當數據列能夠等式匹配時，基于函數的索引才能生效，   這樣對于這種索引的計劃和維護的要求都很高。請注意，向表中添加索引是非常危險的操作，因為這將導致許多查詢執行計劃的變更。   然而，如果我們使用基于函數的索引就不會產生這樣的問題，因為Oracle只有在查詢使用了匹配的內置函數時才會使用這種類型的索引。

  (18)、使用分區索引   在用分析命令對分區索引進行分析時，每一個分區的數據值的范圍信息會放入Oracle的數據字典中。   Oracle可以利用這個信息來提取出那些只與SQL查詢相關的數據分區。      例如，假設你已經定義了一個分區索引，并且某個SQL語句需要在一個索引分區中進行一次索引掃描。   Oracle會僅僅訪問這個索引分區，而且會在這個分區上調用一個此索引范圍的快速全掃描。因為不需要訪問整個索引，所以提高了查詢的速度。     (19)、使用位圖索引   位圖索引可以從本質上提高使用了小于1000個唯一數據值的數據列的查詢速度，因為在位圖索引中進行的檢索是在RAM中完成的，   而且也總是比傳統的B樹索引的速度要快。對于那些少于1000個唯一數據值的數據列建立位圖索引，可以使執行效率更快。     (20)、決定使用全表掃描還是使用索引   最后我們來討論一下是否需要建立索引，也許進行全表掃描更快。在大多數情況下，全表掃描可能會導致更多的物理磁盤輸入輸出，   但是全表掃描有時又可能會因為高度并行化的存在而執行的更快。如果查詢的表完全沒有順序，   那么一個要返回記錄數小于10％的查詢可能會讀取表中大部分的數據塊，這樣使用索引會使查詢效率提高很多。但是如果表非常有順序，   那么如果查詢的記錄數大于40％時，可能使用全表掃描更快。因此，有一個索引范圍掃描的總體原則是：      1)、對于原始排序的表  僅讀取少于表記錄數40％的查詢應該使用索引范圍掃描。    反之，讀取記錄數目多于表記錄數的40％的查詢應該使用全表掃描。      2)、對于未排序的表    僅讀取少于表記錄數7％的查詢應該使用索引范圍掃描。    反之，讀取記錄數目多于表記錄數的7％的查詢應該使用全表掃描。      3)、總結以上的招式，是完全可以相互結合同時運用的。而且各種方法之間相互影響，緊密聯系。這種聯系既存在一致性，   也可能帶來沖突，當沖突發生時，需要根據實際情況進行選擇，沒有固定的模式。   最后決定SQL優化功力的因素就是對ORACLE內功的掌握程度了。