? 最近單位要求寫技術總結，我根據《ORACLE9i_優化設計與系統調整.》以及網上相關文章，主要結合自己的實際工作經歷，寫了一篇《oracle 9i 應用系統優化》,本人水平有限，如果有誤的話請多多指正，避免誤導他人，同時也希望大家共享相關的經驗，讓我也學習學習。
寫這篇文章還是花了不少時間，如果轉載的話請注明出處。
Oracle9i 應用系統優化
1、優化前提
應用系統方案制定準確，對應用系統運行環境分析合理、正確，在數據庫服務器性能、存儲空間、網絡帶寬等方面的配置能夠達到系統運行要求。
2、優化目標
l?????? 響應時間與吞吐量平衡
l?????? 臨界資源
2.1 響應時間與吞吐量平衡
根據應用類型的不同，性能優化的目標不同：
在線事務處理系統OLTP）把吞吐量定義為性能指標；
決策支持系統（DSS）把響應時間定義為性能指標。
響應時間
響應時間=服務時間+等待時間
系統吞吐量
系統吞吐量指在給定的時間內所完成的工作量。有以下兩種技術：
l???????? 以相同的資源來完成更多的工作（減少服務時間）；
l???????? 通過減少整個響應時間來更快完成工作。
等待時間
當競爭增強的時候，某個任務的服務時間也許保持不變，但它的等待時間將增長。
??? 我們開發的系統一般為OLTP和DSS的復合系統，側重于OLTP,在硬件允許的情況下最好能夠將運行數據庫、分析數據庫分離。
諸如 CPU、內存、I/O容量、網絡帶寬等資源，都是減少時間的關鍵因素。性能好壞取決于以下因素：
l???????? 可用資源的數量
l???????? 需要該資源的客戶方的數目
l???????? 客戶方等待資源所消耗的時間
l???????? 客戶保持資源的時間長短
隨著請求單元的增加，服務時間也增加。為了處理這種情形，用戶可以選擇：
l???????? 通過限制請求的速率，從而維護可接受響應時間
l???????? 還可通過增加資源數目，如CPU和硬盤（增加資源的前提是應用系統設計良好，并且已經做了充分的優化）
3、優化階段
從實際做的項目過程來看，除了系統安裝優化外，系統優化往往都是在系統實施、運行時才考慮，其實到這階段做系統優化的局限性比較大，因為系統架構設計都成型、固化，大幅度調整設計的代價非常昂貴，一般只能在局部領域做優化，只能通過重新分配內存或優化I/O來或多或少地提高性能，實際上優化應該貫穿系統設計、開發、安裝、測試、運行整個過程。
3.1 設計階段
為了達到最佳的效果，優化工作應當從設計階段進行，而不是在系統實施后進行。
在數據庫設計階段，個人認為需要注意如下幾個方面：
l???????? 業務對象不能建立在系統表空間；
l???????? 索引表空間和業務表空間分開；
l???????? 將LOB類型的字段與其它的類型分開；
l???????? 根據應用系統功能確定是否要采用冗余字段；
l???????? 正確的主鍵字段的選擇，建議采用數字，不推薦使用復合主鍵；
3.2 開發、測試階段
在開發實現階段，個人認為需要注意如下幾個方面：
l???????? 執行sql使用變量綁定的方式，盡可能的保留在共享內存中，提高sql命中率；
l???????? 多表關聯查詢時采用有效的連接順序；
l???????? 盡可能的降低客戶端和服務器的網絡數據交互，某個業務功能點需要頻繁和數據庫交互的，建議采用存儲過程、臨時表實現；
l???????? 根據查詢條件建立必要的索引，查詢條件中使用oracle函數建立相對應的函數索引,數據值范圍較小的采用位圖索引
l???????? 多張表關聯查詢時，有時可采用先查詢符合條件對應的表中關鍵字，然后通過關鍵字再查詢對應表中相關信息；
l???????? 頻繁訪問，較少更新的數據量較小的表信息可采用緩存的方式；
l???????? 在實現批量更新、插入時，要采用jdbc批量執行方法，并且調整對應的fetchsize參數。
在測試階段，應該模擬實際運行環境，測試出相關性能較差的功能點。
因為在設計、開發階段往往因為并發用戶少、數據量小，很多性能問題顯現不出來，如果軟件測試充分，很多性能問題都可以顯現出來，現在有很多優秀的軟件測試工具，如LoadRunner、Robert在做壓力測試方面都比較方便、優秀。
盡量將系統因程序設計、編碼不當導致的性能問題暴露在測試階段。
3.3 安裝階段
???? 一般在安裝生產數據庫時，我們根據系統最早的規劃，集合軟、硬件環境，需要調整操作系統以及數據庫參數，
3.3.1操作系統交換區
交換區是Oracle的一項基本的要求。可以根據Oracle的發行要求來確定。一般交換區大小的要求是該服務器內存的2倍至4倍之間，建議是內存的4倍
3.3.2操作系統內核參數
??????
shmmax | 共享內存段，建議設大點， 達到最大SGA |
shmmin | 最小的共享內存段. |
shmmni? | 共享內存標志符的數量. |
shmseg? | 一個進程可分配的最大內存段數. |
shmall? | 最大可允許的內存數，比SGA還要大. |
semmns | 信號量，跟ORACLE的PROCESS數有關. |
semmsl ? | 一個信號量中最大的信號量數. |
3.3.3 oracle 文件設置
當服務器平臺已完成操作系統的安裝后，就應該開始認真的考慮下面的問題：
l???????? 是否采用裸設備
?
實際應用的生產系統基本都是采用裸設備，使用裸設備對于讀寫頻繁的數據庫應用來說，可以極大地提高數據庫系統的性能。
?
l???????? 安裝點的考慮
?
Oracle的安裝點就是指數據文件、日志文件和控制文件的安置路徑，為了使系統在以后運行性能達到優化，建議將數據文件、日志文件和控制文件的安置路徑與數據庫系統存放在不同的路徑上。最好將數據文件、日志文件和控制文件分別存放在不同的路徑。
?
l???????? SYSTEM表空間對應數據文件
?
在自定義安裝會話中，建議你根據需要設置system表空間所對應的數據文件的大小。一般要設置比默認值的2倍。該數據文件的大小最好是在300MB至500MB間。因為數據文件太小不利于系統的運行。
?
l???????? 臨時表空間對應的數據文件
?
臨時表空間對應的數據文件可以根據將來系統存放的應用的處理情況來定。比如系統將來可能要經常進程排序處理，則需要設置較大的臨時表空間，也可能需要再建立新的臨時表空間。這里建議臨時表空間的數據文件在100MB至300MB左右。
?
l???????? 回滾段表空間對應的數據文件
?
9i回滾表空間都是系統管理，初始值也是根據系統事務量預估計的值，實際到運行階段如果系統常出現ora-01555錯誤的時候，可能就需要增加回滾表空間的大小。
?
l???????? 日志文件的大小
?
日志文件的大小對于Oracle系統的運行也是相當重要。默認值是太小。實際根據事務繁忙預估計日志大小，沒有固定的具體值范圍，建議重做日志切換時間不能過短也不能過長，一般在20－40分鐘左右。該參數可以在系統運行期間根據數據庫系統日志切換時間重新調整，控制文件的大小。
?
l???????? 數據庫塊的大小
?
如果你的應用系統是OLTP的話，可以采用較小的數據庫塊。如果是DSS類型的應用系統，則可以設置較大的數據庫塊，目前Oracle產品所允許的數據庫塊可以是2KB至64KB之間。無論你選擇較大的塊或較小的塊，它的值都必須是2的整數倍，比如2048,4096,8192等。但需要注意的是，如果操作系統為64位，則可選擇較大的塊。
?
?
l???????? 字符集的選擇
?
字符集是Oracle系統專門支持的一項技術。詳細請參考另外的章節。一般不要與另外的已經存放的Oracle系統的字符集產生沖突即可。但如果你的環境是一個新的平臺，不需要與其它平臺進行數據交換的話，建議選擇默認的字符集。這樣可以利于將來的修改。
?
3.3.4數據庫啟動參數
sga_max_size | 例程存活期間所占用的系統全局區的最大大小，一般為物理內存的1/2-1/3 |
shared_pool_size | 指定共享池的大小，共享池包含：共享游標、存儲的過程、控制結構和并行執行消息緩沖區等對象，較大的值用于改善多用戶系統的性能，該參數調整不能過大，會增加管理負擔和latch 的開銷，一般是在200M-500M左右 |
db_cache_size | 該參數指定數據緩沖區的大小，原則上時越大越好，
取代了8i中的db_block_size * db_block_buffers |
log_buffer | 重做日志緩沖區大小，該參數設置大沒有意義，
Oracle推薦log_buffer最大為cpu_count乘以128KB或512KB中最大值 |
processes | 系統用戶進程的最大數量，該參數設置為系統最繁忙時估計并發用戶數 |
large_pool_size | 如果不設置MTS，通常在 RMAN 、OPQ 會使用到，但是在10M --50M應該差不多了。可以考慮為 session * (sort_area_size + 2M)。 |
Java_pool_size | 它用于存放java代碼，若不使用java，建議設置為30M |
pga_aggregate_target | 程序全局區大小,
1.對于OLTP系統PGA_AGGREGATE_TARGET? = （<Total Physical Memory > * 80%） * 20%
2.對于DSS系統PGA_AGGREGATE_TARGET? = （<Total Physical Memory > * 80%） * 50% |
timed_statistics | 建議將timed_statistics 設置為true,否則無法查看到準確的統計信息（9i版本后的設置為true對系統性能影響較小，千分之一） |
?
上述參數基本是初始估計值，在運行階段可能會根據實際運行情況再調整。
?
3.4 運行階段
這也是實際優化工作最多的階段，個人認為運行階段優化的真正工作是解決因為實際運行數據庫參數設置不當、表、索引統計信息不準確，執行路徑不當等導致的性能問題。
????? 優化工作應該作為日常工作的一部分，而不是等到用戶反映系統慢，系統宕機時才去優化，那時已經是亡羊補牢，為時有點晚，從實際項目來看，往往都是應用程序編寫的sql、表、索引統計信息不準確，執行路徑不當而導致的性能問題，個人認為一般的sql調優還是有章可循的,基本三步： 查找、分析、優化。
3.4.1查找
3.4.1.1 非實時查找
??? 查找工具常用的就是statspack,該工具的安裝、使用比較簡便。
????腳本路徑${oracle_home}/rdbms/admin目錄下，常用腳本如下：
spdrop.sql?? | ?刪除腳本，丟棄統計分析的相關包、視圖、表、同義詞等對象(首次創建無須執行) |
spcreate.sql | ?創建腳本，生成統計分析的相關包、視圖、表、同義詞等對象（首次執行前建議創建一個統計用的表空間） |
spreport.sql | 生成報告記錄sql ,生成的報告文件在系統當前路徑下，
文件名默認為：sp_開始快照號_結束快照號.lst? |
sprepsql.sql? | 分析相關快照中的sql執行計劃。 |
sppurge.sql? | 刪除在兩個快照號之間包括本身的所有統計分析數據。 |
sptrunc.sql? | 截取statspack統計分析的相關數據? 在統計分析的對應用戶perfstat下執行 |
?
執行時間：
統計時生成兩次快照，一般在30-40分鐘左右
????執行方法：
?????????用sys登陸sqlplus后間隔對應時間執行兩次 exec statspack.snap;
統計結果視圖：
stats$snapshot | 快照相關信息； select snap_id,snap_time from stats$snapshot; |
stats$sqltext | 快照統計sql信息,查詢統計sql(statspack報告中sql過長會被截掉）select sql_text? from stats$sqltext where hash_value=查詢值 and last_snap_id=begin_snap_id order by piece; |
?
3.4.1.2 實時查找
?如果需要實時的查找性能隱患的相關sql,通過v$session_wait,v$session,v$sqltext_with_newlines三張動態視圖就可以基本查找到相關的sql，腳本如下：
select?sql_text?,sw.event
from?v$sqltext_with_newlines?st,v$session?se,v$session_wait?sw
where?st.address=se.sql_address?and?st.hash_value=se.sql_hash_value
and?se.sid?=sw.sid?and??
(sw.event?=?'buffer?busy?waits'?or
??sw.event?=?'enqueue'?or
??sw.event?=?'free?buffer?waits'?or
??sw.event?=?'global?cache?freelist?wait'?or
??sw.event?=?'latch?free'?or
??sw.event?=?'log?buffer?space'?or
??sw.event?=?'parallel?query?qref?latch'?or
??sw.event?=?'pipe?put'?or
??sw.event?=?'write?complete?waits'?or
??sw.event?like?'library?cache%'?or
??sw.event?like?'log?file?switch%'?
)??
order?by?st.hash_value,st.piece;?
3.4.2分析
分析報告個人一般主要關注top 5 event以及相關的讀邏輯塊、物理塊、執行次數較多的sql,實際上更多的側重在sql分析上
一般常見的top 5 事件如下：
db file sequential read | 等待事件，一般問題出現在讀索引上，建議將業務表空間和索引表空間分開存儲在不同的物理卷下，以提高磁盤的I/O性能。 |
db file scattered read | 建議程序中盡量避免使用全表掃描的語句，或者可以增大db_file_multiblock_read_count的值，提高全表掃描一次讀取數據塊的速度，減少磁盤I/O。 |
db file parallel write | 說明DBWR進程正等待把緩沖區的內容并行寫入數據文件中去,等待將一直持續到所有的I/O全部完成。建議增大初始化參數中的db_writer_processes的值 |
log file sync | 說明任何時候一個事物提交時，它將通知LGWR將LOG_BUFFER寫入日志文件，如果此部分占用時間較長，應減少COMMIT的次數，建議將重做日志放到較快的磁盤上進行存儲。 |
log file parallel write | 等待事件，和上面一樣建議將重做日志放到較快的磁盤上進行存儲。 |
提取出sql以后就可以進行分析，主要采用分析執行計劃的方式。個人一般喜歡如下的方式進行分析：
l???????? 生成計劃表（初次）
以sys用戶執行腳本${oracle_home}/rdbms/admin/utlxplan.sql，
l???????? 創建公用同義詞，方便在每個用戶下生成執行計劃（初次）
Create public synonym plan_table for plan_table;
Grant all on plan_table to public;
l???????? 每次分析時設置sqlplus環境變量
Set timing on
Set autotrace traceonly
l???????? 查看相關sql執行計劃
其他客戶端軟件pl/sql developer,toad 分析執行計劃都比較方便。
l???????? 執行計劃路徑解釋
常見路徑解釋：
Full Table Scans | 全表掃描、無可用索引 |
Index Unique Scans | 索引唯一掃描 |
IndexRange Scans | 索引范圍掃描 |
IndexRange Scans Descending | 索引降序范圍掃描 |
Index Skip Scans | 索引跳躍掃描 |
Full Scans | 全索引掃描 |
Fast Full Index Scans | 快速全索引掃描 |
Index Joins | 索引連接 |
Bitmap Joins | 位圖連接 |
?
常見連接解釋：
Nested Loops | 會循環外表（驅動表），逐個比對和內表的連接是否符合條件。
基于Cost的Oracle優化器(CBO)會自動選擇較小的表做外表。 | 優點：在驅動表比較小，內表比較大，而且內外表的連接列有索引的時候比較好，嵌套循環連接比其他連接方法有優勢,它可以快速地從結果集中提取第一批記錄,而不用等待整個結果集完全確定下來。
缺點：如果內部行源表(讀取的第二張表（內表）已連接的列上不包含索引,或者索引不是高度可選時, 嵌套循環連接效率是很低的。 |
SORT- merge JOIN | 將兩表的連接列各自排序然后合并，只能用于連接列相等的情況，適合兩表大小相若的情況 | 優點：在缺乏數據的選擇性或者可用的索引時,或者兩個源表都過于龐大(超過記錄數的5%)時,排序合并連接將比嵌套循環連更加高效。
局限性：排列合并連接只能用于等價連接排列合并連接需要臨時的內存塊,以用于排序( 如果SORT_AREA_SIZE設置得太小的話)。這將導致在臨時表空間占用更多的內存和磁盤I/O |
HASH JOIN | 在其中一表的連接列上作散列，因此只有另外一個表做排序合并，
只有基于代價的優化器才可以使用哈希連接。 | 當缺少有用的索引時，哈希連接比嵌套循環連接更加有效。哈希連接可能比排序合并連接更快，，哈希連接使用內存資源，并且當用于排序內存不足時，會增加臨時表空間的I/O（這將使這種連接方法速度變得極慢 |
3.4.3優化
???? Oracle運行階段優化的更多是對sql的優化，個人理解工作主要是：
?????? 分析性能較差sql;
調整性能較差的sql的實現方式，協助程序員更改相關程序;
對相關的查詢條件建立合理的索引;
根據需要合理的更新表、索引的程序信息;
3.4.3.1 oracle 優化器
l???????? 優化器優化方式
Oracle的優化器共有兩種的優化方式,即基于規則的優化方式(Rule-Based Optimization,簡稱為RBO)和基于代價的優化方式(Cost-Based Optimization,簡稱為CBO)。
A、RBO方式：優化器在分析SQL語句時,所遵循的是Oracle內部預定的一些規則。比如我們常見的,當一個where子句中的一列有索引時去走索引。
B、CBO方式：依詞義可知,它是看語句的代價(Cost)了,這里的代價主要指Cpu和內存。優化器在判斷是否用這種方式時,主要參照的是表及索引的統計信息。統計信息給出表的大小、有少行、每行的長度等信息。這些統計信息起初在庫內是沒有的,是你在做analyze后才出現的,很多的時侯過期統計信息會令優化器做出一個錯誤的執行計劃,因些我們應及時更新這些信息。在Oracle8及以后的版本,Oracle推薦用CBO的方式。
l???????? 優化器的優化模式(Optermizer Mode)
Rule:走基于規則的方式。
?
Choose:默認的情況下Oracle用的是這種方式，不建議修改該參數。指的是當一個表或索引有統計信息,則走CBO的方式,如果表或索引沒統計信息,表又不是特別的小,而且相應的列有索引時,那么就走索引,走RBO的方式。
?
First Rows:它與Choose方式是類似的,所不同的是當一個表有統計信息時,它將是以最快的方式返回查詢的最先的幾行,從總體上減少了響應時間。
?
All Rows:也就是我們所說的Cost的方式,當一個表有統計信息時,它將以最快的方式返回表的所有的行,從總體上提高查詢的吞吐量。沒有統計信息則走基于規則的方式。
3.4.3.2 常見優化問題
l???????? ????明明有索引，表的數據量也非常大，執行路徑不走索引
??????? 對應表、索引的統計信息有誤，可以通過dba_tables,dba_indexes視圖中的num_rows查看對應表、索引的統計信息，如果有誤，重新統計。
??????? Analyze table table_name compute statistics
??????? For table? /*統計表*/
??????? For all indexed columns? /*統計有索引的表列*/
l???????? ????統計后性能反而變差
??????? 雖然oracle推薦采用CBO方式，但有時對應的執行路徑并不是最佳，所
??????? 以我們在統計信息時只有針對性的統計相關表、索引信息。
??????? 一般有兩種處理方法
??????? a. 刪除對應的統計信息
??????? Analyze table table_name delete statistics
?????? ?Analyze index index_name delete statistics
?
??????? 對應的系統包dbms_stats也可實現生成、刪除表、索引的統計信息
??????? b. 使用hints 明確指定對應的執行路徑
3.4.3.3 hints
常用的幾種hints如下：
Hint Syntax? | Description? |
優化方式 | |
/*+ ALL_ROWS */ ? | 表明對語句塊選擇基于開銷的優化方法,并獲得最佳吞吐量,使資源消耗最小化 |
/*+ CHOOSE */ ? | 表明如果數據字典中有訪問表的統計信息,將基于開銷的優化方法,并獲得最佳的吞吐量;
表明如果數據字典中沒有訪問表的統計信息,將基于規則開銷的優化方法; |
/*+ FIRST_ROWS */ ? | 表明對語句塊選擇基于開銷的優化方法,并獲得最佳響應時間,使資源消耗最小化，在做分頁查詢時有時可以改善性能 |
/*+ RULE */ ? | 表明對語句塊選擇基于規則的優化方法 |
處理方法 | |
/*+ AND_EQUAL(table index) */ ? | 提示明確進行執行規劃的選擇,將幾個單列索引的掃描合起來.
SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306' |
/*+ CLUSTER(table) */ ? | Explicitly chooses a cluster scan to access the specified table. ? |
/*+ FULL(table) */ ? | 表明對表選擇全局掃描的方法
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT' |
/*+ INDEX(table index) */ ? | 表明對表選擇索引的掃描方法.
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M'; |
/*+ INDEX_ASC(table index) */ ? | 表明對表選擇索引升序的掃描方法 |
/*+ INDEX_COMBINE(table index) */ ? | 為指定表選擇位圖訪問路經,如果INDEX_COMBINE中沒有提供作為參數的索引,將選擇出位圖索引的布爾組合方式.
例如:
??? SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL<5000000 AND HIREDATE ? |
/*+ INDEX_DESC(table index) */ ? | 表明對表選擇索引降序的掃描方法. |
/*+ INDEX_FFS(table index) */ ? | 對指定的表執行快速全索引掃描,而不是全表掃描的辦法. |
/*+ NO_INDEX(table index) */ ? | 表明優化器不采用掃描相對應的索引 |
/*+ ROWID(table) */ ? | 明確表明對指定表根據ROWID進行訪問.
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA' AND EMP_NO='SCOTT'; |
/*+ USE_CONCAT */ ? | 對查詢中的WHERE后面的OR條件進行轉換為UNION ALL的組合查詢. |
連接操作 | |
/*+ USE_HASH(table) */ ? | 將指定的表與其他行源通過哈希連接方式連接起來.
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO; |
/*+ USE_MERGE(table) */ ? | 將指定的表與其他行源通過合并排序連接方式連接起來.
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO; |
/*+ USE_NL(table) */ ? | 將指定表與嵌套的連接的行源進行連接,并把指定表作為內部表.
? |
4、參考資料
l???????? 《ORACLE9i_優化設計與系統調整》
l???????? http://oracle.chinaitlab.com/induction/398193.html