在這里我們將介紹Oracle 數據庫優化方案與實踐，不同的環境會有不同的調試，但是也會有差別，希望大家能合理的吸收。

一、前言

二、ORACLE數據庫優化概述

1、內存等參數配置的優化 

2、減少物理讀寫的優化 

3、批量重復操作的SQL語句及大表操作的優化

二、ORACLE數據庫優化方案 

1、內存等Oracle系統參數配置

2、使用索引 

3、表分區 

4、Procedure優化 

5、其他改造

6、維護作業計劃 

三、ORACLE數據庫優化前后比較 

1、批量重復的SQL語句執行性能

2、一些單次、不常用的操作的語句執行性能 

四、參考 

1、常用的優化工具 

2、參考文獻

一、前言

隨著實際項目的啟動，實際項目中使用的 Oracle數據庫經過一段時間的運行，在線保存的數據量和業務處理的數據量在逐漸增大，最初的Oracle設置，與現在實際需要的運行性能有一定差距， 需要進行一些優化調整。

本文將結合本人實際維護經驗，相應地提出實際項目數據處理的一些優化方法，以供參考。

適用于Oracle 9i。

二、Oracle數據庫優化概述

Oracle數據庫的優化，針對不同的應用，會有側重點不同的優化方法，根據我們實際項目的應用特點，我們主要關心的是每次事務執行完成的時間長 短。

從Oracle數據庫本身的特點，我們可以把優化工作劃分為初始優化設置，微優化。

在初始優化設置時，我們只能根據硬件情況，估計業務運行的情況，綜合經驗，給出一種經驗設置，大體上來說，這種經驗設置離滿足優化需求的目標不是很 遠。在完成了初始優化設置后，經過一段時間的業務運行，已可開始收集實際運行環境的性能數據，此時，就可以對各種Oracle性能指標、各種關心的事務操 作進行性能評估，然后進行微優化了。

Oracle優化，不是一個一蹴而就的工作，也不是一個一勞永逸的工作，需要定期維護，定期觀察，在發現性能瓶頸時及時進行調整。Oracle總是 存在性能瓶頸的，不使用、不操作的數據庫總是最快的，在解決當前瓶頸后，總是會有另一個瓶頸出現，所以在優化前，我們需要確定一個優化目標，我們的目標是 滿足我們的應用性能要求就可以了。

Oracle優化，涉及的范圍太廣泛，包含的有主機性能，內存使用性能，網絡傳輸性能，SQL語句執行性能等等，從我們面向網管來說，滿足事務執行 速度性能主要表現在：

1）批量重復的SQL語句執行性能（主要是通過Procedure計算完成數據合并和數據匯總的性能和批量數據采集入庫的性能）；

2）一些單次、不常用的操作的語句執行性能（主要是GUI的非規律操作）。

根據這兩個特點，我們可把優化方法歸納到3個重要方向：

1）內存等參數配置的優化。內存優化，是性能受益最快的地方。

2）減少物理讀寫的優化。內存邏輯I/O操作的時間，遠遠小于物理I/O的操作時間。

3）批量重復操作的SQL語句及大表操作的優化。減少SQL執行次數，減少大表操作次數。

下面主要針對得益最大的這三個方向的優化進行闡述。

1、內存等參數配置的優化

對于大多數應用來說，最直接、最快速得到優化收益的，肯定屬于內存的優化。給每個Oracle內存塊分配合理的大小，可以有效的使用數據庫。通過觀 察各種數據庫活動在內存里的命中率，執行情況，我們能很快的掌握數據庫的主要瓶頸。我們從下面的一條SQL語句的執行步驟就可知道。

一個SQL語句，從發布到執行，會按順序經歷如下幾個步驟：

1）Oracle把該SQL的字符轉換成它們的ASCII等效數字碼。

2）該ASCII數字碼被傳送給一個散列算法，生成一個散列值。

3）用戶server process查看該散列值是否在shared pool內存塊中存在。

若存在：

4）使用shared pool中緩存的版本來執行。

若不存在：

4）檢查該語句的語義正確性。

5）執行對象解析（這期間對照數據字典，檢查被引用的對象的名稱和結構的正確性）。

6）檢查數據字典，收集該操作所引用的所有對象的相關統計數據。

7）準備執行計劃，從可用的執行計劃中選擇一個執行計劃。（包括對stored outline和materialized view的相關使用的決定）

8）檢查數據字典，確定所引用對象的安全性。

9）生成一個編譯代碼（P-CODE）。

10）執行。

這里，通過內存的合理分配，參數的合理設置，我們主要解決：

1）減少執行到第五步的可能，節約SQL語句解析的時間。第五步以后的執行過程，是一個很消耗資源的操作過程。

2）通過內存配置，盡可能讓SQL語句所做的操作和操作的數據都在內存里完成。大家都知道，從內存讀取數據的速度，要遠遠快于從物理硬盤上讀數據， 一次內存排序要比硬盤排序快很多倍。

3）根據數據庫內存活動，減少每個內存塊活動的響應時間，充分利用每個內存塊，減少內存latch爭用發生的次數。

2、減少物理讀寫的優化

無論如何配置Oracle數據庫，我們的網管系統，每小時周期性的都會有新數據被處理，就會發生物理讀寫，這是避免不了的。

減少物理讀寫的優化，一般所用的方法有：

1） 增加內存data buffer的大小，盡可能讓數據庫操作的數據都能在內存里找到，不需要進行物理讀寫操作。

2） 通過使用索引，避免不必要的全表掃描。

3） 大表物理分區，Oracle具有很好的分區識別功能，減少數據掃描范圍。

上述3個方法，是從整體上改善數據庫物理I/O性能最明顯的3個方法。能非常快速的減少數據庫在物理I/O，最直接的反應是數據庫事務執行時間能能 以數量級為單位減少。其他的一些減少物理讀寫的優化方法，比如使用materialized view，Cluster等方法；還有一些分散I/O的方法，比如 Oracle日志文件不與數據文件放在一個物理硬盤，數據熱點文件物理I/O分開等等方法，就目前我們的網管系統而言，能得到的效果不是很明顯，在網管系 統中，為了不增加數據庫維護的復雜性，不推薦使用。

3、批量重復操作的SQL語句及大表操作的優化

批量重復執行的SQL語句，一般出現在每個周期時間內的數據批量入庫的insert語句，和數據合并、匯總的周期性select、delete、 insert操作。

我們需要注意以下幾點：

1） 減少不必要的SQL語句執行和SQL語句的執行次數。

每條SQL語句執行，都會消費系統資源，都有執行時間。減少不必要的SQL語句執行和減少SQL語句的執行次數，自然能減少業務執行時間。需要根據 業務流程，重新設計數據處理的代碼。此方法主要適用于procedure執行的數據合并、匯總。

2） 這些SQL語句，由于每個SQL語句都要執行很多次，應該盡量讓該SQL的散列值在shared pool內存塊中存在。也就是使用動態SQL，避免SQL硬解析。

可通過Oracle參數的設置，和動態SQL語句的應用，通過綁定變量的方式，減少SQL語句的解析次數。

3）減少大表的操作，確保在一次事務中，同類操作只對大表執行一次。主要在數據合并和數據匯總的pprocedure和數據采集時出現

三、Oracle數據庫優化方案

1、內存等Oracle系統參數配置

Oracle 的parameter參數，分動態參數和靜態參數，靜態參數需要重新啟動數據庫才能生效，動態參數不需要重新啟動數據庫即可生效。

Oracle 9i可以使用spfile的特性，使用alter system set 參數名=參數值 scope=both[spfile];的方法進行修改。也可以直接修改pfile。

以下給出了網管Oracle 數據庫重點關注的parameter的初始優化設置。

最大可使用的內存SGA總和

靜態參數sga_max_size=物理內存的大小減1.5G

Shared pool

動態參數shared_pool_size= 600 ~ 800 M

靜態參數shared_pool_reserved_size= 300 M

動態參數open_cursors= 400 ~ 600

靜態參數cursor_space_for_time= TRUE

靜態參數session_cached_cursors= 60 ~ 100

動態參數cursor_sharing= SIMILAR

Data buffer

動態參數db_cache_advice= READY

動態參數db_cache_size

動態參數Db_keep_cache_size

動態參數db_recycle_cache_size

（sga_max_size大小，除了分配給所有非data buffer的size，都分配給data buffer）

Sga other memory

動態參數large_pool_size= 50 M

靜態參數java_pool_size= 100 M

動態參數log_buffer= 3 M

Other memory

動態參數sort_area_size= 3 M

靜態參數sort_area_retained_size= 0.5 M

靜態參數pga_aggregate_target= 800 M

動態參數workarea_size_policy= AUTO

磁盤I/O配置

靜態參數sql_trace= FALSE

動態參數timed_statistics= true

動態參數db_file_multiblock_read_count= 16

靜態參數dbwr_io_slaves= 0

靜態參數db_writer_processes= 3

靜態參數undo_management= AUTO

動態參數undo_retention= 7200

2、使用索引

我們初步定義，表數據超過1000行的表，我們都要求使用索引。（不區分事務操作的數據在表數據中所占的比例）

索引所包含的字段不超過4個。

檢查SQL語句是否使用了索引，我們使用execute plan來看，獲得explain的方法，我們通過SQL*PLUS工具，使用如下命令進行查看：

setautotraceon
setautotracetraceonlyexplain 
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、表分區

在網管數據庫里，比較突出的大表有小區表和告警表。

性能表，使用范圍分區。

以時間點start_time為范圍分區字段。

告警表，使用range-hash的混合分區和范圍分區。

范圍分區以時間點starttime為分區字段，混合分區增加ALARMNUMBER為字段的hash子分區。

同時，創建本地分區索引。

4、Procedure優化

1）取消地市一級的Procedure，只保留其上層調用Procedure，并保持參數輸入方法，調用方法不變。

2）確保大表數據查詢操作只有1次，確保大表數據刪除只有一次。

3）確保單條SQL語句執行已優化。

4）減少SQL執行次數。

5、其他改造

修改表存儲參數，提前預先分配extents。

修改表空間存儲參數（采集表空間所用塊設置為大塊，比如32k一個塊；修改ptcfree,pctused,pctincrease等）。

避免使用唯一索引和非空約束。

創建合理的索引。

各模塊SQL語句優化，比如使用提示固定索引等。

確認每一條歷史數據刪除語句已優化和刪除方法。

臨時表的使用。

6、維護作業計劃

表分析（包含確定具體的表的分析方法，分區表分析方法，索引分析方法）。

空間回收維護（包括確定HWM，回收多余分配給表的塊，合并數據塊碎片等）。

索引維護（包括定期重建索引，索引使用情況監視等）。

歷史數據刪除檢查（檢查保存的數據是否符合要求，檢查歷史數據刪除方法是否正確-比如批量刪除提交的方法等）。

全庫性能分析和問題報告及優化（比如使用statspack進行性能趨勢分析，檢查有問題的SQL或事務，確定當前系統等待的top 5事件等等）。

表數據keep,default及reclye（比如把一些常用的配置表固定在內存里等）。

數據庫參數核查（防止數據庫參數被修改，定期對系統配置參數進行比較）。

日志文件分析（定期檢查Oracle生成的日志文件，定期備份、刪除）。

硬盤空間維護（定期對Oracle 對象使用的空間情況進行監視）。

四，Oracle數據庫優化前后比較

1、批量重復的SQL語句執行性能

根據網元數量，各地的執行的完成時間有所區別。

用于數據合并和匯總的Procedure的計算性能

通過statspack的周期性采集數據，我們可以使用以下語句，計算我們想統計的Procedure的執行情況：

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用戶執行該SQL，輸入“to_comp”，可以觀察到數據庫里保存的有的to_comp存儲過程的執行時間，我們發 現，其執行時間，從優化前的幾千秒，最后穩定在優化后的幾十秒。

注：to_comp是整體調用執行一次所有網元的數據合并和匯總的procedure。

用于小區分析數據的Procedure的計算性能

使用上面的方法，我們一樣可以知道，小區分析的procedure執行，從優化前的約幾千秒，最后穩定在優化后的幾十秒。

批量數據采集入庫性能

使用bcp，能從以前約15分鐘，減少到約4分鐘。

2、一些單次、不常用的操作的語句執行性能

GUI上的性能數據查詢，告警數據查詢，響應時間都極快，幾乎不再出現長時間等待響應的情況。

五，參考

常用的優化工具

statspack

sql*plus

TOAD