在這里我們將介紹Oracle數(shù)據(jù)庫(kù)優(yōu)化方案與實(shí)踐，不同的環(huán)境會(huì)有不同的調(diào)試，但是也會(huì)有差別，希望大家能合理的吸收。
一、前言
二、ORACLE數(shù)據(jù)庫(kù)優(yōu)化概述
1、內(nèi)存等參數(shù)配置的優(yōu)化
2、減少物理讀寫的優(yōu)化
3、批量重復(fù)操作的SQL語句及大表操作的優(yōu)化
二、ORACLE數(shù)據(jù)庫(kù)優(yōu)化方案
1、內(nèi)存等Oracle系統(tǒng)參數(shù)配置
2、使用索引
3、表分區(qū)
4、Procedure優(yōu)化
5、其他改造
6、維護(hù)作業(yè)計(jì)劃
三、ORACLE數(shù)據(jù)庫(kù)優(yōu)化前后比較
1、批量重復(fù)的SQL語句執(zhí)行性能
2、一些單次、不常用的操作的語句執(zhí)行性能
四、參考
1、常用的優(yōu)化工具
2、參考文獻(xiàn)
一、前言
隨著實(shí)際項(xiàng)目的啟動(dòng)，實(shí)際項(xiàng)目中使用的 Oracle數(shù)據(jù)庫(kù)經(jīng)過一段時(shí)間的運(yùn)行，在線保存的數(shù)據(jù)量和業(yè)務(wù)處理的數(shù)據(jù)量在逐漸增大，最初的Oracle設(shè)置，與現(xiàn)在實(shí)際需要的運(yùn)行性能有一定差距，需要進(jìn)行一些優(yōu)化調(diào)整。
本文將結(jié)合本人實(shí)際維護(hù)經(jīng)驗(yàn)，相應(yīng)地提出實(shí)際項(xiàng)目數(shù)據(jù)處理的一些優(yōu)化方法，以供參考。
適用于Oracle 9i。
二、Oracle數(shù)據(jù)庫(kù)優(yōu)化概述
Oracle數(shù)據(jù)庫(kù)的優(yōu)化，針對(duì)不同的應(yīng)用，會(huì)有側(cè)重點(diǎn)不同的優(yōu)化方法，根據(jù)我們實(shí)際項(xiàng)目的應(yīng)用特點(diǎn)，我們主要關(guān)心的是每次事務(wù)執(zhí)行完成的時(shí)間長(zhǎng)短。
從Oracle數(shù)據(jù)庫(kù)本身的特點(diǎn)，我們可以把優(yōu)化工作劃分為初始優(yōu)化設(shè)置，微優(yōu)化。
在初始優(yōu)化設(shè)置時(shí)，我們只能根據(jù)硬件情況，估計(jì)業(yè)務(wù)運(yùn)行的情況，綜合經(jīng)驗(yàn)，給出一種經(jīng)驗(yàn)設(shè)置，大體上來說，這種經(jīng)驗(yàn)設(shè)置離滿足優(yōu)化需求的目標(biāo)不是很遠(yuǎn)。在完成了初始優(yōu)化設(shè)置后，經(jīng)過一段時(shí)間的業(yè)務(wù)運(yùn)行，已可開始收集實(shí)際運(yùn)行環(huán)境的性能數(shù)據(jù)，此時(shí)，就可以對(duì)各種Oracle性能指標(biāo)、各種關(guān)心的事務(wù)操作進(jìn)行性能評(píng)估，然后進(jìn)行微優(yōu)化了。
Oracle優(yōu)化，不是一個(gè)一蹴而就的工作，也不是一個(gè)一勞永逸的工作，需要定期維護(hù)，tb定期觀察，在發(fā)現(xiàn)性能瓶頸時(shí)及時(shí)進(jìn)行調(diào)整。Oracle總是存在性能瓶頸的，不使用、不操作的數(shù)據(jù)庫(kù)總是最快的，在解決當(dāng)前瓶頸后，總是會(huì)有另一個(gè)瓶頸出現(xiàn)，所以在優(yōu)化前，我們需要確定一個(gè)優(yōu)化目標(biāo)，我們的目標(biāo)是滿足我們的應(yīng)用性能要求就可以了。
Oracle優(yōu)化，涉及的范圍太廣泛，包含的有主機(jī)性能，內(nèi)存使用性能，網(wǎng)絡(luò)傳輸性能，SQL語句執(zhí)行性能等等，從我們面向網(wǎng)管來說，滿足事務(wù)執(zhí)行速度性能主要表現(xiàn)在：
1）批量重復(fù)的SQL語句執(zhí)行性能（主要是通過Procedure計(jì)算完成數(shù)據(jù)合并和數(shù)據(jù)匯總的性能和批量數(shù)據(jù)采集入庫(kù)的性能）；
2）一些單次、不常用的操作的語句執(zhí)行性能（主要是GUI的非規(guī)律操作）。
根據(jù)這兩個(gè)特點(diǎn)，我們可把優(yōu)化方法歸納到3個(gè)重要方向：
1）內(nèi)存等參數(shù)配置的優(yōu)化。內(nèi)存優(yōu)化，是性能受益最快的地方。
2）減少物理讀寫的優(yōu)化。內(nèi)存邏輯I/O操作的時(shí)間，遠(yuǎn)遠(yuǎn)小于物理I/O的操作時(shí)間。
3）批量重復(fù)操作的SQL語句及大表操作的優(yōu)化。減少SQL執(zhí)行次數(shù)，減少大表操作次數(shù)。
下面主要針對(duì)得益最大的這三個(gè)方向的優(yōu)化進(jìn)行闡述。
1、內(nèi)存等參數(shù)配置的優(yōu)化
對(duì)于大多數(shù)應(yīng)用來說，最直接、最快速得到優(yōu)化收益的，肯定屬于內(nèi)存的優(yōu)化。給每個(gè)Oracle內(nèi)存塊分配合理的大小，可以有效的使用數(shù)據(jù)庫(kù)。通過觀察各種數(shù)據(jù)庫(kù)活動(dòng)在內(nèi)存里的命中率，執(zhí)行情況，我們能很快的掌握數(shù)據(jù)庫(kù)的主要瓶頸。我們從下面的一條SQL語句的執(zhí)行步驟就可知道。
一個(gè)SQL語句，從發(fā)布到執(zhí)行，會(huì)按順序經(jīng)歷如下幾個(gè)步驟：
1）Oracle把該SQL的字符轉(zhuǎn)換成它們的ASCII等效數(shù)字碼。
2）該ASCII數(shù)字碼被傳送給一個(gè)散列算法，生成一個(gè)散列值。
3）用戶server process查看該散列值是否在shared pool內(nèi)存塊中存在。
若存在：
4）使用shared pool中緩存的版本來執(zhí)行。
若不存在：
4）檢查該語句的語義正確性。
5）執(zhí)行對(duì)象解析（這期間對(duì)照數(shù)據(jù)字典，檢查被引用的對(duì)象的名稱和結(jié)構(gòu)的正確性）。
6）檢查數(shù)據(jù)字典，收集該操作所引用的所有對(duì)象的相關(guān)統(tǒng)計(jì)數(shù)據(jù)。
7）準(zhǔn)備執(zhí)行計(jì)劃，從可用的執(zhí)行計(jì)劃中選擇一個(gè)執(zhí)行計(jì)劃。（包括對(duì)stored outline和materialized view的相關(guān)使用的決定）
8）檢查數(shù)據(jù)字典，確定所引用對(duì)象的安全性。
9）生成一個(gè)編譯代碼（P-CODE）。
10）執(zhí)行。
這里，通過內(nèi)存的合理分配，參數(shù)的合理設(shè)置，我們主要解決：
1）減少執(zhí)行到第五步的可能，節(jié)約SQL語句解析的時(shí)間。第五步以后的執(zhí)行過程，是一個(gè)很消耗資源的操作過程。
2）通過內(nèi)存配置，盡可能讓SQL語句所做的操作和操作的數(shù)據(jù)都在內(nèi)存里完成。大家都知道，從內(nèi)存讀取數(shù)據(jù)的速度，要遠(yuǎn)遠(yuǎn)快于從物理硬盤上讀數(shù)據(jù)，一次內(nèi)存排序要比硬盤排序快很多倍。
3）根據(jù)數(shù)據(jù)庫(kù)內(nèi)存活動(dòng)，減少每個(gè)內(nèi)存塊活動(dòng)的響應(yīng)時(shí)間，充分利用每個(gè)內(nèi)存塊，減少內(nèi)存latch爭(zhēng)用發(fā)生的次數(shù)。
2、減少物理讀寫的優(yōu)化
無論如何配置Oracle數(shù)據(jù)庫(kù)，我們的網(wǎng)管系統(tǒng)，每小時(shí)周期性的都會(huì)有新數(shù)據(jù)被處理，就會(huì)發(fā)生物理讀寫，這是避免不了的。
減少物理讀寫的優(yōu)化，一般所用的方法有：
1） 增加內(nèi)存data buffer的大小，盡可能讓數(shù)據(jù)庫(kù)操作的數(shù)據(jù)都能在內(nèi)存里找到，不需要進(jìn)行物理讀寫操作。
2） 通過使用索引，避免不必要的全表掃描。
3） 大表物理分區(qū)，Oracle具有很好的分區(qū)識(shí)別功能，減少數(shù)據(jù)掃描范圍。
上述3個(gè)方法，是從整體上改善數(shù)據(jù)庫(kù)物理I/O性能最明顯的3個(gè)方法。能非常快速的減少數(shù)據(jù)庫(kù)在物理I/O，最直接的反應(yīng)是數(shù)據(jù)庫(kù)事務(wù)執(zhí)行時(shí)間能能以數(shù)量級(jí)為單位減少。其他的一些減少物理讀寫的優(yōu)化方法，比如使用materialized view，Cluster等方法；還有一些分散I/O的方法，比如 Oracle日志文件不與數(shù)據(jù)文件放在一個(gè)物理硬盤，數(shù)據(jù)熱點(diǎn)文件物理I/O分開等等方法，就目前我們的網(wǎng)管系統(tǒng)而言，能得到的效果不是很明顯，在網(wǎng)管系統(tǒng)中，為了不增加數(shù)據(jù)庫(kù)維護(hù)的復(fù)雜性，不推薦使用。
3、批量重復(fù)操作的SQL語句及大表操作的優(yōu)化
批量重復(fù)執(zhí)行的SQL語句，一般出現(xiàn)在每個(gè)周期時(shí)間內(nèi)的數(shù)據(jù)批量入庫(kù)的insert語句，和數(shù)據(jù)合并、匯總的周期性select、delete、insert操作。
我們需要注意以下幾點(diǎn)：
1） 減少不必要的SQL語句執(zhí)行和SQL語句的執(zhí)行次數(shù)。
每條SQL語句執(zhí)行，都會(huì)消費(fèi)系統(tǒng)資源，都有執(zhí)行時(shí)間。減少不必要的SQL語句執(zhí)行和減少SQL語句的執(zhí)行次數(shù)，自然能減少業(yè)務(wù)執(zhí)行時(shí)間。需要根據(jù)業(yè)務(wù)流程，重新設(shè)計(jì)數(shù)據(jù)處理的代碼。此方法主要適用于procedure執(zhí)行的數(shù)據(jù)合并、匯總。
2） 這些SQL語句，由于每個(gè)SQL語句都要執(zhí)行很多次，應(yīng)該盡量讓該SQL的散列值在shared pool內(nèi)存塊中存在。也就是使用動(dòng)態(tài)SQL，避免SQL硬解析。
可通過Oracle參數(shù)的設(shè)置，和動(dòng)態(tài)SQL語句的應(yīng)用，通過綁定變量的方式，減少SQL語句的解析次數(shù)。
3）減少大表的操作，確保在一次事務(wù)中，同類操作只對(duì)大表執(zhí)行一次。主要在數(shù)據(jù)合并和數(shù)據(jù)匯總的pprocedure和數(shù)據(jù)采集時(shí)出現(xiàn)
三、Oracle數(shù)據(jù)庫(kù)優(yōu)化方案
1、內(nèi)存等Oracle系統(tǒng)參數(shù)配置
Oracle 的parameter參數(shù)，分動(dòng)態(tài)參數(shù)和靜態(tài)參數(shù)，靜態(tài)參數(shù)需要重新啟動(dòng)數(shù)據(jù)庫(kù)才能生效，動(dòng)態(tài)參數(shù)不需要重新啟動(dòng)數(shù)據(jù)庫(kù)即可生效。
Oracle 9i可以使用spfile的特性，使用alter system set 參數(shù)名=參數(shù)值 scope=both[spfile];的方法進(jìn)行修改。也可以直接修改pfile。
以下給出了網(wǎng)管Oracle 數(shù)據(jù)庫(kù)重點(diǎn)關(guān)注的parameter的初始優(yōu)化設(shè)置。
最大可使用的內(nèi)存SGA總和
靜態(tài)參數(shù)sga_max_size=物理內(nèi)存的大小減1.5G
Shared pool
動(dòng)態(tài)參數(shù)shared_pool_size= 600 ~ 800 M
靜態(tài)參數(shù)shared_pool_reserved_size= 300 M
動(dòng)態(tài)參數(shù)open_cursors= 400 ~ 600
靜態(tài)參數(shù)cursor_space_for_time= TRUE
靜態(tài)參數(shù)session_cached_cursors= 60 ~ 100
動(dòng)態(tài)參數(shù)cursor_sharing= SIMILAR
Data buffer
動(dòng)態(tài)參數(shù)db_cache_advice= READY
動(dòng)態(tài)參數(shù)db_cache_size
動(dòng)態(tài)參數(shù)Db_keep_cache_size
動(dòng)態(tài)參數(shù)db_recycle_cache_size
（sga_max_size大小，除了分配給所有非data buffer的size，都分配給data buffer）
Sga other memory
動(dòng)態(tài)參數(shù)large_pool_size= 50 M
靜態(tài)參數(shù)java_pool_size= 100 M
動(dòng)態(tài)參數(shù)log_buffer= 3 M
Other memory
動(dòng)態(tài)參數(shù)sort_area_size= 3 M
靜態(tài)參數(shù)sort_area_retained_size= 0.5 M
靜態(tài)參數(shù)pga_aggregate_target= 800 M
動(dòng)態(tài)參數(shù)workarea_size_policy= AUTO
磁盤I/O配置
靜態(tài)參數(shù)sql_trace= FALSE
動(dòng)態(tài)參數(shù)timed_statistics= true
動(dòng)態(tài)參數(shù)db_file_multiblock_read_count= 16
靜態(tài)參數(shù)dbwr_io_slaves= 0
靜態(tài)參數(shù)db_writer_processes= 3
靜態(tài)參數(shù)undo_management= AUTO
動(dòng)態(tài)參數(shù)undo_retention= 7200
2、使用索引
我們初步定義，表數(shù)據(jù)超過1000行的表，我們都要求使用索引。（不區(qū)分事務(wù)操作的數(shù)據(jù)在表數(shù)據(jù)中所占的比例）
索引所包含的字段不超過4個(gè)。
檢查SQL語句是否使用了索引，我們使用execute plan來看，獲得explain的方法，我們通過SQL*PLUS工具，使用如下命令進(jìn)行查看：
setautotraceonsetautotracetraceonlyexplain settimingon或通過SQL*PLUS trace，然后查看user_dump_dest下的跟蹤文件，使用tkprof工具格式化后閱覽。
altersessionsetevents'10046tracenamecontextforever,level12'; altersessionsetevents'10046tracenamecontextoff'; SELECTp.spid,s.usernameFROMv$sessions,v$processpWHEREs.audsid=USERENV('sessionid')ANDs.paddr=p.addr;3、表分區(qū)
在網(wǎng)管數(shù)據(jù)庫(kù)里，比較突出的大表有小區(qū)表和告警表。
性能表，使用范圍分區(qū)。
以時(shí)間點(diǎn)start_time為范圍分區(qū)字段。
告警表，使用range-hash的混合分區(qū)和范圍分區(qū)。
范圍分區(qū)以時(shí)間點(diǎn)starttime為分區(qū)字段，混合分區(qū)增加ALARMNUMBER為字段的hash子分區(qū)。
同時(shí)，創(chuàng)建本地分區(qū)索引。
4、Procedure優(yōu)化
1）取消地市一級(jí)的Procedure，只保留其上層調(diào)用Procedure，并保持參數(shù)輸入方法，調(diào)用方法不變。
2）確保大表數(shù)據(jù)查詢操作只有1次，確保大表數(shù)據(jù)刪除只有一次。
3）確保單條SQL語句執(zhí)行已優(yōu)化。
4）減少SQL執(zhí)行次數(shù)。
5、其他改造
修改表存儲(chǔ)參數(shù)，提前預(yù)先分配extents。
修改表空間存儲(chǔ)參數(shù)（采集表空間所用塊設(shè)置為大塊，比如32k一個(gè)塊；修改ptcfree,pctused,pctincrease等）。
避免使用唯一索引和非空約束。
創(chuàng)建合理的索引。
各模塊SQL語句優(yōu)化，比如使用提示固定索引等。
確認(rèn)每一條歷史數(shù)據(jù)刪除語句已優(yōu)化和刪除方法。
臨時(shí)表的使用。
6、維護(hù)作業(yè)計(jì)劃
表分析（包含確定具體的表的分析方法，分區(qū)表分析方法，索引分析方法）。
空間回收維護(hù)（包括確定HWM，回收多余分配給表的塊，合并數(shù)據(jù)塊碎片等）。
索引維護(hù)（包括定期重建索引，索引使用情況監(jiān)視等）。
歷史數(shù)據(jù)刪除檢查（檢查保存的數(shù)據(jù)是否符合要求，檢查歷史數(shù)據(jù)刪除方法是否正確-比如批量刪除提交的方法等）。
全庫(kù)性能分析和問題報(bào)告及優(yōu)化（比如使用statspack進(jìn)行性能趨勢(shì)分析，檢查有問題的SQL或事務(wù)，確定當(dāng)前系統(tǒng)等待的top 5事件等等）。
表數(shù)據(jù)keep,default及reclye（比如把一些常用的配置表固定在內(nèi)存里等）。
數(shù)據(jù)庫(kù)參數(shù)核查（防止數(shù)據(jù)庫(kù)參數(shù)被修改，定期對(duì)系統(tǒng)配置參數(shù)進(jìn)行比較）。
日志文件分析（定期檢查Oracle生成的日志文件，定期備份、刪除）。
硬盤空間維護(hù)（定期對(duì)Oracle 對(duì)象使用的空間情況進(jìn)行監(jiān)視）。
四，Oracle數(shù)據(jù)庫(kù)優(yōu)化前后比較
1、批量重復(fù)的SQL語句執(zhí)行性能
根據(jù)網(wǎng)元數(shù)量，各地的執(zhí)行的完成時(shí)間有所區(qū)別。
用于數(shù)據(jù)合并和匯總的Procedure的計(jì)算性能
通過statspack的周期性采集數(shù)據(jù)，我們可以使用以下語句，計(jì)算我們想統(tǒng)計(jì)的Procedure的執(zhí)行情況：
SELECTTO_CHAR(sn.snap_time,'yyyy-mm-ddhh24:mi:ss')ASsnap_time,s.disk_reads, s.buffer_gets,s.elapsed_time/1000000ASelapsedtime FROM(SELECThash_value,sql_text,address,last_snap_id FROMSTATS$SQLTEXTWHEREpiece=0ANDsql_textLIKE'%&sqltext_key%')t, (SELECTaddress,hash_value,snap_id,sql_text,disk_reads,executions, buffer_gets,rows_processed,elapsed_time FROMSTATS$SQL_SUMMARY)s,STATS$SNAPSHOTsn WHEREs.hash_value=t.hash_value ANDs.address=t.address ANDs.snap_id=t.last_snap_id ANDsn.snap_id=s.snap_id;比如，我們以perfstat用戶執(zhí)行該SQL，輸入“to_comp”，可以觀察到數(shù)據(jù)庫(kù)里保存的有的to_comp存儲(chǔ)過程的執(zhí)行時(shí)間，我們發(fā)現(xiàn)，其執(zhí)行時(shí)間，從優(yōu)化前的幾千秒，最后穩(wěn)定在優(yōu)化后的幾十秒。
注：to_comp是整體調(diào)用執(zhí)行一次所有網(wǎng)元的數(shù)據(jù)合并和匯總的procedure。
用于小區(qū)分析數(shù)據(jù)的Procedure的計(jì)算性能
使用上面的方法，我們一樣可以知道，小區(qū)分析的procedure執(zhí)行，從優(yōu)化前的約幾千秒，最后穩(wěn)定在優(yōu)化后的幾十秒。
批量數(shù)據(jù)采集入庫(kù)性能
使用bcp，能從以前約15分鐘，減少到約4分鐘。
2、一些單次、不常用的操作的語句執(zhí)行性能
GUI上的性能數(shù)據(jù)查詢，告警數(shù)據(jù)查詢，響應(yīng)時(shí)間都極快，幾乎不再出現(xiàn)長(zhǎng)時(shí)間等待響應(yīng)的情況。
五，參考
常用的優(yōu)化工具
statspack
sql*plus
TOAD