Oracle性能優(yōu)化總結(jié)材料

1、個(gè)人理解，數(shù)據(jù)庫(kù)性能最關(guān)鍵的因素在于10,因?yàn)椴僮鲀?nèi)存是快速的，但是讀寫(xiě)磁盤(pán)是速度很慢的，優(yōu)化數(shù)據(jù)庫(kù)最關(guān)鍵的問(wèn)題在于減少磁盤(pán)的10,就個(gè)人理解應(yīng)該分為物理的和邏輯的優(yōu)化，物理的是指oracle產(chǎn)品本身的一些優(yōu)化，邏輯優(yōu)化是指應(yīng)用程序級(jí)別的優(yōu)化物理優(yōu)化：、優(yōu)化內(nèi)存內(nèi)存是指OracleSGA(系統(tǒng)全局區(qū))，它由共享池、數(shù)據(jù)緩沖區(qū)、日志緩沖區(qū)組成；共享池又分為共享SQL區(qū)和數(shù)據(jù)字典緩存區(qū)。1數(shù)據(jù)庫(kù)管理員通過(guò)執(zhí)行下述語(yǔ)句，來(lái)查看共享sql區(qū)的使用率select(sum(pins-reloads)/sum(pins)"libCache"fromv$librarycache(DBA權(quán)限

2、)共享SQL區(qū)的使用率應(yīng)該在90%以上，否則增加共享池大小v$rowcache視圖結(jié)構(gòu)NAMESPACEVARCHAR2(15)名稱(chēng)空間，例如：sql_area,indexGETSNUMBER請(qǐng)求的語(yǔ)句句柄數(shù)GETHITSNUMBER獲得的句柄數(shù)GETHITRATIONUMBER前兩者之比PINSNUMBER根據(jù)句柄查找對(duì)像并執(zhí)行的次數(shù)PINHITSNUMBER對(duì)象在內(nèi)存中的次數(shù)PINHITRATIONUMBER前兩者之比RELOADSNUMBER由于是第一次執(zhí)行,或者語(yǔ)句體被調(diào)出內(nèi)存時(shí)需要重新reload次數(shù)oracle執(zhí)行一條語(yǔ)句會(huì)首先去獲得該語(yǔ)句句柄(gets),然后根據(jù)句柄查找對(duì)應(yīng)的語(yǔ)

3、句，對(duì)像(pins)執(zhí)行，如果該語(yǔ)句體因?yàn)槟承┮驗(yàn)闆](méi)有在內(nèi)存中則需要重載語(yǔ)句體(reloads)所以reloads最好不要超過(guò)1%,sum(pinhits)/sum(pins)要達(dá)到95%以上.sum(gethits)/sum(gets)命中率也應(yīng)在95%以上.2. 數(shù)據(jù)庫(kù)管理員可以執(zhí)行下述語(yǔ)句，查看數(shù)據(jù)字典緩沖區(qū)的使用率select(sum(gets-getmisses-usage-fixed)/sum(gets)"RowCache"fromv$rowcache(DBA權(quán)限)數(shù)據(jù)字典緩沖區(qū)也應(yīng)該在90%以上，否則增加共享池大小。本視圖顯示數(shù)據(jù)字典緩存(也叫rowcache

4、)的各項(xiàng)統(tǒng)計(jì)。每一條記錄包含不同類(lèi)型的數(shù)據(jù)字典緩存數(shù)據(jù)統(tǒng)計(jì)，注意數(shù)據(jù)字典緩存有層次差別，因此同樣的緩存名稱(chēng)可能不止一次出現(xiàn)V$ROWCACHE視圖結(jié)構(gòu)PARAMETERVARCHAR2(15)緩存名COUNTNUMBER緩存項(xiàng)總數(shù)USAGENUMBER包含有效數(shù)據(jù)的緩存項(xiàng)數(shù)GETSNUMBER請(qǐng)求總數(shù)GETMISSESNUMBER請(qǐng)求失敗數(shù)SCANSNUMBER掃描請(qǐng)求數(shù)SCANMISSESNUMBER掃描請(qǐng)求失敗次數(shù)MODIFICATIONSNUMBER添加'修改'刪除操作數(shù)DLM_REQUESTSNUMBERDLM請(qǐng)求數(shù)DLM_CONFLICTSNUMBERDLM沖突數(shù)DL

5、M_RELEASESNUMBERDLM釋放數(shù)3. 管理員可以通過(guò)下述語(yǔ)句來(lái)查看數(shù)據(jù)緩沖區(qū)的使用情況selectname,valuefromv$sysstatwherenamein('dbblockgets','consistentgets','physicalreads');數(shù)據(jù)緩沖區(qū)使用命中率(physicalreads除以dbblockgets加consistentgets之和)一定要小于10%，否則需要增加數(shù)據(jù)緩沖區(qū)大小4. 管理員可以通過(guò)執(zhí)行下述語(yǔ)句，查看日志緩沖區(qū)的使用情況selectname,valuefromv$sysstatwhe

6、renamein('redoentries','redologspacerequests')根據(jù)查詢(xún)出的結(jié)果可以計(jì)算出日志緩沖區(qū)的申請(qǐng)失敗率：requests除以entries申請(qǐng)失敗率應(yīng)該解決與0否則說(shuō)明日志緩沖區(qū)開(kāi)設(shè)太小，需要增加Oracle數(shù)據(jù)庫(kù)的日志緩沖區(qū)二、物理I/0的優(yōu)化1在磁盤(pán)上建立數(shù)據(jù)文件前首先運(yùn)行磁盤(pán)碎片整理程序?yàn)榱税踩卣泶疟P(pán)碎片，需關(guān)閉打開(kāi)數(shù)據(jù)文件的實(shí)例，并且停止服務(wù)。如果有足夠的連續(xù)磁盤(pán)空間建立數(shù)據(jù)文件，那么就容易避免數(shù)據(jù)文件產(chǎn)生碎片。2. 不要使用磁盤(pán)壓縮(Oracle文件不支持磁盤(pán)壓縮)3. 不要使用磁盤(pán)加密加密像磁盤(pán)壓縮一樣加了一

7、個(gè)處理層，降低磁盤(pán)讀寫(xiě)速度。如果擔(dān)心自己的數(shù)據(jù)可能泄露,可以使用dbms_obfuscation包和labelsecurity選擇性地加密數(shù)據(jù)的敏感部分4使用RAIDraid使用應(yīng)注意：選擇硬件raid超過(guò)軟件raid；日志文件不要放在raid5卷上，因?yàn)閞aid5讀性能高而寫(xiě)性能差；把日志文件和歸檔日志放在與控制文件和數(shù)據(jù)文件分離的磁盤(pán)控制系統(tǒng)上5. 分離頁(yè)面交換文件到多個(gè)磁盤(pán)物理卷跨越至少兩個(gè)磁盤(pán)建立兩個(gè)頁(yè)面文件?？梢越⑺膫€(gè)頁(yè)面文件并在性能上受益，確保所有頁(yè)面文件的大小之和至少是物理內(nèi)存的兩倍。三、cpu優(yōu)化調(diào)整1. cpu使用情況般unix操作系統(tǒng)，可以使用sar-u命令查看cpu的使

8、用率；NT操作系統(tǒng)的服務(wù)器，可以使用NT的性能管理器來(lái)查看CPU的使用率出現(xiàn)CPU資源不足的情況很多：SQL語(yǔ)句的重解析、低效率的SQL語(yǔ)句、鎖沖突都會(huì)引起cpu資源不足2. 查看sql語(yǔ)句的解析情況數(shù)據(jù)庫(kù)管理員可以執(zhí)行下述語(yǔ)句來(lái)查看SQL語(yǔ)句的解析情況：select*fromv$sysstatwherenamein('parsetimecpu','parsetimeelapsed','parsecount(hard)');這里parse_time_cpu是系統(tǒng)服務(wù)時(shí)間，parse_time_elapsed是響應(yīng)時(shí)間。waite_time二par

9、se_time_elapsed-parse_time_cpu由此可以得到用戶(hù)SQL語(yǔ)句平均解析等待時(shí)間：用戶(hù)SQL語(yǔ)句平均解析等待時(shí)間二waitetime/parsecount數(shù)據(jù)庫(kù)管理員還可以通過(guò)下述語(yǔ)句，查看低效率的SQL語(yǔ)句selectbuffer_gets,executlons,sql_textfromv$sqlarea;優(yōu)化這些低效率的SQL語(yǔ)句也有助于提高CPU的利用率3. 查看Oracle數(shù)據(jù)庫(kù)的沖突情況數(shù)據(jù)庫(kù)管理員可以通過(guò)v$system_event數(shù)據(jù)字典中的"latchfree"統(tǒng)計(jì)項(xiàng)查看Oracle數(shù)據(jù)庫(kù)的沖突情況，如果沒(méi)有沖突的話，latchfree

10、查詢(xún)出來(lái)沒(méi)有結(jié)果。如果沖突太大的話，數(shù)據(jù)庫(kù)管理員可以降低spin_count參數(shù)值，來(lái)消除4. cpu優(yōu)化取消屏幕保護(hù)、把系統(tǒng)配置為應(yīng)用服務(wù)器、監(jiān)視系統(tǒng)中消耗中斷的硬件、保持最小的安全審計(jì)記錄、在專(zhuān)門(mén)服務(wù)器上運(yùn)行Oracle、禁止非必須的服務(wù)四、網(wǎng)絡(luò)配置優(yōu)化網(wǎng)絡(luò)配置是性能調(diào)整的一項(xiàng)很重要的內(nèi)容，而且很容易隱藏性能瓶頸配置網(wǎng)卡使用最快和有效模式、刪除不需要的網(wǎng)絡(luò)協(xié)議、優(yōu)化網(wǎng)絡(luò)協(xié)議綁定順序，為Oracle禁止或優(yōu)化文件共享五、使用CBO優(yōu)化器參見(jiàn)本目錄CBD優(yōu)化器六、合理配置oracle實(shí)例參數(shù)序號(hào)參數(shù)名稱(chēng)建議值說(shuō)明1log_chcckpoint_intcrval12log_checkpoint_

11、timeout03parallcl_automatic_tuningTRUE4OPEN_CURSORS10245Dml_locks100006MTS_servers10根據(jù)用戶(hù)數(shù)量可適當(dāng)調(diào)整，一般為用戶(hù)數(shù)量的10%7cursor_sharingSIMILAR七、索引優(yōu)化（減少I(mǎi)O）如何某表的某個(gè)字段有主鍵約束和唯一性約束，則Oracle則會(huì)自動(dòng)在相應(yīng)的約束列上建議唯一索引。數(shù)據(jù)庫(kù)索引主要進(jìn)行提高訪問(wèn)速度。建設(shè)原則：1、索引應(yīng)該經(jīng)常建在Where子句經(jīng)常用到的列上。如果某個(gè)大表經(jīng)常使用某個(gè)字段進(jìn)行查詢(xún)，并且檢索行數(shù)小于總表行數(shù)的5%。則應(yīng)該考慮。2、對(duì)于兩表連接的字段，應(yīng)該建立索引。經(jīng)常在某表的

12、一個(gè)字段進(jìn)行OrderBy則也經(jīng)過(guò)進(jìn)行索引。3、不應(yīng)該在小表上建設(shè)索引。優(yōu)缺點(diǎn)：索引主要進(jìn)行提高數(shù)據(jù)的查詢(xún)速度。當(dāng)進(jìn)行DML時(shí)，會(huì)更新索引。因此索引越多，則DML越慢，其需要維護(hù)索引。因此在創(chuàng)建索引及DML需要權(quán)衡。創(chuàng)建索引：?jiǎn)我凰饕?CreateIndex<lndex-Name>On<Table_Name>(Column_Name);復(fù)合索弓I：CreateIndexi_deptno_jobonemp(deptno,job);>在emp表的deptno、job列建立索引。select*fromempwheredeptno=66andjob二'sals&#

13、39;走索弓I。select*fromempwheredeptno=66ORjob二'sals'->將進(jìn)行全表掃描。不走索引select*fromempwheredeptno二66->走索弓I。select*fromempwherejob二'sals'->進(jìn)行全表掃描、不走索引。如果在where子句中有OR操作符或單獨(dú)引用Job歹U(索引列的后面列)則將不會(huì)走索引，將會(huì)進(jìn)行全表掃描。4分析表與索引(analyze不會(huì)重建索引)analyzetabletablenamecomputestatistics等同于analyzetabletablena

14、mecomputestatisticsfortableforallindexesforallcolumnsfortable的統(tǒng)計(jì)信息存在于視圖：user_tables、all_tables、dba_tablesforallindexes的統(tǒng)計(jì)信息存在于視圖：user_indexes、all_indexes、dba_indexesforallcolumns的統(tǒng)計(jì)信息存在于視圖：user_tab_columns、all_tab_columns、dba_tab_columns注：分析表與索引見(jiàn)AnalyzeAllTable存儲(chǔ)過(guò)程5、一般來(lái)講可以采用以下三種方式來(lái)手工分析索引。analyzeinde

15、xidx_tvalidatestructure：analyzeindexidx_tcomputestatistics：analyzeindexidx_testimatestatisticssample10percent1) analyzeindexidx_tvalidatestructure：這段分析語(yǔ)句是用來(lái)分析索引的block中是否有壞塊兒,那么根據(jù)分析我們可以得到索引的結(jié)構(gòu)數(shù)據(jù)，這些數(shù)據(jù)會(huì)保留到index_stats中，來(lái)判斷這個(gè)索引是否需要rebuild.需要注意的是這樣的分析是不會(huì)收集索引的統(tǒng)計(jì)信息的。2) validatestrueture有二種模式：online,offline,

16、般來(lái)講默認(rèn)的方式是offline。當(dāng)以offline的模式analyze索引時(shí)，會(huì)對(duì)table加一個(gè)表級(jí)共享鎖，對(duì)目前table的一些實(shí)時(shí)DMl操作會(huì)產(chǎn)生一定的影響。而以online模式分析時(shí)候，則不會(huì)加任何lock,但在index_stats中是看不到任何信息的。3) analyzeindexidx_tcomputestatistics：用來(lái)統(tǒng)計(jì)索引的統(tǒng)計(jì)信息(全分析)，主要為CBO服務(wù)。4) analyzeindexidx_testimatestatisticssample10percent主要是用來(lái)指定比例進(jìn)行抽樣分析，也是為CBO服務(wù).例中是抽樣10%6. 常用來(lái)表連接的字段應(yīng)用聚簇索

17、引先建聚簇：這個(gè)跟索引列類(lèi)型一致createclusteremp_dept_cluster(deptnonumber(2)size1024建立聚簇索引，把索引建在聚簇上createindexemp_dept_cluster_idxonclusteremp_dept_cluster建表使用聚簇createtabledept(deptnonumber(2)primarykey,dnamevarchar2(14),locvarchar2(13)clusteremp_dept_cluster(deptno)八、建立表分區(qū)，將索引數(shù)據(jù)和表數(shù)據(jù)分開(kāi)在不同的表空間上對(duì)于邏輯結(jié)構(gòu)的優(yōu)化，還應(yīng)將表數(shù)據(jù)和索引數(shù)據(jù)

18、分開(kāi)表空間存儲(chǔ)，分別使用獨(dú)立的表空間。因?yàn)槿绻麑⒈頂?shù)據(jù)和索引數(shù)據(jù)放在一起，表數(shù)據(jù)的I/O操作和索引的I/O操作將產(chǎn)生影響系統(tǒng)性能的I/O競(jìng)爭(zhēng),降低系統(tǒng)的響應(yīng)效率。將表數(shù)據(jù)和索引數(shù)據(jù)存放在不同的表空間中,并在物理層面將這兩個(gè)表空間的數(shù)據(jù)文件放在不同的物理磁盤(pán)上，就可以避免這種競(jìng)爭(zhēng)了。邏輯優(yōu)化：、可以對(duì)表進(jìn)行邏輯分割如中國(guó)移動(dòng)用戶(hù)表，可以根據(jù)手機(jī)尾數(shù)分成10個(gè)表，這樣對(duì)性能會(huì)有一定的作用二、Sql語(yǔ)句使用占位符語(yǔ)句，并且必須按照規(guī)定編寫(xiě)sql語(yǔ)句(如全部大寫(xiě)，全部小寫(xiě)等)oracle解析語(yǔ)句后會(huì)放置到共享池中如：select*fromEmpwherename二？這個(gè)語(yǔ)句只會(huì)在共享池中有一條，而如果

19、是字符串的話，那就根據(jù)不同名字存在不同的語(yǔ)句，所以占位符效率較好三、一些耗時(shí)的操作，可以通過(guò)存儲(chǔ)過(guò)程等在用戶(hù)較少的情況下執(zhí)行數(shù)據(jù)庫(kù)不僅僅是一個(gè)存儲(chǔ)數(shù)據(jù)的地方，同樣是一個(gè)編程的地方，一些耗時(shí)的操作，可以通過(guò)存儲(chǔ)過(guò)程等在用戶(hù)較少的情況下執(zhí)行，從而錯(cuò)開(kāi)系統(tǒng)使用的高峰時(shí)間，提高數(shù)據(jù)庫(kù)性能四、盡量不要整表查詢(xún)，盡量用wherehaving語(yǔ)句篩選如select*fromEmp,因?yàn)橐D(zhuǎn)化為具體的列名是要查數(shù)據(jù)字典，比較耗時(shí)五、選擇有效的表名對(duì)于多表連接查詢(xún)，可能oracle的優(yōu)化器并不會(huì)優(yōu)化到這個(gè)程度，oracle中多表查詢(xún)是根據(jù)FROM字句從右到左的數(shù)據(jù)進(jìn)行的，那么最好右邊的表(也就是基礎(chǔ)表)選擇數(shù)據(jù)較少的表，這