ORACLE性能調整
2024-08-29 13:41:26
供稿：網友
一、 為什么要進行數據庫優化
數據庫優化不僅僅是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表空間進行分離，把系統的數據表空間同索引表空間分離，把操作頻繁和不經常操作的表劃分在不同的表空間中。對于表空間的設計來說，大體上又可細劃分為以下原則：
把用戶數據與數
