oracle記憶體調整相關

chuanzhongdu1發表於2011-10-14
Oracle記憶體

db_cache_size與sga_target關係

db_cache_size設定buffer cache的大小與DB_BLOCK_SIZE相關

當sga_target設定非零時，如果沒有設定預設為零（由oracle調整），如果設定，表示使用者指定的最小值。

如果sga_target沒有設定，預設值為48m或者4MB * number of CPUs * granule size，以較大者為主。

sga_target設定所有sga元件的大小包括如下

Buffer cache (DB_CACHE_SIZE)
* Shared pool (SHARED_POOL_SIZE)
* Large pool (LARGE_POOL_SIZE)
* Java pool (JAVA_POOL_SIZE)
* Streams pool (STREAMS_POOL_SIZE)

如果這些值未設定，由oracle分配，如下池不由自動記憶體管理控制

* Log buffer
* Other buffer caches, such as KEEP, RECYCLE, and other block sizes
* Fixed SGA and other internal allocations

計算buffer cache命令中率

SELECT NAME, VALUE
  FROM V$SYSSTAT
WHERE NAME IN ('db block gets from cache', 'consistent gets from cache', 'physical reads cache');

公式：

1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache')
如果命中率小於90%應該考慮增加buffer cache


buffer cache按段來儲存db_block並按大小分為keep pool和recycle pool
keep儲存高頻率訪問的段，recycle設定可防止不必要的default buffer佔用
alter table tablename storage(buffer_pool keep);
計算緩衝池的命中率
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
      1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
  FROM V$BUFFER_POOL_STATISTICS;

 
檢視物件在buffer cache中的段的大小
SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
     FROM DBA_OBJECTS o, V$BH bh
    WHERE o.DATA_OBJECT_ID = bh.OBJD
      AND o.OWNER         != 'SYS'
    GROUP BY o.OBJECT_NAME
    ORDER BY COUNT(*);

查詢物件段的ID號 
SELECT DATA_OBJECT_ID, OBJECT_TYPE
  FROM DBA_OBJECTS 
 WHERE OBJECT_NAME = UPPER('segment_name');
segment_name一般為資料庫物件名。

檢視oracle動態元件資訊 
select * from v$sga_dynamic_component;
share pool
包括library cach與directory cache
前者儲存plsql塊和sql語句
後者儲存資料字典
alter parameter set cursor_sharing=similar;
設定遊標共享可以重用相同sql語句
查詢時最好用繫結變數方式，查詢時物件標識使用schema.表物件格式，這些都會起到最大化使用share cache的目地
如果經常使用sequence可以使用alter sequence sequencename cache value;使用cache儲存sequence
檢視library cache的資訊
SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS
  FROM V$LIBRARYCACHE
 ORDER BY NAMESPACE;
計算命中率 
select sum(PINS),sum(PINHITS) from v$LIBRARYCACHE order by namespace;
計算公式：
sum(PINHITS)/sum(PINS);
share pool分析檢視
V$SHARED_POOL_ADVICE

V$LIBRARY_CACHE_MEMORY

V$JAVA_POOL_ADVICE

V$JAVA_LIBRARY_CACHE_MEMORY

計算dircotry cache命中率
檢視directory cache相關資訊
SELECT parameter
     , sum(gets)
     , sum(getmisses)
     , 100*sum(gets - getmisses) / sum(gets)  pct_succ_gets
     , sum(modifications)                     updates
  FROM V$ROWCACHE
 WHERE gets > 0
 GROUP BY parameter;
命中率
SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
large pool
用途：並行查詢，恢復管理，共享服務（共享模式）
CURSOR_SPACE_FOR_TIME使用者可以通過設定這個引數為True，使得Oracle使用更多的Shared SQL Area去儲存SQL，從而提高反覆執行SQL解析的速度

SESSION_CACHED_CURSORS,就是說的是一個session可以快取多少個cursor，讓後續相同的SQL語句不再開啟遊標,從而避免軟解析的過程來提高效能

ALTER SESSION SET SESSION_CACHED_CURSORS = value;
DBMS_SHARED_POOL將常用物件放入share pool,忽略LRU
DBMS_SHARED_POOL.KEEP 常用遊標，plsql,sequence
設定設定CURSOR_SHARING值(similar,force)提高相似語句查詢效能


redo log buffer
LGWR寫redo log在下列三種情況
log buffer三分之一
LGWR呼叫commit roolback時
DBWR呼叫LGWR時

批量提交可以提升log buffer效能
當載入大數量資料時使用nologing引數
預設值 
MAX(0.5M, (128K * number of cpus))

PGA
儲存排序操作，雜湊連線，點陣圖合併，點陣圖建立

預設大小為sga 20%由PGA_AGGREGATE_TARGET設定
OLTP PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%

DSS  PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

total_mem為可用的作業系統記憶體
檢視pga資訊
SELECT * FROM V$PGASTAT;
pga命中率
SELECT NAME, CASE WHEN unit='bytes' THEN VALUE/1024/1024 ELSE VALUE END AS VALUE, unit 
from v$pgastat

命中率[bytes processed]/([bytes processed] + [extra bytes read/written]) * 100

V$PROCESS查詢每個oracle程式連線的例項

V$PROCESS_MEMORY查詢每個型別

V$SQL_WORKAREA 顯示被SQL遊標使用的工作區資訊

相關文章