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游标使用的工作区信息