夢幻之旅

數(shù)據(jù)庫優(yōu)化，應(yīng)該首先從大的方面考慮：網(wǎng)絡(luò)、服務(wù)器硬件配置、操作系統(tǒng)配置、Oracle服務(wù)器配置、數(shù)據(jù)結(jié)構(gòu)組織、然后才是具體的調(diào)整。實際上網(wǎng)絡(luò)、硬件等往往無法決定更換，應(yīng)用程序一般也無法修改，因此應(yīng)該著重從數(shù)據(jù)庫配置、數(shù)據(jù)結(jié)構(gòu)上來下手，首先讓數(shù)據(jù)庫有一個良好的配置，然后再考慮具體優(yōu)化某些過慢的語句。我在給我的用戶系統(tǒng)進行優(yōu)化的過程中，總結(jié)了一些基本的，簡單易行的辦法來優(yōu)化數(shù)據(jù)庫的方法.　　
　　一．設(shè)置合適的SGA
　　
　　常常有人抱怨服務(wù)器硬件很好，但是Oracle就是很慢。很可能是內(nèi)存分配不合理造成的。
　　
　　(1)假設(shè)內(nèi)存有512M，這通常是小型應(yīng)用。建議Oracle的SGA大約240M，其中：共享池（SHARED_POOL_SIZE）可以設(shè)置 60M到80M，根據(jù)實際的用戶數(shù)、查詢等來定。數(shù)據(jù)塊緩沖區(qū)可以大致分配120M-150M，8i下需要設(shè)置 DB_BLOCK_BUFFERS，DB_BLOCK_BUFFER*DB_BLOCK_SIZE等于數(shù)據(jù)塊緩沖區(qū)大小。9i 下的數(shù)據(jù)緩沖區(qū)可以用db_cache_size來直接分配。
　　
　　(2)假設(shè)內(nèi)存有1G，Oracle 的SGA可以考慮分配500M：共享池分配100M到150M，數(shù)據(jù)緩沖區(qū)分配300M到400M。
　　
　　(3)內(nèi)存2G，SGA可以考慮分配1.2G，共享池300M到500M，剩下的給數(shù)據(jù)塊緩沖區(qū)。
　　
　　(4)內(nèi)存2G以上：共享池300M到500M就足夠啦，再多也沒有太大幫助；(Biti_rainy有專述)數(shù)據(jù)緩沖區(qū)是盡可能的大，但是一定要注意兩個問題：一是要給操作系統(tǒng)和其他應(yīng)用留夠內(nèi)存，二是對于32位的操作系統(tǒng)，Oracle的SGA有1.75G的限制。有的32位操作系統(tǒng)上可以突破這個限制，方法還請看Biti的大作吧。
　　
　　二．分析表和索引，更改優(yōu)化模式
　　
　　Oracle默認(rèn)優(yōu)化模式是CHOOSE，在這種情況下，如果表沒有經(jīng)過分析，經(jīng)常導(dǎo)致查詢使用全表掃描，而不使用索引。這通常導(dǎo)致磁盤I/O太多，而導(dǎo)致查詢很慢。如果沒有使用執(zhí)行計劃穩(wěn)定性，則應(yīng)該把表和索引都分析一下，這樣可能直接會使查詢速度大幅提升。分析表命令可以用ANALYZE TABLE 分析索引可以用ANALYZE INDEX命令。對于少于100萬的表，可以考慮分析整個表，對于很大的表，可以按百分比來分析，但是百分比不能過低，否則生成的統(tǒng)計信息可能不準(zhǔn)確。可以通過DBA_TABLES的LAST_ANALYZED列來查看表是否經(jīng)過分析或分析時間，索引可以通過DBA_INDEXES的 LAST_ANALYZED列。
　　
　　下面通過例子來說明分析前后的速度對比。（表CASE_GA_AJZLZ大約有35萬數(shù)據(jù)，有主鍵）首先在SQLPLUS中打開自動查詢執(zhí)行計劃功能。(第一次要執(zhí)行\(zhòng)RDBMS\ADMIN\utlxplan.sql來創(chuàng)建PLAN_TABLE這個表)
　　SQL> SET AUTOTRACE ON
　　SQL>SET TIMING ON
　　
　　通過SET AUTOTRACE ON 來查看語句的執(zhí)行計劃，通過SET TIMING ON 來查看語句運行時間。
　　SQL> select count(*) from CASE_GA_AJZLZ;
　　COUNT(*)
　　----------
　　346639
　　已用時間: 00: 00: 21.38
　　Execution Plan
　　----------------------------------------------------------
　　0 SELECT STATEMENT Optimizer=CHOOSE
　　1 0 SORT (AGGREGATE)
　　2 1 TABLE ACCESS (FULL) OF 'CASE_GA_AJZLZ'
　　……………………
　　請注意上面分析中的TABLE ACCESS(FULL)，這說明該語句執(zhí)行了全表掃描。而且查詢使用了21.38秒。這時表還沒有經(jīng)過分析。下面我們來對該表進行分析：
　　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 Optimizer=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大幅減少，速度也大幅提升！下面的實用語句可以用來生成分析某個用戶的所有表和索引，假設(shè)用戶是GAXZUSR：
　　SQL> set pagesize 0
　　SQL> spool d:\analyze_tables.sql;
　　SQL> select 'analyze table '||owner||'.'||table_name||' compute statistics;' from dba_tables where owner='GAXZUSR';
　　SQL> spool off
　　SQL> spool spool d:\analyze_indexes.sql;
　　SQL> select 'analyze index '||owner||'.'||index_name||' compute statistics;' from dba_indexes where owner='GAXZUSR';
　　SQL> spool off
　　SQL> @d:\analyze_tables.sql
　　SQL> @d:\analyze_indexes.sql
　　
　　解釋：上面的語句生成了兩個sql文件，分別分析全部的GAXZUSR的表和索引。如果需要按照百分比來分析表，可以修改一下腳本。通過上面的步驟，我們就完成了對表和索引的分析，可以測試一下速度的改進啦。建議定期運行上面的語句，尤其是數(shù)據(jù)經(jīng)過大量更新。
　　
　　當(dāng)然，也可以通過dbms_stats來分析表和索引，更方便一些。但是我仍然習(xí)慣上面的方法，因為成功與否會直接提示出來。
　　
　　另外，我們可以將優(yōu)化模式進行修改。optimizer_mode值可以是RULE、CHOOSE、FIRST_ROWS和ALL_ROWS。對于 OLTP系統(tǒng)，可以改成FIRST_ROWS，來要求查詢盡快返回結(jié)果。這樣即使不用分析，在一般情況下也可以提高查詢性能。但是表和索引經(jīng)過分析后有助于找到最合適的執(zhí)行計劃。
　　
　　三．設(shè)置cursor_sharing=FORCE 或SIMILAR
　　
　　這種方法是8i才開始有的，oracle805不支持。通過設(shè)置該參數(shù)，可以強制共享只有文字不同的語句解釋計劃。例如下面兩條語句可以共享：
　　
　　SQL> SELECT * FROM MYTABLE WHERE NAME='tom'
　　
　　SQL> SELECT * FROM MYTABLE WHERE NAME='turner'
　　
　　這個方法可以大幅降低緩沖區(qū)利用率低的問題，避免語句重新解釋。通過這個功能，可以很大程度上解決硬解析帶來的性能下降的問題。個人感覺可根據(jù)系統(tǒng)的實際情況，決定是否將該參數(shù)改成FORCE。該參數(shù)默認(rèn)是exact。不過一定要注意，修改之前，必須先給ORACLE打補丁，否則改之后oracle會占用100%的CPU,無法使用。對于ORACLE9i，可以設(shè)置成SIMILAR，這個設(shè)置綜合了FORCE和EXACT的優(yōu)點。不過請慎用這個功能，這個參數(shù)也可能帶來很大的負(fù)面影響！
　　
　　四．將常用的小表、索引釘在數(shù)據(jù)緩存KEEP池中
　　
　　內(nèi)存上數(shù)據(jù)讀取速度遠(yuǎn)遠(yuǎn)比硬盤中讀取要快，據(jù)稱，內(nèi)存中數(shù)據(jù)讀的速度是硬盤的14000倍！如果資源比較豐富，把常用的小的、而且經(jīng)常進行全表掃描的表給釘內(nèi)存中，當(dāng)然是在好不過了。可以簡單的通過ALTER TABLE tablename CACHE來實現(xiàn)，在ORACLE8i之后可以使用ALTER TABLE table STORAGE(BUFFER_POOL KEEP)。一般來說，可以考慮把200數(shù)據(jù)塊之內(nèi)的表放在keep池中，當(dāng)然要根據(jù)內(nèi)存大小等因素來定。關(guān)于如何查出那些表或索引符合條件，可以使用本文提供的access.sql和access_report.sql。這兩個腳本是著名的Oracle專家 Burleson寫的，你也可以在讀懂了情況下根據(jù)實際情況調(diào)整一下腳本。對于索引，可以通過ALTER INDEX indexname STORAGE(BUFFER_POOL KEEP)來釘在KEEP池中。
　　
　　將表定在KEEP池中需要做一些準(zhǔn)備工作。對于 ORACLE9i 需要設(shè)置DB_KEEP_CACHE_SIZE，對于8i，需要設(shè)置buffer_pool_keep。在8i中，還要修改 db_block_lru_latches，該參數(shù)默認(rèn)是1，無法使用buffer_pool_keep。該參數(shù)應(yīng)該比2*3*CPU數(shù)量少，但是要大于 1，才能設(shè)置DB_KEEP_CACHE_BUFFER。buffer_pool_keep從db_block_buffers中分配，因此也要小于 db_block_buffers。設(shè)置好這些參數(shù)后，就可以把常用對象永久釘在內(nèi)存里。
　　
　　五．設(shè)置optimizer_max_permutations
　　
　　對于多表連接查詢，如果采用基于成本優(yōu)化(CBO)，ORACLE會計算出很多種運行方案，從中選擇出最優(yōu)方案。這個參數(shù)就是設(shè)置oracle究竟從多少種方案來選擇最優(yōu)。如果設(shè)置太大，那么計算最優(yōu)方案過程也是時間比較長的。Oracle805和8i默認(rèn)是80000，8建議改成2000。對于9i，已經(jīng)默認(rèn)是2000了。
　　
　　六．調(diào)整排序參數(shù)
　　
　　(1) SORT_AREA_SIZE:默認(rèn)的用來排序的SORT_AREA_SIZE大小是32K，通常顯得有點小，一般可以考慮設(shè)置成1M（1048576）。這個參數(shù)不能設(shè)置過大，因為每個連接都要分配同樣的排序內(nèi)存。
　　
　　(2) SORT_MULTIBLOCK_READ_COUNT:增大這個參數(shù)可以提高臨時表空間排序性能，該參數(shù)默認(rèn)是2，可以改成32來對比一下排序查詢時間變化。注意，這個參數(shù)的最大值與平臺有關(guān)系。
　　
　　七．調(diào)整其它幾個關(guān)鍵的性能參數(shù)
　　
　　很多人認(rèn)為使用oracle數(shù)據(jù)庫，系統(tǒng)的默認(rèn)參數(shù)就是最好的，其實不是這樣，很多參數(shù)都需要調(diào)整，而且調(diào)整前后性能大不一樣。
　　
　　(1) log_buffer
　　
　　日志緩沖區(qū)大小默認(rèn)設(shè)置32k太小了，建議設(shè)置成512K或者1M。
　　log_buffer=524288
　　
　　(2) optimizer_index_caching
　　
　　這個參數(shù)可以設(shè)置索引的緩沖度，范圍是0到100，默認(rèn)是0，可以考慮設(shè)置成90
　　
　　(3) optimizer_index_cost_adj
　　
　　這個參數(shù)是一個百分比，表明索引掃描與全表掃描的代價范圍是1到1000。默認(rèn)=100表名索引掃描與全表掃描代價一樣。將這個參數(shù)設(shè)小表名索引代價要小于全表掃描，這樣就使得使用CBO進行成本計算時更傾向于使用索引掃描。建議把這個參數(shù)設(shè)置成30到50。
　　
　　八．改變聯(lián)機日志文件大小(一般用于oracle805)
　　
　　Oracle805的聯(lián)機日志文件默認(rèn)只有1M大小，這實在是太小了，通過查看數(shù)據(jù)庫的日志，很可能發(fā)現(xiàn)“checkpoint not complete”之類的錯誤提示。這會導(dǎo)致系統(tǒng)穩(wěn)定性，同樣也降低了數(shù)據(jù)庫性能。建議修改成10M。修改方法是刪除一個組、添加一個組，直到3個組都換成新的大小。說明：這個操作需要實施人員具有較多的數(shù)據(jù)庫知識，如果不太了解，最好不要試驗。
　　
　　九．改變數(shù)據(jù)塊大小(一般用于oracle805)
　　
　　Oracle805默認(rèn)的塊(DB_BLOCK_SIZE)大小是2K，太小了，因為塊小，所以請求同樣的數(shù)據(jù)量的時候，讀的次數(shù)就要增多，導(dǎo)致性能低下。當(dāng)然如果服務(wù)器性能比較好，還是升級Oracle更好，如果服務(wù)器配置比較差，建議改成8K。但是數(shù)據(jù)塊不能直接修改，唯一的辦法就是將數(shù)據(jù)導(dǎo)出，重新創(chuàng)建數(shù)據(jù)庫，然后將數(shù)據(jù)導(dǎo)入。說明：這個操作需要實施人員具有較多的數(shù)據(jù)庫知識，如果不太了解，最好不要試驗。
　　
　　十．設(shè)置合適的表存儲參數(shù)
　　
　　對于有很多并發(fā)寫入用戶的系統(tǒng)來說，如果系統(tǒng)沒有經(jīng)過調(diào)整，經(jīng)常會有數(shù)據(jù)等待現(xiàn)象。這是因為9i之前的表設(shè)置的默認(rèn)的自由隊列freelists為1，這樣就可能造成數(shù)據(jù)等待。通過查看v$waitstat，如果發(fā)現(xiàn)data block 或者free list類的count次數(shù)很大，則說明等待情況嚴(yán)重，需要增加freelists。這個參數(shù)在8i、9i中可以動態(tài)修改（需要打補丁，否則會有ORA- 10620: Operation not allowed on this segment）在ORACLE805中，只能通過重新創(chuàng)建表來修改。
　　SQL> select * from v$waitstat;
　　CLASS COUNT TIME
　　------------------ ---------- ----------
　　data block 11922013 342456
　　sort block 0 0
　　save undo block 0 0
　　segment header 1 0
　　free list 0 0
　　
　　如果測算經(jīng)常有10個并發(fā)的寫用戶，可以把表的freelists改成10。例如下面的腳本可以把GAXZUSR用戶的所有表重新設(shè)置FREELISTS的語句寫在D:\FREELISTS.SQL里：
　　SQL> SET PAGESIZE 0
　　SQL> SPOOL D:\FREELISTS.SQL
　　SQL> SELECT 'ALTER TABLE '||TABLE_NAME||' STORAGE(FREELISTS 10);' FROM DBA_TABLES WHERE OWNER=’GAXZUSR’;
　　SQL>SPOOL OFF
　　
　　檢查D:\FREELISTS.SQL,沒有錯誤后運行修改FREELISTS：
　　SQL>@D:\FREELISTS.SQL
　　
　　十一．重新組織表結(jié)構(gòu)
　　
　　(1) 按照主鍵重新排序。
　　
　　數(shù)據(jù)庫運行了一段時間后，可能會有很多數(shù)據(jù)，而這些數(shù)據(jù)又可能是經(jīng)常按照某個字段來選取區(qū)段數(shù)據(jù)。如果我們能夠把主鍵按照順序重新來組織一下表，那么用主鍵進行的查詢就會明顯快很多，主要是因為經(jīng)過排序后，相似的編號都放在同一個數(shù)據(jù)塊里，ORACLE在進行主鍵范圍查找的時候，就會大大減少物理塊度讀取數(shù)量。在對表和索引分析之后，可以通過DBA_INDEXES的CLUSTERING_FACTOR列來判斷表是否需要重新排序。如果該字段的值與表的 BLOCK數(shù)量差不多，則不需要重新排序，如果和表的行數(shù)差不多，則應(yīng)該考慮重新組織一下了。下面的例子示意性說明怎樣對表CASE_GA_AJZLZ按照主鍵PK_AJZLZ進行重新排序：
　　
　　<1> 將表CASE_GA_AJZLZ的索引、外間約束引用等找出來備用。
　　SET PAGESIZE 0
　　SET LINESIZE 300
　　SPOOL DISABLE_CONSTRAINTS.SQL
　　SELECT 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||CONSTRAINT_NAME||';' FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='R' AND R_CONSTRAINT_NAME='PK_AJZLZ';
　　SPOOL OFF
　　SPOOL CREATE_CONSTRAINTS.SQL
　　SELECT 'ALTER TABLE '||TABLE_NAME||' ADD CONSTRAINT '||CONSTRAINT_NAME||' FOREIGN KEY(CASEID) REFERENCES CASE_GA_AJZLZ(CASEID);' FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='R' AND R_CONSTRAINT_NAME='PK_AJZLZ';
　　SPOOL OFF
　　SPOOL CREATE_INDEX.SQL
　　SELECT 'CREATE INDEX '||INDEX_NAME||' ON '||TABLE_NAME||'('||COLUMN_NAME||') TABLESPACE INDX ;' FROM USER_IND_COLUMNS WHERE TABLE_NAME='CASE_GA_AJZLZ' AND INDEX_NAME<>'PK_AJZLZ';
　　
　　<2> 創(chuàng)建新的表CASE_GA_AJZLZ_NEW：
　　SQL> CREATE TABLE CASE_GA_AJZLZ_NEW AS SELECT
　　/*+INDEX(CASE_GA_AJZLZ PK_AJZLZ) */ * FROM CASE_GA_AJZLZ ;
　　
　　注意，上面的注釋（紅顏色部分）表名按照PK_AJZLZ排序來重新組織表。
　　
　　<3>禁用CASE_GA_AJZLZ的外間約束，將表CASE_GA_AJZLZ TRUNCATE，然后刪除之
　　
　　<4> 將表CASE_GA_AJZLZ_NEW更名為CASE_GA_AJZLZ
　　
　　SQL> ALTER TABLE CASE_GA_AJZLZ_NEW RENAME TO CASE_GA_AJZLZ;
　　
　　<5>創(chuàng)建CASE_GA_AJZLZ的所有索引、主鍵約束等。
　　
　　SQL> ALTER TABLE CASE_GA_AJZLZ ADD CONSTRAINT PK_AJZLZ PRIMARY KEY(CASEID);
　　SQL> @CREATE_INDEX.SQL
　　SQL> @CREATE_CONSTRAINTS.SQL
　　
　　(2) 將BLOB字段存儲到單獨的表空間中。
　　
　　基本上每個業(yè)務(wù)系統(tǒng)都有很多BLOB字段，而且很可能占據(jù)了整個數(shù)據(jù)庫大小的大部分。默認(rèn)情況下，BLOB字段會將4000個字節(jié)的指針與表的行存在一起，這直接會導(dǎo)致行遷移。而且BLOB字段會與表處于同一個表空間，這也對性能有不小的影響。從設(shè)計角度來說，BLOB字段都應(yīng)該單獨存儲，遺憾的是我所遇到的很多系統(tǒng)都沒有單獨存儲BLOB字段。如果BLOB字段占據(jù)了很大的存儲，那么將BLOB字段單獨存儲后，帶來的整體性能收益可能會非常的大。 另外BLOB字段存儲子句中有一個DISABLE STORAGE IN ROW 屬性，在將BLOB字段單獨存放時，也應(yīng)該實用該屬性，這樣可以有效避免行遷移。