ORACLE效能調整--1

一、 為什麼要進行資料庫優化
　　
　　資料庫優化不僅僅是DBA（資料庫治理員）的事情，它也是應用設計人員、應用開發人員必須作的事情。
　　在確認了由誰來進行資料庫優化之後，就要考慮從何時開始進行資料庫優化。許多人認為對資料庫的優化不急，等到使用者開始抱怨系統執行速度無法忍受時，再進行優化。但此時某些有效的優化手段已無法有效的使用。
　　對於熟悉軟體工程的人來說，在一個系統的生命週期內，對系統進行調整，想利用較小的人力、物力而能夠收到較好的收益的話，最好在系統的設計和開發期內進行。假如一軟體已成為產品，此時再進行系統調整，則耗費的精力最多，而收益最小。同樣，對於資料庫的優化，最好的時期是在系統的設計和開發階段，儘量避免在一系統成型之後再進行優化。
　　無論是設計或維護資料庫系統，都必須建立專門的效能指標，使人們能夠有明確的目標，知道在何時進行調整。調整一個資料庫系統的最有效的步驟如下：
　　在設計系統時考慮系統的效能
　　在開發應用程式時考慮系統的效能
　　調整作業系統的硬體和軟體設定
　　識別系統的效能的瓶頸
　　確認問題的原因
　　採取糾正的動作
　　對於任何一個系統而言，良好設計的系統可以防止在應用生命週期以後產生的效能問題。同時，每一個系統設計人員和應用開發人員必須瞭解Oracle的查詢處理機制來編寫有效的SQL語句。以下提出進行系統設計時，應儘量遵循的原則：
　　消除客戶機/伺服器應用中不必要的網路傳輸。例如：使用ORACLE的REPORT時，儘可能對單表進行處理，不要對多表進行JOIN處理，以免造成不必要的網路傳輸。
　　使用適用於自己系統的相應的ORACLE伺服器選件（例如：並行查詢或分散式資料庫等）。
　　除非系統有非凡的需要，請使用預設的ORACLE鎖，無須自己對應用程式進行加鎖處理，以免產生不可猜測的錯誤。
　　為了便於對資料庫的每個應用進行跟蹤調測，儘可能記住每一個使用者所執行的模組。便於今後對系統效能的跟蹤。
　　在資料庫建立時，需從自身的實際出發，建立合適的資料塊長度。DB_BLOCK_SIZE
　　
　　二、 資料庫優化過程
　　
　　調整資料庫的效能必須有一個明確的目標，總的來說可以是以下的 幾個目標之一或多個：
　　改善指定型別的SQL語句的效能。
　　改善專門的資料庫應用的效能。
　　改善所有同時應用資料庫的使用者及其應用的所有效能。
　　在調整ORACLE效能之前，首先要有一個效能良好的應用設計及高效的SQL語句，在此基礎上調整ORACLE效能的過程有三步：
　　調整記憶體分配
　　調整I/O
　　調整資源爭用
　　因此，根據上述的原則並根據自己的工作經驗，認為對資料庫的優化大體上可分為如下幾個階段進行：
　　安裝資料庫時，對資料庫的資料塊大小進行確認。此引數在資料庫安裝之後就不能通過修改初始化引數進行修改或重新建立控制檔案進行修改，要改變該值，唯一的方法是重新安裝資料庫
　　在資料庫安裝完畢之後，對資料庫初始化引數進行修改。一個經過調優過的引數，對一個系統而言，可作到事半功倍的功效。例如：調整資料庫SGA大小，主要是DB_BLOCK_BUFFERS, SHARE_POOL_SIZE, OPEN_CURSORS, SORT_AREA_SIZE等引數。
　　調整主機的硬體效能和作業系統的軟體效能，使之配合資料庫，發揮最大的效能。
　　進行應用系統的物理設計。
　　進行應用程式的編寫時，對SQL語句的優化。
　　在試執行時對系統的物理設計以及應用程式的調整。
　　在系統執行過程中，通過對系統的監控，熟悉到系統的瓶頸，對系統再進行一次效能調整，此步驟在今後的系統執行中可能要反覆多次。
　　
　　資料庫優化內容
　　
　　1. ORACLE系統的預備知識
　　
　　1) ORACLE資料庫系統的資料儲存的物理結構和邏輯結構構成
　　
　　2) 模式物件的組成
　　
　　3) ORACLE資料庫系統的程式以及記憶體結構構成
　　
　　4) ORACLE鎖的概念介紹
　　
　　5) 二階段提交的概念
　　
　　6) 使用者、角色、許可權的概念的介紹
　　
　　7) 舉例介紹ORACLE是如何處理一個事務
　　
　　a 首先必須有一臺主機或資料庫伺服器執行一個ORACLE INSTANCE。
　　
　　b 一臺本地機器或客戶端工作站執行一個應用，它試圖通過適當的SQLNET驅動同伺服器取得聯絡。
　　
　　c 假如該伺服器也正在執行適當的SQLNET驅動。伺服器檢測到應用的連線請求，開始為此使用者程式建立一個專用的伺服器程式。
　　
　　d 客戶端的使用者執行一個SQL語句並提交此程式。
　　
　　e 伺服器程式收到此SQL語句，並開始檢驗在ORACLE的共享池中是否存在同樣的SQL語句。
假如在共享池中發現該SQL語句，伺服器程式開始檢驗該使用者是否對請求的資料有操作的許可權，然後使用在共享池中的SQL語句去執行該語句。假如該SQL語句在共享池中不存在，就為此語句分配一個新的共享池區以便它能夠被解析、執行。
　　
　　f 伺服器程式從實際的資料檔案或共享池中取回必須的資料。
　　
　　g 伺服器程式在在共享池中修改資料。在上述所作的生效之後，DBWR後臺程式把修改後的資料塊永久的寫入硬碟。在此事務提交成功之後，LGWR程式立即把此事務記錄到線上的redo log file。
　　
　　h 假如此事務成功，伺服器程式通過網路返回一個成功的資訊給應用程式。假如該事務不成功，將返回一個適當的資訊。
　　
　　i 在上述的事務過程中，其餘的後臺程式同樣在執行，等待著條件符合而被觸發。此外，資料庫伺服器還治理著其他使用者的事務，並且在不同事務之間提供資料一致性，防止不同事務對相同資料操作。
　　
　　2. 在安裝資料庫時作的優化
　　
　　在資料庫安裝時作的優化工作主要是關於DB_BLOCK_SIZE引數的設定，該引數決定了ORACLE每次操作多少的資料。該引數在安裝時一經確認就不能修改，除非重新安裝數 據庫。對於一個應用而言，一般對於一箇中型的應用系統，它的DB_BLOCK_SIZE大小為設為4K，而對於一個較大型的應用而言，它的DB_BLOCK_SIZE一般設為8K或更大一點為16K。
　　
　　對於一個較大的DB_BLOCK_SIZE，不僅可以加快系統的執行速度，（因為從系統的I/O吞吐能力來說，一次性讀取較多的資料可以比一次性讀取較少的資料的的過程減少I/O的讀取次數）而且可以有較大的系統擴充套件能力。因為對於一個系統而言，在它的DB_BLOCK_SIZE確認之後，它的最大EXTENT的數目其實也已經確認下來。假如一個系統的擴充套件能力有限的話，則系統輕易發生顯示終止的事情。而就是說，發生ORA錯誤，導致系統無法正常運轉。截止至目前，在ORACLE7.3之後的版本中，ORACLE在建表空間時，有一個引數autoextent，假如此引數設定為ON時，ORACLE在達到最大的擴充套件值時，ORACLE就自動擴充套件，不再受最大擴充套件數的限制。現就把DB_BLOCK_SIZE和MAX EXTENTS的關係羅列如下：
　　
　　DB_BLOCK_SIZE（資料塊數目） MAX EXTENTS（最大擴充套件數）
　　
　　512BYTES 25
　　
　　1K 57
　　
　　2K 121
　　
　　4K 249
　　
　　8K 505
　　
　　
　　3. 在安裝之後，在資料庫初始化時對INITXXX.ORA檔案作的優化
　　
　　對於SHARE_POOL_SIZE的設定：對於不同的系統根據使用者對於記憶體區的要求，考慮使用者是否需要多少的記憶體空間存放使用者的儲存過程或要多少空間存放使用者要編譯的程式。
　　
　　對於需要進行大量資料操作的使用者可考慮增大使用者的DB_BLOCK_BUFFERS的數目，該引數可以使使用者在緩衝區中的資料較大，使使用者查詢的資料儘可能的在緩衝區中，不要到表中去再次查詢。
　　
　　根據使用者的實際需要，設定較好的PROCESS該引數決定能夠有多少個使用者在系統中執行，假如該引數設定不當會導致使用者無法正常執行。並且該引數與作業系統的有些引數（如Digital unix的max_proc_per_users）有關，該型別的引數限制了每個使用者答應最大多少使用者登入的限制，因為對於我們而言，每一個使用者最終都體現為一個ORACLE使用者，假如此引數開的不夠大的話，則會造成後登入的使用者無法登入，應用終止。
　　
　　根據使用者實際使用系統的SQL語句的多少，決定最終要開的OPEN_CURSORS數目的多少，因為一個SQL的DML語句就是一個隱含的CURSOR，假如上述引數的數目開的不夠大的話，系統會提示使用者的SGA區不足，導致系統出錯。
　　
　　對於要進行大量資料分組和排序工作的應用要加大系統的SORT_AREA_SIZE的大小，該引數決定分配給每一位使用者的排序空間，該引數用到系統的記憶體空間。
　　
　　為保證系統能夠正常運轉，要保證系統有足夠的DML_LOCKS，假如該值不夠的話，會導致系統發生中斷，半途終止系統。
　　
　　為保證系統能夠有足夠的資料庫鏈路可用，要保證OPEN_LINKS的數目足夠大。
　　
　　對於會發生CORE DUMP的使用者的機器，可考慮設定使該CORE DUMP最終不要形成檔案，（在ORACLE的init引數中為shadow_core_dump=none，預設為full）。因為系統在許多時候由於檔案系統滿的緣故，導致系統無法正常運轉，最終會導致資料庫系統崩潰。
　　
　　4. 在進行空間設計時作的優化
　　
　　在一個資料庫安裝完畢之後，系統中已存在如下表空間，它們分別是：SYSTEM，TOOLS，RBS，USERS，TEMP等，上述表空間在安裝時使用者可根據當地的系統的實際情況進行系統表空間的劃分，使它們儘可能分離。
　　
　　在系統安裝時，還應該考慮控制檔案和可重作日誌檔案要儘可能的分配在不經常使用的盤上。
　　
　　表空間設計的原則為：把由使用者建立的其餘表空間同SYSTEM表空間進行分離，把系統的資料表空間同索引表空間分離，把操作頻繁和不經常操作的表劃分在不同的表空間中。對於表空間的設計來說，大體上又可細劃分為以下原則：

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