Oracle 參數調優

Oracle 參數調優

一.升級到11gR2之後
Oracle資料庫升級助手(DBUA)組態工具包括一個自動擴充系統檔案的命令選項，能夠從oracle express(XE或免費版)升級到其 他版本。
 升級前指令碼檢查以下各項：
 1.無效使用者或角色
 2.無效資料類型或對象
 3.不支援的字元集
 4.統計資訊的收集
 5.足夠的資源（undo/rollback段，資料表空間和空閑磁碟空間）
 6.缺失的升級需要的指令碼
 7.啟動並執行監聽器
 8.oracle資料庫軟體已串連到database vault選件
 如果在安裝過程中指定ORACLE_BASE環境變數，oracle將使用此值設定DIAGNOSTIC_DEST參數，其中包括所有的ADR目錄。
 
1.11g新特性
 預設安裝完，密碼是區分大小寫
 SEC_CASE_SENSITIVE_LOGON  預設是true 大小寫敏感
 SEC_MAX_FAILED_LOGIN_ATTEMPTS 預設值是10 設定嘗試次數。
 
 alter user username account unlock;
 
2.oracle的重要參數
 MEMORY_TARGET
 MEMORY_MAX_TARGET
 SGA_TARGET
 SGA_MAX_SIZE
 PAG_AGGREGATE_TARGET
 DB_CACHE_SIZE
 SHARED_POOL_SIZE

 預設讀取參數檔案的順序
 1.spfile<SID>.ora
 2.spfile.ora
 3.init<SID>.ora
 
 如果使用alter system命令只修改spfile，而且在啟動的時候發現設定錯誤，資料庫將不會啟動。這時，不能使用alter system命令去解決這個問題，需要根據spfile建立一個pfile，修改這個pfile，然後使用這個pfile來啟動資料庫。之後需要再建立spfile然後使用spfile重啟資料庫。
 
 在V$PARAMETER視圖裡有兩個關鍵的欄位(V$PARAMETER顯示會話層級有效參數，V$SYSTEM_PARAMETER顯示在整個執行個體層級有效參數):
 ISSES_MODIFIABLE:表明擁有alter session許可權的使用者是否可以在他們的會話層級修改這個初始化參數
 ISSYS_MODIFIABLE:表明擁有ALTER SYSTEM許可權的使用者是否可以修改這個參數。
 
 select name，value，isdefault，isses_modifiable，issys_modifiable from V$PARAMETER where issys_modifiable <> 'FALSE' or  isses_modifiable <> 'FALSE' order by name;
 alter session set sort_area_size=10000000;
 動態地修改初始化參數對開發人員和DBA來說是非常強大的特性。因此，如果不做限制的話，擁有alter session 特權的使用者就可以隨意地為某個會話的sort_area_size 分配大於100M的記憶體。
 
 
3.最佳化DB_CACHE_SIZE來提高效能
 oracle 10g DB_BLOCK_BUFFERS變為隱含參數，在11g又被啟用，預設為0，意思是除非設定它，否則它不會被使用(用DB_CACHE_SIZE取而代之)。
 DB_CACHE_SIZE是為主要資料庫緩衝或存放資料而初始分配的記憶體量。如果設定了MEMORY_TARGET或SGA_TARGET，那麼該參數就無須設定。我們的目標應該是實現一個駐留在記憶體的資料庫，至少要把所有將被查詢的資料都放進記憶體裡。
 如果DB_CACHE_SIZE設定太低，不論怎樣最佳化這個系統，oracle也沒有足夠的記憶體來有效執行操作，系統運行狀態也會很糟糕。如果設定過高，您的系統可能會使用交換空間，甚至停機。DB_CACHE_SIZE是SGA的一部分，用於儲存和處理資料以及查詢訪問。設定過低，那麼最近使用的資料會從記憶體中清除出去，如果有另外一個查詢重新調用這些被清除的資料，就必須重新從磁碟中讀取(將會使用到I/O和CPU資源).
 MEMORY_TARGET,SGA_TARGET（如果使用的話）和DB_CACHE_SIZE(如果設定了最小值) 是用來最佳化資料快取命中率的關鍵參數:命中率就是指那些不用從磁碟上執行物理讀操作就可以訪問到的資料區塊的比例。
 如果系統負載情況不變，而快取命中率劇烈變化，就應該立刻調查發生的原因。糟糕的串連和索引也會由於讀取許多索引塊而產生非常高的命中率，因此一定要保證命中率不是因為這些因素而提高的，而是因為系統經過良好調優而得到的。異常高的命中率通常也暗示有代碼用到了糟糕的索引或串連。
 通過比較隨時間變化的命中率，可以協助您注意系統某天發生的重大改變。
 
4.使用V$DB_CACHE_ADVICE最佳化DB_CACHE_SIZE
 可以利用如下清單查看修改DB_CACHE_SIZE後對資料快取命中率的影響
 select name，size_for_estimate，size_factor，estd_pyhsical_read_factor from v$db_cache_advice;
 NAME            size_for_estimate          size_factor          estd_pyhsical_read_factor
 DEFAULT  4    .1667   1.8322
 DEFAULT  8    .3333   1.0169
 DEFAULT  12    .5   1.0085
 DEFAULT  16    .6667   1
 DEFAULT  20    .8333   1
 DEFAULT  24    1   1
 當前的緩衝大小為24M size_factor=1
 我們可以吧緩衝大小減小為16M 並維持當前的快取命中率，因為SGA減小到16M時，PHYSICAL_READ_FACTOR仍為1

保持資料快取命中率超過95%
 有些例子中，將命中率從95%增大到98%，就可以顯著得提高效能--特別是最後命中在磁碟的那5%是系統的主要延遲，或者說磁碟的緩衝已經不夠用了。

 
5.監控V$SQLAREA視圖以尋找較慢的查詢
 儘管低於95%的命中率通常都表明DB_CACHE_SIZE被設定得過低。命中率失真和那些非DB_CACHE_SIZE問題包括：
 1.遞迴調用
 2.缺少索引或抑制索引
 3.記憶體中駐留的資料
 4.UNDO/復原段
 5.數倍的邏輯讀
 6.導致系統使用CPU的物理讀
 通過監控V$SQLAREA視圖或企業管理器可以找到較慢的查詢。

 
6.設定DB_BLOCK_SIZE來反映資料讀的大小
 如果系統中交易處理的輸送量非常高或者系統記憶體有限，或許可以考慮把塊大小設定為小於8K
 可以把DB_BLOCK_SIZE增大到8K或者16K  或者把DB_FILE_MULTIBLOCK_READ_COUNT的值設定為(最大IO大小)DB_BLOCK_SIZE.這樣 可以增大每次IO讀到記憶體中的資料量。
 如果由於DB_FILE_MULTIBLOCK_READ_COUNT造成很多全表掃描(因為最佳化器確定執行全表掃描更快，所以決定更多地使用)，那麼 把OPTIMIZER_INDEX_COST_ADJ設定於在1~10之間，這樣可以強制索引使用的更頻繁。
 
 如果增大DB_BLOCK_SIZE，就必須重新建立資料庫，增大DB_FILE_MULTIBLOCK_READ_COUNT可以允許在一次IO裡讀取更多塊，這 樣可以帶來和增大塊大小一樣的好處。
 
7.把SGA_MAX_SIZE設定為主記憶體大小的25%到50%
 如果使用SGA_MAX_SIZE參數，一般經驗是一開始將主記憶體的20%至25%分配給它。
 如果SGA_MAX_SIZE<1G  那麼_KSM_GRANULE_SIZE的值是4M
 如果1<SGA_MAX_SIZE<8G，那麼_KSM_GRANULE_SIZE的值是16M
 
 如果SGA_MAX_SIZE設定為2000M 將DB_CACHE_SIZE設定為9M，那麼DB_CACHE_SIZE被四捨五入至16M(因為粒度是16M)
8.最佳化Shared_pool_size以擷取最佳效能
 oracle使用最近最少使用演算法(LRU)
 使用結果集緩衝，需要設定RESULT_CACHE_SIZE=<需要大小> 和 RESULT_CACHE_MODE=FORCE參數(設定為FORCE以自動使用這個特性)。
 為了確保最佳的利用共用SQL地區，請盡量使用預存程序，因為被解析的SQL每次都完全相同，因此可以將其共用。
 SQL的編寫必須完全一樣，這樣才能被重用。
 PL/SQL把每條語句都轉換成大寫，然後整理了空格或分行符號。
 如果設定CURSOR_SHARING=FORCE 針對V$SQLAREA的查詢結果將會改變，這是因為oracle可以在內部構建前面所有語句共用的語句。現在共用語句只包含一條所有使用者共用的簡單語句。
 設定足夠大的shared_pool_size以保證充分利用db_cache_size
 保證資料字典命中率高於95%
 select ((1-（sum(getmisses)）/ (sum(gets) + sum(getmisses))））*100） "hit rats" from v$rowcache where gets+ getmisses <> 0;

 可以對V$ROWCACHE視圖使用修改後的查詢，以查看這些參數如何組成資料字典緩衝，也稱為行緩衝
 select parameter，gets，getmisses，modifications，flushes，(getmisses/decode(gets，0,1，gets)) getmiss_ratio，(case when (getmisses/decode(gets，0,1，gets)) > .1 then '*' else ' ' end) " " from v$rowcache where gets + getmisses <> 0;

 保證庫緩衝的重載率為0，並使命中率在95%以上，如果重載率超過1%，可能就應該增大參數shared_pool_size;
 有兩種方法可以監控庫緩衝:
  1.產生STATSPACK報告
  2.使用V$LIBRARYCACHE
 使庫緩衝的PIN命中率接近100%
 資料字典緩衝的丟失率應該少於10%~15%
 oracle通過SHARED_POOL_RESERVED_SIZE參數為緊急操作保留了空間，V$SGASTAT視圖顯示了共用池記憶體的消耗速度。
 使用X$KSMSP表來查看共用池裡的詳細情況。