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

1、目錄目錄目錄目錄 .1數(shù)據(jù)庫的優(yōu)化數(shù)據(jù)庫的優(yōu)化 .2概述.2監(jiān)控?cái)?shù)據(jù)庫的性能：.2優(yōu)化數(shù)據(jù)庫磁盤 I/O.2建立和優(yōu)化數(shù)據(jù)庫文件的方針：.6監(jiān)控磁盤I/O的方法：.7優(yōu)化回滾段.7檢測回滾段爭用：.7通過以下公式計(jì)算等待比率：.8若任何一個(gè)的比率大于1則建議再創(chuàng)一個(gè)回滾段：.8避免動(dòng)態(tài)分配空間.8優(yōu)化 REDO日志.8檢測Redo日志緩沖區(qū)鎖存：.8減少Redo日志轉(zhuǎn)儲I/O爭用：.8優(yōu)化系統(tǒng)全局區(qū)（SGA）.9優(yōu)化數(shù)據(jù)庫緩沖區(qū)高速緩存:.錯(cuò)誤！未定義書簽。錯(cuò)誤！未定義書簽。優(yōu)化共享池：.錯(cuò)誤！未定義書簽。錯(cuò)誤！未定義書簽。優(yōu)化數(shù)據(jù)字典高速緩存：.14優(yōu)化游標(biāo)：.15注釋：以上增加SGA分配的

2、調(diào)整以SGA不被換出實(shí)存為限，否則SGA部分換出實(shí)存反而降低Oracle性能。.15優(yōu)化數(shù)據(jù)庫對象：.15管理表的動(dòng)態(tài)分配。.15避免鏈接行。.錯(cuò)誤！未定義書簽。錯(cuò)誤！未定義書簽。優(yōu)化索引存儲參數(shù)。.16數(shù)據(jù)庫的優(yōu)化數(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)控?cái)?shù)據(jù)庫的性能：監(jiān)控?cái)?shù)據(jù)庫的性能：在 init.ora 參數(shù)文件中設(shè)置 TIMED_STATISTICS=TRUE 和在你的會話層設(shè)置 ALTER SESSION SET STATISTICS=TRUE 。運(yùn)

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

4、e Monitor）.反映 oracle 文件 I/O 的進(jìn)程進(jìn)程進(jìn)程文件文件LGWRDBWNARCHSMONPMONCKPTFore_groundPQ Slave數(shù)據(jù)庫文件數(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 減少磁盤競爭減少磁盤競爭將數(shù)據(jù)文件和 redo log 文件分開Stripi

5、ng 表數(shù)據(jù)分開表和索引減少與 oracle 無關(guān)的磁盤 I/O避免動(dòng)態(tài)空間管理避免動(dòng)態(tài)空間管理在創(chuàng)建如表或回滾段的數(shù)據(jù)庫實(shí)體時(shí)，在數(shù)據(jù)庫中會為這些數(shù)據(jù)分配空間，該空間被稱為段。如果數(shù)據(jù)庫操作引起數(shù)據(jù)增加并超出了分配的表空間，oracle 會擴(kuò)展該段，動(dòng)態(tài)擴(kuò)展會降低系統(tǒng)性能。確定動(dòng)態(tài)擴(kuò)展確定動(dòng)態(tài)擴(kuò)展select name,value from v$sysstat where name=recursive calls ;分配分區(qū)分配分區(qū)確定實(shí)體的最大大小；選擇存儲參數(shù)值，使 oracle 分配足夠大的分區(qū)，在創(chuàng)建實(shí)體時(shí)可以裝入所有數(shù)據(jù)避免回滾段的動(dòng)態(tài)空間管理避免回滾段的動(dòng)態(tài)空間管理回滾段大小由其

6、存儲參數(shù)所決定，回滾段必須能保存所有交易的回滾入口；使用 set transaction 命令可以為回滾段賦予交易的合適的大??；對長的查詢的修改數(shù)據(jù)，應(yīng)賦予大的回滾段，以保持所有的回滾入口；對 OLTP 交易，由于頻繁交易，每個(gè)交易只修改小量的數(shù)據(jù)，因此賦予小的回滾段。減少遷移和鏈接行減少遷移和鏈接行1.使用 ANALYZE 收集遷移和鏈接行的信息；2.查詢輸出表：chained_rows;3.如果有許多遷移和鏈接行，就需要消除遷移行，方法如下：A.創(chuàng)建與原表相同列的中間表，以保存遷移和鏈接行；B.從原表中刪除遷移和鏈接行；C.將中間表中的行插入到原表中；D.刪除中間表4.刪除第一步收集的信息

7、；5.重新使用 ANALYZE 命令查詢輸出表6.在輸出表中出現(xiàn)的行都是鏈接行，只能通過增加數(shù)據(jù)塊的大小來清除。調(diào)整排序調(diào)整排序內(nèi)存中排序使用動(dòng)態(tài)表 V$SYSSTAT 的信息反映排序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í)段才能完成數(shù)據(jù)的排序數(shù)目。增大 SORT_AREA_SIZE 以避免磁盤排序使用 NOSORT 創(chuàng)建非排序的索引CREATE IND

8、EX INDEX_NAME ON TABLE TABLE_NAME(COLUMN_NAME) NOSORT ;調(diào)整調(diào)整 Checkpoints一個(gè) checkpoint 是 oracle 自動(dòng)執(zhí)行的一種操作，當(dāng)檢查點(diǎn)操作時(shí)，數(shù)據(jù)庫中的所有緩沖區(qū)會寫回磁盤，所有數(shù)據(jù)庫的控制文件被更新。Checkpoint 頻繁發(fā)生會加快數(shù)據(jù)庫的恢復(fù)，但是增加了 I/O 次數(shù)，會降低系統(tǒng)的性能。調(diào)整調(diào)整 LGWR 和和 DBWn I/O調(diào)整 LGWR I/O每次 I/O 寫的大小依賴于 LOG 緩沖區(qū)的大小，該大小由 LOG BUFFER 所設(shè)置，緩沖區(qū)太大會延遲寫操作，太小可能導(dǎo)致頻繁的小的 I/O 操作。如果

9、 I/O 操作的平均大小很大，那么 LOG 文件就會成為瓶頸，可以使用 STRIPE REDO LOG 文件避免這個(gè)問題。調(diào)整 DBWN I/O使用初始參數(shù) DB_WRITER_PROCESSES，可以創(chuàng)建多個(gè)數(shù)據(jù)庫寫進(jìn)程。調(diào)整競爭調(diào)整競爭由多個(gè)進(jìn)程同時(shí)請求使用相同的資源時(shí)，就產(chǎn)生了競爭確定競爭問題視圖 V$RESOURCE_LIMIT 提供了一些系統(tǒng)資源的使用限制。如果系統(tǒng)存在無反應(yīng)的現(xiàn)象，檢查 V$SYSTEM_EVENT，檢查最大平均等待時(shí)間的事件；如果存在過量的緩沖區(qū)等待，檢查 V$WAITSTAT，確定哪個(gè)類型的塊有最多的等待次數(shù)和最長的等待時(shí)間，再查詢 V$SESSION_WAIT

10、 得到每個(gè)緩沖區(qū)的等待時(shí)間。減少回滾段的競爭通過檢查 V$WAITSTAT 可以確定回滾段的競爭：SELECT CLASS,COUNT FROM V$WAITSTAT WHERE CLASS IN (SYSTEM UODO HEADER,SYSTEM UODO BLOCK,UODO HEADER,UODO BLOCK) ;減少調(diào)度進(jìn)程的競爭檢查調(diào)度進(jìn)程的檢查調(diào)度進(jìn)程的 busy 率率SELECT NETWORK”PROTOCOL”, SUM(BUSY)/(SUM(BUSY)+SUM(IDLE) “TOTAL BUSY RATE” FROM V$DISPATCHER GROUP BY NETWO

11、RK ;如果指定協(xié)議的調(diào)度進(jìn)程忙的時(shí)間超過 50的有效工作時(shí)間，那么，增加調(diào)度進(jìn)程可以提高使用該協(xié)議連接到 oracle 的性能。檢查調(diào)度進(jìn)程相應(yīng)隊(duì)列的等待時(shí)間檢查調(diào)度進(jìn)程相應(yīng)隊(duì)列的等待時(shí)間SELECT NETWORK “PROTOCOL” DECODE(SUM(TOTALQ),0.NO RESPONSES,SUM(WAIT)/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

12、.PADDR GROUP BY NETWORK ;增加調(diào)度進(jìn)程：使用增加調(diào)度進(jìn)程：使用 MTS_DISPATCHERS 參數(shù)和參數(shù)和 ALTER_SYSTEM 命令可以增加調(diào)度進(jìn)程命令可以增加調(diào)度進(jìn)程減少共享服務(wù)器進(jìn)程的競爭減少共享服務(wù)器進(jìn)程的競爭共享服務(wù)器進(jìn)程競爭可以由不斷增加的請求等待時(shí)間所反映，使用如下查詢：select decode(totalq,0,No Requests, wait/totalq|hundredths of seconds) “Average Wait Time Per Requests” from v$queue where type=COMMON ;使用如下查詢

13、可以得到當(dāng)前運(yùn)行的共享服務(wù)進(jìn)程數(shù)：select count(*) “Shared Server Processes” from V$shared_servers where status!=QUIT;oracle 能自動(dòng)增加共享服務(wù)進(jìn)程，但是 MTS_MAX_SERVERS 的值可以更改。減少減少 redo log 緩沖區(qū)緩沖區(qū) latches 競爭競爭在 LGWR 進(jìn)程將 redo 入口從 redo log 緩沖區(qū)寫入 redo log 文件后，該入口就會被新入口覆蓋，供其他 log 的使用。V$SYSSTAT 中 redo buffer allocation retries 反映用戶進(jìn)程等

14、待 redo log 空間的次數(shù)：Select name,value from v$sysstat where name=redo buffer allocation retries ;redo buffer allocation retries 的值應(yīng)該接近 0，如果該值持續(xù)增加，那么，說明進(jìn)程需要等待緩沖區(qū)的空間。增大參數(shù) LOG_BUFFER 的值可以增大 redo log 的大小。確定確定 redo log 緩沖區(qū)緩沖區(qū) latches 競爭競爭redo 分配 latch；redo 復(fù)制 latches。一次只能有一個(gè)用戶分配緩沖區(qū)中的空間，在分配了 redo 入口的空間后，用戶進(jìn)程將

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

16、請求數(shù)；sleeps請求不成功的等待時(shí)間；表 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# ;可以計(jì)算出各類請求的等待率。減少減少 l

17、atch 競爭競爭要減少減少 redo allocation latch 競爭競爭，必須減少單個(gè)進(jìn)程占用 latch 的時(shí)間。要減少這個(gè)時(shí)間，可以減少該 redo allocation latch 的復(fù)制。減少 LOG_SMALL_ENTRY_MAX_SIZE 初始參數(shù)可以減少在 redo allocation latch 的 redo 入口的復(fù)制次數(shù)和大小。減少減少 redo copy Latches 競爭競爭可以用增加 LOG_SIMULTANEOUS_COPIES 的值來增加 LATCH數(shù)，最多可以達(dá)到 CPU 的兩倍。減少減少 Free List 競爭競爭確定確定 Free List

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

19、_id+blocks ;5.使用如下查詢找到 FREE LIST:SELECT SEGMENT_NAME,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ù)庫文件的方針

20、：建立和優(yōu)化數(shù)據(jù)庫文件的方針：為表和索引創(chuàng)建不同的表空間。將表和索引的表空間放在不同的磁盤上。將 REDO 日志和回滾段放在不同的磁盤上。將 Oracle 可執(zhí)行文件和數(shù)據(jù)庫文件放在單獨(dú)的磁盤上。確定最常用的表、索引，以及他們的表空間，并放在單獨(dú)的磁盤上。不要在 Oracle 數(shù)據(jù)磁盤上安裝其他第三方軟件。監(jiān)控磁盤監(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

21、 v$datafile.file# = v$filestat.file# 結(jié)果如下：NAME PHYRDS PHYWRTS- - -/u/oracle/OraHome/oradata/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/

22、OraHome/oradata/pb/indx01.dbf 4 2/u/oracle/OraHome/oradata/pb/drsys01.dbf 4 27 rows selected.優(yōu)化回滾段優(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(v

23、alue) from v$sysstat where name in (consistent gets,db block gets) SUM(VALUE) - 20589 通過以下公式計(jì)算等待比率：通過以下公式計(jì)算等待比率：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 / t

24、otal reads若任何一個(gè)的比率大于若任何一個(gè)的比率大于 1則建議再創(chuàng)一個(gè)回滾段：則建議再創(chuàng)一個(gè)回滾段：create rollback segment rbs21 tablespace rbs storage (inittial 10k optimal 20k next 10k maxextents 8) ;避免動(dòng)態(tài)分配空間避免動(dòng)態(tài)分配空間用以下語句檢查回滾段的動(dòng)態(tài)分配：select name,shrinks from v$rollstat,v$rollname where v$rollstat.usn=v$roll ;NAME SHRINKS- -SYSTEM 0RBS0 100RBS1

25、 1若動(dòng)態(tài)分配次數(shù)較多可增大回滾段的初始容量。ALTER ROLLBACK SEGMENT RBS0 STORAGE (inittial 20k optimal 40k next 10k maxextents 8) ;優(yōu)化優(yōu)化 Redo 日志日志檢測檢測 Redo 日志緩沖區(qū)鎖存：日志緩沖區(qū)鎖存：select name,value from v$sysstatwhere name = rado log space requests ;value 值應(yīng)接近 0 若較大則應(yīng)加大 INITXXX.ORA 中的 LOG_BUFFER 項(xiàng)的值。減少減少 Redo 日志轉(zhuǎn)儲日志轉(zhuǎn)儲 I/O 爭用：爭用：將

26、 Redo 日志分布在不同磁盤中。1. 優(yōu)化系統(tǒng)全局區(qū)（優(yōu)化系統(tǒng)全局區(qū)（SGA）調(diào)整操作系統(tǒng)的內(nèi)存需求調(diào)整操作系統(tǒng)的內(nèi)存需求減少頁的換入換出；減少頁的換入換出；將將 SGA 置于主存之中置于主存之中 使用 SGA 的主要目的是為了在內(nèi)存中存儲數(shù)據(jù)，以利于快速訪問。通過設(shè)置初始化參數(shù) PRE_PAGE_SGA=YES，在數(shù)據(jù)庫啟動(dòng)時(shí)，可以將整個(gè) SGA 讀入內(nèi)存，這樣會減少在啟動(dòng)后 ORACLE 達(dá)到全部性能的總的時(shí)間。使用如下命令可以查看 SGA 所分配的內(nèi)存以及其內(nèi)部的結(jié)構(gòu)：SVRMGR SHOW SGATotal System Global Area 107720688 bytesFixe

27、d Size 69616 bytesVariable Size 90701824 bytesDatabase Buffers 16777216 bytesRedo Buffers 172032 bytes為單個(gè)用戶分配足夠的內(nèi)存為單個(gè)用戶分配足夠的內(nèi)存調(diào)整調(diào)整 redo log 緩沖區(qū)緩沖區(qū) 參數(shù) LOG_BUFFER 指定了 REDO LOG 的緩沖區(qū)的保留大小。LOG 寫進(jìn)程（LGWR）在該緩沖區(qū)被填充時(shí)總是運(yùn)行的，在新的 LOG 進(jìn)入緩沖區(qū)時(shí)，原來的 LOG 應(yīng)已經(jīng)寫入磁盤。調(diào)整私有調(diào)整私有 sql 和和 pl/sql 區(qū)區(qū)標(biāo)識不必要的語法分析調(diào)用標(biāo)識不必要的語法分析調(diào)用1.在在 sql

28、 跟蹤工具有效時(shí)運(yùn)行應(yīng)用跟蹤工具有效時(shí)運(yùn)行應(yīng)用2.查看視圖查看視圖 V$SQLAREA viewSVRMGR select sql_text,parse_calls,executions from v$sqlarea ;如果 parse_calls 值接近 execution 值，可能就是不斷地對 sql 語句進(jìn)行語法分析3.執(zhí)行如下查詢：執(zhí)行如下查詢：select *from v$statname where name in (parse_count(hard),execute_count);其結(jié)果類似于其結(jié)果類似于:statistic#, name-100parse_count90exec

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

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

31、) “Executions”,sum(reloads)”Cache Misses while Executing” from v$librarycache ;“Executions”列指明列指明 sql 語句，語句，pl/sql 塊和實(shí)體定義被訪問執(zhí)行了的次數(shù)，塊和實(shí)體定義被訪問執(zhí)行了的次數(shù)，”Cache Misses while Executing”指明其中沒有命中的次數(shù)。指明其中沒有命中的次數(shù)。2減少庫快存的非命中減少庫快存的非命中1、分配更多的庫快存（可以增加初始化參數(shù) shared pool size 的值；為了利用增加的共享sql 區(qū)，增加初始化參數(shù) open cursors 的值）

32、；2、盡可能使用標(biāo)準(zhǔn)的 sql 語句（sql 語句或 pl/sql 塊必須一致，包括字符和空格；sql 語句或 pl/sql 塊中對 schema 實(shí)體的引用必須解析到同一 schema 的同一對象；sql 語句中試用的變量的名字和數(shù)據(jù)類型必須匹配；sql 語句必須使用相同的優(yōu)化方法和優(yōu)化目標(biāo)）；盡可能使用標(biāo)準(zhǔn)的 sql 語句，策略：語句中盡量使用變量而不要使用常量確保應(yīng)用用戶不會改變優(yōu)化方法和目標(biāo)標(biāo)準(zhǔn)化的變量命名和空格轉(zhuǎn)換盡可能使用存儲過程3、使用 cursor_space_for_time 加速共享 sql 區(qū)的訪問：該參數(shù)指定是否共享 sql 區(qū)可以釋放，如果為 false（默認(rèn)值），一

33、個(gè)共享 sql 區(qū)就可以被釋放；如果為 true，一個(gè)共享的sql 區(qū)只存在所有與其相關(guān)的游標(biāo)關(guān)閉后才可以被釋放。如果庫快存在語句執(zhí)行時(shí)有非命中，不要將其設(shè)置為 true，否則對新的 sql 語句將沒有空間。調(diào)整調(diào)整 Data Dictionary Cache1、監(jiān)視數(shù)據(jù)字典快存的活動(dòng)監(jiān)視數(shù)據(jù)字典快存的活動(dòng)select sum(gets) “Data Dictionary Gets”, sum(getmisses) “Data Dictionary Cache Get Misses” from v$rowcache ;2、減少數(shù)據(jù)字典快存的非命中減少數(shù)據(jù)字典快存的非命中對頻繁訪問的數(shù)據(jù)字典快存

34、沒命中和命中比應(yīng)少于 1015。要增加數(shù)據(jù)字典快存可用的內(nèi)存數(shù)，需要增加初始參數(shù) shared_pool_size 的值。調(diào)整多線程服務(wù)器的共享池調(diào)整多線程服務(wù)器的共享池查詢動(dòng)態(tài)表查詢動(dòng)態(tài)表 v$sesstat 可以確定會話信息的大?。嚎梢源_定會話信息的大?。簊elect 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#;顯示信息如下：顯示信息

35、如下：Total memory for all sessions-157125 bytes 結(jié)果指明當(dāng)前分配給所有會話的內(nèi)存。結(jié)果指明當(dāng)前分配給所有會話的內(nèi)存。調(diào)整共享池的保留空間調(diào)整共享池的保留空間shared_pool_reserved_size 為大的存儲保留的 shared_pool_size 總量；shared_pool_reserved_min_alloc控制保留內(nèi)存的分配；要?jiǎng)?chuàng)建一個(gè)保留列表，shared_pool_reserved_size 必須大于shared_pool_reserved_min_alloc。優(yōu)化共享池：優(yōu)化共享池：用以下語句檢測命中率：select sum(

36、pins)pinned,sum(reloads)reloaded from v$librarycache pinned reloaded - - 2474 17命中率pinned/ reloaded若命中率低可增大 INIT.ORA 中 SHARED_POOL_SIZE 的值。調(diào)整快速緩存調(diào)整快速緩存利用緩存的命中率評價(jià)快速緩存的活動(dòng)利用緩存的命中率評價(jià)快速緩存的活動(dòng)select name,value from v$sysstat where name in (db block gets,consistent_gets,physical reads);db block gets, consis

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

38、命中。查詢 v$recent_bucket 視圖：select sum(count) ach from v$recent_bucket where rownum=90 ;計(jì)算命中率（ACM 為增加的非命中次數(shù)）：Hit Ratio=1-(physical reads + ACM/(db block gets+consistent gets);也可以將 100 個(gè)緩沖區(qū)分為 4 個(gè) 25 個(gè)緩沖區(qū)段：select 25*TRUNC(ROWNUM/25)+1|to|25*(TRUNC(ROWNUM/25+1)”Interval”,sum(count)”Buffer Cache Hits” from

39、 v$CURRENT_BUCKET where rownum0 group by TRUNC(ROWNUM/25);其中其中 INTERVAL 為緩沖區(qū)段，為緩沖區(qū)段，BUFFER CACHE HITS 為對應(yīng)段的緩存命中率。為對應(yīng)段的緩存命中率。優(yōu)化數(shù)據(jù)庫緩沖區(qū)高速緩存優(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 17603phys

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

41、體數(shù)據(jù)段的數(shù)目：select data_object_id,object_type from user_objects where object_name= ;2查找對應(yīng)該段名的緩存中的緩沖區(qū)數(shù)：查找對應(yīng)該段名的緩存中的緩沖區(qū)數(shù)：select count(*) buffers from x$bh where obj= ;3查找實(shí)例中的緩沖區(qū)總數(shù)：查找實(shí)例中的緩沖區(qū)總數(shù)：select value “total buffers ” from v$parameter where name=db_block_buffers ;4計(jì)算當(dāng)前有指定段使用的緩存所占的比率計(jì)算當(dāng)前有指定段使用的緩存所占的比率:

42、%cache used by segment_name=buffers(step 2)/buffers(step 3);使用多緩沖池調(diào)節(jié)緩存使用多緩沖池調(diào)節(jié)緩存減少 I/O 操作；隔離緩存中的實(shí)體；限制實(shí)體在緩存中的一個(gè)部分使多緩沖池生效使多緩沖池生效定義新緩沖池：使用初始參數(shù) BUFFER_POOL_NAME 來定義緩沖池，每個(gè)緩沖池可以指定兩個(gè)屬性：緩沖池中的緩沖區(qū)數(shù)；分配該緩沖池的 LRU latches 數(shù)。用于定義緩沖池的初始參數(shù)有：用于定義緩沖池的初始參數(shù)有：BUFFER_POOL_KEEP定義 keep 緩沖池BUFFER_POOL_RECYCLE定義 recycle 緩沖池DB

43、_BLOCK_BUFFERS定義數(shù)據(jù)庫實(shí)例的緩沖區(qū)數(shù)DB_BLOCK_LRU_LATCHES定義數(shù)據(jù)庫實(shí)例的 LRU latches 數(shù)Oracle8 定義了三個(gè)緩沖池：keep，recycle 和 default。其中 default 緩沖池是已經(jīng)存在的。使用多緩沖池使用多緩沖池為一個(gè)實(shí)體建立一個(gè)默認(rèn)緩沖池，語法為：為一個(gè)實(shí)體建立一個(gè)默認(rèn)緩沖池，語法為：BUFFER_POOLKEEP | RECYCLE | DEFAULT如何為各緩沖池設(shè)置大小如何為各緩沖池設(shè)置大小1.KEEP 緩沖池緩沖池使用 keep 緩沖池的目的是將實(shí)體保留在內(nèi)存，避免 I/O 操作select physical_re

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

45、池中由本來需要置于 recycle 緩沖池中數(shù)據(jù)段的緩沖區(qū)數(shù)，除以 4，該結(jié)果就可以用作 recycle 緩沖池的大小。3.確定數(shù)據(jù)段置于確定數(shù)據(jù)段置于 keep 還是還是 recycle 緩沖池緩沖池對于至少兩倍 DEFAULT 緩沖池大小，并且占用系統(tǒng)整個(gè) I/O 的一定百分比的數(shù)據(jù)段，那么最好放置于 recycle 緩沖池；對于大小小于 DEFAULT 緩沖池的 10，并且占用至少系統(tǒng)整個(gè) I/O 的 1的數(shù)據(jù)段，那么最好放置于 keep 緩沖池；如果對表空間超過一個(gè)段時(shí)，可以通過查詢 V$SESSION_WAIT 確定每個(gè)段的 I/O 操作。如何識別和減少如何識別和減少 LRU Lat

46、ch 競爭競爭 LRU latches 規(guī)劃了那些緩存中最近最少使用的緩沖區(qū)列表，使用DB_BLOCK_LRU_LATCHES 參數(shù)可以設(shè)置數(shù)據(jù)庫實(shí)例中的 latches 總數(shù)?？梢酝ㄟ^如下語句確定系統(tǒng)中是否有 latch 競爭：select child#,sleeps/gets ratio from v$latch_children where name=cache buffers lru chain ;每個(gè) LRU latch 的非命中率應(yīng)少于 1，任何大于 1的 latch 說明存在競爭，通過如下語句查找出來：select name from v$buffer_pool_statisti

47、cs 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ū)調(diào)整排序區(qū) 如果經(jīng)常進(jìn)行大的排序，應(yīng)增大參數(shù) SORT_AREA_SIZE 的值，將參數(shù) SORT_AREA_SIZE和 SORT_AREA_RETAINED_SIZE 聯(lián)合使用，更有利于使用大的排序區(qū)。重新分配內(nèi)存重新分配內(nèi)存 在重新定義了 oracle 內(nèi)存結(jié)構(gòu)、調(diào)整了庫快存、數(shù)

48、據(jù)字典快存和快速緩存后，如果減少了這些結(jié)構(gòu)中某一個(gè)結(jié)構(gòu)的內(nèi)存使用，就可能需要將內(nèi)存分配到其他結(jié)構(gòu)中；在改變了 oracle 內(nèi)存結(jié)構(gòu)以后，oracle 的內(nèi)存需求也會改變。減少整個(gè)內(nèi)存使用減少整個(gè)內(nèi)存使用采用如下方法：采用如下方法：增加可用內(nèi)存總數(shù)；減少內(nèi)存使用優(yōu)化數(shù)據(jù)字典高速緩存：優(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/ Rea

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