Oracle效能調優原則__Oracle

 任何事情都有它的源頭，要解決問題，也得從源頭開始，影響ORACLE效能的源頭非常多，主要包括如下方面：資料庫的硬體設定:CPU、記憶體、網路條件。

　　1. CPU:在任何機器中CPU的資料處理能力往往是衡量電腦效能的一個標誌，並且ORACLE是一個提供並行能力的資料庫系統，在CPU方面的要求就更高了，如果運行隊列數目超過了CPU處理的數目，效能就會下降，我們要解決的問題就是要適當增加CPU的數量了，當然我們還可以將需要許多資源的進程KILL掉;

　　2. 記憶體:衡量機器效能的另外一個指標就是記憶體的多少了，在ORACLE中記憶體和我們在建資料庫中的交換區進行資料的交換，讀資料時，磁碟I/O必須等待物理I/O操作完成，在出現ORACLE的記憶體瓶頸時，我們第一個要考慮的是增加記憶體，由於I/O的回應時間是影響ORACLE效能的主要參數，我將在這方面進行詳細的講解

　　3. 網路條件:NET*SQL負責資料在網路上的來往，大量的SQL會令網路速度變慢。比如10M的網卡和100的網卡就對NET*SQL有非常明顯的影響，還有交換器、集線器等等網路裝置的效能對網路的影響很明顯，建議在任何網路中不要試圖用3個集線器來將網段互聯。

　　OS參數的設定

　　下表給出了OS的參數設定及說明，DBA可以根據實際需要對這些參數進行設定

　　核心參數名

　　說明

　　bufpages

　　對buffer空間不按靜態分配，採用動態分配，使bufpages值隨nbuf一起對buffer空間進行動態分配。

　　create_fastlinks

　　對HFS檔案系統允許快速符號連結

　　dbc_max_pct

　　加大最大動態buffer空間所佔實體記憶體的百分比，以滿足應用系統的讀寫命中率的需要。

　　dbc_min_pct

　　設定最小動態buffer空間所佔實體記憶體的百分比

　　desfree

　　提高開始交換操作的最低空閑記憶體下限，保障系統的穩定性，防止出現不可預見的系統崩潰(Crash)。

　　fs_async

　　允許進行磁碟非同步作業，提高CPU和磁碟的利用率

　　lotsfree

　　提高系統解除換頁操作的空閑記憶體的上限值，保證應用程式有足夠的可用記憶體空間。

　　maxdsiz

　　針對系統資料量大的特點，加大最大資料區段的大小，保證應用的需要。(32位)

　　maxdsiz_64bit

　　maximum process data segment size for 64_bit

　　Maxssiz

　　加大最大堆棧段的大小。(32_bit)

　　maxssiz_64bit

　　加大最大堆棧段的大小。(64_bit)

　　Maxtsiz

　　提高最大程式碼片段大小，滿足應用要求

　　maxtsiz_64bit

　　原值過大，應調小

　　Minfree

　　提高停止交換操作的自由記憶體的上限

　　Shmem

　　允許進行記憶體共用，以提高記憶體的利用率

　　Shmmax

　　設定最大共用記憶體段的大小，完全滿足目前的需要

　　Timeslice

　　由於系統的瓶頸主要反映在磁碟I/O上，因此　降低時間片的大小，一方面可避免因磁碟I/O不暢造成CPU的等待，從而提高了CPU的綜合利用率。另一方面減少了進程的阻塞量。

　　unlockable_mem

　　提高了不可鎖記憶體的大小，使可用於換頁和交換的記憶體空間擴大,用以滿足系統對記憶體管理的要求。 使用者SQL品質

　　以上講的都是硬體方面的東西，在條件有限的條件下，我們可以調整應用程式的SQL品質:

　　1. 不要進行全表掃描(Full Table Scan):全表掃描導致大量的I/O

　　2. 盡量建好和使用好索引:建索引也是有講究的，在建索引時，也不是索引越多越好，當一個表的索引達到4個以上時，ORACLE的效能可能還是改善不了，因為OLTP系統每表超過5個索引即會降低效能，而且在一個sql 中， Oracle 從不能使用超過 5個索引;當我們用到GROUP BY和ORDER BY時,ORACLE就會自動對資料進行排序,而ORACLE在INIT.ORA中決定了sort_area_size區的大小,當排序不能在我們給定的排序區完成時,ORACLE就會在磁碟中進行排序,也就是我們講的暫存資料表空間中排序, 過多的磁碟排序將會令 free buffer waits 的值變高,而這個區間並不只是用於排序的,對於開發人員我提出如下忠告:

　　1)、select,update,delete 語句中的子查詢應當有規律地尋找少於20%的表行.如果一個語句尋找的行數超過總行數的20%,它將不能通過使用索引獲得效能上的提高.

　　2)、索引可能產生片段,因為記錄從表中刪除時,相應也從表的索引中刪除.表釋放的空間可以再用,而索引釋放的空間卻不能再用.頻繁進行刪除操作的被索引的表,應當階段性地重建索引,以避免在索引中造成空間片段,影響效能.在許可的條件下,也可以階段性地truncate表,truncate命令刪除表中所有記錄,也刪除索引片段.

　　3)、在使用索引時一定要按索引對應欄位的順序進行引用。

　　4)、用(+)比用NOT IN更有效率。

　　降低ORACLE的競爭:

　　先講幾個ORACLE的幾個參數，這幾個參數關係到ORACLE的競爭:

　　1)、freelists 和 freelist 組:他們負責ORACLE的處理表和索引的空間管理;

　　2)、pctfree 及 pctused:該參數決定了freelists 和 freelist 組的行為，pctfree 和pctused 參數的唯一目的就是為了控制塊如何在 freelists 中進出

　　設定好pctfree 及 pctused對塊在freelists的移走和讀取很重要。

　　其他參數的設定

　　1)、包括SGA區(系統全域區):系統全域區(SGA)是一個分配給Oracle 的包含一個 Oracle 執行個體的資料庫的控制資訊記憶體段。

　　主要包括資料庫快取(the database buffer cache)，

　　重演日誌緩衝(the redo log buffer)，

　　共用池(the shared pool)，

　　資料字典緩衝(the data dictionary cache)以及其它各方面的資訊

　　2)、db_block_buffers(資料高速緩衝區)訪問過的資料都放在這一片記憶體地區，該參數越大，Oracle在記憶體中找到相同資料的可能性就越大，也即加快了查詢速度。

　　3)、share_pool_size (SQL共用緩衝池):該參數是庫快取和資料字典的快取。

　　4)、Log_buffer (重演日誌緩衝區)

　　5)、sort_area_size(排序區)

　　6)、processes (同時串連的進程數)

　　7)、db_block_size (資料庫塊大小):Oracle預設塊為2KB，太小了，因為如果我們有一個8KB的資料，則2KB塊的資料庫要讀4次盤，才能讀完，而8KB塊的資料庫只要1次就讀完了，大大減少了I/O操作。資料庫安裝完成後，就不能再改變db_block_size的值了，只能重建立立資料庫並且建庫時，要選擇手工安裝資料庫。

　　8)、open_links (同時開啟的連結數)

　　9)、dml_locks

　　10)、open_cursors (開啟游標數)

　　11)、dbwr_io_slaves (後台寫進程數)