oracle數(shù)據(jù)庫優(yōu)化

1、目錄目錄1數(shù)據(jù)庫的優(yōu)化2概述2監(jiān)控數(shù)據(jù)庫的性能：2優(yōu)化數(shù)據(jù)庫磁盤I/O2檢查系統(tǒng)的I/O問題2使用分布I/O減少磁盤競爭2避免動態(tài)空間管理2調(diào)整排序3調(diào)整Checkpoints3調(diào)整LGWR和DBWn I/O4建立和優(yōu)化數(shù)據(jù)庫文件的方針：6監(jiān)控磁盤I/O的方法：6優(yōu)化回滾段7檢測回滾段爭用：7通過以下公式計算等待比率：7若任何一個的比率大于1則建議再創(chuàng)一個回滾段：7避免動態(tài)分配空間7優(yōu)化Redo日志8檢測Redo日志緩沖區(qū)鎖存：8減少Redo日志轉(zhuǎn)儲I/O爭用：81.優(yōu)化系統(tǒng)全局區(qū)（SGA）8調(diào)整操作系統(tǒng)的內(nèi)存需求8調(diào)整redo log緩沖區(qū)8調(diào)整私有sql和pl/sql區(qū)8調(diào)整共享池9調(diào)整快

2、速緩存10調(diào)整多緩沖池12調(diào)整排序區(qū)13重新分配內(nèi)存13減少整個內(nèi)存使用13優(yōu)化數(shù)據(jù)字典高速緩存：13優(yōu)化游標：13注釋：以上增加SGA分配的調(diào)整以SGA不被換出實存為限，否則SGA部分換出實存反而降低Oracle性能。14優(yōu)化數(shù)據(jù)庫對象：14管理表的動態(tài)分配。14優(yōu)化索引存儲參數(shù)。14數(shù)據(jù)庫的優(yōu)化概述影響數(shù)據(jù)庫性能的因素包括：系統(tǒng)、數(shù)據(jù)庫、網(wǎng)絡(luò)。數(shù)據(jù)庫的優(yōu)化包括：優(yōu)化數(shù)據(jù)庫磁盤I/O、優(yōu)化回滾段、優(yōu)化Rrdo日志、優(yōu)化系統(tǒng)全局區(qū)、優(yōu)化數(shù)據(jù)庫對象。監(jiān)控數(shù)據(jù)庫的性能：在init.ora參數(shù)文件中設(shè)置TIMED_STATISTICS=TRUE 和在你的會話層設(shè)置ALTER SESSION SET

3、STATISTICS=TRUE 。運行svrmgrl 用 connect internal 注冊，在你的應(yīng)用系統(tǒng)正?；顒悠陂g，運行utlbstat.sql 開始統(tǒng)計系統(tǒng)活動，達到一定的時間后，執(zhí)行utlestat.sql 停止統(tǒng)計。統(tǒng)計結(jié)果將產(chǎn)生在report.txt 文件中。（utlbstat.sql utlestat.sql 一般存放在$ORACLE_HOME/RDBMS/ADMIN 子目錄下）優(yōu)化數(shù)據(jù)庫磁盤I/O檢查系統(tǒng)的I/O問題在UNIX系統(tǒng)中工具sard能檢查整個系統(tǒng)的iostat（IO statistics），在NT系統(tǒng)上則使用性能監(jiān)視器（Performance Monitor）

4、.反映oracle文件I/O的進程文件進程LGWRDBWNARCHSMONPMONCKPTFore_groundPQ Slave數(shù)據(jù)庫文件YYYYYYLog文件Y歸檔文件Y控制文件YYYYYYYY使用V$FILESTAT確定oracle數(shù)據(jù)文件I/OSELECT NAME,PHYRDS,PHYWRTS FROM V$DATAFILE DF,V$FILESTAT FS WHERE DF.FILE#=FS.FILE# ;使用分布I/O減少磁盤競爭l 將數(shù)據(jù)文件和redo log文件分開l Striping 表數(shù)據(jù)l 分開表和索引l 減少與oracle無關(guān)的磁盤I/O避免動態(tài)空間管理在創(chuàng)建如表或回滾

5、段的數(shù)據(jù)庫實體時，在數(shù)據(jù)庫中會為這些數(shù)據(jù)分配空間，該空間被稱為段。如果數(shù)據(jù)庫操作引起數(shù)據(jù)增加并超出了分配的表空間，oracle會擴展該段，動態(tài)擴展會降低系統(tǒng)性能。l 確定動態(tài)擴展select name,value from v$sysstat where name=recursive calls ;l 分配分區(qū)確定實體的最大大小；選擇存儲參數(shù)值，使oracle分配足夠大的分區(qū)，在創(chuàng)建實體時可以裝入所有數(shù)據(jù)l 避免回滾段的動態(tài)空間管理回滾段大小由其存儲參數(shù)所決定，回滾段必須能保存所有交易的回滾入口；使用set transaction 命令可以為回滾段賦予交易的合適的大小；對長的查詢的修改數(shù)據(jù)，應(yīng)

6、賦予大的回滾段，以保持所有的回滾入口；對OLTP交易，由于頻繁交易，每個交易只修改小量的數(shù)據(jù)，因此賦予小的回滾段。l 減少遷移和鏈接行1. 使用ANALYZE 收集遷移和鏈接行的信息；2. 查詢輸出表：chained_rows;3. 如果有許多遷移和鏈接行，就需要消除遷移行，方法如下：A. 創(chuàng)建與原表相同列的中間表，以保存遷移和鏈接行；B. 從原表中刪除遷移和鏈接行；C. 將中間表中的行插入到原表中；D. 刪除中間表4. 刪除第一步收集的信息；5. 重新使用ANALYZE命令查詢輸出表6. 在輸出表中出現(xiàn)的行都是鏈接行，只能通過增加數(shù)據(jù)塊的大小來清除。調(diào)整排序內(nèi)存中排序使用動態(tài)表V$SYSST

7、AT的信息反映排序SELECT NAME , VALUE FROM V$SYSSTAT WHERE NAME IN (SORTS(MEMORY),SORTS(DISK) ;SORTS(MEMORY)不需要使用I/O操作而完全在內(nèi)存完成的排序數(shù)；SORTS(DISK)需要使用I/O操作與磁盤臨時段才能完成數(shù)據(jù)的排序數(shù)目。增大SORT_AREA_SIZE以避免磁盤排序使用NOSORT創(chuàng)建非排序的索引CREATE INDEX INDEX_NAME ON TABLE TABLE_NAME(COLUMN_NAME) NOSORT ;調(diào)整Checkpoints一個checkpoint是oracle自動執(zhí)行

8、的一種操作，當檢查點操作時，數(shù)據(jù)庫中的所有緩沖區(qū)會寫回磁盤，所有數(shù)據(jù)庫的控制文件被更新。Checkpoint頻繁發(fā)生會加快數(shù)據(jù)庫的恢復(fù)，但是增加了I/O次數(shù)，會降低系統(tǒng)的性能。調(diào)整LGWR和DBWn I/O調(diào)整LGWR I/O每次I/O寫的大小依賴于LOG緩沖區(qū)的大小，該大小由LOG BUFFER 所設(shè)置，緩沖區(qū)太大會延遲寫操作，太小可能導致頻繁的小的I/O操作。如果I/O操作的平均大小很大，那么LOG文件就會成為瓶頸，可以使用STRIPE REDO LOG文件避免這個問題。調(diào)整DBWN I/O使用初始參數(shù)DB_WRITER_PROCESSES，可以創(chuàng)建多個數(shù)據(jù)庫寫進程。調(diào)整競爭由多個進程同時

9、請求使用相同的資源時，就產(chǎn)生了競爭確定競爭問題視圖V$RESOURCE_LIMIT提供了一些系統(tǒng)資源的使用限制。如果系統(tǒng)存在無反應(yīng)的現(xiàn)象，檢查V$SYSTEM_EVENT，檢查最大平均等待時間的事件；如果存在過量的緩沖區(qū)等待，檢查V$WAITSTAT，確定哪個類型的塊有最多的等待次數(shù)和最長的等待時間，再查詢V$SESSION_WAIT得到每個緩沖區(qū)的等待時間。減少回滾段的競爭通過檢查V$WAITSTAT可以確定回滾段的競爭：SELECT CLASS,COUNT FROM V$WAITSTAT WHERE CLASS IN (SYSTEM UODO HEADER,SYSTEM UODO BLOC

10、K,UODO HEADER,UODO BLOCK) ;減少調(diào)度進程的競爭檢查調(diào)度進程的busy率SELECT NETWORK”PROTOCOL”, SUM(BUSY)/(SUM(BUSY)+SUM(IDLE) “TOTAL BUSY RATE” FROM V$DISPATCHER GROUP BY NETWORK ;如果指定協(xié)議的調(diào)度進程忙的時間超過50的有效工作時間，那么，增加調(diào)度進程可以提高使用該協(xié)議連接到oracle的性能。檢查調(diào)度進程相應(yīng)隊列的等待時間SELECT NETWORK “PROTOCOL” DECODE(SUM(TOTALQ),0.NO RESPONSES,SUM(WAIT

11、)/SUM(TOTALQ)|HUNDREDTHS OF SECONDS) “AVERAGE WAIT TIME PER RESPONSE” FROM V$QUEUE Q,V$DISPATCHER D WHERE Q.TYPE=DISPATCHER AND Q.PADDR=D.PADDR GROUP BY NETWORK ;增加調(diào)度進程：使用MTS_DISPATCHERS參數(shù)和ALTER_SYSTEM命令可以增加調(diào)度進程減少共享服務(wù)器進程的競爭共享服務(wù)器進程競爭可以由不斷增加的請求等待時間所反映，使用如下查詢：select decode(totalq,0,No Requests, wait/to

12、talq|hundredths of seconds) “Average Wait Time Per Requests” from v$queue where type=COMMON ;使用如下查詢可以得到當前運行的共享服務(wù)進程數(shù)：select count(*) “Shared Server Processes” from V$shared_servers where status!=QUIT;oracle能自動增加共享服務(wù)進程，但是MTS_MAX_SERVERS的值可以更改。減少redo log緩沖區(qū)latches競爭在LGWR進程將redo入口從redo log緩沖區(qū)寫入redo log文

13、件后，該入口就會被新入口覆蓋，供其他log的使用。V$SYSSTAT中redo buffer allocation retries 反映用戶進程等待redo log空間的次數(shù)：Select name,value from v$sysstat where name=redo buffer allocation retries ;redo buffer allocation retries的值應(yīng)該接近0，如果該值持續(xù)增加，那么，說明進程需要等待緩沖區(qū)的空間。增大參數(shù)LOG_BUFFER的值可以增大redo log的大小。確定redo log緩沖區(qū)latches競爭redo分配latch；redo復(fù)

14、制latches。一次只能有一個用戶分配緩沖區(qū)中的空間，在分配了redo入口的空間后，用戶進程將入口復(fù)制到緩沖區(qū)，其最大大小是由LOG_SMALL_ENTRY_MAX_SIZE指定。Redo復(fù)制latches的數(shù)目由參數(shù)LOG_SIMULTANEOUS_COPIES指定。檢查redo log活動對redo log緩沖區(qū)的頻繁訪問可能導致redo log緩沖區(qū)latches競爭，降低系統(tǒng)性能。Oracle在動態(tài)表V$LATCH中收集了所有LATCH的統(tǒng)計信息。其中：表v$latch反映willing-to-wait 請求的列g(shù)ets成功的willing-to-wait請求數(shù)；misses初始不成

15、功的willing-to-wait請求數(shù)；sleeps請求不成功的等待時間；表v$latch反映immediate請求的列：immediate gets成功的immediate請求數(shù)immediate misses不成功的immediate請求數(shù)使用如下查詢：select ,gets,misses,immediate_gets,immediate_misses from v$latch l,v$latchname ln where in (redo allocation ,redo copy) and ln.latch#=l.latch# ;可以計算出各類請求的等

16、待率。減少latch競爭要減少redo allocation latch競爭，必須減少單個進程占用latch的時間。要減少這個時間，可以減少該redo allocation latch的復(fù)制。減少LOG_SMALL_ENTRY_MAX_SIZE初始參數(shù)可以減少在redo allocation latch的redo入口的復(fù)制次數(shù)和大小。減少redo copy Latches競爭可以用增加LOG_SIMULTANEOUS_COPIES的值來增加LATCH數(shù)，最多可以達到CPU的兩倍。減少Free List競爭確定Free List競爭，可以使用以下幾步：1. 檢查V$WAITSTAT，確定DATA

17、 BLOCKS的競爭；2. 檢查V$SYSTEM_EVENT,確定BUFFER BUSY WAITS，如果數(shù)值高，表明存在競爭；3. 在這種情況下，檢查V$SESSION_WAIT查詢每個緩沖區(qū)的忙等待、FILE、BLOCK及ID；4. 使用如下查詢得到實體和FREE LIST的名稱：SELECT SEGMENT_NAME,SEGMENT_TYPE FROM DBA_EXTENTS WHERE FILE_ID=file AND BLOCK BETWEEN block_id AND block_id+blocks ;5. 使用如下查詢找到FREE LIST:SELECT SEGMENT_NAME

18、,FREELISTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME=segment AND SEGMENT_TYPE=type ;增加Free List要減少表的Free list的競爭， 可以使用freelists參數(shù)重新創(chuàng)建表，方法有：1. 刪除舊表，重新創(chuàng)建表；2. 通過從舊表選擇數(shù)據(jù)插入到新表，刪除舊表，重命名，完成表的重新創(chuàng)建；3. 使用Export 和Import，export舊表，刪除舊表，import該表。建立和優(yōu)化數(shù)據(jù)庫文件的方針：為表和索引創(chuàng)建不同的表空間。將表和索引的表空間放在不同的磁盤上。將REDO日志和回滾段放在不同的磁盤上。將Oracle

19、可執(zhí)行文件和數(shù)據(jù)庫文件放在單獨的磁盤上。確定最常用的表、索引，以及他們的表空間，并放在單獨的磁盤上。不要在Oracle數(shù)據(jù)磁盤上安裝其他第三方軟件。監(jiān)控磁盤I/O的方法：用以下查詢語句可以得到各表空間讀寫次數(shù)，phyrds+phywrts 即是磁盤I/O量。應(yīng)按前面講的方針調(diào)整數(shù)據(jù)文件的分布方式。select name,phyrds,phywrts from v$datafile,v$filestat where v$datafile.file# = v$filestat.file# 結(jié)果如下：NAME PHYRDS PHYWRTS- - -/u/oracle/OraHome/oradata/

20、pb/system01.dbf 2511 8/u/oracle/OraHome/oradata/pb/tools01.dbf 5 2/u/oracle/OraHome/oradata/pb/rbs01.dbf 18 20/u/oracle/OraHome/oradata/pb/temp01.dbf 4 2/u/oracle/OraHome/oradata/pb/users01.dbf 45 5/u/oracle/OraHome/oradata/pb/indx01.dbf 4 2/u/oracle/OraHome/oradata/pb/drsys01.dbf 4 27 rows selected

21、.優(yōu)化回滾段檢測回滾段爭用：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 通過以下公式計算等待比率：system

22、 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則建議再創(chuàng)一個回滾段：create rollback segment rbs21 tablespace rbs storage (inittial 10k optimal 20k next 10

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

24、 from v$sysstatwhere name = rado log space requests ;value值應(yīng)接近0若較大則應(yīng)加大INITXXX.ORA 中的LOG_BUFFER項的值。減少Redo日志轉(zhuǎn)儲I/O爭用：將Redo日志分布在不同磁盤中。1. 優(yōu)化系統(tǒng)全局區(qū)（SGA）調(diào)整操作系統(tǒng)的內(nèi)存需求l 減少頁的換入換出；l 將SGA置于主存之中 使用SGA的主要目的是為了在內(nèi)存中存儲數(shù)據(jù)，以利于快速訪問。通過設(shè)置初始化參數(shù)PRE_PAGE_SGA=YES，在數(shù)據(jù)庫啟動時，可以將整個SGA讀入內(nèi)存，這樣會減少在啟動后ORACLE達到全部性能的總的時間。使用如下命令可以查看SGA所分配

25、的內(nèi)存以及其內(nèi)部的結(jié)構(gòu)：SVRMGR SHOW SGATotal System Global Area 107720688 bytesFixed Size 69616 bytesVariable Size 90701824 bytesDatabase Buffers 16777216 bytesRedo Buffers 172032 bytesl 為單個用戶分配足夠的內(nèi)存調(diào)整redo log緩沖區(qū) 參數(shù)LOG_BUFFER指定了REDO LOG的緩沖區(qū)的保留大小。LOG寫進程（LGWR）在該緩沖區(qū)被填充時總是運行的，在新的LOG進入緩沖區(qū)時，原來的LOG應(yīng)已經(jīng)寫入磁盤。調(diào)整私有sql和pl/s

26、ql區(qū)l 標識不必要的語法分析調(diào)用1. 在sql跟蹤工具有效時運行應(yīng)用2. 查看視圖V$SQLAREA viewSVRMGR select sql_text,parse_calls,executions from v$sqlarea ;如果parse_calls值接近execution值，可能就是不斷地對sql語句進行語法分析3. 執(zhí)行如下查詢：select *from v$statname where name in (parse_count(hard),execute_count);其結(jié)果類似于:statistic#, name-100 parse_count90 execute_coun

27、t然后執(zhí)行如下查詢：select * from v$sesstat where statistics# in(90,100) order by value , sid ;l 減少不必要的語法分析調(diào)用初始化參數(shù)open_cursors的最大值依賴于操作系統(tǒng)，最少值為5調(diào)整共享池數(shù)據(jù)字典或庫快存的沒有命中，其開銷大大多于緩沖快存的沒有命中，因此，首先應(yīng)該為共享池分配足夠的空間。使用如下語句可以確定庫快存和數(shù)據(jù)字典快存的命中率：select（sum（pins-reloads）/sum（pins）“Lib Cache”from v$librarycache ；select （sum（gets-getm

28、isses-usage-fixed）/sum（gets）“Row Cache”from v$librarycache;共享池中的自由內(nèi)存可以查看：select * from v$sgastat where name=free memory;當然，如果共享池滿了并不一定存在問題，只要上面所說的比率接近于1，就不需要增加共享池大??；如果自由內(nèi)存接近于0而且?guī)炜齑婊驍?shù)據(jù)字典快存的命中率小于0.95，那么需要增加共享池的大小。l 調(diào)整Library Cache1 檢查庫快存的活動select sum(pins) “Executions”,sum(reloads)”Cache Misses while

29、Executing” from v$librarycache ;“Executions”列指明sql語句，pl/sql塊和實體定義被訪問執(zhí)行了的次數(shù)，”Cache Misses while Executing”指明其中沒有命中的次數(shù)。2 減少庫快存的非命中1、 分配更多的庫快存（可以增加初始化參數(shù)shared pool size的值；為了利用增加的共享sql區(qū)，增加初始化參數(shù)open cursors的值）；2、 盡可能使用標準的sql語句（sql語句或pl/sql塊必須一致，包括字符和空格；sql語句或pl/sql塊中對schema實體的引用必須解析到同一schema的同一對象；sql語句中試

30、用的變量的名字和數(shù)據(jù)類型必須匹配；sql語句必須使用相同的優(yōu)化方法和優(yōu)化目標）；盡可能使用標準的sql語句，策略：l 語句中盡量使用變量而不要使用常量l 確保應(yīng)用用戶不會改變優(yōu)化方法和目標l 標準化的變量命名和空格轉(zhuǎn)換l 盡可能使用存儲過程3、 使用cursor_space_for_time加速共享sql區(qū)的訪問：該參數(shù)指定是否共享sql區(qū)可以釋放，如果為false（默認值），一個共享sql區(qū)就可以被釋放；如果為true，一個共享的sql區(qū)只存在所有與其相關(guān)的游標關(guān)閉后才可以被釋放。如果庫快存在語句執(zhí)行時有非命中，不要將其設(shè)置為true，否則對新的sql語句將沒有空間。l 調(diào)整Data Dic

31、tionary Cache1、 監(jiān)視數(shù)據(jù)字典快存的活動select sum(gets) “Data Dictionary Gets”, sum(getmisses) “Data Dictionary Cache Get Misses” from v$rowcache ;2、 減少數(shù)據(jù)字典快存的非命中對頻繁訪問的數(shù)據(jù)字典快存沒命中和命中比應(yīng)少于1015。要增加數(shù)據(jù)字典快存可用的內(nèi)存數(shù)，需要增加初始參數(shù)shared_pool_size的值。l 調(diào)整多線程服務(wù)器的共享池查詢動態(tài)表v$sesstat可以確定會話信息的大小：select sum(value)|bytes”Total memory for

32、 all sessions” from v$sesstat, v$statname where name=session uga memort and v$sesstat statistic#=v$statname.statistic#;顯示信息如下：Total memory for all sessions-157125 bytes 結(jié)果指明當前分配給所有會話的內(nèi)存。l 調(diào)整共享池的保留空間shared_pool_reserved_size為大的存儲保留的shared_pool_size總量；shared_pool_reserved_min_alloc控制保留內(nèi)存的分配；要創(chuàng)建一個保留列表，

33、shared_pool_reserved_size必須大于shared_pool_reserved_min_alloc。優(yōu)化共享池：用以下語句檢測命中率：select sum(pins)pinned,sum(reloads)reloaded from v$librarycache pinned reloaded - - 2474 17命中率pinned/ reloaded若命中率低可增大INIT.ORA中SHARED_POOL_SIZE的值。調(diào)整快速緩存l 利用緩存的命中率評價快速緩存的活動select name,value from v$sysstat where name in (db b

34、lock gets,consistent_gets,physical reads);db block gets, consistent_gets其和為數(shù)據(jù)請求總數(shù)，physical reads為數(shù)據(jù)請求導致訪問磁盤數(shù)據(jù)文件的總數(shù)。增加db_block_buffers應(yīng)該遵循如下規(guī)則： 緩存命中率低于0.9； 前面增加的db_block_buffers有效l 提高緩沖區(qū)命中率如果緩沖命中率低，如小于60或70，那么需要增加緩沖區(qū)中的緩存數(shù)。方法是增大db_block_buffers的值。表v$recent_bucket的每一行反映在緩存中增加一個緩沖區(qū)的相應(yīng)性能值，其中rownum列比可能增加到

35、緩存的緩沖區(qū)數(shù)小1；count列通過增加緩沖區(qū)數(shù)rownum1到緩存，增加的緩沖區(qū)命中。查詢v$recent_bucket視圖：select sum(count) ach from v$recent_bucket where rownum=90 ;計算命中率（ACM為增加的非命中次數(shù)）：Hit Ratio=1-(physical reads + ACM/(db block gets+consistent gets);也可以將100個緩沖區(qū)分為4個25個緩沖區(qū)段：select 25*TRUNC(ROWNUM/25)+1|to|25*(TRUNC(ROWNUM/25+1)”Interval”,su

36、m(count)”Buffer Cache Hits” from v$CURRENT_BUCKET where rownum0 group by TRUNC(ROWNUM/25);其中INTERVAL為緩沖區(qū)段，BUFFER CACHE HITS為對應(yīng)段的緩存命中率。優(yōu)化數(shù)據(jù)庫緩沖區(qū)高速緩存:select name,value from v$sysstat where name in (db block gets,consistent gets,physical reads) ;NAME VALUE- -db block gets 3700consistent gets 17603physic

37、al reads 523計算公式：Hit Ratio = 1-(physical reads / ( db block gets + consisten gets) 若命中率低可以加大INITXXX.ORA中DB_BLOCK_BUFFER的值。調(diào)整多緩沖池l 多緩沖池特性概述可以使用keep緩沖池來維護緩存中的實體，使用recycle緩沖池來防止實體占用緩存中不必要的空間；可以將大的數(shù)據(jù)段置于獨立的recycle緩存中，將小的數(shù)據(jù)段置于獨立的keep緩存中。l 何時使用多緩沖池1 查找oracle內(nèi)部實體數(shù)據(jù)段的數(shù)目：select data_object_id,object_type from

38、 user_objects where object_name= ;2 查找對應(yīng)該段名的緩存中的緩沖區(qū)數(shù)：select count(*) buffers from x$bh where obj= ;3 查找實例中的緩沖區(qū)總數(shù)：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 使用多緩沖池調(diào)節(jié)緩存減少I/O操作；隔離緩存中的實體；限

39、制實體在緩存中的一個部分l 使多緩沖池生效定義新緩沖池：使用初始參數(shù)BUFFER_POOL_NAME來定義緩沖池，每個緩沖池可以指定兩個屬性：緩沖池中的緩沖區(qū)數(shù)；分配該緩沖池的LRU latches數(shù)。用于定義緩沖池的初始參數(shù)有：BUFFER_POOL_KEEP定義keep緩沖池BUFFER_POOL_RECYCLE定義recycle緩沖池DB_BLOCK_BUFFERS定義數(shù)據(jù)庫實例的緩沖區(qū)數(shù)DB_BLOCK_LRU_LATCHES定義數(shù)據(jù)庫實例的LRU latches數(shù)Oracle8定義了三個緩沖池：keep，recycle和default。其中default緩沖池是已經(jīng)存在的。l 使用多

40、緩沖池為一個實體建立一個默認緩沖池，語法為：BUFFER_POOLKEEP | RECYCLE | DEFAULTl 如何為各緩沖池設(shè)置大小1. KEEP緩沖池使用keep緩沖池的目的是將實體保留在內(nèi)存，避免I/O操作select 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緩沖池的目的是

41、為了清除內(nèi)存中的不再使用的數(shù)據(jù)塊，如果“free buffer waits”統(tǒng)計數(shù)總是很高，可以確定recycle緩沖池過?。捍_定recycle緩沖池大小的辦法：使recycle緩沖池失效運行系通到穩(wěn)定狀態(tài)，查看default緩沖池中由本來需要置于recycle緩沖池中數(shù)據(jù)段的緩沖區(qū)數(shù)，除以4，該結(jié)果就可以用作recycle緩沖池的大小。3. 確定數(shù)據(jù)段置于keep還是recycle緩沖池對于至少兩倍DEFAULT緩沖池大小，并且占用系統(tǒng)整個I/O的一定百分比的數(shù)據(jù)段，那么最好放置于recycle緩沖池；對于大小小于DEFAULT緩沖池的10，并且占用至少系統(tǒng)整個I/O的1的數(shù)據(jù)段，那么最好放

42、置于keep緩沖池；如果對表空間超過一個段時，可以通過查詢V$SESSION_WAIT確定每個段的I/O操作。l 如何識別和減少LRU Latch競爭 LRU latches規(guī)劃了那些緩存中最近最少使用的緩沖區(qū)列表，使用DB_BLOCK_LRU_LATCHES參數(shù)可以設(shè)置數(shù)據(jù)庫實例中的latches總數(shù)?？梢酝ㄟ^如下語句確定系統(tǒng)中是否有l(wèi)atch競爭：select child#,sleeps/gets ratio from v$latch_children where name=cache buffers lru chain ;每個LRU latch的非命中率應(yīng)少于1，任何大于1的latch說

43、明存在競爭，通過如下語句查找出來：select name from v$buffer_pool_statistics where lo_setid=child_latch_numbers ;通過增加系統(tǒng)的LATCHES總數(shù)和相關(guān)緩沖池的LATCHES數(shù)可以減少LRU latch競爭。所允許的LATCHES最大值應(yīng)少于：number_of_cpus*2*3和number_of_buffers/50調(diào)整排序區(qū) 如果經(jīng)常進行大的排序，應(yīng)增大參數(shù)SORT_AREA_SIZE的值，將參數(shù)SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE聯(lián)合使用，更有利于使用大的排序區(qū)。重新分配

44、內(nèi)存 在重新定義了oracle內(nèi)存結(jié)構(gòu)、調(diào)整了庫快存、數(shù)據(jù)字典快存和快速緩存后，如果減少了這些結(jié)構(gòu)中某一個結(jié)構(gòu)的內(nèi)存使用，就可能需要將內(nèi)存分配到其他結(jié)構(gòu)中；在改變了oracle內(nèi)存結(jié)構(gòu)以后，oracle的內(nèi)存需求也會改變。減少整個內(nèi)存使用采用如下方法：增加可用內(nèi)存總數(shù)；減少內(nèi)存使用優(yōu)化數(shù)據(jù)字典高速緩存：select sum(gets)Read Requests,sum(getmisses)Reads not in Memory from v$rowcache ;Read Requests Reads not in Memory- - 4764 145命中率Read Requests/ Read

45、s not in Memory若低于85 應(yīng)增加 SHARED_POOL_SIZE優(yōu)化游標：可用以下語句檢測游標的命中率：select * from v$session_cursor_cache ;select * from v$system_cursor_cache ;優(yōu)化游標應(yīng)綜合考慮，若打開的游標過多則應(yīng)用程序需要的內(nèi)存增大，可能發(fā)生共享池內(nèi)存被換出到虛存從而影響性能。 以下三個INIT.ORA中的參數(shù)可影響游標的性能：CLOSED_CACHED_OPEN_CURSORS 指示oracle當執(zhí)行commit或rollback語句時，是否顯式的關(guān)閉游標。如果游標需要反復(fù)使用可以設(shè)為false，否則應(yīng)為true從而減少內(nèi)存占用。CURSOR_SPACE_FOR_TIME 若為true指示oracle只要在sql共享區(qū)中有打開的游標就一直保存該區(qū)域，若其值為false oracle可將其換出內(nèi)存，即使游標仍打開。只有共享池能保存所有打開的游標時才將其設(shè)為true。OPEN CURSOR