優(yōu)化數(shù)據(jù)庫以大幅度提高Oracle的性能

優(yōu)化數(shù)據(jù)庫大幅度提高Oracle的性能收藏Q幾個簡單的步驟大幅提高Oracle性能--我優(yōu)化數(shù)據(jù)庫的三板斧。數(shù)據(jù)庫優(yōu)化的討論可以說是一個永恒的主題。資深的Oracle優(yōu)化人員通常會要求提出性能問題的人對數(shù)據(jù)庫做一個statspack，貼出數(shù)據(jù)庫配置等等。還有的人認為要抓出執(zhí)行最慢的語句來進行優(yōu)化。但實際情況是，提出疑問的人很可能根本不懂執(zhí)行計劃，更不要說statspack了。而我認為，數(shù)據(jù)庫優(yōu)化，應該首先從大的方面考慮：網(wǎng)絡、服務器硬件配置、操作系統(tǒng)配置、Oracle服務器配置、數(shù)據(jù)結構組織、然后才是具體的調(diào)整。實際上網(wǎng)絡、硬件等往往無法決定更換，應用程序一般也無法修改，因此應該著重從數(shù)據(jù)庫配置、數(shù)據(jù)結構上來下手，首先讓數(shù)據(jù)庫有一個良好的配置，然后再考慮具體優(yōu)化某些過慢的語句。我在給我的用戶系統(tǒng)進行優(yōu)化的過程中，總結了一些基本的，簡單易行的辦法來優(yōu)化數(shù)據(jù)庫，算是我的三板斧，呵呵。不過請注意，這些不一定普遍使用，甚至有的會有副作用，但是對OLTP系統(tǒng)、基于成本的數(shù)據(jù)庫往往行之有效，不妨試試。(注：附件是Burleson寫的用來報告數(shù)據(jù)庫性能等信息的腳本，本文用到)設置合適的SGA常常有人抱怨服務器硬件很好，但是Oracle就是很慢。很可能是內(nèi)存分配不合理造成的。(1)假設內(nèi)存有512M，這通常是小型應用。建議Oracle的SGA大約240M，其中：共享池(SHARED_POOL_SIZE)可以設置60M到80M，根據(jù)實際的用戶數(shù)、查詢等來定。數(shù)據(jù)塊緩沖區(qū)可以大致分配120M-150M，8i下需要設置DB_BLOCK_BUFFERS，DB_BLOCK_BUFFER*DB_BLOCK_SIZE等于數(shù)據(jù)塊緩沖區(qū)大小。9i下的數(shù)據(jù)緩沖區(qū)可以用db_cache_size來直接分配。假設內(nèi)存有1G，Oracle的SGA可以考慮分配500M:共享池分配100M到150M，數(shù)據(jù)緩沖區(qū)分配300M到400M。內(nèi)存2G，SGA可以考慮分配1.2G，共享池300M到500M，剩下的給數(shù)據(jù)塊緩沖區(qū)。內(nèi)存2G以上：共享池300M到500M就足夠啦，再多也沒有太大幫助；(Biti_rainy有專述)數(shù)據(jù)緩沖區(qū)是盡可能的大，但是一定要注意兩個問題：一是要給操作系統(tǒng)和其他應用留夠內(nèi)存，二是對于32位的操作系統(tǒng)，Oracle的SGA有1.75G的限制。有的32位操作系統(tǒng)上可以突破這個限制，方法還請看Biti的大作吧。分析表和索引，更改優(yōu)化模式Oracle默認優(yōu)化模式是CHOOSE，在這種情況下，如果表沒有經(jīng)過分析，經(jīng)常導致查詢使用全表掃描，而不使用索引。這通常導致磁盤I/O太多，而導致查詢很慢。如果沒有使用執(zhí)行計劃穩(wěn)定性，則應該把表和索引都分析一下，這樣可能直接會使查詢速度大幅提升。分析表命令可以用ANALYZETABLE分析索引可以用ANALYZEINDEX命令。對于少于100萬的表，可以考慮分析整個表，對于很大的表，可以按百分比來分析，但是百分比不能過低，否則生成的統(tǒng)計信息可能不準確?？梢酝ㄟ^DBA_TABLES的LAST_ANALYZED列來查看表是否經(jīng)過分析或分析時間，索引可以通過DBA_INDEXES的LAST_ANALYZED歹U。下面通過例子來說明分析前后的速度對比。（表CASE_GA_AJZLZ大約有35萬數(shù)據(jù)，有主鍵）首先在SQLPLUS中打開自動查詢執(zhí)行計劃功能。（第一次要執(zhí)行\(zhòng)RDBMS\ADMIN\utlxplan.sql來創(chuàng)建PLAN_TABLE這個表）SQL>SETAUTOTRACEONSQL>SETTIMINGON通過SETAUTOTRACEON來查看語句的執(zhí)行計劃，通過SETTIMINGON來查看語句運行時間。SQL>selectcount(*)fromCASE_GA_AJZLZ;COUNT(*)346639已用時間：00:00:21.38ExecutionPlan0SELECTSTATEMENTOptimizer二CHOOSE10SORT(AGGREGATE)21TABLEACCESS(FULL)OF'CASE_GA_AJZLZ請注意上面分析中的TABLEACCESS(FULL)，這說明該語句執(zhí)行了全表掃描。而且查詢使用了21.38秒。這時表還沒有經(jīng)過分析。下面我們來對該表進行分析:SQL>analyzetableCASE_GA_AJZLZcomputestatistics;表已分析。已用時間：00:05:357.63。然后再來查詢:SQL>selectcount(*)fromCASE_GA_AJZLZ;COUNT(*)346639已用時間：00:00:00.71ExecutionPlan0SELECTSTATEMENTOptimizer二FIRST_ROWS

(Cost=351Card=1)10SORT(AGGREGATE)21INDEX(FASTFULLSCAN)OF'PK_AJZLZ'(UNIQUE)

(Cost=351

Card=346351)請注意，這次時間僅僅用了0.71秒！這要歸功于INDEX(FASTFULLSCAN)。通過分析表，查詢使用了PK_AJZLZ索引，磁盤I/O大幅減少，速度也大幅提升！下面的實用語句可以用來生成分析某個用戶的所有表和索引，假設用戶是GAXZUSR:SQL>setpagesize0SQL>spoold:\analyze_tables.sql;SQL>select'analyzetable'||owner||'.'||table_name||'

computestatistics;'fromdba_tableswhereowner='GAXZUSR';SQL>spooloffSQL>spoolspoold:\analyze_indexes.sql;SQL>select'analyzeindex'||owner||'.'||index_name||'

computestatistics;'fromdba_indexeswhere

owner='GAXZUSR';SQL>spooloffSQL>@d:\analyze_tables.sqlSQL>@d:\analyze_indexes.sql解釋：上面的語句生成了兩個sql文件，分別分析全部的GAXZUSR的表和索引。如果需要按照百分比來分析表，可以修改一下腳本。通過上面的步驟，我們就完成了對表和索引的分析，可以測試一下速度的改進啦。建議定期運行上面的語句，尤其是數(shù)據(jù)經(jīng)過大量更新。當然，也可以通過dbms_stats來分析表和索引，更方便一些。但是我仍然習慣上面的方法，因為成功與否會直接提示出來。另外，我們可以將優(yōu)化模式進行修改。optimizer_mode值可以是RULE、CHOOSE、FIRST_ROWS和ALL_ROWS。對于OLTP系統(tǒng)，可以改成FIRST_ROWS,來要求查詢盡快返回結果。這樣即使不用分析，在一般情況下也可以提高查詢性能。但是表和索引經(jīng)過分析后有助于找到最合適的執(zhí)行計劃。三.設置cursor_sharing=FORCE或SIMILAR這種方法是8i才開始有的，oracle805不支持。通過設置該參數(shù)，可以強制共享只有文字不同的語句解釋計劃。例如下面兩條語句可以共享：SQL>SELECT*FROMMYTABLEWHERENAME='tom'SQL>SELECT*FROMMYTABLEWHERENAME='turner這個方法可以大幅降低緩沖區(qū)利用率低的問題，避免語句重新解釋。通過這個功能，可以很大程度上解決硬解析帶來的性能下降的問題。個人感覺可根據(jù)系統(tǒng)的實際情況，決定是否將該參數(shù)改成FORCE。該參數(shù)默認是exact。不過一定要注意，修改之前，必須先給ORACLE打補丁，否則改之后oracle會占用100%的CPU,無法使用。對于ORACLE9i，可以設置成SIMILAR，這個設置綜合了FORCE和EXACT的優(yōu)點。不過請慎用這個功能，這個參數(shù)也可能帶來很大的負面影響！四.將常用的小表、索引釘在數(shù)據(jù)緩存KEEP池中內(nèi)存上數(shù)據(jù)讀取速度遠遠比硬盤中讀取要快，據(jù)稱，內(nèi)存中數(shù)據(jù)讀的速度是硬盤的14000倍！如果資源比較豐富，把常用的小的、而且經(jīng)常進行全表掃描的表給釘內(nèi)存中，當然是在好不過了?？梢院唵蔚耐ㄟ^ALTERTABLEtablenameCACHE來實現(xiàn)，在ORACLE8i之后可以使用ALTERTABLEtableSTORAGE(BUFFER_POOLKEEP)。一般來說，可以考慮把200數(shù)據(jù)塊之內(nèi)的表放在keep池中，當然要根據(jù)內(nèi)存大小等因素來定。關于如何查出那些表或索引符合條件，可以使用本文提供的access.sql和access_report.sql。這兩個腳本是著名的Oracle專家Burleson寫的，你也可以在讀懂了情況下根據(jù)實際情況調(diào)整一下腳本。對于索引，可以通過ALTERINDEXindexnameSTORAGE(BUFFER_POOLKEEP)來釘在KEEP池中。將表定在KEEP池中需要做一些準備工作。對于ORACLE9i需要設置DB_KEEP_CACHE_SIZE，對于8i，需要設置buffer_pool_keep。在8i中，還要修改db_block_lru_latches，該參數(shù)默認是1，無法使用buffer_pool_keep。該參數(shù)應該比2*3*CPU數(shù)量少，但是要大于1，才能設置DB_KEEP_CACHE_BUFFER。buffer_pool_keep從db_block_buffers中分配，因此也要小于db_block_buffers。設置好這些參數(shù)后，就可以把常用對象永久釘在內(nèi)存里。五.設置optimizer_max_permutations對于多表連接查詢，如果采用基于成本優(yōu)化(CBO),ORACLE會計算出很多種運行方案，從中選擇出最優(yōu)方案。這個參數(shù)就是設置oracle究竟從多少種方案來選擇最優(yōu)。如果設置太大，那么計算最優(yōu)方案過程也是時間比較長的。Oracle805和8i默認是80000,8建議改成2