ORACLE資料庫效能最佳化)

Technorati 標籤: oracle

字型: 小 中 大 | 列印 發表於: 2004-10-27 13:54    作者: tangshan_ph    來源: CNOUG部落格首頁

可以通過調整資料庫應用程式、資料庫和作業系統來增強ORACLE的效能。適當的調整ORACLE可以針對特定的應用程式和硬體設定獲得較好的效能。效能調整要考慮回應時間、輸送量、限制這三個因素。
依賴於應用的不同特性，對這三個因素的要求也不盡相同。對於OLTP來說，要求有較高的回應時間，而對於OLAP來說，對輸送量有較高的要求。系統的回應時間可分為服務時間和等待時間，對於回應時間效能調整來說，就是縮短服務時間和等待時間。對於輸送量效能調整來說，就是利用同樣的資源做更多的事情（縮短服務時間）、較快的完成工作（縮短等待時間）。
象CPU、記憶體、I/O能力、網路頻寬等資源對減少服務時間和等待時間有很大影響。增加資源可能會獲得較高的輸送量和較快的回應時間。系統效能以來於：
可用資源的數量
請求資源的客戶的數量
等待資源的時間
佔用資源的時間
通常，應用程式的開發和設計對效能的影響最大，一旦應用程式被付諸實施，資料庫管理員只能在系統設計的限制之下進行基本的調整。效能調整包含了10個步驟。在此主要討論象CPU、記憶體、I/O能力、網路頻寬等資源對效能的影響，並如何配置資源來提高系統的效能。
首先要瞭解系統的運行情況，有幾種工具可以用來擷取表示系統效能的資料。能表示系統效能的資料來源有以下幾種：
資料卷
線上資料字典
作業系統工具
動態效能表
SQL跟蹤工具
警示日誌
應用程式輸出
使用者
初始化參數檔案
程式文本
設計（分析）字典
可比較資料
而能獲得這些資料的可用工具主要有以下幾種：
動態效能檢視：Oracle利用一組動態效能檢視來監控資料庫。這些視圖名以"V$"開始。SYS使用者擁有這些視圖。
Oracle與SNMP（Simple Network Management Protocol）支援：SNMP使使用者可以寫自己的工具和應用程式。
解釋計劃（EXPLAIN PLAN）：EXPLAIN PLAN是一個SQL語句，它能列出查詢最佳化工具選擇的存取路徑。
SQL跟蹤工具和TKPROF：SQL跟蹤工具以文本方式記錄下語句在每個階段的資源消耗，分析，執行，擷取結果，提交或復原。
TKPROF匯總SQL跟蹤工具所產生的檔案，有選擇的包含在EXPLAIN PLAN的輸出裡。
指令碼：Oracle支援許多PL/SQL包，它們可用於對資料庫執行個體進行調整。例如：UTLBSTAT.SQL UTLESTAT, SQLUTLCHAIN.SQL, UTLDTREE.SQL, 和UTLLOCKT.SQL。
應用程式註冊：可以在資料庫中記錄下應用程式的名字和它執行的操作，這樣管理員就可以根據模組來跟蹤效能。應用程式的名字和活動記錄在V$SESSION 和V$SQLAREA視圖內。
Oracle企業管理器：它是一個用於管理Oracle環境的工具集。包括：
Oracle Performance Manager ：它擷取、計算和顯示資料庫的效能資料，通過它可以監控資料庫，來從而有效地使用記憶體，最小化磁碟I/O，避免資源競爭。
Oracle TopSessions ：用於監控每個會話的活動。
Oracle Trace ：在Oracle Performance Manager和Oracle TopSessions採用抽樣技術週期性從資料庫動態效能檢視中收集資料，而Oracle Trace則根據預定義的事件來收集效能資料。
Oracle Tablespace Manager ：如果你懷疑資料庫的效能問題來自資料表空間，可以使用該工具檢測和矯正這些問題。
Oracle Expert：它提供自動的效能調整。由Oracle Performance Manager, Oracle TopSessions, and Oracle Trace檢測出的問題可以在Oracle Expert中進行分析。
Oracle並行伺服器管理（OPSM）：OPSM是一個用於Oracle並行伺服器的全面的管理方案，它通過開放的client-server體系來管理異構環境中的多個資料庫執行個體。
（一） （一）最佳化CPU資源
確定CPU有關的問題：首先需要確定CPU是否足夠大，在此方面主要通過以下三個方面來考慮：
在空閑期間的CPU的利用率
在高峰期間的CPU的利用率
平均的CPU的利用率
如果懷疑CPU是系統效能的瓶頸，可以通過如下方法來確定：
         檢查系統的CPU使用率：ORACLE只統計ORACLE會話對CPU的使用方式，而系統中的任何進程都使用CPU，所以調整其他應用程式也有可能改善ORACLE的CPU效能。
在UNIX系統中可以通過sar –u命令來獲得有關CPU 使用方式的資訊，一般情況下，如果CPU的空閑時間或I/O等待時間接近於0，說明CPU是瓶頸。在WINDOWS NT 中，可以通過效能監控器來獲得有關CPU 使用方式的資訊。
記憶體管理：Paging and Swapping。通過UNIX的sar 或 vmstat命令，以及WINDOWS NT 中的效能監控器來分析發生分頁和交換的原因。
I/O管理：Thrashing：確保工作量適宜於記憶體的大小，避免過多的頁交換。如果CPU花費時間片的很大部分來確保程式可運行，則可能只有50%的時間用於實際工作。Client/Server Round Trips：一些隱含的訊息發送會加重CPU的負擔，應用程式經常產生一些訊息在網路中來回傳遞。
進程管理：Scheduling and Switching：作業系統可能會花費很多時間用於切換進程，檢查是否啟動了大量的進程。Context Switching：進程的環境切換同樣會耗費大量的CPU時間。
檢查ORACLE使用CPU的使用率：有兩個動態效能檢視可以用於檢查ORCALE的進程資訊。V$SYSSTAT：顯示所有會話的ORACLE CPU的使用。V$SESSTAT：顯示每個會話的ORACLE CPU的使用。
主要在如下幾個方面考察ORACLE CPU的使用：
重新分析SQL語句：低效率的SQL共用引起語句的重新分析。
SELECT * FROM V$SYSSTAT
WHERE NAME IN
('parse time cpu', 'parse time elapsed', 'parse count (hard)');
SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA
ORDER BY PARSE_CALLS;
效率低的SQL語句：低效率的SQL語句會消耗大量的CPU時間。
SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA;
讀一致性：通過復原段實現讀一致性可能會需要大量的CPU時間，可通過增加復原段的數量來解決。
解決辦法：
在已經達到CPU的最大限制和無法通過調整CPU來最佳化效能時，則必須考慮重新設計應用。可以考慮遷移到不同的應用體繫結構上，以獲得適宜的CPU使用率。
從單層結構遷移到兩層結構：將一台機器上的客戶和伺服器分離。
多層，使用較小的客戶機：可以將一台機器上的多個客戶分離到各個較小的客戶機上。
兩層到三層：使用交易處理器
利用ORACLE的並行伺服器
（二） （二）最佳化記憶體配置
對於在記憶體中的資料的訪問要快於對磁碟的訪問，所以記憶體最佳化的目的就是提高記憶體命中率。通常在最佳化完應用和SQL語句後，才開始最佳化記憶體配置。記憶體的分配決定了磁碟I/O的數量。
如何解決記憶體配置問題：
最佳化作業系統的記憶體需求： 主要通過以下方法解決，減少記憶體頁交換；確保SGA在記憶體中；為每個使用者指派足夠的記憶體（可能只有一些作業系統支援）。
最佳化Redo日誌緩衝區：LOG_BUFFER參數用於控制Redo日誌緩衝區，對於磁碟速度慢，CPU快的電腦，Redo日誌緩衝區的使用可能會發生衝突。可適當增大Redo日誌緩衝區的大小。一般Redo日誌緩衝區只佔SGA中很小的一部分，適當的增加即可提高很大的輸送量。
最佳化私人的SQL 和PL/SQL區：該最佳化主要有兩部分工作，一個是區分不必要的重新分析調用，另一個是減少不必要的重新分析調用。可以通過SQL跟蹤工具、檢查V$SQLAREA視圖來區分不必要的重新分析調用；可以通過HOLD_CURSOR = yes 、RELEASE_CURSOR = no 、MAXOPENCURSORS = desired value這三個屬性來減少不必要的重新分析調用。
最佳化共用池：SGA的共用池部分由庫快取、字典快取和某些使用者和伺服器會話資訊組成。
庫快取包含分析和執行SQL語句。每個SQL語句的SGA共用部分為相應共用池中的記憶體量，在此共用池中包含語句的分析樹和執行計畫。執行SGA最佳化的一個重要問題就是確保庫快取足夠大，以使ORACLE能在快取中保持分析和執行計畫。可以通過系統效能監控器或者v$librarycache視圖查看庫快取的效能。
字典快取包含了有關資料庫的資料字典資訊、檔案空間的可用性和對象許可權。通過命中率可以發現此字典快取是否失效，可以通過系統效能監控器或者V$ROWCACHE視圖查看相關資料。如果需要可通過SHARED_POOL_SIZE參數來增加緩衝池大小。
最佳化排序緩衝區：如果都大量的排序動作，需要增加排序緩衝區的大小。可以通過SORT_AREA_SIZE參數實現。
（三） （三）最佳化I/O操作
I/O最佳化被安排在記憶體最佳化之後，通過記憶體的最佳化，可以是I/O衝突減少，在此情況下，可以通過一些調整以使I/O效能進一步提高。
對於新系統，應自頂向下分析I/O需求，確定所需要的資源。而對於已存在的系統應採用自底向上的方法：
1． 1．瞭解系統的磁碟數量。
2． 2．瞭解ORACLE使用的磁碟數量。
3． 3．瞭解應用系統的I/O類型。
4． 4．瞭解I/O操作是針對檔案系統還是原始裝置。
5． 5．瞭解對象在磁碟上的分布。
可以通過如下方法檢查I/O問題：
檢查系統I/O的使用：可以使用作業系統提供的工具來監視整個系統對磁碟檔案的訪問，可以將大量訪問磁碟的應用與ORACLE的相關檔案分別存放。在UNIX系統中可以通過sar –d 來獲得有關資料。在WINDOWS NT 中可通過效能監控器查看。
檢查ORACLE的I/O的使用：對於ORACLE ，可以通過下列視圖來獲得相關的資訊：
File Type Where to Find Statistics
Database Files V$FILESTAT
  Log Files V$SYSSTAT, V$SYSTEM_EVENT, V$SESSION_EVENT
  Archive Files V$SYSTEM_EVENT, V$SESSION_EVENT
  Control Files V$SYSTEM_EVENT, V$SESSION_EVENT
可以通過如下的方法來解決I/O問題：
減少磁碟競爭：
磁碟競爭：當多個進程同時訪問同一個磁碟時就會產生磁碟競爭。要減少高負荷磁碟的訪問，可以將高訪問量的檔案移到低負荷的磁碟上。
分離Redo記錄檔和資料檔案：ORACLE 總是經常的訪問Redo記錄檔和資料檔案，將二者放在一起，可能會增加磁碟衝突。
條帶化表資料：條帶化，就是將一個大表的資料分布到不同磁碟的不同資料檔案中，這樣也可以減少磁碟衝突。
分離表和索引：這並不是必須的，由於索引和表的讀取是串列的，也可以做到將表和索引放在一起而不發生磁碟衝突。
磁碟條帶化：就是將一個大表的資料分布到不同磁碟的不同資料檔案中，條帶化允許不同的進程同時訪問一個表的不同部分。這尤其對隨機訪問一個表的多行很有協助。條帶化可以是磁碟的I/OServer Load Balancer。有兩種條帶化方法。
手動方法：利用資料表空間以及分區表的方式。
利用作業系統：RAID
避免動態空間管理：當對象建立後，ORACLE會給對象指派至，當後續的資料庫操作需要額外的空間時，ORACLE會擴充該段。動態擴充對效能有損害。
偵測動態擴充：動態擴充可引起SQL語句的遞迴調用（ORACLE自己要發出一些SQL語句），可以通過V$SYSSTAT視圖來獲得有關遞迴調用的資訊。如果發現在應用程式運行時有大量的遞迴調用，可以使用較大的擴充。
分配擴充段（Extent）：通過確定最大的對象的大小，選擇合適的儲存參數值，以時ORACLE分配的段可以滿足儲存的要求。
（四） （四）最佳化網路：主要是保證網路的速度和網路上的流量。
可以通過如下方法檢查網路問題：可以通過V$SESSION_EVENT, V$SESSION_WAIT和 V$SESSTAT視圖來檢查與網路延遲有關的資訊。
V$SESSION_EVENT視圖中的 AVERAGE_WAIT列表明了ORACLE等待的時間。
V$SESSION_WAIT視圖中的 EVENT列列出了正在等待的活動會話
V$SESSION_WAIT視圖描述了活動會話在等待什麼，也可以看到收發的位元組數。
可通過以下方法解決網路問題：
使用數組介面：用數組介面取代每次只取一行的做法.在一個網路來回中取多行要比取一行要有效。
使用預先啟動的進程：在沒有使用多線程伺服器的情況下，如果有預先啟動的進程，可以大大縮短連線時間。
      調整會話資料單元緩衝區的大小：當有大量的資料要傳輸的時候，增加資料單元緩衝區的大小會改善網路的效能。可以通過ORACLE網路管理器來定義資料單元緩衝區的大小。
增加Listener隊列的大小：通過增加Listener隊列的大小，可以處理更多的請求。
使用TCP.NODELAY：
使用連線管理員：通過連線管理員可以使多個會話共用一個傳輸層串連，這樣可以使進程處理會話的數量增加。
（五） （五）最佳化作業系統
作業系統效能問題主要是進程管理和記憶體管理。如果已經最佳化過ORACLE後，還希望得到更好的效能，則需要最佳化作業系統。不過不能期望效能會有很大的提高。
檢測作業系統效能問題：從作業系統效能監控器中要獲得的資料指標有以下幾種：
CPU負載：檢查系統運行在使用者模式和系統模式的時間
裝置隊列
網路活動（排隊）
記憶體管理（頁交換）
解決作業系統問題：
基於UNIX的系統：瞭解系統的CPU時間的花費比例，在使用者模式是60%-75%的時間，在系統模式下是25%-40%的時間。該比率表現了系統的底層問題，如過度的頁交換、執行太多的系統調用、進程太多。
基於NT的系統：同基於UNIX的系統類別似。只是監視工具更方便。
（六） （六）最佳化資源競爭
當多個進程同時訪問資源時會發生競爭。可以通過V$SYSTEM_EVENT視圖來檢查資源競爭。
檢測競爭問題：
V$RESOURCE_LIMIT視圖，提供了當前和最大資源使用方式。
V$SYSTEM_EVENT視圖，檢查最高的平均等待時間。
V$LATCH視圖，檢查latch的使用方式。
V$WAITSTAT視圖和V$SESSION_WAIT視圖，等待緩衝區的時間。
解決競爭問題：
減少復原段的競爭：可通過考察包含復原段緩衝區的競爭來確定。可通過V$WAITSTAT視圖來確定。可用下列語句獲得資料，如果復原段緩衝區的等待時間佔總時間的1%以上，則考慮建立更多的復原段。
SELECT class, count
    FROM v$waitstat
    WHERE class IN ('system undo header', 'system undo block',
       'undo header', 'undo block');
減少多線程伺服器處理序的競爭：包括減少分發進程和共用伺服器處理序的競爭。
通過V$DISPATCHER視圖來獲得有關分發進程的資訊。
SELECT network             "rotocol",
       SUM(busy) / ( SUM(busy) + SUM(idle) ) "Total Busy Rate"
    FROM v$dispatcher
    GROUP BY network;
V$QUEUE視圖分發進程的響應隊列活動的有關資訊。
SELECT network "rotocol",
    DECODE( SUM(totalq), 0, 'No Responses',
       SUM(wait)/SUM(totalq)' '' hundredths of seconds')
    "Average Wait Time per Response"
    FROM v$queue q, v$dispatcher d
    WHERE q.type = 'DISPATCHER'
       AND q.paddr = d.paddr
    GROUP BY network;
可以使用ALTER SYSTEM命令的MTS_DISPATCHERS參數改變分發進程的數量。使用初始化參數MTS_MAX_DISPATCHERS改變最大分發進程的數量。
可以通過請求隊列中的請求的數量來判斷共用伺服器處理序的競爭。V$QUEUE視圖包含了請求隊列中的活動的資訊。
SELECT DECODE( totalq, 0, 'No Requests',
       wait/totalq' '' hundredths of seconds')
   "Average Wait Time Per Requests"
   FROM v$queue
    WHERE type = 'COMMON';
ORACLE在負載大的時候自動增加共用進程的數量，所以不可能手動改變它的值。但可以通過初始化參數MTS_MAX_SERVERS來改變共用進程的數量的最大值。
減少並行伺服器處理序的競爭：可通過V$PQ_SYSSTAT視圖來確定適當的並行伺服器處理序的數量。一般情況下，並行伺服器的最大數量依賴於系統的處理能力（CPU、I/O），無法改變該值。但如果頻繁啟動和關閉伺服器，可以考慮改變參數PARALLEL_MIN_SERVERS的值。可以周期的檢查V$PQ_SYSSTAT的值，以瞭解並行伺服器的數量是否合適：
SELECT * FROM V$PQ_SYSSTAT
WHERE statistic = "Servers Busy";
減少Redo日誌緩衝區閂的競爭：Redo日誌緩衝區很少會影響資料庫的效能。可以通過V$SYSSTAT視圖的REDO BUFFER ALLOCATION RETRIES檢查等待緩衝區的時間。
SELECT name, value
    FROM v$sysstat
    WHERE name = 'redo buffer allocation retries';
可通過初始化參數LOG_BUFFER改變緩衝區的大小