Oracle9i 應用系統優化

tolywang發表於2007-04-24
Oracle優化

1、優化前提

應用系統方案制定準確，對應用系統執行環境分析合理、正確，在資料庫伺服器效能、儲存空間、網路頻寬等方面的配置能夠達到系統執行要求.


2、優化目標

l 響應時間與吞吐量平衡

l 臨界資源

2.1 響應時間與吞吐量平衡

根據應用型別的不同，效能優化的目標不同：

線上事務處理系統OLTP）把吞吐量定義為效能指標；

決策支援系統（DSS）把響應時間定義為效能指標。

響應時間

響應時間=服務時間+等待時間

系統吞吐量

系統吞吐量指在給定的時間內所完成的工作量。有以下兩種技術：

l 以相同的資源來完成更多的工作（減少服務時間）；

l 通過減少整個響應時間來更快完成工作。

等待時間

當競爭增強的時候，某個任務的服務時間也許保持不變，但它的等待時間將增長。

我們開發的系統一般為OLTP和DSS的複合系統，側重於OLTP,在硬體允許的情況下最好能夠將執行資料庫、分析資料庫分離。

2.2 臨界資源

諸如 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 = （ * 80%） * 20%
2.對於DSS系統PGA_AGGREGATE_TARGET = （ * 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.eventfrom v$sqltext_with_newlines st,v$session se,v$session_wait swwhere st.address=se.sql_address and st.hash_value=se.sql_hash_valueand 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

全索引掃描

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-84600/，如需轉載，請註明出處，否則將追究法律責任。

0
0
上一篇： 剎車時車輪被抱死的利弊(ABS)
下一篇： 投資基金的至高境界是“忘記”
Oracle9i 應用系統優化
請登入後發表評論 登入
全部評論
tolywang
專家姓名：王琦
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技術討論，希望在這裡一起分享知識，討論技術，暢談人生 。

註冊時間：2007-12-10

  • 博文量
    5595
  • 訪問量
    13513274

最新文章

支援我們 作者招募 使用者協議 FAQ Contact Us

北京盛拓優訊資訊科技有限公司. 版權所有  京ICP備09055130號-4  北京市公安局海淀分局網監中心備案編號：11010802021510

廣播電視節目製作經營許可證(京) 字第1234號 中國網際網路協會會員

相關文章