1、ORACLE數據庫的參數調整概述ORACLE數據庫參數主要包括磁盤I/O，回滾段、RODO日志、SGA參數，適當調整ORACLE數據庫的參數可以提高系統性能，但不恰當的調整也會降低系統的性能。ORACLE數據庫的參數，一部分可以通過執行SQL語句或ORACLE的工具DBA STUDIO調整，另外一部分則必須通過修改服務器上ORACLE的安裝目錄下的文件INIT.ORA來調整，INIT.ORA文件一般放在ORACLE安裝目錄adminORACLE服務名pfile下。優化數據庫磁盤I/O使用分布I/O減少磁盤競爭l 將數據文件和redo log文件分開l 減少與ORACLE無關的磁盤I/O避免動態

2、空間管理如果數據庫操作引起數據增加并超出了分配的表空間，ORACLE會自動擴展表空間，動態擴展會降低系統性能。確定一段比較長的時期內數據的最大大?。贿x擇存儲參數值，使ORACLE分配足夠大的分區，避免頻繁自動擴展；調整Checkpoints一個checkpoint是ORACLE自動執行的一種操作，當檢查點操作時，數據庫中的所有緩沖區會寫回磁盤，所有數據庫的控制文件被更新。Checkpoint頻繁發生會加快數據庫的恢復，但是增加了I/O次數，會降低系統的性能。修改INIT.ORA文件中的參數LOG_CHECKPOINT_TIMEOUT和LOG_CHECKPOINT_INTERVAL ，增大這兩個

3、參數會減少I/O次數，提高系統性能。調整LGWR和DBWn I/O調整LGWR I/O每次I/O寫的大小依賴于LOG緩沖區的大小，該大小由LOG BUFFER 所設置，緩沖區太大會延遲寫操作，太小可能導致頻繁的小的I/O操作。如果I/O操作的平均大小很大，那么LOG文件就會成為瓶頸，可以使用STRIPE REDO LOG文件避免這個問題。調整INIT.ORA中的參數LOG_BUFFER以調節LOG緩沖區大小，把REDO LOG文件分為幾個文件，放在不同的磁盤上。調整DBWN I/O使用初始參數DB_WRITER_PROCESSES，可以創建多個數據庫寫進程。調整INIT.ORA中的參數DB_W

4、RITER_PROCESSES。優化回滾段檢測回滾段爭用：select class,count from v$waitstatwhere class in (undo header,undo block,system undo header,system undo bolck)CLASS COUNT- -system undo header 0undo header 0undo block 0select sum(value) from v$sysstat where name in (consistent gets,db block gets) SUM(VALUE) - 20589 通過以下

5、公式計算等待比率：system header waits = system undo header / total reads system block waits = system block / totalreadsrollback header waits = undo header / total reads rollback block waits = undo block / total reads若任何一個的比率大于1則建議再創一個回滾段：create rollback segment rbs21 tablespace rbs storage (inittial 10k opti

6、mal 20k next 10k maxextents 8) ;避免動態分配空間用以下語句檢查回滾段的動態分配：select name,shrinks from v$rollstat,v$rollname where v$rollstat.usn=v$roll ;NAME SHRINKS- -SYSTEM 0RBS0 100RBS1 1若動態分配次數較多可增大回滾段的初始容量。ALTER ROLLBACK SEGMENT RBS0 STORAGE (inittial 20k optimal 40k next 10k maxextents 8) ;優化Redo日志檢測Redo日志緩沖區鎖存：se

7、lect name,value from v$sysstatwhere name = rado log space requests ;value值應接近0若較大則應加大INIT.ORA 中的LOG_BUFFER項的值。減少Redo日志轉儲I/O爭用：將Redo日志分布在不同磁盤中。優化系統全局區（SGA）調整操作系統的內存需求l 減少頁的換入換出；l 將SGA置于主存之中 使用SGA的主要目的是為了在內存中存儲數據，以利于快速訪問。通過設置初始化參數PRE_PAGE_SGA=YES，在數據庫啟動時，可以將整個SGA讀入內存，這樣會減少在啟動后ORACLE達到全部性能的總的時間。使用如下命令可

8、以查看SGA所分配的內存以及其內部的結構：SVRMGR SHOW SGATotal System Global Area 107720688 bytesFixed Size 69616 bytesVariable Size 90701824 bytesDatabase Buffers 16777216 bytesRedo Buffers 172032 bytesl 為單個用戶分配足夠的內存調整redo log緩沖區 參數LOG_BUFFER指定了REDO LOG的緩沖區的保留大小。LOG寫進程（LGWR）在該緩沖區被填充時總是運行的，在新的LOG進入緩沖區時，原來的LOG應已經寫入磁盤。調整共

9、享池數據字典或庫快存的沒有命中，其開銷大大多于緩沖快存的沒有命中，因此，首先應該為共享池分配足夠的空間。使用如下語句可以確定庫快存和數據字典快存的命中率：select（sum（pins-reloads）/sum（pins）“Lib Cache”from v$librarycache ；select （sum（gets-getmisses-usage-fixed）/sum（gets）“Row Cache”from v$librarycache;共享池中的自由內存可以查看：select * from v$sgastat where name=free memory;當然，如果共享池滿了并不一定存在

10、問題，只要上面所說的比率接近于1，就不需要增加共享池大小；如果自由內存接近于0而且庫快存或數據字典快存的命中率小于0.95，那么需要增加共享池的大小。l 調整Library Cache1 檢查庫快存的活動select sum(pins) “Executions”,sum(reloads)”Cache Misses while Executing” from v$librarycache ;“Executions”列指明sql語句，pl/sql塊和實體定義被訪問執行了的次數，”Cache Misses while Executing”指明其中沒有命中的次數。2 減少庫快存的非命中1、 分配更多的

11、庫快存（可以增加初始化參數shared pool size的值；為了利用增加的共享sql區，增加初始化參數open cursors的值）；2、 盡可能使用標準的sql語句（sql語句或pl/sql塊必須一致，包括字符和空格；sql語句或pl/sql塊中對schema實體的引用必須解析到同一schema的同一對象；sql語句中試用的變量的名字和數據類型必須匹配；sql語句必須使用相同的優化方法和優化目標）；盡可能使用標準的sql語句，策略：l 語句中盡量使用變量而不要使用常量l 確保應用用戶不會改變優化方法和目標l 標準化的變量命名和空格轉換l 盡可能使用存儲過程3、 使用CURSOR_SPAC

12、E_FOR_TIME加速共享sql區的訪問：該參數指定是否共享sql區可以釋放，如果為false（默認值），一個共享sql區就可以被釋放；如果為true，一個共享的sql區只存在所有與其相關的游標關閉后才可以被釋放。如果庫快存在語句執行時有非命中，不要將其設置為true，否則對新的sql語句將沒有空間。l 調整Data Dictionary Cache1、 監視數據字典快存的活動select sum(gets) “Data Dictionary Gets”, sum(getmisses) “Data Dictionary Cache Get Misses” from v$rowcache ;2

13、、 減少數據字典快存的非命中對頻繁訪問的數據字典快存沒命中和命中比應少于1015。要增加數據字典快存可用的內存數，需要增加初始參數SHARED_POOL_SIZE的值。l 調整多線程服務器的共享池查詢動態表v$sesstat可以確定會話信息的大小：select sum(value)|bytes”Total memory for all sessions” from v$sesstat, v$statname where name=session uga memort and v$sesstat statistic#=v$statname.statistic#;顯示信息如下：Total memo

14、ry for all sessions-157125 bytes 結果指明當前分配給所有會話的內存。l 調整共享池的保留空間SHARED_POOL_RESERVED_SIZE為大的存儲保留的SHARED_POOL_SIZE總量；SHARED_POOL_RESERVED_MIN_ALLOC控制保留內存的分配；要創建一個保留列表，SHARED_POOL_RESERVED_SIZE必須大于SHARED_POOL_RESERVED_MIN_ALLOC。優化數據庫緩沖區高速緩存:select name,value from v$sysstat where name in (db block gets,c

15、onsistent gets,physical reads) ;NAME VALUE- -db block gets 3700consistent gets 17603physical reads 523計算公式：Hit Ratio = 1-(physical reads / ( db block gets + consisten gets) 若命中率低可以加大INIT.ORA中DB_BLOCK_BUFFERS的值。DB_BLOCK_BUFFERS的單位是塊，每塊大小由DB_BLOCK_SIZE確定，DB_BLOCK_SIZE的單位是字節，高速緩沖區的大小=DB_BLOCK_BUFFERSDB

16、_BLOCK_SIZE；調整多緩沖池l 多緩沖池特性概述可以使用keep緩沖池來維護緩存中的實體，使用recycle緩沖池來防止實體占用緩存中不必要的空間；可以將大的數據段置于獨立的recycle緩存中，將小的數據段置于獨立的keep緩存中。l 何時使用多緩沖池1 查找ORACLE內部實體數據段的數目：select data_object_id,object_type from user_objects where object_name= ;2 查找對應該段名的緩存中的緩沖區數：select count(*) buffers from x$bh where obj= ;3 查找實例中的緩沖區

17、總數：select value “total buffers ” from v$parameter where name=db_block_buffers ;4 計算當前有指定段使用的緩存所占的比率:%cache used by segment_name=buffers(step 2)/buffers(step 3);l 使用多緩沖池調節緩存減少I/O操作；隔離緩存中的實體；限制實體在緩存中的一個部分l 使多緩沖池生效定義新緩沖池：使用初始參數BUFFER_POOL_NAME來定義緩沖池，每個緩沖池可以指定兩個屬性：緩沖池中的緩沖區數；分配該緩沖池的LRU latches數。用于定義緩沖池的初

18、始參數有：BUFFER_POOL_KEEP定義keep緩沖池BUFFER_POOL_RECYCLE定義recycle緩沖池DB_BLOCK_BUFFERS定義數據庫實例的緩沖區數DB_BLOCK_LRU_LATCHES定義數據庫實例的LRU latches數ORACLE8定義了三個緩沖池：keep，recycle和default。其中default緩沖池是已經存在的。l 使用多緩沖池為一個實體建立一個默認緩沖池，語法為：BUFFER_POOLKEEP | RECYCLE | DEFAULTl 如何為各緩沖池設置大小1. KEEP緩沖池使用keep緩沖池的目的是將實體保留在內存，避免I/O操作s

19、elect physical_reads,block_gets,consistent_gets from v$buffer_pool statistics where name=KEEP;計算緩沖池命中率的公式：Hit Ratio=1-physical_reads/(block_gets+consistent_gets)2. Recycle緩沖池使用recycle緩沖池的目的是為了清除內存中的不再使用的數據塊，如果“free buffer waits”統計數總是很高，可以確定recycle緩沖池過小：確定recycle緩沖池大小的辦法：使recycle緩沖池失效運行系通到穩定狀態，查看defa

20、ult緩沖池中由本來需要置于recycle緩沖池中數據段的緩沖區數，除以4，該結果就可以用作recycle緩沖池的大小。3. 確定數據段置于keep還是recycle緩沖池對于至少兩倍DEFAULT緩沖池大小，并且占用系統整個I/O的一定百分比的數據段，那么最好放置于recycle緩沖池；對于大小小于DEFAULT緩沖池的10，并且占用至少系統整個I/O的1的數據段，那么最好放置于keep緩沖池；如果對表空間超過一個段時，可以通過查詢V$SESSION_WAIT確定每個段的I/O操作。l 如何識別和減少LRU Latch競爭 LRU latches規劃了那些緩存中最近最少使用的緩沖區列表，使用

21、DB_BLOCK_LRU_LATCHES參數可以設置數據庫實例中的latches總數?？梢酝ㄟ^如下語句確定系統中是否有latch競爭：select child#,sleeps/gets ratio from v$latch_children where name=cache buffers lru chain ;每個LRU latch的非命中率應少于1，任何大于1的latch說明存在競爭，通過如下語句查找出來：select name from v$buffer_pool_statistics where lo_setid=child_latch_numbers ;通過增加系統的LATCHES總

22、數和相關緩沖池的LATCHES數可以減少LRU latch競爭。所允許的LATCHES最大值應少于：number_of_cpus*2*3和number_of_buffers/50調整排序區使用動態表V$SYSSTAT的信息反映排序SELECT NAME , VALUE FROM V$SYSSTAT WHERE NAME IN (SORTS(MEMORY),SORTS(DISK) ;SORTS(MEMORY)不需要使用I/O操作而完全在內存完成的排序數；SORTS(DISK)需要使用I/O操作與磁盤臨時段才能完成數據的排序數目。增大SORT_AREA_SIZE以避免磁盤排序修改INIT.ORA文件中SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE的值，單位字節重新分配內存 在重新定義了ORACLE內存結構、調整了庫快存、數據字典快存和快速緩存后，如果減少了這些結構中某一個結構的內存使用，就可能需要將內存分配到