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

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

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

3、ns)執(zhí)行,如果該語句體因?yàn)槟承┮驗(yàn)闆]有在內(nèi)存中則需要重載語句體(reloads)所以reloads最好不要超過1%,sum(pinhits)/sum(pins)要達(dá)到95%以上.sum(gethits)/sum(gets)命中率也應(yīng)在95%以上.2.數(shù)據(jù)庫管理員可以執(zhí)行下述語句，查看數(shù)據(jù)字典緩沖區(qū)的使用率select(sum(gets-getmisses-usage-fixed)/sum(gets)RowCachefromv$rowcache（DBA權(quán)限）數(shù)據(jù)字典緩沖區(qū)也應(yīng)該在90%以上，否則增加共享池大小。本視圖顯示數(shù)據(jù)字典緩存(也叫rowcache)的各項(xiàng)統(tǒng)計。每一條記錄包含不同類型的數(shù)

4、據(jù)字典緩存數(shù)據(jù)統(tǒng)計，注意數(shù)據(jù)字典緩存有層次差別，因此同樣的緩存名稱可能不止一次出現(xiàn)V$ROWCACHE視圖結(jié)構(gòu)PARAMETERVARCHAR2(15)緩存名COUNTNUMBER緩存項(xiàng)總數(shù)USAGENUMBER包含有效數(shù)據(jù)的緩存項(xiàng)數(shù)GETSNUMBER請求總數(shù)GETMISSESNUMBER請求失敗數(shù)SCANSNUMBER掃描請求數(shù)SCANMISSESNUMBER掃描請求失敗次數(shù)MODIFICATIONSNUMBER添加、修改、刪除操作數(shù)DLM_REQUESTSNUMBERDLM請求數(shù)DLM_CONFLICTSNUMBERDLM沖突數(shù)DLM_RELEASESNUMBERDLM釋放數(shù)3.管理員可

5、以通過下述語句來查看數(shù)據(jù)緩沖區(qū)的使用情況selectname,value fromv$sysstatwherenamein(dbblockgets,consistentgets,physicalreads);數(shù)據(jù)緩沖區(qū)使用命中率（physicalreads除以dbblockgets加consistentgets之和)一定要小于10%，否則需要增加數(shù)據(jù)緩沖區(qū)大小4.管理員可以通過執(zhí)行下述語句，查看日志緩沖區(qū)的使用情況select name,value from v$sysstat where name in (redo entries,redo log space requests)根據(jù)查詢出的

6、結(jié)果可以計算出日志緩沖區(qū)的申請失敗率： requests除以entries申請失敗率應(yīng)該解決與0，否則說明日志緩沖區(qū)開設(shè)太小，需要增加Oracle數(shù)據(jù)庫的日志緩沖區(qū)二、物理I/0的優(yōu)化1.在磁盤上建立數(shù)據(jù)文件前首先運(yùn)行磁盤碎片整理程序?yàn)榱税踩卣泶疟P碎片，需關(guān)閉打開數(shù)據(jù)文件的實(shí)例，并且停止服務(wù)。如果有足夠的連續(xù)磁盤空間建立數(shù)據(jù)文件，那么就容易避免數(shù)據(jù)文件產(chǎn)生碎片。2.不要使用磁盤壓縮（Oracle文件不支持磁盤壓縮）3.不要使用磁盤加密加密像磁盤壓縮一樣加了一個處理層，降低磁盤讀寫速度。如果擔(dān)心自己的數(shù)據(jù)可能泄露，可以使用dbms_obfuscation包和label security選擇性

7、地加密數(shù)據(jù)的敏感部分4.使用RAIDraid使用應(yīng)注意：選擇硬件raid超過軟件raid；日志文件不要放在raid5卷上，因?yàn)閞aid5讀性能高而寫性能差；把日志文件和歸檔日志放在與控制文件和數(shù)據(jù)文件分離的磁盤控制系統(tǒng)上5.分離頁面交換文件到多個磁盤物理卷跨越至少兩個磁盤建立兩個頁面文件?？梢越⑺膫€頁面文件并在性能上受益，確保所有頁面文件的大小之和至少是物理內(nèi)存的兩倍。三、cpu優(yōu)化調(diào)整1.cpu使用情況一般unix操作系統(tǒng)，可以使用sar -u命令查看cpu的使用率；NT操作系統(tǒng)的服務(wù)器，可以使用NT的性能管理器來查看CPU的使用率出現(xiàn)CPU資源不足的情況很多：SQL語句的重解析、低效率的

8、SQL語句、鎖沖突都會引起cpu資源不足2.查看sql語句的解析情況數(shù)據(jù)庫管理員可以執(zhí)行下述語句來查看SQL語句的解析情況：select * from v$sysstat where name in (parse time cpu,parse time elapsed,parse count (hard);這里parse_time_cpu是系統(tǒng)服務(wù)時間，parse_time_elapsed是響應(yīng)時間。waite_time = parse_time_elapsed-parse_time_cpu由此可以得到用戶SQL語句平均解析等待時間:用戶SQL語句平均解析等待時間=waite time/par

9、se count數(shù)據(jù)庫管理員還可以通過下述語句，查看低效率的SQL語句select buffer_gets,executlons,sql_text from v$sqlarea;優(yōu)化這些低效率的SQL語句也有助于提高CPU的利用率3.查看Oracle數(shù)據(jù)庫的沖突情況數(shù)據(jù)庫管理員可以通過v$system_event數(shù)據(jù)字典中的latch free統(tǒng)計項(xiàng)查看Oracle數(shù)據(jù)庫的沖突情況，如果沒有沖突的話，latch free查詢出來沒有結(jié)果。如果沖突太大的話，數(shù)據(jù)庫管理員可以降低spin_count參數(shù)值，來消除4.cpu優(yōu)化取消屏幕保護(hù)、把系統(tǒng)配置為應(yīng)用服務(wù)器、監(jiān)視系統(tǒng)中消耗中斷的硬件、保持最小

10、的安全審計記錄、在專門服務(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)化器參見本目錄CBD優(yōu)化器六、合理配置oracle實(shí)例參數(shù)序號參數(shù)名稱建議值說明1log_chcckpoint_intcrval12log_checkpoint_timeout03parallcl_automatic_tuningTRUE4OPEN_CURSORS10245Dml_locks100006MTS_servers10根據(jù)用戶數(shù)

11、量可適當(dāng)調(diào)整，一般為用戶數(shù)量的10%7cursor_sharingSIMILAR七、索引優(yōu)化（減少IO）如何某表的某個字段有主鍵約束和唯一性約束，則Oracle 則會自動在相應(yīng)的約束列上建議唯一索引。數(shù)據(jù)庫索引主要進(jìn)行提高訪問速度。建設(shè)原則:1、索引應(yīng)該經(jīng)常建在Where 子句經(jīng)常用到的列上。如果某個大表經(jīng)常使用某個字段進(jìn)行查詢，并且檢索行數(shù)小于總表行數(shù)的5%。則應(yīng)該考慮。2、對于兩表連接的字段，應(yīng)該建立索引。經(jīng)常在某表的一個字段進(jìn)行Order By 則也經(jīng)過進(jìn)行索引。3、不應(yīng)該在小表上建設(shè)索引。優(yōu)缺點(diǎn):索引主要進(jìn)行提高數(shù)據(jù)的查詢速度。 當(dāng)進(jìn)行DML時，會更新索引。因此索引越多，則DML越慢，

12、其需要維護(hù)索引。 因此在創(chuàng)建索引及DML需要權(quán)衡。創(chuàng)建索引:單一索引:Create Index On (Column_Name);復(fù)合索引: Create Index i_deptno_job on emp(deptno,job); 在emp表的deptno、job列建立索引。select * from emp where deptno=66 and job=sals -走索引。select * from emp where deptno=66 OR job=sals -將進(jìn)行全表掃描。不走索引select * from emp where deptno=66 -走索引。select * fr

13、om emp where job=sals -進(jìn)行全表掃描、不走索引。如果在where 子句中有OR 操作符或單獨(dú)引用Job 列(索引列的后面列) 則將不會走索引，將會進(jìn)行全表掃描。4.分析表與索引（analyze 不會重建索引）analyze table tablename compute statistics等同于 analyze table tablename compute statistics for table for all indexes for all columnsfor table 的統(tǒng)計信息存在于視圖：user_tables 、all_tables、dba_tables

14、for all indexes 的統(tǒng)計信息存在于視圖: user_indexes 、all_indexes、dba_indexesfor all columns 的統(tǒng)計信息存在于視圖：user_tab_columns、all_tab_columns、dba_tab_columns注：分析表與索引見 AnalyzeAllTable存儲過程5、一般來講可以采用以下三種方式來手工分析索引。analyze index idx_t validate structure：analyze index idx_t compute statistics：analyze index idx_t estimate

15、statistics sample 10 percent1）analyze index idx_t validate structure：這段分析語句是用來分析索引的block中是否有壞塊兒，那么根據(jù)分析我們可以得到索引的結(jié)構(gòu)數(shù)據(jù)，這些數(shù)據(jù)會保留到index_stats中，來判斷這個索引是否需要rebuild. 需要注意的是這樣的分析是不會收集索引的統(tǒng)計信息的。2）validate structure有二種模式： online, offline， 一般來講默認(rèn)的方式是offline。當(dāng)以offline的模式analyze索引時，會對table加一個表級共享鎖，對目前table的一些實(shí)時DMl操

16、作會產(chǎn)生一定的影響。而以online模式分析時候，則不會加任何lock，但在index_stats中是看不到任何信息的。3）analyze index idx_t compute statistics：用來統(tǒng)計索引的統(tǒng)計信息(全分析)，主要為CBO服務(wù)。4）analyze index idx_t estimate statistics sample 10 percent主要是用來指定比例進(jìn)行抽樣分析，也是為CBO服務(wù). 例中是抽樣10%6.常用來表連接的字段應(yīng)用聚簇索引先建聚簇：這個跟索引列類型一致create cluster emp_dept_cluster( deptno number(2

17、) )size 1024建立聚簇索引 ，把索引建在聚簇上create index emp_dept_cluster_idxon cluster emp_dept_cluster建表使用聚簇create table dept( deptno number(2) primary key, dname varchar2(14), loc varchar2(13)cluster emp_dept_cluster(deptno)八、建立表分區(qū)，將索引數(shù)據(jù)和表數(shù)據(jù)分開在不同的表空間上對于邏輯結(jié)構(gòu)的優(yōu)化，還應(yīng)將表數(shù)據(jù)和索引數(shù)據(jù)分開表空間存儲，分別使用獨(dú)立的表空間。因?yàn)槿绻麑⒈頂?shù)據(jù)和索引數(shù)據(jù)放在一起，表數(shù)據(jù)的

18、I/O操作和索引的I/O操作將產(chǎn)生影響系統(tǒng)性能的I/O競爭，降低系統(tǒng)的響應(yīng)效率。將表數(shù)據(jù)和索引數(shù)據(jù)存放在不同的表空間中，并在物理層面將這兩個表空間的數(shù)據(jù)文件放在不同的物理磁盤上，就可以避免這種競爭了。邏輯優(yōu)化：一、可以對表進(jìn)行邏輯分割如中國移動用戶表，可以根據(jù)手機(jī)尾數(shù)分成10個表，這樣對性能會有一定的作用二、Sql語句使用占位符語句，并且必須按照規(guī)定編寫sql語句（如全部大寫，全部小寫等）oracle解析語句后會放置到共享池中如： select * from Emp where name=?這個語句只會在共享池中有一條，而如果是字符串的話，那就根據(jù)不同名字存在不同的語句，所以占位符效率較好三、一些耗時的操作，可以通過存儲過程等在用戶較少的情況下執(zhí)行數(shù)據(jù)庫不僅僅是一個存儲數(shù)據(jù)的地方，同樣是一個編程的地方，一些耗時的操作，可以通過存儲過程等在用戶較少的情況下執(zhí)行，從而錯開系統(tǒng)使用的高峰時間，提高數(shù)據(jù)庫性能四、 盡量不要整表查詢，盡量用where having語句篩選如select * from Emp，因?yàn)橐D(zhuǎn)化為具體的列名是要查數(shù)據(jù)字典，比較耗時五、選擇有效的表名對于多表連接查詢，可能oracle的優(yōu)化器并不會優(yōu)化到這個程度， oracle 中多表查詢是根據(jù)FROM字句從右到左的數(shù)據(jù)進(jìn)行的，那么最好右邊的表（也就是基礎(chǔ)表）選擇數(shù)據(jù)較少的表，這樣排序更快速，如果有l(wèi)ink表（