優化資料庫大幅度提高Oracle的效能（轉）

幾個簡單的步驟大幅提高Oracle效能--我優化資料庫的三板斧。

資料庫優化的討論可以說是一個永恆的主題。資深的Oracle優化人員通常會要求提出效能問題的人對資料庫做一個statspack，貼出資料庫配置等等。還有的人認為要抓出執行最慢的語句來進行優化。但實際情況是，提出疑問的人很可能根本不懂執行計劃，更不要說statspack了。而我認為，資料庫優化，應該首先從大的方面考慮：網路、伺服器硬體配置、作業系統配置、Oracle伺服器配置、資料結構組織、然後才是具體的調整。實際上網路、硬體等往往無法決定更換，應用程式一般也無法修改，因此應該著重從資料庫配置、資料結構上來下手，首先讓資料庫有一個良好的配置，然後再考慮具體優化某些過慢的語句。我在給我的使用者系統進行優化的過程中，總結了一些基本的，簡單易行的辦法來優化資料庫，算是我的三板斧，呵呵。不過請注意，這些不一定普遍使用，甚至有的會有副作用，但是對OLTP系統、基於成本的資料庫往往行之有效，不妨試試。（注：附件是Burleson寫的用來報告資料庫效能等資訊的指令碼，本文用到）

一．設定合適的SGA

常常有人抱怨伺服器硬體很好，但是Oracle就是很慢。很可能是記憶體分配不合理造成的。(1)假設記憶體有512M，這通常是小型應用。建議Oracle的SGA大約240M，其中：共享池（SHARED_POOL_SIZE）可以設定60M到80M，根據實際的使用者數、查詢等來定。資料塊緩衝區可以大致分配120M-150M，8i下需要設定DB_BLOCK_BUFFERS，DB_BLOCK_BUFFER*DB_BLOCK_SIZE等於資料塊緩衝區大小。9i 下的資料緩衝區可以用db_cache_size來直接分配。

(2)假設記憶體有1G，Oracle 的SGA可以考慮分配500M：共享池分配100M到150M，資料緩衝區分配300M到400M。

(3)記憶體2G，SGA可以考慮分配1.2G，共享池300M到500M，剩下的給資料塊緩衝區。

(4)記憶體2G以上：共享池300M到500M就足夠啦，再多也沒有太大幫助；(Biti_rainy有專述)資料緩衝區是儘可能的大，但是一定要注意兩個問題：一是要給作業系統和其他應用留夠記憶體，二是對於32位的作業系統，Oracle的SGA有1.75G的限制。有的32位作業系統上可以突破這個限制，方法還請看Biti的大作吧。

二．分析表和索引，更改優化模式

Oracle預設優化模式是CHOOSE，在這種情況下，如果表沒有經過分析，經常導致查詢使用全表掃描，而不使用索引。這通常導致磁碟I/O太多，而導致查詢很慢。如果沒有使用執行計劃穩定性，則應該把表和索引都分析一下，這樣可能直接會使查詢速度大幅提升。分析表命令可以用ANALYZE TABLE 分析索引可以用ANALYZE INDEX命令。對於少於100萬的表，可以考慮分析整個表，對於很大的表，可以按百分比來分析，但是百分比不能過低，否則生成的統計資訊可能不準確。可以通過DBA_TABLES的LAST_ANALYZED列來檢視錶是否經過分析或分析時間，索引可以通過DBA_INDEXES的LAST_ANALYZED列。

下面通過例子來說明分析前後的速度對比。（表CASE_GA_AJZLZ大約有35萬資料，有主鍵）首先在SQLPLUS中開啟自動查詢執行計劃功能。(第一次要執行\RDBMS\ADMIN\utlxplan.sql來建立PLAN_TABLE這個表)

 

　　
　　SQL> SET AUTOTRACE ON
　　SQL>SET TIMING ON

　　
通過SET AUTOTRACE ON 來檢視語句的執行計劃，通過SET TIMING ON 來檢視語句執行時間。

 

　　
　　SQL> select count(*) from CASE_GA_AJZLZ;
　　COUNT(*)
　　----------
　　346639
　　
　　已用時間: 00: 00: 21.38
　　
　　Execution Plan
　　　　0 SELECT STATEMENT ptimizer=CHOOSE
　　1 0 SORT (AGGREGATE)
　　2 1 TABLE ACCESS (FULL) OF 'CASE_GA_AJZLZ'
　　……………………

　　
請注意上面分析中的TABLE ACCESS(FULL)，這說明該語句執行了全表掃描。而且查詢使用了21.38秒。這時表還沒有經過分析。下面我們來對該表進行分析：

 

　　
　　SQL> analyze table CASE_GA_AJZLZ compute statistics;

　　
表已分析。已用時間: 00: 05: 357.63。然後再來查詢：

 

　　
　　SQL> select count(*) from CASE_GA_AJZLZ;
　　COUNT(*)
　　----------
　　346639
　　
　　已用時間: 00: 00: 00.71
　　
　　Execution Plan
　
　　0 SELECT STATEMENT ptimizer=FIRST_ROWS (Cost=351 Card=1)
　　1 0 SORT (AGGREGATE)
　　2 1 INDEX (FAST FULL SCAN) OF 'PK_AJZLZ' (UNIQUE) (Cost=351
　　Card=346351)
　　…………………………

　　
請注意，這次時間僅僅用了0.71秒！這要歸功於INDEX(FAST FULL SCAN)。通過分析表，查詢使用了PK_AJZLZ索引，磁碟I/O大幅減少，速度也大幅提升！下面的實用語句可以用來生成分析某個使用者的所有表和索引，假設使用者是GAXZUSR：

 

　　
　　SQL> set pagesize 0
　　SQL> spool d:\analyze_tables.sql;
　　SQL> select 'analyze table '||owner||'.'||table_name||'
compute statistics;' from dba_tables where wner='GAXZUSR';
　　SQL> spool off
　　SQL> spool spool d:\analyze_indexes.sql;
　　SQL> select 'analyze index '||owner||'.'||index_name||'
compute statistics;' from dba_indexes where wner='GAXZUSR';
　　SQL> spool off
　　SQL> @d:\analyze_tables.sql
　　SQL> @d:\analyze_indexes.sql

　　
解釋：上面的語句生成了兩個sql檔案，分別分析全部的GAXZUSR的表和索引。如果需要按照百分比來分析表，可以修改一下指令碼。通過上面的步驟，我們就完成了對錶和索引的分析，可以測試一下速度的改進啦。建議定期執行上面的語句，尤其是資料經過大量更新。

當然，也可以通過dbms_stats來分析表和索引，更方便一些。但是我仍然習慣上面的方法，因為成功與否會直接提示出來。

 
 

另外，我們可以將優化模式進行修改。optimizer_mode值可以是RULE、CHOOSE、FIRST_ROWS和ALL_ROWS。對於OLTP系統，可以改成FIRST_ROWS，來要求查詢儘快返回結果。這樣即使不用分析，在一般情況下也可以提高查詢效能。但是表和索引經過分析後有助於找到最合適的執行計劃。

三．設定cursor_sharing=FORCE 或SIMILAR

這種方法是8i才開始有的，oracle805不支援。通過設定該引數，可以強制共享只有文字不同的語句解釋計劃。例如下面兩條語句可以共享：

 

　　
　　SQL> SELECT * FROM MYTABLE WHERE NAME='tom'
　　SQL> SELECT * FROM MYTABLE WHERE NAME='turner'

　　
這個方法可以大幅降低緩衝區利用率低的問題，避免語句重新解釋。通過這個功能，可以很大程度上解決硬解析帶來的效能下降的問題。個人感覺可根據系統的實際情況，決定是否將該引數改成FORCE。該引數預設是exact。不過一定要注意，修改之前，必須先給ORACLE打補丁，否則改之後oracle會佔用100%的CPU,無法使用。對於ORACLE9i，可以設定成SIMILAR，這個設定綜合了FORCE和EXACT的優點。不過請慎用這個功能，這個引數也可能帶來很大的負面影響！

四．將常用的小表、索引釘在資料快取KEEP池中

記憶體上資料讀取速度遠遠比硬碟中讀取要快，據稱，記憶體中資料讀的速度是硬碟的14000倍！如果資源比較豐富，把常用的小的、而且經常進行全表掃描的表給釘記憶體中，當然是在好不過了。可以簡單的通過ALTER TABLE tablename CACHE來實現，在ORACLE8i之後可以使用ALTER TABLE table STORAGE(BUFFER_POOL KEEP)。一般來說，可以考慮把200資料塊之內的表放在keep池中，當然要根據記憶體大小等因素來定。關於如何查出那些表或索引符合條件，可以使用本文提供的access.sql和access_report.sql。這兩個指令碼是著名的Oracle專家 Burleson寫的，你也可以在讀懂了情況下根據實際情況調整一下指令碼。對於索引，可以通過ALTER INDEX indexname STORAGE(BUFFER_POOL KEEP)來釘在KEEP池中。

將表定在KEEP池中需要做一些準備工作。對於ORACLE9i 需要設定DB_KEEP_CACHE_SIZE，對於8i，需要設定buffer_pool_keep。在8i中，還要修改db_block_lru_latches，該引數預設是1，無法使用buffer_pool_keep。該引數應該比2*3*CPU數量少，但是要大於1，才能設定DB_KEEP_CACHE_BUFFER。buffer_pool_keep從db_block_buffers中分配，因此也要小於db_block_buffers。設定好這些引數後，就可以把常用物件永久釘在記憶體裡。

五．設定optimizer_max_permutations

對於多表連線查詢，如果採用基於成本優化(CBO)，ORACLE會計算出很多種執行方案，從中選擇出最優方案。這個引數就是設定oracle究竟從多少種方案來選擇最優。如果設定太大，那麼計算最優方案過程也是時間比較長的。Oracle805和8i預設是80000，8建議改成2000。對於9i，已經預設是2000了。

六．調整排序引數

(1) SORT_AREA_SIZE:預設的用來排序的SORT_AREA_SIZE大小是32K，通常顯得有點小，一般可以考慮設定成1M（1048576）。這個引數不能設定過大，因為每個連線都要分配同樣的排序記憶體。

(2) SORT_MULTIBLOCK_READ_COUNT:增大這個引數可以提高臨時表空間排序效能，該引數預設是2，可以改成32來對比一下排序查詢時間變化。注意，這個引數的最大值與平臺有關係。

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/wenbingcai/archive/2007/10/15/1826161.aspx

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7656893/viewspace-661550/，如需轉載，請註明出處，否則將追究法律責任。