優化數據庫的三板斧 大幅提高Oracle性能

　　幾個簡單的步驟大幅提高Oracle性能我優化數據庫的三板斧
　　
　　數據庫優化的討論可以說是一個永恆的主題資深的Oracle優化人員通常會要求提出性能問題的人對數據庫做一個statspack貼出數據庫配置等等還有的人認為要抓出執行最慢的語句來進行優化但實際情況是提出疑問的人很可能根本不懂執行計劃更不要說statspack了而我認為數據庫優化應該首先從大的方面考慮網絡服務器硬件配置操作系統配置Oracle服務器配置數據結構組織然後才是具體的調整實際上網絡硬件等往往無法決定更換應用程序一般也無法修改因此應該著重從數據庫配置數據結構上來下手首先讓數據庫有一個良好的配置然後再考慮具體優化某些過慢的語句我在給我的用戶系統進行優化的過程中總結了一些基本的簡單易行的辦法來優化數據庫算是我的三板斧呵呵不過請注意這些不一定普遍使用甚至有的會有副作用但是對OLTP系統基於成本的數據庫往往行之有效不妨試試（注附件是Burleson寫的用來報告數據庫性能等信息的腳本本文用到）
　　
　　一．設置合適的SGA
　　
　　常常有人抱怨服務器硬件很好但是Oracle就是很慢很可能是內存分配不合理造成的
　　
　　()假設內存有M這通常是小型應用建議Oracle的SGA大約M其中共享池（SHARED_POOL_SIZE）可以設置M到M根據實際的用戶數查詢等來定數據塊緩沖區可以大致分配MMi下需要設置DB_BLOCK_BUFFERSDB_BLOCK_BUFFER*DB_BLOCK_SIZE等於數據塊緩沖區大小i 下的數據緩沖區可以用db_cache_size來直接分配
　　
　　()假設內存有GOracle 的SGA可以考慮分配M共享池分配M到M數據緩沖區分配M到M
　　
　　()內存GSGA可以考慮分配G共享池M到M剩下的給數據塊緩沖區
　　
　　()內存G以上共享池M到M就足夠啦再多也沒有太大幫助(Biti_rainy有專述)數據緩沖區是盡可能的大但是一定要注意兩個問題一是要給操作系統和其他應用留夠內存二是對於位的操作系統Oracle的SGA有G的限制有的位操作系統上可以突破這個限制方法還請看Biti的大作吧
　　
　　二．分析表和索引更改優化模式
　　
　　Oracle默認優化模式是CHOOSE在這種情況下如果表沒有經過分析經常導致查詢使用全表掃描而不使用索引這通常導致磁盤I/O太多而導致查詢很慢如果沒有使用執行計劃穩定性則應該把表和索引都分析一下這樣可能直接會使查詢速度大幅提升分析表命令可以用ANALYZE TABLE 分析索引可以用ANALYZE INDEX命令對於少於萬的表可以考慮分析整個表對於很大的表可以按百分比來分析但是百分比不能過低否則生成的統計信息可能不准確可以通過DBA_TABLES的LAST_ANALYZED列來查看表是否經過分析或分析時間索引可以通過DBA_INDEXES的LAST_ANALYZED列
　　
　　下面通過例子來說明分析前後的速度對比（表CASE_GA_AJZLZ大約有萬數據有主鍵）首先在SQLPLUS中打開自動查詢執行計劃功能(第一次要執行\RDBMS\ADMIN\utlxplansql來創建PLAN_TABLE這個表)
　　
　　SQL> SET AUTOTRACE ON
　　SQL>SET TIMING ON
　　
　　通過SET AUTOTRACE ON 來查看語句的執行計劃通過SET TIMING ON 來查看語句運行時間
　　
　　SQL> select count(*) from CASE_GA_AJZLZ;
　　COUNT(*)
　　
　　
　　
　　已用時間: : :
　　
　　Execution Plan
　　
　　 SELECT STATEMENT Optimizer=CHOOSE
　　 SORT (AGGREGATE)
　　 TABLE ACCESS (FULL) OF CASE_GA_AJZLZ
　　……………………
　　
　　請注意上面分析中的TABLE ACCESS(FULL)這說明該語句執行了全表掃描而且查詢使用了秒這時表還沒有經過分析下面我們來對該表進行分析
　　
　　SQL> analyze table CASE_GA_AJZLZ compute statistics;
　　
　　表已分析
　　
　　已用時間: : :
　　
　　然後再來查詢
　　
　　SQL> select count(*) from CASE_GA_AJZLZ;
　　COUNT(*)
　　
　　
　　
　　已用時間: : :
　　
　　Execution Plan
　　
　　 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost= Card=)
　　 SORT (AGGREGATE)
　　 INDEX (FAST FULL SCAN) OF PK_AJZLZ (UNIQUE) (Cost=
　　Card=)
　　…………………………
　　
　　請注意這次時間僅僅用了秒！這要歸功於INDEX(FAST FULL SCAN)通過分析表查詢使用了PK_AJZLZ索引磁盤I/O大幅減少速度也大幅提升！下面的實用語句可以用來生成分析某個用戶的所有表和索引假設用戶是GAXZUSR
　　
　　SQL> set pagesize
　　SQL> spool d:\analyze_tablessql;
　　SQL> select analyze table ||owner||||table_name|| compute statistics; from dba_tables where owner=GAXZUSR;
　　SQL> spool off
　　SQL> spool spool d:\analyze_indexessql;
　　SQL> select analyze index ||owner||||index_name|| compute statistics; from dba_indexes where owner=GAXZUSR;
　　SQL> spool off
　　SQL> @d:\analyze_tablessql
　　SQL> @d:\analyze_indexessql
　　
　　解釋上面的語句生成了兩個sql文件分別分析全部的GAXZUSR的表和索引如果需要按照百分比來分析表可以修改一下腳本通過上面的步驟我們就完成了對表和索引的分析可以測試一下速度的改進啦建議定期運行上面的語句尤其是數據經過大量更新
　　
　　當然也可以通過dbms_stats來分析表和索引更方便一些但是我仍然習慣上面的方法因為成功與否會直接提示出來
　　
　　另外我們可以將優化模式進行修改optimizer_mode值可以是RULECHOOSEFIRST_ROWS和ALL_ROWS對於OLTP系統可以改成FIRST_ROWS來要求查詢盡快返回結果這樣即使不用分析在一般情況下也可以提高查詢性能但是表和索引經過分析後有助於找到最合適的執行計劃
　　
　　三．設置cursor_sharing=FORCE 或SIMILAR
　　
　　這種方法是i才開始有的oracle不支持通過設置該參數可以強制共享只有文字不同的語句解釋計劃例如下面兩條語句可以共享
　　
　　SQL> SELECT * FROM MYTABLE WHERE NAME=tom
　　SQL> SELECT * FROM MYTABLE WHERE NAME=turner
　　
　　這個方法可以大幅降低緩沖區利用率低的問題避免語句重新解釋通過這個功能可以很大程度上解決硬解析帶來的性能下降的問題個人感覺可根據系統的實際情況決定是否將該參數改成FORCE該參數默認是exact不過一定要注意修改之前必須先給ORACLE打補丁否則改之後oracle會占用%的CPU無法使用對於ORACLEi可以設置成SIMILAR這個設置綜合了FORCE和EXACT的優點不過請慎用這個功能這個參數也可能帶來很大的負面影響！
　　
　　四．將常用的小表索引釘在數據緩存KEEP池中
　　
　　內存上數據讀取速度遠遠比硬盤中讀取要快據稱內存中數據讀的速度是硬盤的倍！如果資源比較豐富把常用的小的而且經常進行全表掃描的表給釘內存中當然是在好不過了可以簡單的通過ALTER TABLE tablename CACHE來實現在ORACLEi之後可以使用ALTER TABLE table STORAGE(BUFFER_POOL KEEP)一般來說可以考慮把數據塊之內的表放在keep池中當然要根據內存大小等因素來定關於如何查出那些表或索引符合條件可以使用本文提供的accesssql和access_reportsql這兩個腳本是著名的Oracle專家 Burleson寫的你也可以在讀懂了情況下根據實際情況調整一下腳本對於索引可以通過ALTER INDEX indexname STORAGE(BUFFER_POOL KEEP)來釘在KEEP池中
　　
　　將表定在KEEP池中需要做一些准備工作對於ORACLEi 需要設置DB_KEEP_CACHE_SIZE對於i需要設置buffer_pool_keep在i中還要修改db_block_lru_latches該參數默認是無法使用buffer_pool_keep該參數應該比**CPU數量少但是要大於才能設置DB_KEEP_CACHE_BUFFERbuffer_pool_keep從db_block_buffers中分配因此也要小於db_block_buffers設置好這些參數後就可以把常用對象永久釘在內存裡
　　
　　五．設置optimizer_max_permutations
　　
　　對於多表連接查詢如果采用基於成本優化(CBO)ORACLE會計算出很多種運行方案從中選擇出最優方案這個參數就是設置oracle究竟從多少種方案來選擇最優如果設置太大那麼計算最優方案過程也是時間比較長的Oracle和i默認是建議改成對於i已經默認是了
　　
　　六．調整排序參數
　　
　　() SORT_AREA_SIZE:默認的用來排序的SORT_AREA_SIZE大小是K通常顯得有點小一般可以考慮設置成M（）這個參數不能設置過大因為每個連接都要分配同樣的排序內存
　　
　　() SORT_MULTIBLOCK_READ_COUNT:增大這個參數可以提高臨時表空間排序性能該參數默認是可以改成來對比一下排序查詢時間變化注意這個參數的最大值與平台有關系
　　
　　七．調整其它幾個關鍵的性能參數
　　
　　很多人認為使用oracle數據庫系統的默認參數就是最好的其實不是這樣
From:http://tw.wingwit.com/Article/program/Oracle/201311/16704.html