Oracle内存与调整

 

对于SGA,可以用sqlplus查询：

SQL>select * from v$sga;

Fixed Size

Variable Size    

包含了shared_pool_size、java_pool_size、large_pool_size等内存设置和管理内存缓冲区等内存结构的hash table,块头信息等

DataBase Buffers  

指数据缓冲区，在8i中有default pool 、default_pool_keep、defaule_pool _recyle三部分内存，在9i中有db_cache_size,db_keep_cache_size,db_reycle_cache_size, db_cn_cache_size等几部分内存，  Oracle8i三部分内存的总和是：db_block_buffer* db_ block_size .

Redo buffers

指日志缓冲区，log_buffer,额外一点：对于v$parameter,v$sgastat,v$sga查询值可能不一样。V$parameter是用户在初始化的时候设置的值，v$sgastat是实际分配缓冲区的大小（因为实际分配的缓冲区是离散的，也不是以block为单位的），v$sga是在分配了日志缓冲区以后，为了保护日之缓冲区，设置了一些保护页，通常我们发现保护页的大小大约为11k左右。

SQL>select substr(name,1,10) name,substr(value,1,10) value from v$parameter where name=’log_buffer’;

SQL>select * from v$sgastat;

SQL>select * from v$sga;

8iSGA

ORACLE 8.0.X 版本

SGA=((db_block_buffers*blocksize)+(shared_pool_size+large_pool_size+ log_buffers) +1MB

ORACLE 8.1.X 版本

SGA=(db_block_buffers*blocksize)+(share_pool_size+large_pool_size+java_pool_size+log_buffers)+1MB

理论上SGA可占用os系统物理内存的1/2——1/3，

推荐：SGA=0.45*（os  RAM）

关于数据库有多少个并发连接，这实际上关系到PGA的大小(MTS下还有large_pool_size).事实上这个问题应该说还跟OLTP类型或者OLAP类型相关，对于OLTP类型oracle倾向于可使用的MTS，对于OLAP类型使用独立模式，同时OLAP还可能涉及到大量的排序操作的查询，这些都影响到我们内存的使用，那么所有的问题综合起来，实际上主要反映在UGA的大小上，UGA主要包含以下部分内存设置

SQL>show parameters area_size

NAME                                 TYPE                VALUE

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

bitmap_merge_area_size               integer                 1048576

create_bitmap_area_size               integer                  388608

hash_area_size                       integer                 1048576

sort_area_size                        integer                 524288

workarea_size_policy                  string                  AUTO

通常我们习惯通过直观的公式来表达内存分配的问题：

        Os使用内存+sga+并发执行进程数*(sort_area_size+2M)<0.7*总内存

(公式是死的，系统是活得，实际应用的调整不必框公式，这不过是一个参考建议)

在我们实际应用中，假如采用的是裸设备，我们可适当的增大SGA（如果需要的话）。由于目前几乎所有的操作系统都使用虚拟缓存，所以实际上如果就算SGA设置的比较大也不会导致错误，而是可能出现频繁的内存页的换入换出(page in/out)。在操作系统一级如果观察这个现象，那么我们就需要调整内存的设置。

SGA内存参数设置：

Log_buffer

   对于日志缓冲区的大小设置，通常我觉得没有过多的建议，因为参考LGWR写的触发条件之后，我们会发现通常超过3M意义不是很大，作为一个正式系统，可能考虑先设置这部分log_buffer=1—3M大小，然后对具体情况在调整

Large_pool_size

   对于大缓冲池的设置，假如不使用MTS，加以在20--30M足够了，这部分主要用来保存并行查询时候的一些信息，还有就是RMAN在备份的时候可能使用到，如果设置了MTS，则由于UGA部分要移入到这里，则需要具体根据server process数量和相关会话内存参数的设置来综合考虑这部分大小的设置。

Java_pool_size

   假如数据库没有使用java，我们通常认为保留10M--20M大小足够，甚至更小到32K，但是具体安装数据库的时候的组件相关。

Shared_pool_size

   这是迄今为止最有争议的一部分内存设置，这部分是为了缓存已经被解析过的SQL，而是其能被重用，不再解析，share_pool里面不存在已经解析的可用的相同的SQL，对于一个新的,数据库将执行硬解析，这过程很耗资源，若已经存在，则进行的仅仅是软分析（在共享池中寻找相同的SQL），这样消耗的资源大大的减少，所以期望共享一些SQL,并且如果参数设置不大，经常出现ora-04031错误，表示为了解析新的sql,没有足够大的连续空闲空间，这样自然我们期望参数能大一些，但是太大，由于需要维护共享的结构，内存的增大也使得sql的老化代价更高，带来大量的管理的开销，可能导致cpu的严重问题。

一般，在一个较大的系统中，share_pool_size的开销通常应该维持在300M,除非使用大量的存储过程、函数、包、比如oracle erp 这样的应用，可能会达到500M甚至更高。

Data buffer

    在确定了SGA的大小并分配玩了前面部分内存后，其余的都可以分配给这部分内存，通常，在允许的情况下，我们都尝试这是这部分内存更大，这部分内存的作用主要是缓存DB BLOCK，减少甚至避免从磁盘上获取数据，在8i中通常是由db_blocke_buffers* db_block_size来决定大小的，如果设置了buffer_pool_keep和buffer_pool_reycle,则应该加上后边这两部分内存的大小。

9i下部分参数变化

   8i                                  9i

Buffer_pool_keep-----------------------------------------àdb_keep_cache_size

Buffer_pool_recycle--------------------------------------àdb_recycle_cache_size

Db_block_buffers-----------------------------------------àdb_cache_size

 

查看SGA_MAX_SIZE

SQL>show parameters sga_max_size

SQL>alter system set db_cache_size=128000000

SQL>alter system set shared_pool_size=300000000

关于内存的参数调整：

缓冲区命中率：

SQL>select value from v$sysstat where name=’physical reads’;

SQL>select value from v$sysstat where name=’physical reads direct’;

SQL>select value from v$sysstat where name=’physical reads direct (lob)’;

SQL>select value from v$sysstat where name=’consistent gets’;

SQL>select value from v$sysstat where name=’db block gets’;

命中率的计算应该是

   X=physical reads direct + physical reads direct (lob)

命中率=100-(physical reads –x)/(consistent gets + db block gets –x)*100

共享池的命中率

SQL>select sum(pinhits)/sum(pins)*100 “hit radio” from v$librarycache;

关于排序部分

SQL>select name,value from v$sysstat where name like ‘%sort%’;

关于log_buffer

SQL>select name,value from v$sysstat where name in(‘redo entries’,’redo buffer allocation retries’);