Oracle 資料庫引數調整

每個Oracle資料庫都是由例項(Instance)與資料庫(datafiles，controlfiles、redologfiles)組成，其中所謂例項就是使用者同資料庫互動的媒介，使用者透過與一個例項相連來運算元據庫。而例項又是由統一的記憶體結構(SGA，PGA，UGA)和一批記憶體駐留程式組成。例項在作業系統中用ORACLE_SID來標識，在Oracle中用引數INSTANCE_NAME來標識，它們兩個的值是相同的。資料庫啟動時，系統首先在伺服器記憶體中分配系統全域性區(SGA)，構成了Oracle的記憶體結構，然後啟動若干個常駐記憶體的作業系統程式，即組成了Oracle的 程式結構，記憶體區域和後臺程式合稱為一個Oracle例項。

Oracle的記憶體配置與oracle效能息息相關。關於記憶體的配置，是最影響Oracle效能的配置。記憶體還直接影響到其他兩個重要資源的消耗：CPU和IO。

[@more@]

1. SGA 和 PGA

先來看看初始化引數：

SQL> SHOW PARAMETER SGA;

NAME TYPE VALUE

-------------------- ----------- -----------------------

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 584M

sga_target big integer 584M

SQL> col name format a30;

SQL> col value format a15;

SQL> col sys_mode format a20;

SQL> SELECT NAME , VALUE , ISSYS_MODIFIABLE "SYS_MODE" FROM V$PARAMETER WHERE NAME LIKE '%sga%';

NAME VALUE SYS_MODE

---------- --------------- --------------------

sga_max_size 612368384 FALSE

pre_page_sga FALSE FALSE

lock_sga FALSE FALSE

sga_target 612368384 IMMEDIATE

SQL> SHOW PARAMETER PGA;

NAME TYPE VALUE

------------------------------------ ----------- -----------

pga_aggregate_target big integer 194M

SQL> SELECT NAME , VALUE, ISSYS_MODIFIABLE "SYS_MODE" FROM V$PARAMETER WHERE NAME LIKE '%pga%';

NAME VALUE SYS_MODE

-------------------- --------------- --------------------

pga_aggregate_target 203423744 IMMEDIATE

如果ISSYS_MODIFIABLE返回的值為FALSE，則表示該引數修改後需要重啟資料庫，而且不能使用ALTER SYSTEM 語句動態修改，SGA_MAX_SIZE 是不可以動態調整的。

一般我們是在安裝之後設定SGA和PGA,因為SGA_MAX_SIZE是非動態引數，修改之後需要重啟資料庫，所以我們可以把SGA_MAX_SIZE設定大一些，SGA_TARGET是動態的，可以根據需要進行調整，這個主要根據業務的命中率來調整。SGA_TARGET的調整必須小於等於SGA_MAX_SIZE，當設定小於SGA_MAX_SIZE時，重啟後SGA_TARGET自動調整等於SGA_MAX_SIZE。對於OLTP系統SGA和PGA一般將SGA_MAX_SIZE設定為實體記憶體的40%-60%，PGA一般設定為實體記憶體的20%左右，這個具體需要根據業務需求來設定。

引數可以在pfile裡修改，也可以透過命令直接修改。

alter system set pga_aggregate_target=2g scope=spfile sid='tangyun';

--動態(根據連線數來調整)

alter system set sga_max_size=4g scope=spfile sid='tangyun';

--靜態(根據主機記憶體大小來調整)

alter system set sga_target=4g scope=spfile sid='tangyun';

--動態，根據主機記憶體大小來調整,不能超過sga_max_size的值,一般設跟sga_max_size一樣大，不然浪費記憶體資源)

以上都是針對Oracle 10g 版本的。 如果是9i的話，還需要對每個引數進行配置，如Share Pool，DB buffer，Java Pool，redo log buffer等。

2. 調整processes、sessions、db_files、open_cursors引數

SQL> SELECT NAME , VALUE , ISSYS_MODIFIABLE "SYS_MODE" FROM V$PARAMETER WHERE NAME ='sessions';

NAME VALUE SYS_MODE

------------------------------ --------------- --------------------

sessions 170 FALSE

SQL> SELECT NAME , VALUE , ISSYS_MODIFIABLE "SYS_MODE" FROM V$PARAMETER WHERE NAME ='processes';

NAME VALUE SYS_MODE

------------------------------ --------------- --------------------

processes 150 FALSE

SQL> SELECT NAME , VALUE , ISSYS_MODIFIABLE "SYS_MODE" FROM V$PARAMETER WHERE NAME ='open_cursors';

NAME VALUE SYS_MODE

------------------------------ --------------- --------------------

open_cursors 300 IMMEDIATE

SQL> SELECT NAME , VALUE , ISSYS_MODIFIABLE "SYS_MODE" FROM V$PARAMETER WHERE NAME ='db_files';

NAME VALUE SYS_MODE

------------------------------ --------------- --------------------

db_files 200 FALSE

資料庫預設的sessions 是170，processes 是150，db_files是200，open_cursors是300這4個數值肯定不能滿足系統需求。我們需要把這4個引數調大一點。其中sessions、processes、db_files是靜態的，修改後需要重啟，我們可以透過直接修改pfile後重啟也可以使用SQL修改後重啟資料庫。建議把不能動態修改的引數設定大一些(參考：sessions:2000，processes:1200，db_files:2000, open_cursors:1024)

alter system set sessions=2000 scope=spfile sid='tangyun';

--靜態

alter system set open_cursors=1024 scope=spfile sid='tangyun';

--動態

alter system set processes=1200 scope=spfile sid='tangyun';

--靜態(控制連線數)

alter system set db_files=2000 scope=spfile sid='tangyun';

--靜態(資料檔案數)

3. 調整temp和undo 表空間

臨時表空間主要用途是在資料庫進行排序運算、管理索引、訪問檢視等操作時提供臨時的運算空間，當運算完成之後系統會自動清理。當oracle裡需要用到sort的時候，PGA中sort_area_size大小不夠時，將會把資料放入臨時表空間裡進行排序，同時如果有異常情況的話，也會被放入臨時表空間,正常來說，在完成Select語句、create index等一些使用TEMP表空間的排序操作後，Oracle是會自動釋放掉臨時段的。但有些有侯我們則會遇到臨時段沒有被釋放，TEMP表空間幾乎滿的狀況，甚至是我們重啟了資料庫仍沒有解決問題。

SQL>ALTER DATABASE TEMPFILE 'D:ORACLEPRODUCT10.2.0ORADATATANGYUNTEMP01.DBF' RESIZE 100M;

和UNDO 一樣，可以設為20G 或者8G，具體情況具體對待。如果遇到temp tablespace 滿了的話，我們也可以重建其表空間。

undo 表空間放的是資料的前映象，當做某個記錄多修改時，原記錄就會放到undo 中。所以Undo 表空間的大小影響資料的恢復能力。

SQL> show parameter undo;

NAME TYPE VALUE

------------------------------------ ----------- ---------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

undo_retention 只是指定undo 資料的過期時間，預設是900s，15分鐘。建議改成10800s，即3個小時。

SQL> alter system set undo_retention=10800 scope=both;

至於undo 表空間的大小，如果磁碟空間允許，就將表空間設為20G，如果空間有限，就設為8G。不過現在的伺服器硬碟都是比較大，如果放在儲存上，那空間更大，所以20G。相對而言就就是一個很小的空間了。

SQL>ALTER DATABASE DATAFILE 'D:ORACLEPRODUCT10.2.0ORADATATANGYUNUNDOTBS01.DBF' RESIZE 100M;

大量的DML 操作會產生大量的undo，尤其是update，delete。當Undo 特別大的時候，我們可以把undo 刪了重建。

Oracle 預設只會建立spfile，但這是個二進位制檔案，無法進行修改。Windows 下生成的pfile 檔案預設在$ORACLE_HOME/database 下，Linux 預設位置在$ORACLE_HOME/dbs 下。

為了在設定引數時保險起見。我們要在開始就建立一個pfile 檔案,語句很簡單。

SQL>create pfile=' u01apporacleproduct10.2.0tangyuninittangyun.ora' from spfile;

當然，調整完引數最好再做一個備份。具體可以參考：

http://tangyunoracle.itpub.net/post/42756/509383

可以解決一些ORA-27101之類的問題。